# DB2/SQL Statements The primary interface for accessing DB2 from REXX is Structured Query Language (SQL) statements. The following sections discuss the types of SQL statements you can use, and how you code them in your REXX programs.

Coding SQL Statements | SQL Command Format
Supported SQL Statements | Special Statements

## Coding SQL Statements

SQL statements in REXX programs are coded as commands for the SQL host command environment (see "ADDRESS SQL"). A host command is any expression not recognized by the REXX interpreter as a language instruction or assignment statement. The expressions that make up host commands are usually literal strings. However, host command expressions can be very complex, and may contain literals, variables, function calls, and operators (such as the concatenation operator "||"). REXX transforms all of the variable elements of host command expressions into character strings.

## SQL Command Format

The basic format for all SQL host commands is:

         [EXEC SQL] sqlstatement [;]

Where sqlstatement is any of the statements described under the topics, "Supported SQL Statements" and "Special Statements".

Please note the following:

1. You may enclose a host command in either single or double quotes. Double quotes is usually the better choice, since literals within SQL statements must be contained in single quotes. If you choose to enclose your SQL host commands in single quotes, remember that you must "double-up" on any single quotes within the command. For example:

       'exec sql select lastname from dsn8230.emp',
                 'where firstnme = ''FRED'';'

2. As indicated by the syntax diagram, the "EXEC SQL" and the semicolon are optional.

## Supported SQL Statements

The following table describes the SQL statements supported by the DB2/SQL Service. For more information on what these commands do, and their exact syntax, refer to the DB2 SQL Reference for your system's level of DB2.

Command | Description
--- | ---
ALTER | Structurally modifies certain DB2 objects.
BEGIN DECLARE SECTION | Indicates the beginning of variable declarations.
CLOSE | Closes a cursor.
COMMENT | Updates the descriptions of various DB2 objects.
COMMIT | Terminates a unit of work and makes permanent any changes which may have taken place. Also closes "no hold" cursors.
CONNECT | Connects an application process to a server.
CREATE | Creates various DB2 objects.
DECLARE CURSOR | Associates a SELECT statement with a cursor.
DECLARE TABLE | Used to document the structure of a table.
DELETE | Used to delete rows from a table or a view. Can be used in "searched" or "positioned" modes.
DESCRIBE TABLE | Places column information into an SQLDA.
DROP | "Un-creates" or removes DB2 objects.
END DECLARE SECTION | Marks the end of variable declarations.
EXPLAIN | Obtains path selection information about statements that use searches.
FETCH | Retrieves a row from an open cursor's result set.
GRANT | Authorizes access to DB2 objects.
INSERT | Adds new rows to a table or a view.
LABEL | Updates the labels associated with various DB2 objects.
LOCK | Obtains a table-wide lock.
OPEN | Opens a cursor for processing.
RELEASE | Releases connections.
REVOKE | Removes authorizations.
ROLLBACK | Terminates a unit of work and cancels any changes that may have been made. Closes both "hold" and "no hold" cursors.
SELECT INTO | Retrieves rows of information into host variables. The REXXTOOLS implementation retrieves multiple rows. See "Multi-row SELECT".
SET CONNECTION | Establishes an application server.
SET CURRENT DEGREE | Controls use of parallel I/O.
SET CURRENT PACKAGESET | Changes the value of the CURRENT PACKAGESET special register.
SET CURRENT SQLID | Changes the SQL authorization ID.
SET :var = special register | Assigns the value of a special register into a host variable.
UPDATE | Changes the rows in a table or view. Can be used in "searched" or "positioned" modes.

## Special Statements

The DB2/SQL Service includes several SQL extensions. These are:

Multi-row SELECT
  A statement for retrieving multiple rows from a table or view.
OPTIONS
  A statement for setting Shadow REXXTOOLS processing options.
DECLARE VARIABLE
  A statement for specifying the type and size of host variables.
FREE VARIABLE
  A statement for removing variable definitions.
FREE CURSOR
  A statement for removing cursor definitions.

The following sections describe each of these statements in detail.

### Multi-row SELECT

The DB2/SQL Dynamic Service provides a special implementation of the SELECT command that returns -- in a manner similar to SELECT under SPUFI -- the entire result table in one or more REXX stem arrays. The syntax of the SELECT command is as follows:

    select-clause
      [INTO host-var-expression[,host-var-expression...]]
      from-clause
      [where-clause]

Where select-clause, from-clause, and where-clause are valid clauses as defined in the DB2 SQL Reference.

The host-var-expressions of the optional INTO clause specify the names of the stem arrays into which the columns and rows of returned data are to be copied. If you do not code an INTO clause, the REXX stem names will be based on the names of the columns specified in the select-clause (See the topic "Variables")

The following program demonstrates the operation of the SELECT command:

    /* REXX */
    address sql
    "select firstnme, salary",
      "into :fn:fni, :sal:sali",
      "from dsn8230.emp",
      "where salary > 20000"
    if (rc=0) & (sqlca.sqlrows>0) then
      do i = 1 to sqlca.sqlrows
        say fn.i fni.i sal.i sali.i
      end

The SELECT command in this example causes 4 stem arrays to be created: FN., FNI., SAL. and SALI.. The SQLCA.SQLROWS variable contains the number of elements (the number of rows) in each of the 4 arrays.

### OPTIONS

The OPTIONS statement is used to set Shadow REXXTOOLS processing options. The syntax of this statement is as follows:

    OPTIONS [SQLCA|NOSQLCA] [SQLDA|NOSQLDA] [MSGS|NOMSGS]
            [SUBSYS(subsys)] [PLAN(plan)]

Where:

SQLCA
  indicates that the interface is to produce a full complement of SQLCA variables for all eligible statements (some statements such as declare never cause an SQLCA to be produced).
NOSQLCA
  indicates that the interface is not to produce all of the SQLCA variables. This is the default setting. Please note the following exceptions:
  1. SQLCA.SQLCODE is always produced for eligible statements (statements other than BEGIN, END, DECLARE, and FREE VARIABLE).
  2. SQLCA.SQLROWS is always produced for Multi-row SELECT statements.
  3. Whenever a non-zero SQLCODE is encountered, the entire SQLCA is produced.
SQLDA
  indicates that the interface is to produce all of the SQLDA variables for all eligible statements (OPEN and SELECT).
NOSQLDA
  indicates that the interface is not to produce any SQLDA variables. This is the default setting.
MSGS
  indicates that messages are to be displayed. This is the default setting.
NOMSGS
  indicates that messages are not to be displayed.
SUBSYS(subsys)
  specifies the 1-4 character subsystem name to use when opening an implicit connection to DB2. See "Implicit OPENs".
PLAN(plan)
  specifies the 1-8 character plan name to use when opening an implicit connection to DB2. See "Implicit OPENs".

Notes:

1. The SQLCA consists of 23 variables, and the SQLDA consists of 2 variables containing header information and 7 variables for each column selected (see "Variables" for more information on these). The creation of SQLCA/SQLDA variables is somewhat time-consuming. You can enhance the performance of your programs by using the NOSQLCA and NOSQLDA options whenever possible.
2. The OPTIONS command of the SQL host command environment shares responsibility for REXXTOOLS message processing with the OPTIONS command of ADDRESS REXXTOOL. Use of the MSGS/NOMSGS keywords in either host command environment affects message processing for the entire product.

#### Example

The following demonstrates the use of the OPTIONS statement:

    address sql "exec sql options sqlca sqlda;"

### DECLARE VARIABLE

The DECLARE VARIABLE statement defines host variable (refer to the topic "Variables") type and size information to REXXTOOLS. The information is used by the dynamic interface to correctly convert data from REXX data types to DB2 data types and back again. The syntax of the DECLARE VARIABLE statement is:

    DECLARE varname[,varname...] VARIABLE db2type

Where varname is any valid REXX variable name, except that variables containing the question mark "?" are not permitted. If you want to use a stem variable in your SQL statements, you must declare the variable exactly as it will be coded. For example, suppose you want to insert values into a table and you are using 2 stem variables "A." and "B.". If your INSERT statement looks like this:

    do i = 1 to 10
      "exec sql insert into mytab (col1, col2)",
         "values(:a.i, :b.i);"
    end

Your declarations, which must precede the INSERT, should look like this:

    "exec sql declare a.i variable integer;"
    "exec sql declare b.i variable decimal(5,0);"

If later in your program you want to use a suffix other than the symbol "i", you must provide a separate declaration for that stem/suffix combination.

The valid values for db2type are:

INTEGER
  For a large integer.
SMALLINT
  For a small integer.
REAL
  For a single precision floating point number.
FLOAT
  For a double precision floating point number.
DECIMAL(integer,integer)
  For a packed decimal number. The first integer is the precision (the total number of digits), and the second is the scale (the number of fractional digits). Precision can range from 1 to 31. Scale can range from 0 to the precision, inclusive.
CHAR(integer)
  For a fixed-length character string, of length integer, where integer can range from 1 to 254.
VARCHAR(integer)
  For a varying-length character string, of maximum length integer, where integer can range from 1 to 32767.
GRAPHIC(integer)
  For a fixed-length graphic (Double Byte Character Set) string of length integer, where integer can range from 1 to 127.
VARGRAPHIC(integer)
  For a varying-length graphic (DBCS) string of maximum length integer, where integer can range from 1 to 16383.
DATE
  For a date.
TIME
  For a time.
TIMESTAMP
  For a timestamp.

For more information on DB2 data types please refer to the DB2 SQL Reference.

The scope of a declaration is a REXX source program. You must ensure that any internal subroutines using declared variables expose them with the EXPOSE option of the REXX PROCEDURE statement.

Notes:

1. For db2type, all of the DB2 data-type specifications described under the topic "DECLARE TABLE" in the DB2 SQL Reference are accepted.
2. Only input variables requiring conversions (see "Input Variables") need to be declared. Undeclared input variables are assumed to be of type VARCHAR. Output variable declarations are accepted, but DB2-provided information will be used to drive conversions in all cases. Indicator variables need never be declared. If an indicator variable is not declared, its type, SMALLINT, is inferred from usage.
3. Variable names can be up to 250 bytes long (the REXX limit) and can contain any symbol that can be used in a REXX variable symbol, except the question mark.
4. The BEGIN/END DECLARE SECTION statements are not required. In the dynamic interface, variable declarations are executed statements, so you may intermingle them with other statements if you wish; however, if you desire a declaration for a variable, it must be executed before the variable is used.
5. The size specifications of declarations are checked, but not used. Neither truncation nor rounding is performed. Because of this, if you try to INSERT a value that is larger than its corresponding column, a negative SQLCODE will result.
6. The data type information for input variables is used to determine what conversions, if any, are required. For numeric types, your program must ensure that a variable's data is consistent with its declaration or a run-time error will result.

#### Example

    "exec sql declare firstnme,lastname variable varchar(15);"
    "exec sql declare salary variable decimal(9,2);"
    "exec sql declare hiredate variable date;"

### FREE VARIABLE

The FREE VARIABLE statement is used to "un-define" a variable to REXXTOOLS, and release its storage. The syntax of the free variable statement is:

    FREE [varname|*]  VARIABLE

Where varname is the name of the variable to be freed. If you specify "*" all variables for the program are freed. At MVS task termination, any variables still defined are freed automatically.

#### Example

    "exec sql free abc variable;"
    "exec sql free * variable;"

### FREE CURSOR

The FREE CURSOR statement is used to disassociate a cursor name from its SELECT statement, and free internal data structure storage. The syntax of this statement is as follows:

    FREE [csrname|*] CURSOR

Where csrname is the name of the cursor to be freed. If you specify "*", all cursors for the current REXXTOOLS environment (usually an MVS task) are freed.

Note: Cursors are unconditionally closed, prior to freeing. Because of this, it is permissible to FREE a cursor without closing it first.

© Copyright 1998 by Open Software Technologies, Inc.