DB2 Server for VSE & VM: Application Programming

Rules for Using SQL Statements in Assembler Language

This section lists the rules for embedding SQL statements within an assembler program.

Note:

OPSYN and ICTL assembler statements may not be used.

Identifying Rules for Case

Uppercase must be used for all SQL statements, except for text within quotation marks, which will be left in the original case.

Declaring Host Variables

The following example shows an SQL declare section for an assembler program:

Col. 1 Col.16 Col. 72 | | | | | | LABEL EXEC SQL BEGIN DECLARE SECTION AA DS F BB DC H'3' comment * comment card or * comment section CC DC CL80'xxxx......................................xxxx* xxxx...............xxxxx' XYZ DSECT DD DS D EE DS CL5 FF DS H,CL40 ORG FF GG DS H HH DS CL40 comment * continued comment II DS PL5 JJ DC PL5'123.45' KK DS 0H LL DS CL12 XX DS CL10 * continuation of comment LABEL2 EXEC SQL END DECLARE SECTION comment

The preceding example illustrates the following rules:

1. All assembler variables that are to be used in SQL statements must be declared, and their declarations must appear within one or more sections that begin with:

      EXEC SQL BEGIN DECLARE SECTION
   

   and end with:

      EXEC SQL END DECLARE SECTION
   

   Each of these two statements must be totally contained on one line.

   Note:

   There is no semicolon delimiter at the end of the SQL statements. There may be a label on either of the statements, and host language comments are allowed after the statements.

2. Host language comments are allowed on any statement within the SQL declare section, as are host language comment line images (* in column 1).
3. The assembler preprocessor processes the statements in the declare section as follows:
   1. If there is no label, the preprocessor ignores the statement and goes on to the next.
   2. If there is a label, but the opcode is not DS or DC, the preprocessor ignores the statement and goes on to the next.
   3. If there is a label and a DS or DC opcode, the operand is checked. The operand must be an acceptable data type, as shown in Figure 105. Here are some examples:

         F
         F'5'
         H
         H'100'
         CL255
         CL5'ABCDE'
         H,CL5
         H'5',CL5'ABCDE'
         D
         D'2.5E10'
         PL2
         PL5'123.45'
         P'123'
         P'123.45'
         P'1234'
         P'123.456'
         H,CL32767
      

      The first character of the operand may also be zero and used as follows:

         0H
         0F
         0D
         0C
      

      In this case, the line is ignored and the next line is processed.

      If there are no errors at this stage, the variable is validly defined as a host variable. If there are errors, the line is flagged as an error, and the next line is processed.

4. The database manager allows host variable names, statement labels, and SQL descriptor area names of up to 256 characters in length, subject to any assembler language restrictions mentioned in this appendix.
5. The opcode for a declare statement must be coded on the first line of the statement. Because the line length is 71, this limits the length of host variable names to 68 characters.
6. Continuations are allowed by coding a non-blank character in column 72 of the line to be continued, and coding the continuation anywhere from columns 16 to 71 inclusive on the next line, leaving 1-15 blank.
7. Continuation of tokens (the basic syntactical units of a language) is allowed from one line to the next, by coding the first part of the token up to column 71 of the line to be continued, and coding the second part of the token from column 16 on the continuation line. If either column 71 of the continued line or column 16 of the continuation line is blank, the token will not be continued. See the DB2 Server for VSE & VM SQL Reference manual for a discussion on tokens.
8. The declare section can be anywhere that a normal DS or DC can be used. Because the assembler preprocessor is a two-pass operation, the declare section can come after the SQL statements that use the host variables.
9. There can be more than one SQL declare section in a program.
10. Host variable names cannot contain variable symbols (for example, &ABCDEFG, &SYSNDX, &SYSPARM). These names must be resolved at preprocessing time. Variable symbols will be resolved at assembly time.

Embedding SQL Statements

The following are the rules for embedding SQL statements within assembler programs:

1. Each SQL statement must be preceded by EXEC SQL, which must be on the same line. Only blanks can appear between the EXEC and SQL. There must not be a semicolon (;) delimiter on the SQL statement.
2. The first line of an SQL statement can have a label beginning in column 1. If there is no label, the statement must begin in column 2 or greater.
3. Rules for continuation of statements and tokens are the same as those described for host variables.
4. No host language comments are allowed within an SQL statement. Any such comments are considered part of the SQL statement.
5. If an entire statement must be contained on one line, there cannot be SQL comments embedded in the statement. There are three such statements:
   • BEGIN DECLARE SECTION
   • END DECLARE SECTION
   • INCLUDE.
6. Avoid using labels or variable names that begin with SQL, ARI, or RDI. Also avoid names beginning with PID, PBC, PA, PB, PC, PD, PE, PL, or PN where these letters are followed by numbers. These names may conflict with names generated by the assembler preprocessor.
7. All SQL statements must be in one CSECT.
8. The EXEC SQL must be coded on the first line of the statement. Because the line length is 71, this limits the length of a LABEL to 62 characters.

Using the INCLUDE Statement

To include external secondary input, specify the following at the point in the source code where the secondary input is to be included:

   EXEC SQL INCLUDE text_name

Text_name is the A-Type source member of a VSE library. Text_name is the file name of a CMS file with an "ASMCOPY" file type, located on a CMS minidisk accessed by the user.

The INCLUDE statement must be completely contained on one line. There may be a label on the command, and host language comments are allowed after the command.

Using Host Variables in SQL Statements

When you place host variables within an SQL statement, you must put a colon (:) in front of every host variable, to distinguish them from the SQL identifiers (such as a column name). When the same variable is used outside of an SQL statement, do not use a colon.

A host variable can represent a data value, but not an SQL identifier. For example, you cannot assign a character constant, such as "MUSICIANS", to a host variable, and then use that host variable in a CREATE TABLE statement to represent the table name. The following pseudocode sequence is invalid:

Using DBCS Characters in Assembler Language

The rules for the format and use of DBCS characters in SQL statements are the same for assembler language as for other host languages supported by the database manager. For a discussion of these rules, see Using a Double-Byte Character Set (DBCS).

Assembler language does not provide a way to define graphic host variables. If you want to add graphic data to or retrieve it from DB2 Server for VSE & VM tables, you must execute the affected statements dynamically. By doing so, the data areas that are referenced by each statement can be described in an SQLDA. In the SQLDA, you must set the data type of the areas containing graphic data to one of the graphic data types. For a discussion of the SQLDA, refer to the DB2 Server for VSE & VM SQL Reference manual.