AWS Blu Insights

SQL

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
  • FUNCTION

<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.

View

For the creation of the View object, a link will be formed from view object <id> to the SQL objects used in SQL sub queries. For the below sample SQL query, a dependency link is formed from VIEW1 object to the TABLE1 object. 

 CREATE VIEW VIEW1 (COL1, COL2) AS SELECT COL1, COL2 FROM TABLE1;

Trigger

For the creation of the Trigger object, a link will be formed from trigger object <id> to the SQL objects used in ON statement. For the below sample SQL query, a dependency link is formed from TRIGGER1 object to the TABLE1 object. 

 CREATE TRIGGER TRIGGER1 BEFORE ON TABLE1;

Execute

 ..... EXECUTE <object id>

The command EXECUTE is part of TRIGGER creation. A dependency link from the TRIGGER object will be created to the object id. Object id refers to FUNCTION or PROCEDURE.

 

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 object <id> and also from the object <id> 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 object <alias id> and also from the object <alias id> to the referenced node identified by <alias target> will be created.

SYNONYM’s targets are treated like this

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 <constraint 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 <constraint id> and from <constraint id> to the reference <object id> typed 'Constraint on' 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 and logical file used as a TABLE

SQLC files

SQLC files are similar to SQL but with extra margins, the same analysis for SQL is done for SQLC files.