Assembler applications that issue SQL statements
You can code SQL statements in assembler programs wherever you can use executable statements.
Each SQL statement in an assembler program must begin with EXEC SQL. The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.
EXEC SQL UPDATE DSN8C10.DEPT X SET MGRNO = :MGRNUM X WHERE DEPTNO = :INTDEPT
- You cannot include assembler comments in SQL statements. However, you can include SQL comments in any embedded SQL statement. For more information, see SQL comments.
- Continuation for SQL statements
- The line continuation rules for SQL statements are the same as those for assembler statements, except that you must specify EXEC SQL within one line. Any part of the statement that does not fit on one line can appear on subsequent lines, beginning at the continuation margin (column 16, the default). Every line of the statement, except the last, must have a continuation character (a non-blank character) immediately after the right margin in column 72.
- Delimiters for SQL statements
- Delimit an SQL statement in your assembler program with the beginning
EXEC SQLand an end of line or end of last continued line.
- Declaring tables and views
- Your assembler program should include a DECLARE statement to describe each table and view the program accesses.
- Including code
- To include SQL statements or assembler host variable declaration
statements from a member of a partitioned data set, place the following
SQL statement in the source code where you want to include the statements:
EXEC SQL INCLUDE member-name
You cannot nest SQL INCLUDE statements.
- Use the precompiler option MARGINS to set a left margin, a right margin, and a continuation margin. The default values for these margins are columns 1, 71, and 16, respectively. If EXEC SQL starts before the specified left margin, the Db2 precompiler does not recognize the SQL statement. If you use the default margins, you can place an SQL statement anywhere between columns 2 and 71.
- Multiple-row FETCH statements
- You can use only the FETCH ... USING DESCRIPTOR form of the multiple-row FETCH statement in an assembler program. The Db2 precompiler does not recognize declarations of host-variable arrays for an assembler program.
You can use any valid assembler name for a host variable. However, do not use external entry names or access plan names that begin with 'DSN' or host variable names that begin with 'SQL'. These names are reserved for Db2.
The first character of a host variable that is used in embedded SQL cannot be an underscore. However, you can use an underscore as the first character in a symbol that is not used in embedded SQL.
- Statement labels
- You can prefix an SQL statement with a label. The first line of an SQL statement can use a label beginning in the left margin (column 1). If you do not use a label, leave column 1 blank.
- WHENEVER statement
- The target for the GOTO clause in an SQL WHENEVER statement must be a label in the assembler source code and must be within the scope of the SQL statements that WHENEVER affects.
- Special assembler considerations
- The following considerations apply to programs written in assembler:
- To allow for reentrant programs, the precompiler
puts all the variables and structures it generates within a DSECT
called SQLDSECT, and it generates an assembler symbol called SQLDLEN.
SQLDLEN contains the length of the DSECT. Your program must allocate
an area of the size indicated by SQLDLEN, initialize it, and provide
addressability to it as the DSECT SQLDSECT. The precompiler does not
generate code to allocate the storage for SQLDSECT; the application
program must allocate the storage.CICS®: An example of code to support reentrant programs, running under CICS, follows:
In this example, the actual storage allocation is done by the DFHEIENT macro.
DFHEISTG DSECT DFHEISTG EXEC SQL INCLUDE SQLCA * DS 0F SQDWSREG EQU R7 SQDWSTOR DS (SQLDLEN)C RESERVE STORAGE TO BE USED FOR SQLDSECT ⋮ XXPROGRM DFHEIENT CODEREG=R12,EIBREG=R11,DATAREG=R13 * * * SQL WORKING STORAGE LA SQDWSREG,SQDWSTOR GET ADDRESS OF SQLDSECT USING SQLDSECT,SQDWSREG AND TELL ASSEMBLER ABOUT IT *TSO: The sample program in prefix.SDSNSAMP(DSNTIAD) contains an example of how to acquire storage for the SQLDSECT in a program that runs in a TSO environment. The following example code contains pieces from prefix.SDSNSAMP(DSNTIAD) with explanations in the comments.
DSNTIAD CSECT CONTROL SECTION NAME SAVE (14,12) ANY SAVE SEQUENCE LR R12,R15 CODE ADDRESSABILITY USING DSNTIAD,R12 TELL THE ASSEMBLER LR R7,R1 SAVE THE PARM POINTER * * Allocate storage of size PRGSIZ1+SQLDSIZ, where: * - PRGSIZ1 is the size of the DSNTIAD program area * - SQLDSIZ is the size of the SQLDSECT, and declared * when the DB2 precompiler includes the SQLDSECT * L R6,PRGSIZ1 GET SPACE FOR USER PROGRAM A R6,SQLDSIZ GET SPACE FOR SQLDSECT GETMAIN R,LV=(6) GET STORAGE FOR PROGRAM VARIABLES LR R10,R1 POINT TO IT * * Initialize the storage * LR R2,R10 POINT TO THE FIELD LR R3,R6 GET ITS LENGTH SR R4,R4 CLEAR THE INPUT ADDRESS SR R5,R5 CLEAR THE INPUT LENGTH MVCL R2,R4 CLEAR OUT THE FIELD * * Map the storage for DSNTIAD program area * ST R13,FOUR(R10) CHAIN THE SAVEAREA PTRS ST R10,EIGHT(R13) CHAIN SAVEAREA FORWARD LR R13,R10 POINT TO THE SAVEAREA USING PRGAREA1,R13 SET ADDRESSABILITY * * Map the storage for the SQLDSECT * LR R9,R13 POINT TO THE PROGAREA A R9,PRGSIZ1 THEN PAST TO THE SQLDSECT USING SQLDSECT,R9 SET ADDRESSABILITY ... LTORG ********************************************************************** * * * DECLARE VARIABLES, WORK AREAS * * * ********************************************************************** PRGAREA1 DSECT WORKING STORAGE FOR THE PROGRAM ... DS 0D PRGSIZE1 EQU *-PRGAREA1 DYNAMIC WORKAREA SIZE ... DSNTIAD CSECT RETURN TO CSECT FOR CONSTANT PRGSIZ1 DC A(PRGSIZE1) SIZE OF PROGRAM WORKING STORAGE CA DSECT EXEC SQL INCLUDE SQLCA ...
- Db2 does not process set symbols in SQL statements.
- Generated code can include more than two continuations per comment.
- Generated code uses literal constants (for example, =F'-84'), so an LTORG statement might be necessary.
- Generated code uses registers 0, 1, 14, and 15. Register 13 points
to a save area that the called program uses. Register 15 does not
contain a return code after a call that is generated by an SQL statement. CICS: A CICS application program uses the DFHEIENT macro to generate the entry point code. When using this macro, consider the following:
- If you use the default DATAREG in the DFHEIENT macro, register 13 points to the save area.
- If you use any other DATAREG in the DFHEIENT macro, you must provide
addressability to a save area. For example, to use SAVED, you can code instructions to save, load, and restore register 13 around each SQL statement as in the following example.
ST 13,SAVER13 SAVE REGISTER 13 LA 13,SAVED POINT TO SAVE AREA EXEC SQL . . . L 13,SAVER13 RESTORE REGISTER 13
- If you have an addressability error in precompiler-generated code because of input or output host variables in an SQL statement, check to make sure that you have enough base registers.
- Do not put CICS translator options in the assembly source code. Instead, pass the options to the translator by using the PARM field.
- To allow for reentrant programs, the precompiler puts all the variables and structures it generates within a DSECT called SQLDSECT, and it generates an assembler symbol called SQLDLEN. SQLDLEN contains the length of the DSECT. Your program must allocate an area of the size indicated by SQLDLEN, initialize it, and provide addressability to it as the DSECT SQLDSECT. The precompiler does not generate code to allocate the storage for SQLDSECT; the application program must allocate the storage.
- Handling SQL error codes
- Assembler applications can request more information about SQL errors from Db2. For more information, see Handling SQL error codes in assembler applications.