# Dynamic SQL

The Dynamic DB2/SQL Service provides a command-based interface to DB2. The following sections describe the use of this facility.

Types of Statements | Preparing to Execute Commands
How Commands Are Processed |

## Types of Statements

The SQL statements that are accepted by the interface can be classified as follows:

- 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.

## Preparing to Execute Commands

You must use the REXX ADDRESS instruction to establish SQL as the current host command environment before you can execute groups of SQL statements. If you only want to execute a single SQL command while retaining the current host command setting, you can append the SQL command onto the end of the ADDRESS SQL instruction. The following examples show how the ADDRESS instruction is used:

    /* 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"

## How Commands Are Processed

The SQL host command environment routine processes SQL statements in the following manner:

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.

© Copyright 1998 by Open Software Technologies, Inc.