AWS Transform for mainframe refactor

SQL

AWS Transform for mainframe refactor 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 source files

Files with the extensions .sql, .sqlc and .ddl (any case) are analysed as SQL. They are processed identically; the file node is typed SQL, SQLC or DDL according to the extension. SQLC files carry extra margins, see SQLC files.

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.

DECLARE <id> TABLE and DECLARE <id> VIEW (the DCLGEN form, <id> before the object kind) are an exception: they do not define an object. They are treated as a reference to the existing table or view, typed "Declare query" (see Queries). DECLARE GLOBAL TEMPORARY TABLE <id> remains a definition.

An <id> may be schema-qualified (SCHEMA.TABLE, see Schema-qualified names) and each segment may be written as a double-quoted delimited identifier. Quoted and unquoted forms of the same name refer to the same SQL object, so a quoted definition and its unquoted references resolve together.

An unquoted (ordinary) identifier segment may contain letters, digits and the characters _, -, / and &, as well as the national characters @, # and $ allowed by DB2 for z/OS. Objects whose schema or name contains a national character, such as @TESTSCHEMA.SAMPLETABLE, are therefore created and resolved like any other identifier. This also applies to SQL embedded in COBOL (EXEC SQL) and to SQL in JCL in-stream data.

CREATE TABLE @TESTSCHEMA.SAMPLETABLE
     (F1 DECIMAL(10, 0) WITH DEFAULT NULL,
      F2 TIMESTAMP (6) WITHOUT TIME ZONE NOT NULL)
    IN tbspace
    CCSID EBCDIC ;

Creates:

{
    "dependencyType" : "Define [SQL]TABLE",
    "name" : "@TESTSCHEMA.SAMPLETABLE",
    "path" : "Global:TABLE:@TESTSCHEMA.SAMPLETABLE",
    "type" : "[SQL]TABLE"
}

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. The trigger event (INSERT, UPDATE or DELETE) between the activation time and ON is mandatory. For the below sample SQL query, a dependency link is formed from TRIGGER1 object to the TABLE1 object. 

 CREATE TRIGGER TRIGGER1 BEFORE INSERT 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.

 

Schema-qualified names

Table and view identifiers can be written unqualified (DEPARTMENTS), qualified by their schema (HR.DEPARTMENTS), or qualified by their database and schema (TESTDB.SALES.ORDERS). Qualifiers are separated by a dot or a slash.

When an object is created with a qualifier, the full qualified name is kept as the object identifier, exactly as written in the defining statement (upper-cased), and its path is Global:<object kind>:<identifier>. Its unqualified part is indexed, so that a query that references the object without a schema still resolves to the qualified definition. A link from the query source file to the referenced node identified by the qualified name will be created. Matching is case-insensitive. A reference qualified with a different schema (SALES.DEPARTMENTS for a table created as HR.DEPARTMENTS) is not resolved to it.

For a qualified reference such as SELECT ... FROM HR.DEPARTMENTS, the dependency targets the qualified table:

{
    "dependencyType" : "Select query",
    "name" : "HR.DEPARTMENTS",
    "path" : "Global:TABLE:HR.DEPARTMENTS",
    "type" : "[SQL]TABLE"
}

When the same unqualified name is defined under several schemas (e.g. DATA.CUSTOMERS and SALES.CUSTOMERS), an unqualified reference such as SELECT ... FROM CUSTOMERS is ambiguous and a link is created to each candidate definition:

[
    {
        "dependencyType" : "Select query",
        "name" : "DATA.CUSTOMERS",
        "path" : "Global:TABLE:DATA.CUSTOMERS",
        "type" : "[SQL]TABLE"
    },
    {
        "dependencyType" : "Select query",
        "name" : "SALES.CUSTOMERS",
        "path" : "Global:TABLE:SALES.CUSTOMERS",
        "type" : "[SQL]TABLE"
    }
]

DB2 variable substitution syntax is accepted in a qualified name: &VAR..NAME (the variable &VAR. followed by the qualifier separator). The variable is not substituted; the identifier is kept verbatim.

Example, TXXXX41.ddl:

CREATE TABLE &USERID..TXXXX41 (COLUMN CHAR(8));

and a Cobol program executing EXEC SQL SELECT COLUMN INTO :MY-COLUMN FROM TXXXX41 END-EXEC

Creates the node:

{
    "name" : "&USERID..TXXXX41",
    "path" : "Global:TABLE:&USERID..TXXXX41",
    "type" : "[SQL]TABLE"
}

with a "Define [SQL]TABLE" link from TXXXX41.ddl and a "Select query" link from the Cobol program.

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 targets are treated the same way.

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>

DECLARE <object id> (TABLE|VIEW)

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> (<column> IS '…', …)

A link of type "Comment query" from the query source file to the node identified by object id will be created.

For COMMENT ON COLUMN, object id names a column (schema.table.column or table.column): the last qualifier is removed and the link targets the parent table or view. Column nodes are never created.

object description can be one of the following (not exhaustive):

  • COLUMN
  • CONSTRAINT <id2> ON
  • CONSTRAINT <id2> ON DOMAIN
  • DATABASE
  • DOMAIN
  • INDEX
  • MATERIALIZED VIEW
  • ROLE
  • RULE <id2> ON
  • SCHEMA
  • SEQUENCE
  • TABLE
  • TABLESPACE
  • TRIGGER <id2> ON
  • TYPE
  • VIEW

Example:

COMMENT ON COLUMN DB811.BET_UTL.BETALERS_ADR1 IS 'Payer address';

Creates:

{
    "dependencyType" : "Comment query",
    "name" : "DB811.BET_UTL",
    "path" : "Global:TABLE:DB811.BET_UTL",
    "type" : "[SQL]TABLE"
}

When the table is not defined in the project the link is reported as "Missing Database Object". When the CREATE TABLE and the COMMENT ON are in the same file, the two links are merged into one dependency typed "Comment query; Define [SQL]TABLE".

SQL using mainframe element

In a mainframe environment, a SQL query can use some mainframe elements, like SQL objects. The following object 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 files but with extra margins; the same analysis as for SQL is done for SQLC files.

Common Table Expressions (CTE)

WITH [RECURSIVE] <cte name> [(<column list>)] AS (<sub query>) [, <cte name> [(<column list>)] AS (<sub query>)] ... <main query>

Common Table Expressions define temporary named result sets that exist only for the duration of the SQL statement. CTE names are recognized and excluded from dependency analysis — they do not produce links to external objects. 

Dependencies are extracted from: 

  • The sub queries inside each CTE definition (e.g. FROM within the AS clause) 
  • The main query following the CTE definitions 

CTE names referenced in FROM, JOIN, INSERT, UPDATE, or DELETE clauses of the main query are not treated as external dependencies. 

Example: 

WITH ACCT_CTE AS (SELECT ACCT_ID FROM ACCOUNT_MASTER WHERE STATUS = 'A') SELECT ACCT_ID FROM ACCT_CTE

In this example, a link from the query source file to ACCOUNT_MASTER will be created. ACCT_CTE will not produce a dependency link since it is defined locally within the same statement.