AWS Blu Insights detects SQL invocations from program files such as Cobol and PL/I and creates adequate links between those files (e.g. COB) and .SQL ones.
SQL Object creation
CREATE (OR REPLACE)? <modifier> <object kind> <id>
DECLARE <modifier> <object kind> <id>
CREATE (OR REPLACE)? <modifier> <id> <object kind>
DECLARE <modifier> <id> <object kind>
modifier is optional and can be :
- UNIQUE
- FORCE
- PUBLIC
object kind is mandatory and is one of :
- TABLE
- INDEX
- VIEW
- PROCEDURE
- TRIGGER
- ALIAS
- TABLESPACE
- DATABASE
- CURSOR
- SYNONYM
- PARTITION
id is the identifier of a defined SQL object
system name is an alias to the object name defined at the creation of the object.
A node identified with <id> and a type set to <object kind> will be created and a link from the query source file to the created node.
Call
CALL <target id>
A link from the query source file to the referenced procedure or external program identified by target id will be created.
Creation in/on
CREATE (OR REPLACE)? <modifier> <object kind> <id> (IN|ON) <parent id>
A link from the query source file to the referenced node identified by parent id will be created.
Creation for system name
CREATE (OR REPLACE)? <modifier> <object kind> <id> FOR SYSTEM NAME <system id>
A link from the query source file to the referenced node identified by id or system id will be created.
Delete / Drop
DELETE FROM <table id> (WHERE <condition>)
DROP <object kind> (IF EXISTS) <id>
A link from the query source file to the referenced node identified by table id or id will be created.
Alias targets
CREATE (OR REPLACE)? ALIAS <alias id> (<sub query>) FOR <alias target>
DECLARE (OR REPLACE)? ALIAS <alias id> (<sub query>) FOR <alias target>
A link from the query source file to the referenced node identified by alias target will be created
SYNONYM’s targets are treated like this
Trigger targets
(BEFORE|AFTER|INSTEAD OF) INSERT|DELETE|UPDATE OF <trigger conditions> ON <observe element>
A link from the query source file to the referenced node identified by observe element will be created
Queries
UPDATE <object id>
INSERT INTO <object id>
JOIN <object id> ON
FROM <object id> AS <alias>, <object id> … (;|HAVING|WHERE|JOIN|GROUP|ORDER)
ALTER <object kind> <object id>
A link from the query source file to the referenced node identified by object id will be created
Constraint
CONSTRAINT <object id> (PRIMARY KEY|FOREIGN KEY|CHECK)
ADD (PRIMARY KEY|FOREIGN KEY|CHECK) <object id>
FOREIGN KEY <fk id> (…) REFERENCES <object id>
A link from the query source file to the referenced node identified by object id will be created.
Comment
COMMENT ON <object description> <object id> IS
COMMENT ON <object id> ()
A link from the query source file to the referenced node identified by object id will be created.
object description can be one of following (not exhaustive)
– COLUMN
– CONSTRAINT <id2> ON
– CONSTRAINT <id2> ON DOMAIN
– DATABASE
– DOMAIN
– INDEX
– ROLE
– SCHEMA
– TABLE
– TABLESPACE
– VIEW
SQL using mainframe element
In a mainframe environment, a SQL query can use some mainframe elements, like SQL objects. The following object’s kinds represent those cases:
- PROGRAM: a mainframe piece of code used as a PROCEDURE
- DATA_STRUCTURE: a data structure like a physical file used as a TABLE