Special host-language programming considerations are discussed in the following pages. Included is information on embedding SQL statements, language restrictions, and supported data types for host variables.
Because REXX is an interpreted language, no precompiler, compiler, or linker is used. Instead, three DB2 APIs are used to create DB2 applications in REXX. Use these APIs to access different elements of DB2.
It is possible that tokens within statements or commands that are passed to the SQLEXEC, SQLDBS, and SQLDB2 routines could correspond to REXX variables. In this case, the REXX interpreter substitutes the variable's value before calling SQLEXEC, SQLDBS, or SQLDB2.
To avoid this situation, enclose statement strings in quotes (' ' or " "). If you do not use quotes, any conflicting variable names are resolved by the REXX interpreter, instead of being passed to the SQLEXEC, SQLDBS or SQLDB2 routines.
Compound SQL is not supported in REXX/SQL.
REXX/SQL stored procedures are supported on the OS/2, Windows 95, and Windows NT operating systems, but not on AIX.
Before using any of the DB2 APIs or issuing SQL statements in an application, you must register the SQLDBS, SQLDB2 and SQLEXEC routines. This notifies the REXX interpreter of the REXX/SQL entry points. The method you use for registering varies slightly between the OS/2 and AIX platforms. The following examples show the correct syntax for registering each routine:
Sample registration on OS/2 or Windows
/* ------------ Register SQLDBS with REXX -------------------------*/
If Rxfuncquery('SQLDBS') <> 0 then
rcy = Rxfuncadd('SQLDBS','DB2AR','SQLDBS')
If rcy \= 0 then
do
say 'SQLDBS was not successfully added to the REXX environment'
signal rxx_exit
end
/* ------------ Register SQLDB2 with REXX -------------------------*/
If Rxfuncquery('SQLDB2') <> 0 then
rcy = Rxfuncadd('SQLDB2','DB2AR','SQLDB2')
If rcy \= 0 then
do
say 'SQLDB2 was not successfully added to the REXX environment'
signal rxx_exit
end
/* ----------------- Register SQLEXEC with REXX --------------------*/
If Rxfuncquery('SQLEXEC') <> 0 then
rcy = Rxfuncadd('SQLEXEC','DB2AR','SQLEXEC')
If rcy \= 0 then
do
say 'SQLEXEC was not successfully added to the REXX environment'
signal rxx_exit
end
Sample registration on AIX
/* ------------ Register SQLDBS, SQLDB2 and SQLEXEC with REXX --------*/
rcy = SysAddFuncPkg("db2rexx")
If rcy \= 0 then
do
say 'db2rexx was not successfully added to the REXX environment'
signal rxx_exit
end
On OS/2, the RxFuncAdd commands need to be executed only once for all sessions.
On AIX, the SysAddFuncPkg should be executed in every REXX/SQL application.
Details on the RXfuncadd and SysAddFuncPkg APIs are available in the REXX documentation for OS/2 and AIX, respectively.
Use the SQLEXEC routine to process all SQL statements. The character string arguments for the SQLEXEC routine are made up of the following elements:
Make each request by passing a valid SQL statement to the SQLEXEC routine. Use the following syntax:
CALL SQLEXEC 'statement'
SQL statements can be continued onto more than one line. Each part of the statement should be enclosed in single quotes, and a comma must delimit additional statement text as follows:
CALL SQLEXEC 'SQL text',
'additional text',
.
.
.
'final text'
The following is an example of embedding an SQL statement in REXX:
statement = "UPDATE STAFF SET JOB = 'Clerk' WHERE JOB = 'Mgr'"
CALL SQLEXEC 'EXECUTE IMMEDIATE :statement'
IF ( SQLCA.SQLCODE < 0) THEN
SAY 'Update Error: SQLCODE = ' SQLCA.SQLCODE
In this example, the SQLCODE field of the SQLCA structure is checked to determine whether the update was successful.
The following rules apply to embedded SQL statements:
Other SQL statements must be processed dynamically using the EXECUTE IMMEDIATE, or PREPARE and EXECUTE statements in conjunction with the SQLEXEC routine.
The cursor name identifier is used for DECLARE, OPEN, FETCH, and CLOSE statements. It identifies the cursor used in the SQL request.
The statement name identifier is used with the DECLARE, DESCRIBE, PREPARE, and EXECUTE statements.
The pre-declared identifiers must be used for cursor and statement names. Other names are not allowed.
Host variables are REXX language variables that are referenced within SQL statements. They allow an application to pass input data to DB2 and receive output data from DB2. REXX applications do not need to declare host variables, except for LOB locators and LOB file reference variables. Host variable data types and sizes are determined at run time when the variables are referenced. Apply the following rules when naming and using host variables.
Any properly named REXX variable can be used as a host variable. A variable name can be up to 64 characters long. Do not end the name with a period. A host variable name can consist of alphabetic characters, numerics, and the characters @, _, !, ., ?, and $.
The REXX interpreter examines every string without quotes in a procedure. If the string represents a variable in the current REXX variable pool, REXX replaces the string with the current value. The following is an example of how you can reference a host variable in REXX:
CALL SQLEXEC 'FETCH C1 INTO :cm'
SAY 'Commission = ' cm
To ensure that a character string is not converted to a numeric data type, enclose the string with single quotes as in the following example:
VAR = '100'
REXX sets the variable VAR to the 3-byte character string 100. If single quotes are to be included as part of the string, follow this example:
VAR = "'100'"
When inserting numeric data into a CHARACTER field, the REXX interpreter treats numeric data as integer data, thus you must concatenate numeric strings explicitly and surround them with single quotes.
An indicator variable data type in REXX is a number without a decimal point. Following is an example of an indicator variable in REXX using the INDICATOR keyword.
CALL SQLEXEC 'FETCH C1 INTO :cm INDICATOR :cmind'
IF ( cmind < 0 )
SAY 'Commission is NULL'
In the above example, cmind is examined for a negative value. If it is not negative, the application can use the returned value of cm. If it is negative, the fetched value is NULL and cm should not be used. The database manager does not change the value of the host variable in this case.
SQLEXEC, SQLDBS and SQLDB2 set predefined REXX variables as a result of certain operations. These variables are:
| Note: | The values -8 through -18 are returned only by the GET ERROR MESSAGE API. |
When you fetch a LOB column into a REXX host variable, it will be stored as a simple (that is, uncounted) string. This is handled in the same manner as all character-based SQL types (such as CHAR, VARCHAR, GRAPHIC, LONG, and so on). On input, if the size of the contents of your host variable is larger than 32K, or if it meets other criteria set out below, it will be assigned the appropriate LOB type.
In REXX SQL, LOB types are determined from the string content of your host
variable as follows:
| Host variable string content | Resulting LOB type |
|---|---|
:hv1='ordinary quoted string longer than 32K ...' | CLOB |
:hv2="'string with embedded delimiting quotes ", "longer than 32K...'" | CLOB |
:hv3="G'DBCS string with embedded delimiting single ", "quotes, beginning with G, longer than 32K...'" | DBCLOB |
:hv4="BIN'string with embedded delimiting single ", "quotes, beginning with BIN, any length...'" | BLOB |
Figure 48 shows the syntax for declaring LOB locator host variables in REXX.
Figure 48. Syntax for LOB Locator Host Variables in REXX
<-,----------------<
>>--DECLARE----:--variable-name----LANGUAGE TYPE--.-BLOB--.----LOCATOR----><
|-CLOB |
'-DBCLOB-
|
You must declare LOB locator host variables in your application. When REXX/SQL encounters these declarations, it treats the declared host variables as locators for the remainder of the program. Locator values are stored in REXX variables in an internal format.
Example:
CALL SQLEXEC 'DECLARE :hv1, :hv2 LANGUAGE TYPE CLOB LOCATOR'
Data represented by LOB locators returned from the engine can be freed in REXX/SQL using the FREE LOCATOR statement which has the following format:
Figure 49. Syntax for FREE LOCATOR statement in REXX
<-,----------------< >>--FREE--LOCATOR----:--variable-name-------------->< |
Example:
CALL SQLEXEC 'FREE LOCATOR :hv1, :hv2'
You must declare LOB file reference host variables in your application. When REXX/SQL encounters these declarations, it treats the declared host variables as LOB file references for the remainder of the program.
Figure 50 shows the syntax for declaring LOB file reference host variables in REXX.
Figure 50. Syntax for LOB File Reference Variables in REXX
<-,----------------<
>>--DECLARE----:--variable-name----LANGUAGE TYPE--.-BLOB---.---------><
|-CLOB---|
'-DBCLOB-'
|
Example:
CALL SQLEXEC 'DECLARE :hv3, :hv4 LANGUAGE TYPE CLOB FILE'
File reference variables in REXX contain three fields. For the above example they are:
For FILE_OPTIONS, the application sets the following keywords:
| Note: | A file reference host variable is a compound variable in REXX, thus you must set values for the NAME, NAME_LENGTH and FILE_OPTIONS fields in addition to declaring them. |
On OS/2 it may be necessary to explicitly clear REXX SQL LOB locator and file reference host variable declarations as they remain in effect after your application program ends. This is because the application process does not exit until the session in which it is run is closed. If REXX SQL LOB declarations are not cleared, they may interfere with other applications that are running in the same session after a LOB application has been executed.
The syntax to clear the declaration is:
CALL SQLEXEC "CLEAR SQL VARIABLE DECLARATIONS"
You should code this statement at the end of LOB applications. Note that you can code it anywhere as a precautionary measure to clear declarations which might have been left by previous applications (for example, at the beginning of a REXX SQL application).
Certain predefined REXX data types correspond to DB2 column types. Table 19 shows how SQLEXEC and SQLDBS interpret REXX variables in order
to convert their contents to DB2 data types.
Table 19. SQL Column Types Mapped to REXX Declarations
| SQL Column Type1 | REXX Data Type | SQL Column Type Description | ||
|---|---|---|---|---|
SMALLINT (500 or 501) | A number without a decimal point ranging from -32 768 to 32 767 | 16-bit signed integer | ||
INTEGER (496 or 497) | A number without a decimal point ranging from -2 147 483 648 to 2 147 483 647 | 32-bit signed integer | ||
REAL2 (480 or 481) | A number in scientific notation ranging from -3.40282346 x 1038 to 3.40282346 x 1038 | Single-precision floating point | ||
DOUBLE3 (480 or 481) | A number in scientific notation ranging from -1.79769313 x 10308 to 1.79769313 x 10308 | Double-precision floating point | ||
DECIMAL(p,s) (484 or 485) | A number with a decimal point | Packed decimal | ||
CHAR(n) (452 or 453) | A string with a leading and trailing quote ('), which has length
n after removing the two quote marks
A string of length n with any non-numeric characters, other than leading and trailing blanks or the E in scientific notation | Fixed-length character string of length n where n is from 1 to 254 | ||
VARCHAR(n) (448 or 449) | Equivalent to CHAR(n) | Variable-length character string of length n, where n ranges from 1 to 4000 | ||
LONG VARCHAR (456 or 457) | Equivalent to CHAR(n) | Variable-length character string of length n, where nranges from 1 to 32 700 | ||
CLOB(n) (408 or 409) | Equivalent to CHAR(n) | Large object variable-length character string of length n, where n ranges from 1 to 2 147 483 647 | ||
CLOB locator variable4 (964 or 965) | DECLARE :var_name LANGUAGE TYPE CLOB LOCATOR | Identifies CLOB entities residing on the server | ||
CLOB file reference variable4 (808 or 809) | DECLARE :var_name LANGUAGE TYPE CLOB FILE | Descriptor for file containing CLOB data | ||
BLOB(n) (404 or 405) | A string with a leading and trailing apostrophe, preceded by BIN, containing n characters after removing the preceding BIN and the two apostrophes. | Large object variable-length binary string of length n, where n ranges from 1 to 2 147 483 647 | ||
BLOB locator variable4 (960 or 961) | DECLARE :var_name LANGUAGE TYPE BLOB LOCATOR | Identifies BLOB entities on the server | ||
BLOB file reference variable4 (804 or 805) | DECLARE :var_name LANGUAGE TYPE BLOB FILE | Descriptor for the file containing BLOB data | ||
DATE (384 or 385) | Equivalent to CHAR(10) | 10-byte character string | ||
TIME (388 or 389) | Equivalent to CHAR(8) | 8-byte character string | ||
TIMESTAMP (392 or 393) | Equivalent to CHAR(26) | 26-byte character string | ||
| ||||
GRAPHIC(n) (468 or 469) | A string with a leading and trailing apostrophe preceded by a G or N, containing n DBCS characters after removing the preceding character and the two apostrophes | Fixed-length graphic string of length n, where n is from 1 to 127 | ||
VARGRAPHIC(n) (464 or 465) | Equivalent to GRAPHIC(n) | Variable-length graphic string of length n, where n ranges from 1 to 2000 | ||
LONG VARGRAPHIC (472 or 473) | Equivalent to GRAPHIC(n) | Long variable-length graphic string of length n, where n ranges from 1 to 16 350 | ||
DBCLOB(n) (412 or 413) | Equivalent to GRAPHIC(n) | Large object variable-length graphic string of length n, where n ranges from 1 to 1 073 741 823 | ||
DBCLOB locator variable4 (968 or 969) | DECLARE :var_name LANGUAGE TYPE DBCLOB LOCATOR | Identifies DBCLOB entities residing on the server | ||
DBCLOB file reference variable4 (812 or 813) | DECLARE :var_name LANGUAGE TYPE DBCLOB FILE | Descriptor for file containing DBCLOB data | ||
When a cursor is declared in REXX, the cursor is associated with a query. The query is associated with a statement name assigned in the PREPARE statement. Any referenced host variables are represented by parameter markers. The following example shows a DECLARE statement associated with a dynamic SELECT statement.
prep_string = "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?"
CALL SQLEXEC 'PREPARE S1 FROM :prep_string';
CALL SQLEXEC 'DECLARE C1 CURSOR FOR S1';
CALL SQLEXEC 'OPEN C1 USING :schema_name';