Programming assembler applications that issue SQL statements
You can code SQL statements in a assembler program 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 DSN8A10.DEPT X
SET MGRNO = :MGRNUM X
WHERE DEPTNO = :INTDEPT
- Comments
- You cannot include assembler comments in SQL statements. However, you can include SQL comments in any embedded SQL statement.
- 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 keyword EXEC SQL and 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.
- Margins
- 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.
- Names
-
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 return codes in assembler
You can use the subroutine DSNTIAR to convert an SQL return code into a text message. DSNTIAR takes data from the SQLCA, formats it into a message, and places the result in a message output area that you provide in your application program. For concepts and more information about the behavior of DSNTIAR, see Displaying SQLCA fields by calling DSNTIAR.
You can also use the MESSAGE_TEXT condition item field of the GET DIAGNOSTICS statement to convert an SQL return code into a text message. Programs that require long token message support should code the GET DIAGNOSTICS statement instead of DSNTIAR. For more information about GET DIAGNOSTICS, see Checking the execution of SQL statements by using the GET DIAGNOSTICS statement.
DSNTIAR syntax
CALL DSNTIAR,(sqlca, message, lrecl),MF=(E,PARM)
- sqlca
- An SQL communication area.
- message
- An output area, defined as a varying-length string, in which DSNTIAR
places the message text. The first halfword contains the length of
the remaining area; its minimum value is 240. The output lines of text, each line being the length specified in lrecl, are put into this area. For example, you could specify the format of the output area as:
where MESSAGE is the name of the message output area, LINES is the number of lines in the message output area, and LRECL is the length of each line.LINES EQU 10 LRECL EQU 132 ⋮ MSGLRECL DC AL4(LRECL) MESSAGE DS H,CL(LINES*LRECL) ORG MESSAGE MESSAGEL DC AL2(LINES*LRECL) MESSAGE1 DS CL(LRECL) text line 1 MESSAGE2 DS CL(LRECL) text line 2 ⋮ MESSAGEn DS CL(LRECL) text line n ⋮ CALL DSNTIAR,(SQLCA,MESSAGE,MSGLRECL),MF=(E,PARM)
- lrecl
- A fullword containing the logical record length of output messages, between 72 and 240.
The expression MF=(E,PARM) is an z/OS® macro parameter that indicates dynamic execution. PARM is the name of a data area that contains a list of pointers to the call parameters of DSNTIAR.
See Sample applications supplied with DB2 for z/OS for instructions on how to access and print the source code for the sample program.
CALL DSNTIAC,(eib,commarea,sqlca,msg,lrecl),MF=(E,PARM)
- eib
- EXEC interface block
- commarea
- communication area
For more information on these parameters, see the appropriate application programming guide for CICS. The remaining parameter descriptions are the same as those for DSNTIAR. Both DSNTIAC and DSNTIAR format the SQLCA in the same way.
You must define DSNTIA1 in the CSD. If you load DSNTIAR or DSNTIAC, you must also define them in the CSD. For an example of CSD entry generation statements for use with DSNTIAC, see member DSN8FRDO in the data set prefix.SDSNSAMP.
The assembler source code for DSNTIAC and job DSNTEJ5A, which assembles and link-edits DSNTIAC, are also in the data set prefix.SDSNSAMP.