z/OS Assembler SQL queries

This page shows, with small examples, the SQL query objects that the extension creates for embedded EXEC SQL blocks, and the missing tables and procedures that SQL Analyzer creates for them.

SQL queries in a program

Each EXEC SQL block of a program produces a z/OS Assembler SQL Query object, child of the program, with a link from the program. The query is named after its first four words.

With END-EXEC

PGM.asm:

PGM      CSECT
          MVC   EMPNO,=CL6'000010'
          EXEC SQL
             SELECT LASTNAME
               INTO :ENAME
               FROM   EMP
              WHERE  EMPNO = :EMPNO
          END-EXEC
          EXEC SQL COMMIT END-EXEC
          BR    14
          END   PGM

Caller type	Caller	Link	Callee type	Callee
	PGM	call Link		COMMIT
	PGM	call Link		SELECT LASTNAME INTO :ENAME

In CAST Imaging:

Without END-EXEC

The block can also be continued with a character in column 72, without END-EXEC. Each statement is then a query of its own:

PGM.asm:

PGM      CSECT
          MVC   EMPNO,=CL6'000010'
          EXEC SQL                                                     X
             SELECT LASTNAME                                           X
               INTO :ENAME                                             X
               FROM   EMP                                              X
              WHERE  EMPNO = :EMPNO
         EXEC SQL WHENEVER SQLERROR  GO TO SQLERMOD
         EXEC SQL WHENEVER NOT FOUND GO TO SQLERMOD
         EXEC SQL WHENEVER SQLWARNING GO TO SQLERMOD
          BR    14
          END   PGM

Caller	Link	Callee
PGM	call Link	SELECT LASTNAME INTO :ENAME
PGM	call Link	WHENEVER NOT FOUND GO
PGM	call Link	WHENEVER SQLERROR GO TO
PGM	call Link	WHENEVER SQLWARNING GO TO

In CAST Imaging:

SQL queries in a copy member

An EXEC SQL block of a copy member used by a COPY statement produces an SQL query object, child of the copy member, with a link from the copy member. CODEPGM.asm copies CODECPY.asm, which holds:

         EXEC SQL
            SELECT LASTNAME INTO :ENAME FROM EMP
         END-EXEC

Caller type	Caller	Link	Callee type	Callee
AssemblerZOS_CopyMember	CODECPY	call Link		SELECT LASTNAME INTO :ENAME

In CAST Imaging:

See Links from the code of a copy member for the whole example.

Missing tables and procedures

These objects and links are created by SQL Analyzer, not by this extension. SQL Analyzer reads each SQL query and links it to the tables and procedures it uses. When a table or a procedure is not part of the analyzed database, SQL Analyzer creates a missing object for it, under a missing schema attached to the z/OS Assembler project.

For example, the following EXEC SQL blocks of a program use the tables EMP and EMPHIST and the procedure RAISE of the schema PAYROLL, which is not part of the analysis:

         EXEC SQL
            SELECT LASTNAME INTO :ENAME FROM PAYROLL.EMP
            WHERE EMPNO = :EMPNO
         END-EXEC
         EXEC SQL
            INSERT INTO PAYROLL.EMPHIST (EMPNO) VALUES (:EMPNO)
         END-EXEC
         EXEC SQL
            UPDATE PAYROLL.EMP SET SALARY = :SALARY
            WHERE EMPNO = :EMPNO
         END-EXEC
         EXEC SQL
            DELETE FROM PAYROLL.EMPHIST WHERE EMPNO = :EMPNO
         END-EXEC
         EXEC SQL
            CALL PAYROLL.RAISE(:EMPNO)
         END-EXEC

SQL Analyzer creates these missing objects:

Type	Name	Parent
	RAISE	PAYROLL
	PAYROLL
	EMP	PAYROLL
	EMPHIST	PAYROLL

and these links:

Caller	Link	Callee
CALL PAYROLL.RAISE(:EMPNO)	call Link	RAISE
DELETE FROM PAYROLL.EMPHIST WHERE	useDelete Link	EMPHIST
INSERT INTO PAYROLL.EMPHIST (EMPNO)	useInsert Link	EMPHIST
SELECT LASTNAME INTO :ENAME	useSelect Link	EMP
UPDATE PAYROLL.EMP SET SALARY	useUpdate Link	EMP

In CAST Imaging:

When the tables and procedures are part of the analyzed database, SQL Analyzer links the queries to them instead.