After completing this session, you will be able to:
Identify how to connect DB2 and REXX.
Describe how to embed SQL Statements in REXX.
Explain how data is handled in DB2-REXX Support.
Explain about REXX Stored Procedures.
Define how to run DB2-REXX Application.
DB2 REXX Language Support includes the following application programming interfaces.
CONNECT
Connects the REXX procedure to a DB2 subsystem. You must execute CONNECT before you can execute SQL statements.
EXEC SQL
Executes SQL Statements in REXX Procedure.
DISCONNECT
Disconnects the REXX Procedure from a DB2 sub system. You should execute DISCONNECT to release the resources held by DB2.
DB2-REXX Language supports all SQL Statements that DB2 for OS/390 supports, except the following statements.
BEGIN DECLARE SECTION
DECLARE STATEMENT
END DECLARE SECTION
INCLUDE
SELECT INTO
WHENEVER
Each SQL statement in a REXX procedure must begin with EXECSQL. One of the following items must follow EXECSQL.
An SQL statement enclosed in single or double quotation marks.
A REXX Variable that contains an SQL statement. The REXX Variable must not be preceded by a colon.
You cannot execute SELECT, INSERT, UPDATE, DELETE statement that contains host variables. Instead you must execute PREPARE on that statement, with parameter markers substituted for the host variables and then use the host variables in an EXECUTE, OPEN or FETCH statement.
You cannot include REXX Comments (/* ….. */) or SQL comments (--) within SQL statements. However you can include REXX comments anywhere in the procedure.
You can use any valid REXX name that does not end with a period as a host variable. However, host variable names should not begin with 'SQL', 'RDI', 'DSN', 'RXSQL', or 'QRW'. Variable names can be at most 64 bytes.
A REXX null value and an SQL null value are different. The REXX language has a null string (a string of length 0) and a null clause (a clause that contains only blanks and comments). The SQL null value is a special value that is distinct from all non null values and denotes the absence of a value. Assigning a REXX null value to a DB2 column does not make the column value null.
Using cursors and statement names: In REXX SQL applications, you must use a predefined set of names for cursors or prepared statements. The following names are valid for cursors and prepared statements in REXX SQL applications.
c1 to c100 |
Cursor names for DECLARE CURSOR, OPEN, CLOSE, and FETCH statements. Use c1 to c50 for cursors that are defined without the WITH HOLD option. Use c51 to c100 for cursors that are defined with the WITH HOLD option. All cursors are defined with the WITH RETURN option, so any cursor name can be used to return result sets from a REXX stored procedure.
c101 to c200 |
Cursor names for ALLOCATE, DESCRIBE, FETCH, and CLOSE statements that are used to retrieve result sets in a program that calls a stored procedure.
s1 to s100 |
Prepared statement names for DECLARE STATEMENT, PREPARE. DESCRIBE, and EXECUTE statements.
Use only the predefined names for cursors and statements. Do not use any of the predefined names for host variables.
You do not declare host variables in REXX. When you need a new variable, you use it in a REXX command. When you use a REXX variable as a host variable in an SQL statement, you must precede the variable with a colon.
All REXX data is string data. Therefore, when a REXX procedure assigns input data to a table column, DB2 converts the data from a string type to the table column type. When a REXX procedure assigns column data to an output variable, DB2 converts the data from the column type to a string type. To ensure that DB2 REXX Language Support does not interpret character literals as graphic or numeric literals, precede and follow character literals with a double quotation mark, followed by a single quotation mark, followed by another double quotation mark ("'").
Enclosing the string in apostrophes is not adequate because REXX removes the apostrophes when it assigns a literal to a variable. For example, suppose that you want to pass the value in host variable stringvar to DB2. The value that you want to pass is the string '100'. The first thing that you need to do is to assign the string to the host variable.
You might write a REXX command like this:
stringvar = '100' |
After the command executes, stringvar contains the characters 100 (without the apostrophes). DB2 REXX Language Support then passes the numeric value 100 to DB2, which is not what you intended.
However, suppose that you write the command like this:
stringvar = "'"100"'" |
In this case, REXX assigns the string '100' to stringvar, including the single quotation marks. DB2 REXX Language Support then passes the string '100' to DB2, which is the desired result.
When you retrieve a null value from a column, DB2 puts a negative value in an indicator variable to indicate that the data in the corresponding host variable is null. When you pass a null value to DB2, you assign a negative value to an indicator variable to indicate that the corresponding host variable has a null value.
The way that you use indicator variables for input host variables in REXX rocedures is slightly different from the way that you use indicator variables in other languages.
When you want to pass a null value to a DB2 column, in addition to putting a negative value in an indicator variable, you also need to put a valid value in the corresponding host variable. For example, to set a value of WORKDEPT in table EMP to null, use statements like these:When you retrieve a null value from a column, DB2 puts a negative value in an indicator variable to indicate that the data in the corresponding host variable is null. When you pass a null value to DB2, you assign a negative value to an indicator variable to indicate that the corresponding host variable has a null value.
The way that you use indicator variables for input host variables in REXX procedures is slightly different from the way that you use indicator variables in other languages. When you want to pass a null value to a DB2 column, in addition to putting a negative value in an indicator variable, you also need to put a valid value in the corresponding host variable.
For example, to set a value of WORKDEPT in table EMP to null, use statements like these:
SQLSTMT="UPDATE EMP",
"SET WORKDEPT=??"
HVWORKDEPT='000'
INDWORKDEPT=-1
"EXECSQL PREPARE S100 FROM :SQLSTMT"
"EXECSQL EXECUTE S100 USING :HVWORKDEPT :INDWORKDEPT" |
After you retrieve data from a column that can contain null values, you should always check the indicator variable that corresponds to the output host variable for that column. If the indicator variable value is negative, the retrieved value is null, so you can disregard the value in the host variable.
A REXX stored procedure is much like any other REXX procedure and follows the same rules as stored procedures in other languages. It receives input parameters, executes REXX commands, optionally executes SQL statements, and returns at most one output parameter. A REXX stored procedure is different from other REXX procedures in the following ways.
A REXX stored procedure cannot execute the ADDRESS DSNREXX CONNECT and ADDRESS DSNREXX DISCONNECT commands. When you execute SQL statements in your stored procedure, DB2 establishes the connection for you.
As in other stored procedures, you cannot include the following statements in a REXX stored procedure:
CALL
COMMIT
CONNECT
RELEASE
SET CONNECTION
SET CURRENT SQLID
You run DB2 REXX procedures under TSO. You do not precompile, compile, link-edit or bind DB2 REXX procedures before you run them.
In a batch environment, you might use statements like these to invoke procedure REXXPROG:
//RUNREXX EXEC PGM=IKJEFT01,DYNAMNBR=20 //SYSEXEC DD DISP=SHR,DSN=SYSADM.REXX.EXEC //SYSTSPRT DD SYSOUT=* //SYSTSIN DD * %REXXPROG parameters /* |
The SYSEXEC data set contains your REXX application, and the SYSTSIN data set contains the command that you use to invoke the application.
There is a DB2 Table 'DEPT' which contains the names of different departments in a company and its location.
Write a REXX Code to get the name of all departments, Insert new departments, Update the data for existing departments and delete the unwanted departments.
/****************************** REXX **********************************/
ADDRESS TSO
"SUBCOM DSNREXX"
DRC = RC
IF DRC <> 0 THEN
DO
S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
END
ADDRESS DSNREXX "CONNECT" DSN1
RC5 = RC
IF RC5 \= 0 THEN
DO
SAY "ERROR WHILE CONNECTING TO THE DB2"
EXIT
END
/************* READING RECORDS FROM TABLE *****************************/
ADDRESS DSNREXX "EXECSQL CLOSE C1"
SQLSTMT1 = "SELECT * FROM DEPT"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
ADDRESS DSNREXX "EXECSQL OPEN C1"
DO UNTIL(SQLCODE <> 0)
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE = 0
THEN DO
DO I = 1 TO OUTSQLDA.SQLD
SAY 'OUTSQLDA.I.SQLDATA ' OUTSQLDA.I.SQLDATA
END
END
ELSE IF SQLCODE = 100
THEN SAY 'RECORD NOT FOUND'
END
/*************** UPDATING RECORDS IN TABLE ****************************/
ADDRESS DSNREXX "EXECSQL CLOSE C1"
SQLSTMT1 = "UPDATE DEPT SET LOC = 'CHN' WHERE DNAME = 'DW'"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
ADDRESS DSNREXX "EXECSQL OPEN C1"
DO UNTIL(SQLCODE = 0)
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE = 0
THEN SAY 'RECORD UPDATION SUCCESSFUL'
ELSE
DO
SAY 'RECORD UPDATION FAILED'
EXIT
END
END
/*************** DELETING RECORDS FROM TABLE *************************/
ADDRESS DSNREXX "EXECSQL CLOSE C1"
SQLSTMT1 = "DELETE FROM DEPT WHERE(DNAME = 'SAP')"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
ADDRESS DSNREXX "EXECSQL OPEN C1"
DO UNTIL(SQLCODE = 0)
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE = 0
THEN SAY 'RECORD DELETION SUCCESSFUL'
ELSE
DO
SAY 'RECORD DELETION FAILED'
EXIT
END
END
/*************** INSERT RECORDS TO TABLE ****************************/
ADDRESS DSNREXX "EXECSQL CLOSE C1"
SQLSTMT1 = "INSERT INTO DEPT VALUES ('SAP','BLR')"
ADDRESS DSNREXX "EXECSQL DECLARE C1 CURSOR FOR S1"
ADDRESS DSNREXX "EXECSQL PREPARE S1 FROM :SQLSTMT1"
ADDRESS DSNREXX "EXECSQL DESCRIBE S1 INTO :OUTSQLDA"
ADDRESS DSNREXX "EXECSQL OPEN C1"
DO UNTIL(SQLCODE = 0)
ADDRESS DSNREXX "EXECSQL FETCH C1 USING DESCRIPTOR :OUTSQLDA"
IF SQLCODE = 0
THEN SAY 'RECORD INSERTION SUCCESSFUL'
ELSE
DO
SAY 'RECORD INSERTION FAILED'
EXIT
END
END
EXIT |
DB2 connection is established using CONNECT command.
The SQL statements that must be executed are specified. The cursor declared on the SQL statement is fetched and the SQL operation is performed.
Once the desired operations are performed, DISCONNECT command disconnects the established DB2 connection
As a part of coding standards, ensure that proper comments are given wherever necessary. Also, the indentation must be taken care of for better readability.
You can embed SQL statements in REXX, like in other programming languages.
DB2 converts the string data in REXX to the corresponding table column data type and also vice-versa.
In REXX Stored procedure, CONNECT and DISCONNECT statements are not required to interact with DB2. DB2 establishes the connection, in order to execute the SQL statements in a REXX stored procedure.
You can run DB2-REXX Application in TSO as well as Batch.
What command connects to REXX procedure to DB2 subsystem?
DB2-REXX language supports these SQL statements?
SELECT INTO
INCLUDE
Both a and b
Neither a not b
If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!