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:
- SQL keywords
- Pre-declared identifiers
- Literal values.
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:
- You can pass the following SQL directly to the EXECSQL command
environment:
- ALTER
- CREATE
- COMMENT ON
- DELETE
- DROP
- EXPLAIN
- GRANT
- INSERT
- LABEL ON
- LOCK
- REVOKE
- SELECT
- SET CURRENT SQLID
- UPDATE.
- You cannot use the following SQL:
- BEGIN DECLARE SECTION
- CLOSE
- COMMIT
- CONNECT
- DECLARE CURSOR
- DECLARE STATEMENT
- DECLARE TABLE
- DESCRIBE
- END DECLARE SECTION
- EXECUTE
- EXECUTE IMMEDIATE
- FETCH
- INCLUDE
- OPEN
- PREPARE
- ROLLBACK
- SET CURRENT PACKAGESET
- SET HOST VARIABLE
- WHENEVER.
- Host variables are not allowed within the SQL. Instead, you can
use REXX variables to pass input data to the EXECSQL environment.
The REXX variables are not embedded within quotes. The output from
the EXECSQL environment is provided in REXX predefined variables (see
section Receiving the Results).
- When you code a SQL SELECT statement, you cannot use the INTO
clause. Instead, the REXX/CICS DB2 returns the requested items in
compound variables with stem names equal to the DB2 column names.
- The default number of rows returned for a SELECT statement is
250. If you need more or less rows, you can set the REXX variable
SQL_SELECT_MAX before issuing the SELECT statement.
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.
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.
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