SQL

Designate a DB2 table or cursor declaration in the Working-Storage or Linkage Section. Additionally, code native SQL statements and pass them through, without translation, to the precompile process.

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

Syntax: for Format 1

Working-Storage and Linkage Sections

 SQL    SQLdatastructurestatement
                .
                .
                .

Syntax: for Format 2

Procedure Division

        SQL nativecall
                .
                .
                .

Comments:

Use one SQL keyword for each SQL call. AMB generates EXEC SQL and END-EXEC statements before the first and last statements, respectively.
In the Data Division, the SQL keyword designates a DB2 table or one or more cursor declarations in the Working-Storage or Linkage Section. Use one SQL keyword for each SQL call. AMB generates EXEC SQL and END-EXEC statements before the first and last statements, respectively.
In the Procedure Division, start every SQL Procedure Division statement with the SQL verb, coded in columns 12 through 72. Begin subsequent lines of the statement with continuation ellipses. All statements pass to the precompiler without translation. SQL statements can be coded anywhere after the PARA, PROC, or NTRY keywords.

Examples:

Program Painter code

 SQL
        DECLARE DSN8.TDEPT TABLE
        ... (DEPTNO CHAR(3) NOT NULL,
        ... DEPTNAME CHAR(36) NOT NULL,
        ... MGRNO CHAR(3) NOT NULL,
        ... ADMRDEPT CHAR(3) NOT NULL)
 REC    WS-DEPARTMENT
           DEPARTMENT-NUM       X3
           DEPARTMENT-NAME      X36
           DEPARTMENT-MGR-NO    X3
           DEPARTMENT-ADMIN     X3
 NTRY
        SQL
            ... SELECT DEPTNO, DEPTNAME,
            ... MGRNO, ADMRDEPT
            ... INTO :DEPARTMENT-NUM,
            ... :DEPARTMENT-NAME,
            ... :DEPARTMENT-MGR-NO,
            ... :DEPARTMENT-ADMIN
            ... FROM DSN8.TDEPT
            ... WHERE DEPTNO > 0
        IF SQLCODE > +0
            PERFORM ERROR-DISPLAY

Generated code

%   &AP-GEN-VER = 1719
%   &AP-PGM-ID = "TSTSQL"
%   &AP-GEN-DC-TARGET = "ISPF"
%   &AP-TP-ENTRY-KYWD-SEEN = 1
%   &AP-SUBSCHEMA = ""
%   &AP-APPLICATION-ID = "TSTSQL"
%   &AP-GEN-DATE = "861219"
%   &AP-GEN-TIME = "16063323"
IDENTIFICATION DIVISION.
PROGRAM-ID.                     TSTSQL.
AUTHOR.                         AP-SYSTEM GENERATED.
DATE-WRITTEN.                   861219.
DATE-COMPILED.                  &COMPILETIME.
ENVIRONMENT DIVISION.
CONFIGURATION SECTION.
SOURCE-COMPUTER.                &SYSTEM.
OBJECT-COMPUTER.                &SYSTEM.
DATA DIVISION.
WORKING-STORAGE SECTION.
 $TP-WS-MARKER
    EXEC SQL INCLUDE SQLCA
    END-EXEC.
    EXEC SQL
    DECLARE DSN8.TDEPT TABLE             
        (DEPTNO CHAR(3) NOT NULL,        
        DEPTNAME CHAR(35) NOT NULL,
        MGRNO CHAR(3) NOT NULL,          
        ADMRDEPT CHAR(3) NOT NULL)       
    END-EXEC.
 01  WS-DEPARTMENT.
     05  DEPARTMENT-NUM          PIC X(3).
     05  DEPARTMENT-NAME         PIC X(36).
     05  DEPARTMENT-MGR-NO       PIC X(3).
     05  DEPARTMENT-ADMIN        PIC X(3).
 $TP-COMMAREA
 $TP-ENTRY ("", "")
     SQL
         ... SELECT DEPTNO, DEPTNAME,
         ... MGRNO, ADMRDEPT
         ... INTO :DEPARTMENT-NUM,
         ... :DEPARTMENT-NAME,
         ... :DEPARTMENT-MGR-NO,
         ... :DEPARTMENT-ADMIN
         ... FROM DSN8.TDEPT
         ... WHERE DEPTNO > 0
     IF SQLCODE > +0
         PERFORM ERROR-DISPLAY