IBM SC34-5764-01 Using the SQL Communications Area, Example Using SQL Statements

SQLCOLn.1
Some SELECT functions such as CURRENT SQLID, MAX, and AVGare not associated with a
particular DB2 column. To view the results you must reference column name SQLCOLn.1.
The nbegins with, and is incremented by one, for each function included in the SELECT
statement. All columns represented by SQLCOLnappear in the SQL_COLNAME compound
variable.
Using the SQL Communications Area
The fields that make up the SQL Communications Area (SQLCA) are automatically included by the
REXX/CICS DB2 when you issue SQL. The SQLCODE and SQLSTATEfields of the SQLCA contain SQL
return codes. These values are set by the REXX/CICS DB2 after each SQL statement is executed.
The SQLCA fields are maintained in separate variables rather than in a contiguous data area. The
variables that are maintained are defined as follows:
SQLCODE
The primary SQL return code.
SQLERRM
Error and warning message tokens. Adjacent tokens are separated by a byte containing X'FF'.
SQLERRP
Product code and, if there is an error, the name of the module that returned the error.
SQLERRD.n
Six variables containing diagnostic information. (The variable nis a number between 1 and 6.)
Note: The count of the number of SQL rows affected by the DELETE, INSERT,and UPDATE
command is contained in SQLERRD.3.
SQLWARN.n
Eleven variables containing warning flags. (The variable nis a number between 0 and 10.)
SQLSTATE
The alternate SQL return code.
Example Using SQL Statements
In the following example, the REXX/CICS exec prompts for the name of a department, obtains the names
and phone numbers of all members of that department from the EMPLOYEE table, and presents that
information on the screen.
/******************************************************/
/* Exec to list names and phone numbers by department */
/******************************************************/
/*--------------------------------------------------------------*/
/* Get the department number to be used in the select statement */
/*--------------------------------------------------------------*/
Say 'Enter a department number'
Pull dept
/*--------------------------------------------------------------*/
/* Retrieve all rows from the EMPLOYEE table for the department */
/*--------------------------------------------------------------*/
"EXECSQL SELECT LASTNAME, PHONENO FROM EMPLOYEE ",
"WHERE WORKDEPT = '"dept"'"
If rc <> 0 then
doSay''
Say 'Error accessing EMPLOYEE table'
Say 'RC =' rc
Say 'SQLCODE =' SQLCODE
DB2 Interface
288 CICS TS for VSE/ESA: REXX Guide