Dynamic SQL

Types of Statements	Preparing to Execute Commands
How Commands Are Processed

Types of Statements

• Dynamic SQL Statements. Dynamic SQL statements are statements that are PREPAREd and EXECUTEd while your program is running. The Dynamic SQL Service does the work of PREPAREing and EXECUTEing these statements for you.
• Pseudo-static SQL Statements. These statements must be statically prepared and identified to DB2 prior to the execution of your program. The Dynamic SQL Service keeps a pool of generic static statements to be used by the interface whenever they are needed. The pooled statements are:

  CLOSE	CONNECT	DECLARE CURSOR
  DESCRIBE TABLE	FETCH	OPEN
  SET CURRENT PACKAGESET	SET CURRENT SQLID	SET :hostvar = special-register

• The Multi-row SELECT Statement. The Dynamic SQL Service provides special support for processing sets of rows using the SELECT command (similar to SPUFI SELECTs). See "Multi-row SELECT" for more information.
• Interface Control Statements. These statements are used to change the processing characteristics of the interface. See "Special Statements" for more information.

/* REXX */
/* Execute a TSO command */
address TSO
"alloc fi(indd) da(data) shr reu"
.
.
.
/* Execute several SQL commands */
address SQL
"select * from dsn8230.emp"
/* process the output */
"update dsn8230.emp set salary=salary*1.1"
.
.
.
/* Switch back to TSO */
address TSO
.
.
.
/* Issue just one SQL command without
   changing the current host command
   environment */
address SQL "select * from dsn8230.proj"

1. The interface checks to see if a connection to DB2 has been established. If it has not, default values are used to open a connection to DB2.
2. The ordinary tokens of the SQL statement (keywords, column names, etc.) are folded into uppercase characters. Delimiter tokens (i.e., quoted literal strings) are left in the case in which they were entered. Multiple blanks between identifiers are compressed to single blanks. Hence, the following SQL command:

   "select col1, col2 ",
     "from   mytab ",
    "where  a='This is a string'"
   

   will, internally, become:

   SELECT COL1, COL2 FROM MYTAB WHERE A='This is a string'
   

   WARNING: Basic predicate (=, >, <, etc.) comparisons are case sensitive. Take care to use the correct case when typing in literal and host variable values!

3. SELECT and FETCH commands are scanned for INTO clauses. If an INTO clause is found, it is removed from the command, and the information is saved in internal data structures.
4. Most (though not all) commands are scanned for host variable expressions (see the topic "Variables"). Host variable expressions are replaced with parameter markers.

   Note: Explicit use of parameter markers (question marks) is not permitted in SQL commands. Instead, you may use host variable expressions.

5. For dynamically executed statements, pseudo-static OPENs, and Multi-row SELECTS, the processed statement (after uppercase folding, blank compression, and variable substitution) is compared to lists of previously PREPAREd statements. If a statement has already been PREPAREd, the PREPARE is skipped. If not, the statement is PREPAREd.
6. Depending on the type of statement, an EXECUTE, or a pseudo-static statement is executed (i.e., processed by DB2).
7. If the command was a query (SELECT or FETCH) output host variables are created (see the topic "Variables"). Also, the SQLDA variables are created (see "The SQLDA") provided that OPTIONS NOSQLDA is not in effect.
8. If no interface errors have occurred, and the OPTIONS SQLCA is in effect, the SQLCA variables are created (see "The SQLCA").
9. Finally, the RC and REASON variables are created.