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.

You might code an UPDATE statement in an assembler program as follows:

   EXEC SQL UPDATE DSN8C10.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. 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 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:

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
*

In this example, the actual storage allocation is done by the DFHEIENT macro.

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.

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.