Variables

Making Commands Variable Input Variables
Output Variables Special Variables

Making Commands Variable

While many SQL commands shown in this manual consist of a single REXX literal string, it is not a requirement that all your SQL commands be constructed like this. You may use arbitrarily complex REXX expressions to dynamically create commands. REXX resolves these expressions to character strings before invoking the SQL host command environment. To illustrate this point, consider the following code fragment:

address sql
"select name from" tabname

Here, a REXX variable named TABNAME is used to specify the table from which information will be extracted. If, for example, TABNAME contained the value "emptabl", the resulting string would be:

"select name from emptabl"

The DB2/SQL Service also supports a special type of variable substitution that takes place after REXX has evaluated the expression. This type of variable substitution is very similar to the DB2 pre-compiler support for host variables. For example, in the SQL command:

"select firstnme",
  "from dsn8230.emp",
 "where lastname = :ln:lni"

":LN:LNI" is a host variable expression. In this context, the host variable expression describes an input variable, because a value will be used in place of the expression. In other contexts, host variable expressions may be used to specify the names of variables into which SQL command results are to be placed. Host variables such as these are called output variables.

The syntax for both input and output host variable expressions is:

:data-variable[:indicator-variable]

Where data-variable is the name of a REXX variable that contains (or will contain) a data value, and indicator-variable is the name of a REXX variable that contains (or will contain) control information related to the data variable. The indicator-variable component is optional for both input and output variables.

Syntax Notes

In all cases, you must prefix data and indicator variable names with a colon (:). If you do not, the SQL host command environment will not recognize them as host variables.
There can be no blanks between the data variable and the indicator variable, if it is coded.
The variable names must be valid REXX symbols. However, the question mark (?) which is permitted in REXX symbols is NOT permitted in SQL command host variables. For more information on what constitutes a valid host variable name, please refer to the topic "DECLARE VARIABLE".

Input Variables

Input host variable expressions are used to transfer data from your program's variable pool to DB2. You may use input host variable expressions only where they are indicated in the SQL syntax diagrams (see the DB2 SQL Reference).

Since all REXX data including numbers are in printable format, before a statement is sent to DB2 for processing, input variable conversions may need to be performed. The type of conversion, if any, is controlled by the DECLAREs for the variables contained in the statement. The section that follows discusses how these conversions work.

Input Data Variable Conversions

All input host variable values must be in REXX printable character format. You must not explicitly convert numeric or time-related data values to a packed or binary format. REXXTOOLS will perform the necessary conversions, automatically, prior to sending a statement to DB2.

The following table describes the conversions performed by the for non-NULL variables.

Declared Type Dynamic SQL Conversion
INTEGER The REXX number is converted to a fullword integer. Any fraction is truncated. REXX "E" format numbers are not supported.
SMALLINT The REXX number is converted to a halfword integer. Any fraction is truncated. REXX "E" format numbers are not supported.
REAL The REXX number is converted to a 4 byte floating point number. Any valid REXX number is accepted.
FLOAT The REXX number is converted to a 8 byte floating point number. Any valid REXX number is accepted.
DECIMAL The REXX number is converted to a packed decimal number of a precision and scale that fits the number. The precision/scale information is sent to DB2 along with the converted number.
CHAR No conversion.
VARCHAR No conversion.
GRAPHIC No conversion.
VARGRAPHIC No conversion.
DATE No conversion.
TIME No conversion.
TIMESTAMP No conversion.

Declared Type	Dynamic SQL Conversion
INTEGER	The REXX number is converted to a fullword integer. Any fraction is truncated. REXX "E" format numbers are not supported.
SMALLINT	The REXX number is converted to a halfword integer. Any fraction is truncated. REXX "E" format numbers are not supported.
REAL	The REXX number is converted to a 4 byte floating point number. Any valid REXX number is accepted.
FLOAT	The REXX number is converted to a 8 byte floating point number. Any valid REXX number is accepted.
DECIMAL	The REXX number is converted to a packed decimal number of a precision and scale that fits the number. The precision/scale information is sent to DB2 along with the converted number.
CHAR	No conversion.
VARCHAR	No conversion.
GRAPHIC	No conversion.
VARGRAPHIC	No conversion.
DATE	No conversion.
TIME	No conversion.
TIMESTAMP	No conversion.

In addition, you must follow all of the rules for data type compatibility as they are described in the DB2 SQL Reference. You cannot, for example, assign a timestamp value to a date column, or a non-numeric character value to a numeric column.

Input Indicator Variable Conversions

Indicator variables are converted to halfword integers. If the value of an input indicator variable is set to -1, DB2 will interpret the associated data variable's value as NULL. However, the order of operations is not defined. Which means that you can still incur data variable conversion errors, even though a NULL value is indicated.

Note: You may omit the declaration of indicator variables.

Output Variables

Output variables are used to return information retrieved by the SELECT, FETCH, and SET statements. There are two types of output variables, implicit output variables, the names of which are derived for you, and explicit output variables, the names of which you supply using host variable expressions.

For Multi-row SELECT statements, the names of the output variables (both implicit and explicit) are appended with a numeric subscript that varies from 1 to the value contained in SQLCA.SQLROWS (see "The SQLCA"). Thus, the SELECT statement:

"select firstnm, lastname, salary",
  "from dsn8230.emp"

if successful, will generate stem arrays of the form FIRSTNM.n, LASTNAME.n, and SALARY.n, where "n" is the numeric subscript.

Very Important: If you join tables with like-named columns, you must use the INTO clause to distinguish between the columns of one table and the columns of another.

Implicit Output Variables

If you do not code an INTO clause for Multi-row SELECT or FETCH statements, output host variable names will be derived for you, using the names (not the labels) of the columns selected. For SELECT commands, numeric subscripts are appended to the end of the variable names to create stem arrays.

If the SELECT or FETCH involves a computed column value, a synthetic name is supplied when creating the REXX variable(s) associated with the column. The synthetic column names are of the form:

SQLCOLn.k

where n is an integer describing the relative position of the column (1-300), and k is the subscript that is appended to variables retrieved using the Multi-row SELECT statement. For example, the following SELECT:

"select avg(salary) from dsn8230.emp"

will create the single output variable SQLCOL1.1.

For Multi-row SELECTs and FETCHes, the interface will REXX DROP any variable whose corresponding value is NULL.

Explicit Output Variables

If you code an INTO clause for a Multi-row SELECT or FETCH statement, you must supply enough host variable names to hold all of the columns retrieved. For columns that can return NULL values, you must supply an indicator variable in the host variable expression.

For Multi-row SELECT commands, you should not specify stem names (names that end with dots) in the INTO clause, because the SQL host command environment will automatically append a dot and a numeric subscript for you.

Note: For Multi-row SELECTs and FETCHes, the interface will REXX DROP any variable whose corresponding value is NULL.

Output Indicator Variables

If you do not code an INTO clause on a SELECT or FETCH statement, indicator variables will be created for you using the column names as a base. These variables are of the form:

columnname.IND.n

Where columnname is the name of the column being retrieved (or the synthetic column name), and n is the subscript that is appended to variables retrieved using the SELECT statement.

A NULL data variable value is indicated whenever its associated indicator variable contains the value -1. DB2-detected conversion errors or arithmetic expression errors are indicated by a -2 value.

Notes:

If you supply an INTO clause, you must specify the names of indicator variables, if any are required. The interface will not automatically produce data-variables or indicator-variables when the INTO clause is present.
Positive value indicator variable values (these indicate truncation) are not possible, since enough storage will be allocated to return the data variable's value.

Output Data Variable Conversions

Retrieved data is translated into printable character formats, as required. Type information from DB2 drives the conversions. The following describes the conversions that take place by data type.

INTEGER: The data is converted to printable REXX decimal integer format. Leading zeros are removed.
SMALLINT: The data is converted to printable REXX decimal integer format. Leading zeros are removed.
REAL: The data is converted to printable REXX exponential using the REXX rules for formatting "E" format numbers.
FLOAT: The data is converted to printable REXX exponential using the REXX rules for formatting "E" format numbers.
DECIMAL: The data is converted to printable REXX decimal format. Leading zeros are removed, except that decimal numbers with no whole number component are supplied a single zero to the left of the decimal point. Decimal numbers with precisions of up to 31 digits are supported.
CHAR: No conversion takes place.
VARCHAR: No conversion takes place. The data's length field is not included in the data since all REXX strings are varying character by definition.
GRAPHIC: No conversion takes place. However, you may need to use the REXX OPTIONS instruction to accurately handle DBCS data.
VARGRAPHIC: No conversion takes place. See GRAPHIC above.
DATE: DB2 converts date values to characters strings. The format for the data is determined at DB2 installation time.
TIME: DB2 converts time values to characters strings. The format for the data is determined at DB2 installation time.
TIMESTAMP: DB2 converts timestamp values to characters strings. The format for the data is determined at DB2 installation time.

Output Indicator Variable Conversions

Indicator variables, when present, are converted to REXX printable decimal numbers.

Special Variables

The interface produces return codes which indicate the final status of all operations. The SQL host command environment produces as many as 3 groups of returned information, depending on the type of SQL command executed. These are:

RC and REASON variables
SQLCA variables
SQLDA variables

The sections that follow document the status information returned by the SQL host command environment. The information returned by the DB2INFO and DSNALI functions is discussed in the "Reference" chapter.

RC Special Variable

The RC variable is used to indicate the success or failure of an SQL statement. In all cases, you should inspect the value of RC before evaluating any of the SQLCA or SQLDA variables. The RC variable uses 3 special values to give a combined DB2/interface status:

-1: Indicates that the interface encountered no errors, but DB2 returned a negative SQLCODE.
0: Indicates that the interface and DB2 encountered no errors.
1: Indicates that the interface encountered no errors, but DB2 returned a positive SQLCODE.

Positive RC values, other than 0 or 1 indicate some type of interface error. These errors are documented below:

2: Processed string is too long (max.: 32767 bytes).
3: Empty string. No processing performed.
4: The number of variables specified in an INTO clause does not equal the number of columns to be retrieved.
5: INTO clause syntax error.
6: Data variable create/update failed due to an invalid REXX variable name.
7: Invalid input indicator variable value. Indicator variables must contain a value that can be converted to a DB2 SMALLINT data type.
8: Indicator variable create/update failed due to an invalid REXX variable name.
9: End of source found before end of literal.
10: Invalid input data variable name.
11: Missing input variable name.
12: Invalid input indicator variable name.
13: The cursor name is invalid. Cursor names must be valid DB2 short ordinary identifiers.
14: Maximum number of declared cursors exceeded (max.: 10 cursors).
15: Cursor cannot be freed. It is not allocated.
16: Cursor has been used before it has been declared.
17: Parameter markers (?) are not supported. Use a host variable instead.
18: An input REXX variable's value was longer than the maximum supported length (32767 bytes).
20: The SQL command verb is not supported.
21: DB2 did not fill in the SQLCA. This probably means the connection to DB2 is no longer available.
22: Token exceeds maximum size (250 bytes).
23: Unusable "DECLARE CURSOR" command. Check for syntax error.
24: Cursor has already been declared.
25: Unusable "OPEN" command. Check for syntax error.
26: Unusable "FETCH" command. Check for syntax error.
30: Unusable "CLOSE" command. Check for syntax error.
31: Cursor cannot be FREEed. Cursor is still OPEN.
32: Unusable "FREE" command. Check for syntax error.
33: "EXEC SQL" or terminating ";" syntax error.
34: Unusable "BEGIN/END DECLARE SECTION" command. Check for syntax error.
35: Invalid "DECLARE VARIABLE" variable name. Declared variables must be valid REXX variable names and must not include the question mark character.
36: DECLARE command syntax error.
37: Input variable conversion error. Verify that the data contained in the variable can be converted to its declared type.
38: Unsupported DECLARE command. "DECLARE STATEMENT" commands are not supported.
39: Unrecognized DECLARE command. Check for a syntax error.
40: Unrecognized FREE command. Check for a syntax error.
41: Variable not found. The FREE command could not find the specified variable. Execution continues.
42: No variables found. The FREE * command could not find any variables to free for the currently executing exec. Execution continues.
43: Invalid option. The option given on the OPTIONS statement is unrecognized. Check for spelling errors.
44: Missing options. The OPTIONS command requires option operands.
45: OPTIONS command syntax error.
46: CONNECT command syntax error.
47: The variable name for a CONNECT command is invalid.
48: SET command syntax error.
50: The variable name for a SET command is invalid.
51: DESCRIBE command syntax error.
52: Invalid DESCRIBE command variable name. Either the table/view name variable or the SQLDA prefix was not a valid REXX variable name. The SQLDA prefix must also not exceed 8 bytes.
53: Table/view name too long. Maximum length is 254 bytes.
54: RELEASE command syntax error.
55: The variable name for a RELEASE command is invalid.

REASON Special Variable

The REASON variable is used to return Call Attachment Facility reason codes. If you explicitly create a thread to DB2 using the DSNALI function, the REASON variable should contain zero. If you do not use the DSNALI function, the REASON variable will contain the Call Attachment Facility reason code. If, after an implicit OPEN, the REASON code is not zero, then the RC variable contains the Call Attachment Facility return code.

Call Attachment Facility return codes are documented in the DB2 Application Programming and SQL Guide. Call Attachment Facility reason codes are documented in the DB2 Messages and Codes.

The SQLCA

The SQLCA is a family of variables that indicate the status of a statement from the perspective of DB2. These variables begin with the stem "SQLCA.". The following table describes each of the SQLCA variables:

SQLCA Name Description
SQLCODE A printable REXX integer that indicates the status of the operation.
SQLMSG A textual translation of the SQLCODE.
SQLROWS A printable REXX integer that contains the number of rows returned for Multi-row SELECT commands. This variable is undefined for other types of commands.
SQLTXT The text of the SQL command after it has been processed by the interface.
SQLERRP Module detecting error.
SQLERRD1 Internal DB2 error code.
SQLERRD2 Internal DB2 error code.
SQLERRD3 Number of rows affected by INSERT, UPDATE, and DELETE.
SQLERRD4 Timerons (See DB2 documentation).
SQLERRD5 Position of error.
SQLERRD6 Internal DB2 error code.
SQLWARN0 Blank if all other indicators are blank; 'W' if at least one other contains 'W'.
SQLWARN1 'W' if output host variable truncation occurred.
SQLWARN2 'W' if NULL values are removed from the argument of a column function.
SQLWARN3 'W' if more columns than output host variables.
SQLWARN4 'W' if UPDATE or DELETE has no WHERE clause.
SQLWARN5 'W' if statement is not valid.
SQLWARN6 'W' for DATE/TIMESTAMP arithmetic error.
SQLWARN7 'W' for loss of fractional digits in multiply or divide.
SQLWARN8 'W' if character translation failed.
SQLWARN9 'W' if COUNT DISTINCT arithmetic error.
SQLWARNA 'W' if SQLDA/SQLCA character conversion error.
SQLSTATE Alternative return code from DB2.

SQLCA Name	Description
SQLCODE	A printable REXX integer that indicates the status of the operation.
SQLMSG	A textual translation of the SQLCODE.
SQLROWS	A printable REXX integer that contains the number of rows returned for Multi-row SELECT commands. This variable is undefined for other types of commands.
SQLTXT	The text of the SQL command after it has been processed by the interface.
SQLERRP	Module detecting error.
SQLERRD1	Internal DB2 error code.
SQLERRD2	Internal DB2 error code.
SQLERRD3	Number of rows affected by INSERT, UPDATE, and DELETE.
SQLERRD4	Timerons (See DB2 documentation).
SQLERRD5	Position of error.
SQLERRD6	Internal DB2 error code.
SQLWARN0	Blank if all other indicators are blank; 'W' if at least one other contains 'W'.
SQLWARN1	'W' if output host variable truncation occurred.
SQLWARN2	'W' if NULL values are removed from the argument of a column function.
SQLWARN3	'W' if more columns than output host variables.
SQLWARN4	'W' if UPDATE or DELETE has no WHERE clause.
SQLWARN5	'W' if statement is not valid.
SQLWARN6	'W' for DATE/TIMESTAMP arithmetic error.
SQLWARN7	'W' for loss of fractional digits in multiply or divide.
SQLWARN8	'W' if character translation failed.
SQLWARN9	'W' if COUNT DISTINCT arithmetic error.
SQLWARNA	'W' if SQLDA/SQLCA character conversion error.
SQLSTATE	Alternative return code from DB2.

Notes:

The OPTIONS statement controls the production of the SQLCA variables (see "OPTIONS" for more information on this subject).
All SQLCA variables are returned in REXX printable format.
SQLCODEs and SQL messages are explained in DB2 Messages and Codes.
The SQLCA.SQLMSG variable is very useful for debugging SQL statements.
Prior to executing each SQL command, the SQLCA. stem is REXX DROPped (un-assigned).

The SQLDA

The SQL Descriptor Area (SQLDA) variables are produced after successful Multi-row SELECT and OPEN commands. SQLDA variables contain information that describes the columns of data to be retrieved. For SELECT commands, the stem prefix for the SQLDA variables is simply "SQLDA." For OPEN commands, the stem prefix is:

csrname.SQLDA.

Where csrname is the name of the cursor that is being OPENed.

Some of the SQLDA variables are actually REXX arrays that describe the type of information in each SELECTed column. These variables are suffixed with REXX integers that range from 1 to n, where n is the number of columns.

The SQLDA variables are as follows:

prefix.SQLN: Total number of columns.
prefix.SQLD: Actual number of columns.
prefix.SQLNAME.n: Column name
prefix.SQLTYPE.n: DB2's number for the data type.
prefix.SQLLEN.n: DB2's length for the data type (length prior to conversions).
prefix.SQLPRCSN.n: For DECIMAL types, the precision.
prefix.SQLSCALE.n: For DECIMAL types, the scale.
prefix.SQLMAXW.n: Maximum converted column width.
prefix.SQLRXTYP.n: REXX data type (CHAR or NUM).

Notes:

In the dynamic interface, the OPTIONS statement controls whether or not the SQLDA is produced.
The "n" in the variable names above is a REXX integer that ranges from 1 to the value contained in the SQLN variable.
Unlike the SQLCA variables, the SQLDA variables are never REXX DROPped once they are created. To improve the efficiency of your long-running programs, you may want to issue REXX DROP instructions for SQLDA variables when they are no longer needed.
The SQLMAXW variables can be used when calculating the number of print/display positions to reserve for a column. For NUM columns, the SQLMAXW gives the maximum width of converted values.