Embedding SQL Statements

You can use the EXECSQL command environment to process the SQL. Each SQL statement is prepared and executed dynamically using the CICS/DB2 attachment facility.

You can make each request by writing a valid SQL statement as a REXX command directed to the EXECSQL environment. The SQL statement is made up of the following elements:

Use the following syntax:

"EXECSQL statement"

     or

ADDRESS EXECSQL
"statement"
"statement"
     .
     .
     .

SQL can exist on more than one line. Each part of the statement is enclosed in quotes and a comma delimits additional statement text as follows:

ADDRESS EXECSQL
"SQL text",
"additional text",
      .
      .
      .
"final text"

The following rules apply to embedded SQL:

Receiving the Results

The EXECSQL command environment returns results in predefined REXX variables. These variables are:

RC
Each operation sets this return code. Possible values are:
n
Specifies the SQLCODE if the SQL statement resulted in an error or warning.
0
The SQL statement was processed by the EXECSQL environment. The REXX variables for the SQLCA contain the completion status of the SQL statement.
30
There was not enough memory to build the SQLDSECT variable.
31
There was not enough memory to build the SQL statement area.
32
There was not enough memory to build the SQLDA variable.
33
There was not enough memory to build the results area for the SELECT statement.
SQLCODE etc
A set of SQLCA variables are updated after SQL statements are processed. The entries of the SQLCA are described in section Using the SQL Communications Area.
SQL_COLNAME.n
Contains the name of each DB2 column whose data was returned by a SELECT statement. SQL_COLUMNS should be used as the maximum value for n.
SQL_COLTYPE.n
Contains the type of each DB2 column whose data was returned by a SELECT statement. SQL_COLUMNS should be used as the maximum value for n.
Note:
Although all data types are supported, not all are displayable. REXX functions can be used to convert the data into the format desired.

For information about the meaning of specific SQLTYPE codes found in SQL_COLTYPE, see the DB2 Server for VSE & VM SQL Reference, SC09-2671.

SQL_COLLEN.n
Contains the length of each DB2 column whose data was returned by a SELECT statement. If the data type is DECIMAL, the scale is placed after the length of the column (after one blank space). SQL_COLUMNS should be used as the maximum value for n.
SQL_COLUMNS
Contains the count of the number of columns returned.
column.n
The results of a SQL SELECT statement are stored in these REXX compound variables. The column is the name of the DB2 column. Each item contains data for one row from DB2. The count of the number of SQL rows returned is contained in column.0. The count should be used as the maximum value for n.
SQLCOLn.1
Some SELECT functions such as CURRENT SQLID, MAX, and AVG are not associated with a particular DB2 column. To view the results you must reference column name SQLCOLn.1.

The n begins with, and is incremented by one, for each function included in the SELECT statement. All columns represented by SQLCOLn appear 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 SQLSTATE fields 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 n is 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 n is 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
     do
       Say ' '
       Say 'Error accessing EMPLOYEE table'
       Say 'RC      =' rc
       Say 'SQLCODE =' SQLCODE
       Exit rc
     end

/*---------------------------------------*/
/* Display the members of the department */
/*---------------------------------------*/
   Say 'Here are the members of Department' dept
   Do n = 1 to lastname.0
     Say lastname.n phoneno.n
   End

   Exit