The DESCRIBE Statement

The DESCRIBE statement returns information about the host input and/or output variables associated with an RLX SQL statement which has already been processed.

RLX creates an input and/or output SQL Descriptor Area (SQLDA) as part of preparing the SQL statement for execution. These SQLDAs contain information about the input and/or output host variables associated with the prepared SQL statement. A subsequent RLX DESCRIBE request maps these SQL descriptor area(s) into a set of REXX compound variables which your exec can reference.

The format of the DESCRIBE service is:

RLX DESCRIBE <statement>

where <statement> refers to any one of the following:

(1) The name of a declared CURSOR as in the following example:

'RLX declare cursor_name cursor for select ...'

'RLX describe cursor_name'

where 'cursor_name' references the name of the declared cursor.

(2) The name of a declared ISPFTABLE as in the following example:

'RLX declare ispfqry ispftable for ...' select ...'

'RLX describe ispfqry'

where the name 'ispfqry' references the name of the declared ISPFTABLE

(3) The name of a prepared statement as in the following example:

'RLX prepare query_result',

'select * from sysibm.syscolumns where tbname = 'SYSTABLES'

'RLX describe query_result'

where 'query_result' _references the label of the prepared statement.

(4) The name of a prepared statement (using the RxSQL PREP request) as in the following example:

'rxsql prep query_result',

'select * from sysibm.syscolumns where tbname = 'SYSTABLES'

'rxsql describe query_result'

where 'query_result' _references the label of the statement prepared by an RxSQL PREP request.

(5) The value of the REXX variable 'RLXSTNM'. RLX assigns a unique internal label to each statement during its preparation and then updates the value of the REXX variable RLXSTNM with the name of this internal label each time RLX executes the statement. The following example illustrates the use of the RLXSTNM variable.

'RLX insert into DB2_table ....

'RLX describe' rlxstnm

One word of caution concerning the RLXSTNM variable. Its content is volatile in that its value is updated after each RLX statement executes. Therefore, you may wish to assign RLXSTNM to another REXX variable to preserve its value as in the following example:

'rlx insert into DB2_table ....

insert_statement_label = rlxstnm

'rlx describe' insert_statement_label

A DESCRIBE request maps SQL Descriptor Area information about the referenced SQL statement into a set of REXX compound variables which your exec can reference. RLX automatically translates binary values into numeric character strings.

For example, an output SQLDA describes a statement's query result columns -- in other words, the values received from DB2. An output SQLDA applies to the singleton SELECT, DECLARE CURSOR, DECLARE REXXSTEM and DECLARE ISPFTABLE statements.

An input SQLDA describes the host variables that supply values to DB2. These include the host variables that appear in a WHERE clause to parameterize a statement, as well as the host variables that appear in SQL INSERT and UPDATE statements. In addition, statements like SET CURRENT SQLID can also obtain values from a host variable to present to DB2. Lastly, the singleton SELECT, DECLARE CURSOR, DECLARE REXXSTEM and DECLARE ISPFTABLE statements may also have input SQLDAs if their WHERE clauses are parameterized by host input variables.

The next two sections illustrate execs that issue the DESCRIBE service and then reference the REXX simple and compound variables that comprise the output SQLDA (Section 12.2.1) and input SQLDA (Section 12.2.2).

12.2.1 DESCRIBE for Statements that receive data from DB2

Figure 12.1 illustrates how to DESCRIBE the columns and host variables into which query results are returned. (The numbers in the figure's right margin correspond to the numbered, annotating paragraphs that follow the exec.)

_______________________________________________________________________

say 'Does column allow nulls' sqlnulls.index (7)

say 'query column length =' sqllen.index (8)

End

where

(1) This first SQL statement declares the cursor named 'table_names'.

(2) The RLX DESCRIBE service creates (and assigns values to) a series of REXX variables which describe the query's result columns and host output variables.

(3) The REXX variable SQLD returns the number of query result columns described in the SQLDA array. (In this example, the count is 4).

(4) The remaining SQLDA variables are compound symbols referenced by a stem name and subscript value. The number of elements in these stemmed arrays is returned as a printable integer in SQLD. Its value governs the number of iterations through the DO loop.

(5) RLX returns the set of column names into the array of variables which share the stem 'SQLNAME'

(6) RLX returns the set of column datatypes into the array of variables which share the stem 'SQLTYPE'.

(7) The SQLNULLS compound variable is set to 'Y' if null values are allowed for this column. Otherwise, SQLNULLS is set to 'N'.

(8) RLX returns the set of column lengths into the array of variables which share the stem 'SQLLEN'. In the case of decimal datatypes, your exec should reference the compound variables SQLPRCSN and SQLSCALE respectively to obtain discrete values for precision and scale.

_______________________________________________________________________

Figure 12.1 Illustration of the DESCRIBE service for output SQLDA variables

12.2.2 DESCRIBE for Statements that supply data to DB2

Figure 12.2 illustrates how to DESCRIBE the input host variables associated with an INSERT statement that supply values to DB2. The REXX variables which describe input columns and their corresponding host variables are all named SQIxxx to distinguish them from the SQLxxx variables which describe output.

_______________________________________________________________________

'rlx insert into datatype_table

(integer, date, time, timestamp, float) values (:integer:iv1, :date:iv2, :time:iv3, :timestmp:iv4, :float:iv5)'

'rlx describe' rlxstnm (1) say 'Count of columns being inserted =' sqid (2) Do index = 1 to sqid) (3) say 'Name of column associated with host input variable =' sqiname.index (4) say 'Input host variable datatype =' sqitype.index (5) say 'Does column allow nulls' sqinulls.index (6) say 'Input host variable length =' sqilen.index (7) End

where

(1) The REXX variable RLXSTNM contains the internal label of the SQL INSERT statement just processed. The RLX DESCRIBE service creates (and assigns values to) a different set of REXX variables whose names start with SQI rather than SQL. The SQIxxx variables describe the columns of the INSERT statement and their corresponding host input variables.

(2) The REXX variable SQID contains the number of input columns (and input host variables) described by the input SQLDA array. (In this example, the count is 5).

(3) The printable integer value returned in SQID governs the number of iterations through the DO loop.

(4) RLX returns the set of input column names into the array of variables which share the stem 'SQINAME'

(5) RLX returns the set of input column (and host input variable) datatypes into the array of variables which share the stem 'SQITYPE'.

(6) The SQINULLS compound variable is set to 'Y' if null values are allowed for this input column. Otherwise, SQINULLS is set to 'N'.

(7) RLX returns the set of input column (and host input variable) lengths into the array of variables which share the stem 'SQILEN'. In the case of decimal datatypes, your exec should reference the compound variables SQIPRCSN and SQISCALE respectively to obtain discrete values for precision and scale.

_______________________________________________________________________

Figure 12.2 Illustration of the DESCRIBE service for input SQLDA variables