View figure.
You code your DB2 RXSQL requests as part of your REXX program. When DB2 RXSQL receives the first RXSQL or EXECSQL request, it loads RXSQL as a nucleus extension.
You should surround your RXSQL requests with paired single or double quotes so the REXX interpreter, recognizing a literal string, does not treat any of the words as REXX variables and resolve them before passing the string on to RXSQL. The REXX interpreter strips off the paired quotes before passing the statement to RXSQL.
This means that your program can have two different types of quotes included as part of your RXSQL request. For example:
"RXSQL PREPARE stmt UPDATE table SET col2=100 WHERE col1='salary' "
would be passed to RXSQL as:
RXSQL PREPARE stmt UPDATE table SET col2=100 WHERE col1='salary'
If your RXSQL request is too long for one line, you can end the line with a comma and start the remainder of the request on a new line. The REXX interpreter recognizes the comma as a continuation character and replaces it with a blank after concatenating the two strings. The comma must be outside a paired set of quotes as illustrated in the following diagram.
Figure 34. RXSQL Request Processing
View figure.
This illustrates what happens to a RXSQL request as it gets passed from your program to RXSQL, and from RXSQL to the database manager. When you want the REXX interpreter to resolve a variable you leave it outside the quotes like id, and when you want RXSQL to resolve the variable you code it inside quotes and, in most cases, precede it with a colon like :password.
Each RXSQL request is composed of many elements.
When the REXX interpreter is finished with the RXSQL request, it is passed to DB2 RXSQL. DB2 RXSQL then validates the elements and either executes the request or passes it to the database manager.
DB2 RXSQL
| Note: | For keyword scanning, DB2 RXSQL uses rules consistent with folding lowercase characters to uppercase using code page 037. See the DB2 Server for VM System Administration manual. |
A string of characters is a sequence of bytes that RXSQL passes directly to the database manager. RXSQL will not change any part of the string of characters before passing it to the database manager.
An ordinary identifier is an uppercase letter followed by zero or more characters, each of which is an uppercase letter, a digit, or the underscore character. RXSQL passes ordinary identifiers directly to the database manager for validation. A complete description can be found in the DB2 Server for VSE & VM SQL Reference manual.
A parameter marker is a question mark (?) that is used wherever a variable_name could be used in an SQL statement being processed by a PREPARE statement. RXSQL passes values for the parameter markers when the prepared statement is executed by a CALL, EXECUTE, OPEN or PUT statement.
Placeholders are periods (.) used in place of rexx_host_variables when retrieving data from the database manager. These are easy to code, but if performance is a concern you should restrict your select list to the columns of data that your REXX program needs. Placeholders may not have associated indicator_variables or variable_qualifiers.
The term REXX variable name is used to distinguish between the name of a variable and its value. This distinction is important to RXSQL users because the REXX interpreter processes each RXSQL request before it is passed to RXSQL, and RXSQL often expects a REXX variable name, not a REXX variable value as input. To ensure that the REXX interpreter does not resolve a variable before it is passed to RXSQL, it must be enclosed in paired single or double quotes. The REXX interpreter then removes the quotes and passes the REXX variable name to RXSQL without resolving it. RXSQL retrieves the value of the REXX variable before passing the request to the database manager.
The valid status of a REXX variable name, as checked by the REXX SYMBOL function, can be LIT or VAR. It will be LIT if the REXX variable name has never been assigned a value, or if the REXX variable name has been dropped by the REXX DROP instruction. The variable status may be used to determine if a column has a value or is NULL. Note that there is a difference between a variable having a value consisting of an empty string and the variable representing a NULL value.
There are two ways that the REXX interpreter will recognize a DB2 RXSQL request. One is when a command begins with the keyword RXSQL, and the other is when a command begins with the keyword EXECSQL. Error handling and the syntax rules vary with the way that a DB2 RXSQL request was invoked. This allows DB2 RXSQL to support two different rules of syntax; one to support programs developed for previous versions of RXSQL (RXSQL invocation) and the other to support enhanced error handling (EXECSQL invocation). The terms RXSQL invocation and EXECSQL invocation are used to distinguish the different processing rules. The different processing rules for the two invocations are discussed throughout this chapter.
You are strongly encouraged to use EXECSQL invocation when writing new applications to take advantage of the enhanced error handling.
When you are using RXSQL in an RXSQL or EXECSQL subcommand environment, the keyword EXECSQL or RXSQL always overrides the current RXSQL or EXECSQL subcommand environment. For more information see Appendix I, RXSQL Subcommand Environment.
You invoke the DB2 RXSQL interface by issuing DB2 RXSQL requests. Each request begins with either keyword EXECSQL or RXSQL, and is followed by a command or statement and then one or more clauses. The clauses may contain metavariables which represent variable values specified when the request is coded.
The following syntax diagram illustrates this:
(1)
>>-+-EXECSQL-+---------+-Command---+---------------------------->
'-RXSQL---' '-Statement-'
.-.--------------------------------.
V |
>--------+---------+--+--------------+---+---------------------><
'-KEYWORD-' '-metavariable-'
Notes:
|
| Note: | X'00' is allowed by other host languages supported by the database manager. |
A statement_name is a string of characters that represents a prepared SQL statement.
Names can have a maximum length of 18 characters. They must begin with a letter (A-Z or a-z) or a symbol ($, #, or @). The remaining characters can be letters (A-Z or a-z), symbols ($, #, or @), numbers (0-9), or underscores (_). Leading and trailing blanks are removed. DB2 RXSQL does not allow DBCS characters. DB2 RXSQL folds lowercase characters into uppercase. This means that you cannot have two cursors with names abc and ABC, for example.
Notes:
The cursor_name or statement_name cannot be the same as any other cursor_name or statement_name defined in the program.
| Note: | This is more restrictive than for other host languages supported by the database manager which allow statement_names and cursor_names to have the same value. |
| package_name |
|---|
>>-authorization_name--.---package_id-------------------------->< |
| Note: | X'00' is allowed by other host languages supported by the database manager. |
| rexx_host_stem_name on EXECSQL invocation |
|---|
>>-+-+---+--+------------+--+---------------------------------------------+-+->
| '-:-' '-main_stem.-' '-+-----------+--+----+--+-----------------+--' |
| '-INDICATOR-' '- :-' '-indicator_stem.-' |
'-+---+--+------------+--+---+--+-----------------+----------------------'
'-:-' '-main_stem.-' '-:-' '-indicator_stem.-'
>--------------------------------------------------------------><
|
| rexx_host_stem_name on RXSQL invocation |
|---|
>>-+-+---+--+------------+------------------------------+------><
| '-:-' '-main_stem.-' |
'-+---+--+------------+--+---+--+-----------------+--'
'-:-' '-main_stem.-' '-:-' '-indicator_stem.-'
|
DB2 RXSQL assigns to the main_stem variables the values of the row in the result table, and to the indicator_stem variables the indicator values passed from the database manager. DB2 RXSQL sets the first element of the main_stem and the indicator_stem variables (stem.0) to the number of columns returned from the database manager, and the remaining variables (stem.n) to the nth column value and nth indicator value.
For example, suppose you have a REXX variable where:
DB2 RXSQL sets the variables as follows:
ABC.D.0 = 4 (number of columns returned) ABC.D.1 = first column value ABC.D.2 = second column value ABC.D.3 = third column value ABC.D.4 = fourth column value
| rexx_host_variable_list on EXECSQL invocation |
|---|
.-,---------------------. V | >>----rexx_host_variable---+----------------------------------->< |
| rexx_host_variable_list on RXSQL invocation |
|---|
.-,---------------------. V | >>----rexx_host_variable---+----------------------------------->< |
A rexx_host_variable is structured as follows:
| rexx_host_variable on EXECSQL invocation |
|---|
>>-+-+---+--main_variable--+-----------------------------------+-+->
| '-:-' | .-INDICATOR-. | |
| '-+-----------+-:indicator_variable-' |
'-+---+--+---------------+--+---+--+--------------------+-----'
'-:-' '-main_variable-' '-:-' '-indicator_variable-'
>----+----------------------+----------------------------------><
'-(variable_qualifier)-'
|
| rexx_host_variable on RXSQL invocation |
|---|
>>-+-+---+--+---------------+---------------------------------+->
| '-:-' '-main_variable-' |
'-+---+--+---------------+--+---+--+--------------------+--'
'-:-' '-main_variable-' '-:-' '-indicator_variable-'
>-----+----------------------+---------------------------------><
'-(variable_qualifier)-'
|
See the next section for a complete discussion of REXX Host Variables.
Your program uses rexx_host_variables for passing data between the database and your program. The nth rexx_host_variable corresponds to the nth column referenced.
There are differences in the way rexx_host_variables are coded that depend on the way the RXSQL request is invoked.
On EXECSQL invocation,
'EXECSQL FETCH cursor INTO :name:indicator1, :department:indicator2'
'EXECSQL FETCH cursor INTO :name :indicator1, :department :indicator2'
'EXECSQL FETCH cursor INTO :name:indicator1,:department:indicator2(SMALLINT)'
'EXECSQL FETCH cursor INTO :name',
'INDICATOR :indicator1(CHAR(25)),:department:indicator2(SMALLINT)'
On RXSQL invocation,
'RXSQL FETCH cursor INTO :name:indicator1 :department:indicator2'
'RXSQL FETCH cursor INTO :name:indicator1(CHAR(25))',
':department:indicator2(SMALLINT)'
'RXSQL FETCH cursor INTO :name:indicator1(CHAR(25)),',
':department:indicator2(SMALLINT)'
A main_variable is a fully qualified REXX variable name with an optional preceding colon. RXSQL fetches the value of this variable on input or assigns a value to this variable on output.
DB2 RXSQL must infer the data type of your input values, when inserting data and variable qualifiers have not been specified, because REXX variables do not have assigned data types. The rules that RXSQL follows to determine the data type of your input values are outlined under Inferring the Data Type. Variable qualifiers are discussed under Variable_qualifier.
Rexx_host_variables in a list are set in order starting with the first column result. You should be careful in your naming of the main_variables and indicator_variables in the list of rexx_host_variables. If a main_variable is used within a compound variable name elsewhere in another main_variable, unexpected value assignments will result. The following example illustrates this.
n=5 'RXSQL FETCH row INTO :first.col.n,:col,:third.col.n '
| REXX Variable Name | REXX Variable Name after resolved by the REXX interpreter | Variable Value |
|---|---|---|
| first.col.n | FIRST.COL.5 | fun |
| col | COL | for |
| third.col.n | THIRD.FOR.5 | all |
Now, if the program tries to reference first.col.n, REXX translates all references to it as FIRST.for.5. This variable is undefined and will return a literal value equal to the variable name. The value fun that was assigned to the first column is not accessible unless the variable COL is dropped.
An indicator_variable is a REXX variable name that is preceded by a colon. Your program assigns a value to the indicator on input, and RXSQL assigns the value on output. The value of the indicator_variable must be zero or a positive or negative integer.
When used on input, an indicator_variable is used to
indicate whether the main_variable is null.
Indicator_variables are used as described in the following
table.
Figure 35. Indicator Variable Values On Input
| Invocation | Main_variable Status | Indicator_variable Value | Main_variable Value Passed to the Database Manager | ||
|---|---|---|---|---|---|
| EXECSQL | VAR | >= 0 | not null | ||
| EXECSQL | VAR | < 0 | null | ||
| EXECSQL | VAR | indicator_variable not used | not null | ||
| EXECSQL | LIT | >= 0 | generates an RXSQL error condition | ||
| EXECSQL | LIT | < 0 | null | ||
| EXECSQL | LIT | indicator_variable not used | generates an RXSQL error condition | ||
| RXSQL | VAR | >= 0 | not null | ||
| RXSQL | VAR | < 0 | null | ||
| RXSQL | VAR | indicator_variable not used | not null | ||
| RXSQL | LIT | >= 0 | generates an RXSQL error condition | ||
| RXSQL | LIT | < 0 | null | ||
| RXSQL | LIT | indicator_variable not used | null | ||
| |||||
On output, RXSQL assigns to the indicator_variable the value that the database manager passes to DB2 RXSQL. Refer to the DB2 Server for VSE & VM Application Programming manual for information on possible indicator_variable values on output.
If the column value is NULL, then indicator_variable is
assigned a value according to the following table.
Figure 36. Indicator Variable Value On NULL Output
| Invocation | Main variable | Indicator variable | Value of Indicator variable after FETCH | Value of Main variable value after FETCH |
|---|---|---|---|---|
| EXECSQL | present | present | < 0 | unchanged |
| EXECSQL | present | absent | generates an RXSQL error condition | unchanged |
| RXSQL | present | present | < 0 | unchanged |
| RXSQL | present | absent | not applicable | undefined |
As the table illustrates, you must provide an indicator_variable when using the EXECSQL invocation if
You can use variable_qualifiers on input and output to specify how you want the data stored in the to be sent to the database manager or how you want to retrieve data into the main_variables. When the main_variable has a data type supporting a Coded Character Set Identifier (CCSID), the CCSID can be specified following the data type. There must be at least one blank between the data type and the CCSID keyword.
If no data type is given for a main_variable then RXSQL will infer the data type according to the rules in Inferring the Data Type. If no CCSID is given for a main_variable, then the CCSID defined on the application server is used to perform the operation.
The data type is specified first, and can be any of the following:
| Note: | ZDECIMAL(m,n) is also allowed when using the DRDA protocol. |
Your program inserts DATETIME data by specifying a CHAR(n) data type, where n is the length of the DATETIME data string.
The CCSID is specified after the data type as CCSID n. To specify FOR BIT DATA, set the CCSID to 65535.
On input, the variable_qualifier specifies the data type to which you want DB2 RXSQL to convert the input data before passing it to the database manager. Input data is stored in the main_variable.
The following is an example of a statement that uses an input variable_qualifier:
OPEN cursor1 USING :v1:i1(VARCHAR(10) CCSID 500), :v3(INTEGER)
For numeric data, when the value of the main_variable is too large for, or cannot be converted to the data type specified in the variable_qualifier, DB2 RXSQL returns an error indicating that the data would be corrupted if it was passed to the database manager. However, if low order truncation for decimal data occurs because data after the decimal point was lost, then DB2 RXSQL passes the truncated data to the database manager and returns a warning condition.
For character and graphic data, either fixed or variable length, if the variable's data length is larger than specified by the variable qualifier, the data will be truncated on the right. For fixed length character or graphic data, if the variable's data length is shorter than specified by the variable qualifier, then the data is padded with blanks on the right.
On output you can specify the data type and CCSID to which you want a column value being retrieved from the database to be converted. In this case, the database manager does the conversion before the data is passed to RXSQL. For example, you may want data from a column defined as VARCHAR(20) CCSID 290 to be returned to your program as CHAR(26) CCSID 500. If the value returned to this program is less than 26 characters, the database manager will pad the end of the string with blanks and convert it to CCSID 500.
The following is an example showing how to code a variable_qualifier on output:
'EXECSQL FETCH cursor2 INTO:v1:i1(CHAR(26) CCSID 500),:v2(CCSID 500),:v3'
When your program passes input data from REXX to the database manager in a CALL, EXECUTE, OPEN or PUT statement, RXSQL must assign a data type and determine whether the input value is NULL. RXSQL assigns a data type to your input data according to the information you provide. If you use variable_qualifiers, then RXSQL will assign the data type you specified to the input data before passing it to the database manager. If you do not use variable_qualifiers, then RXSQL must infer the data type. Using variable_qualifiers is strongly recommended.
When you are using Extended Dynamic SQL you also have the option to provide attributes of input data when you are preparing the SQL statements. The attributes are used by the database manager to help build the access path which the optimizer uses when executing the prepared statements.
All of the ways to specify input data are discussed in the following section.
RXSQL recognizes NULL input values in two ways: a negative indicator_variable, or a main_variable with an unassigned value. The REXX DROP instruction makes a variable unassigned.
When your DB2 RXSQL request begins with the keyword EXECSQL, you must use indicator_variables to insert NULL values.
You can use variable_qualifiers to specify the data type and CCSID of your input data. See REXX Host Variable. When you use variable_qualifiers, DB2 RXSQL will change your input data to the format requested before passing it to the database manager.
You can override the CCSID of any column for which CCSIDs are allowed by specifying the CCSID after the data type in the variable_qualifier.
You can determine the CCSID of a column using a DESCRIBE request for a prepared SELECT statement. The CCSID of each column is returned in a DB2 RXSQL variable.
After RXSQL retrieves the value of the main_variable from REXX, the following rules are used when assigning a data type if a variable_qualifier is not used:
A string of characters enclosed in paired single quotation marks is a character string. RXSQL removes the leading and trailing quotation marks before passing the character string to the database. A string of characters enclosed in paired double quotation marks is passed with the quotation marks. DB2 RXSQL assumes an empty string is a character string, not a NULL value. A string which does not follow the rules for numeric or graphic data will be assigned a character data type.
To guarantee that RXSQL converts a string to a character data type, ensure that the first and last characters of the string value are single quotation marks. Simply assigning a value in single or double quotes does not work. For example,
stringvar='100'
will cause REXX to set the REXX variable stringvar to the string of characters 100 (without the single quotes) before passing it to RXSQL. RXSQL will assign a data type of integer to the string, and pass it to the database manager without the single quotes. On the other hand, if stringvar=100, then,
stringvar="'"stringvar"'"
will cause REXX to set the REXX variable stringvar to the string of characters '100' (with single quotes). RXSQL assumes that the string has a data type of character because it is enclosed with leading and trailing single quotes, and passes the string to the database manager without the single quotes.
Your input values will be assigned the current default CCSID. You must use variable_qualifiers if you want to specify the CCSID of your input data.
Graphic data is recognized by RXSQL in two different ways, depending on how the request was invoked.
a trailing shift-in character, and contains an even number of bytes is assigned a graphic data type. RXSQL strips off the leading shift-out and the trailing shift-in characters before passing the string to the database as VARGRAPHIC. To ensure that MIXED data is passed to the database manager as VARCHAR, it must be enclosed in single quotes.
Figure 37 expands the above descriptions. Shift-out and
shift-in characters are represented by the symbols < and >,
respectively.
Figure 37. Input Data Inferred as Character or Graphic Data Types
| Data | Invoked by | Data modification | Data sent to the database manager | ||
|---|---|---|---|---|---|
| <...even...> | EXECSQL | VARCHAR | |||
| RXSQL | strip <> | VARGRAPHIC | |||
| G'<...even...>' | EXECSQL | strip G'< >' | VARGRAPHIC | ||
| RXSQL | VARCHAR | ||||
| '.......' | EXECSQL | strip ' ' | VARCHAR | ||
| RXSQL | strip ' ' | VARCHAR | |||
| <...odd....> | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| G'<...odd>' | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| <...>... | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| G'<...>'... | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| ...<...> | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| ...G'<...>' | EXECSQL | VARCHAR | |||
| RXSQL | VARCHAR | ||||
| |||||
Numeric data can be passed to the database manager as one of the three following data types:
If a number is not within the allowed numeric SQL limits supported by the database manager, then RXSQL will pass it to the database as a character string. See the DB2 Server for VSE & VM SQL Reference manual for information on SQL limits.
When your program is inserting data into a DATE, TIME or TIMESTAMP column, then RXSQL passes your data directly as a character string.
When your program issues an Extended PREPARE statement, it can use attributes_variables to provide information about parameter markers in the prepared statement if the data types of the values that will replace the parameter markers are known. However, this does not prevent DB2 RXSQL from assigning a data type when the prepared statements are executed by the Extended CALL, Extended EXECUTE, OPEN and PUT statements as discussed in the previous sections.
DB2 RXSQL returns the results of requests in three ways:
When your program issues a FETCH, Extended CALL, or Extended EXECUTE statement to retrieve data from the database, DB2 RXSQL passes output to the program as requested, using either rexx_host_variables or a rexx_host_stem_name.
When your program uses rexx_host_variables to receive output data, DB2 RXSQL sets the main_variable and the indicator_variable, if supplied, for each column of data requested. The main_variable is set to the value of the output data according to the variable_qualifier setting, if provided, and the indicator_variable is set to the value that the database manager passes to RXSQL.
For EXECSQL invocation, if your program does not provide indicator_variables, DB2 RXSQL may return an error condition for FETCH, Extended CALL or Extended EXECUTE.
You can test for a NULL value returned by checking the indicator_variable as follows:
If column4ind < 0 then column4 = '?'
This example sets the column4 variable to ? if the result is NULL as indicated by its corresponding indicator_variable column4ind.
For more information, see Indicator_variable.
For RXSQL invocation, if your program does not provide indicator_variables for FETCH, Extended CALL or Extended EXECUTE, then RXSQL will drop the main_variable when the column value is NULL.
Do not test for '' (empty string) as if it were a NULL. ' is a valid non-NULL value. You can use the REXX function SYMBOL to test whether a variable is undefined as illustrated in the following example:
If SYMBOL('column5') <> 'VAR'
then column5 = '?'
This example sets the column5 variable to ? if the result is NULL. DB2 RXSQL has done the equivalent of the following REXX instruction:
drop column5
Your program can also use a rexx_host_stem_name to receive the output data and associated indicator values. The same considerations apply to rexx_host_stem_variables as for rexx_host_variables with respect to NULL values returned.
For EXECSQL invocation, if you do not provide an indicator_stem and a NULL value is returned, DB2 RXSQL returns an error condition.
For RXSQL invocation, if you do not provide an indicator_stem and a NULL value is returned for column n, the corresponding rexx_host_stem.n variable is dropped.
RXSQL also returns data to your program in predefined REXX variables. Your program should not assign values to these variables, as RXSQL will reset what you assigned. The following table outlines the variables that RXSQL sets, and which RXSQL request they are assigned on.
The SQLCA variables are set after every RXSQL statement which results in a database manager call. DB2 RXSQL passes the value of these variables directly from the database manager. See the DB2 Server for VSE & VM SQL Reference and the DB2 Server for VM Messages and Codes manuals for a more detailed explanation of these variables.
SQLSTATE does not replace SQLCODE. The SQLCA contains both an SQLSTATE value and an SQLCODE value.
warning flags. SQLWARN is a single string of characters that can have embedded blanks.
Each flag has a distinct meaning based on its position. Check the first character; then, based on its value you may want to check the remaining fields. Descriptions of the characters are given in the DB2 Server for VSE & VM SQL Reference manual.
The way that RXSQL reports error conditions to your program depends on how the RXSQL request was invoked. The following sections summarize the meaning of the return codes for each type of invocation.
Each request invoked with EXECSQL sets a return code that can be tested in the REXX EXEC as variable rc.
The following settings can occur:
For example, after a FETCH statement, a normal warning is SQLCODE=100 SQLSTATE=02000, which signifies that there are no more rows in the result table.
Each request invoked with RXSQL sets a return code that can be tested in the REXX EXEC as variable rc.
The following settings can occur:
For example, after a FETCH statement, a normal warning is SQLCODE=100 SQLSTATE=02000, which signifies that there are no more rows in the result table.
The RXSQL invocation does not support the detection of DB2 RXSQL warnings.
See Appendix E, "RXSQL Return Codes and Messages" for more information on CMS, DB2 Server for VM, and DB2 RXSQL return codes.