General order func='sql' means that input to the action block will be interpreted as SQL. That is, the lines of this input do not trigger the action block; instead the action block is triggered by the rows produced when executing the lines of the input as SQL against a DB2-system.
insql
Func=’sql’ is the most common interpretation in RXS, so a short form exists: Instead of writing
)action in='q1'
)& func='sql'
you may write:
)action insql='q1'
this way indicating that the content of queue ‘q1’ is to be interpreted as SQL. This short form is used in the examples below.
Example 20.1:
)text out='q1'
select account, name from myqualif.mytable
where department = :w_department
)endtext
)action insql='q1'
)& w_department='SALES'
name" has account number "account
)endaction
Output will be something like:
Peter has account number 45476
Hugo has account number 32243
Yrsa has account number 11223
- Presuming that these three employees are running the SALES department according to the DB2 table myqualif.mytable.
'Host variables' might be used in the SQL clause, thereby using RXS variables as input to the SQL call. Host-variables are prefixed by ':'. Example 20.1 above uses the host variable w_department. The value 'SALES' is assigned to this RXS variable in the general orders for the action block. Assignment may also take place in an nesting action block:
Example 20.2:
)text out='q1'
select account, name from myqualif.mytable
where department = :w_department
)endtext
)action
w_department = 1448
)action insql='q1'
name" has account number "account
)endaction
)endaction
Assigning null values to host variables is done this way: If a RXS variable is assigned the value '?', DB2 will read the value as null. This rule does not apply when calling stored procedure - see Section 20e below.
Example 20.3:
)text out='q1'
update myqualif.mytable
set account = :w_account
where department = :w_department
)endtext
)action insql='q1'
)& w_department='SALES'
)& w_account='?'
"Ok, number of rows affected is:" sqlerrd.3
)endaction
In this example 'account 'is updated to null for all rows having department = 'SALES'
Notice the variable sqlerrd.3 in DB2 which always contains the number of rows affected by an insert / update call.
Output from the DB2 call is transported into RXS namespace using their DB2 names:
· select count(*) from myqualif.mytable will not work: count don't have a name and accordingly cannot be used by RXS.
· select count(*) as w_count from myqualif.mytable do work: the counter is transported out of DB2 and into RXS in the variable w_count.
· select count(*) as "number of rows" from myqualif.mytable will not work: "number of rows" is not a valid RXS variable name
· select * from myqualif.mytable do work: DB2 replaces the * by the names of all fields in the row, and the values are transported out of DB2 and into RXS using these names.
Therefore, use of SQL clause 'select into' is illegal. Mapping of SQL variables to RXS variables is handed automatically.
Data are presented to RXS in the same format as in SPUFI or in QMF:
Besides the fields read from DB2, a SELECT call assigns values to these 'extra' variables in RXS:
|
sqlnames |
this variable holds all data names that are selected. The names are listed in the variable separated by one blank |
|
sqltypes |
this variable holds a 'type' for every data that are selected. The type is 'A' for alphanumeric and 'N' for numeric. The types are listed in the variable separated by one blank |
|
sqllengths |
this variable holds the length of every data that are selected. The lengths are listed in the variable separated by one blank (For BLOB/CLOB fields the length is set to 100000 - but the actual length of a BLOB/CLOB determines the length of the receiving RXS variable when a row is read - up to 16 MB) |
|
sqlvalues |
this variable holds all data read in the select. The data are listed in the variable separated by one blank. All data are of fixed length, according to sqllengths. Numeric data is prefixed by spaces, alphanumeric data are suffixed by spaces |
|
sqlnulls |
this variable holds a null indicator for all data read in the select. '-' indicates the data is read as null, '0' indicates the data is given a value. The indicators are listed in this variable separated by one blank |
sql
The DB2 system to be accessed is specified at the installation of RXS. The actual RXS program may deviate from this specification, and access another DB2 system. General order sql is used for this purpose. Use for instance sql='ddb2' if your installation holds a DB2 system called 'ddb2'. If you are to access more than one DB2 system during one execution of a RXS program, use qualification in your table names - you cannot use different values of sql to switch between different systems during one execution of a RXS program. Violating this rule, an error message will correct you.
See Example 20.3 above.
If DB2 update or delete or insert is used in the RXS program, these changes are committed at termination of the RXS program.
If any part of the RXS program ends in error (error related to DB2 or any other error), if the user leaves the program reversing out through the first window of the dialogue (according to Section 21a), or if the RXS program terminates in a programmed exit or return, then all DB2 changes are rolled back.
If an action block is driven by a SQL-call which uses set, update, delete or insert, then the action block will by triggered once - provided that this SQL call results in SQLCODE = 0.
Variable sqlerrd.3 contains the numbers of rows affected by an update or delete statement.
Any SQL call resulting in an SQLCODE not equal to zero and 100 will terminate the RXS program with an error message. Any SQL call giving SQLCODE = 100 (no data) will trigger a )notrigger part of the action block. If )notrigger is not coded, nothing happens: the action block is not triggered, and no error is raised.
The handling of null values is somewhat different when calling a DB2 stored-procedure, as shown i example 20.4.
Example 20.4 demonstrates access of IMS data from RXS. CICS data may be accessed using similar principles.
Example 20.4:
)text out='sqa'
CALL SYSPROC.DSNAIMS (
:IN_DSNAIMS_FUNCTION ,
:IN_DSNAIMS_2PC ,
:IN_XCF_GROUP_NAME ,
:IN_XCF_IMS_NAME ,
:IN_RACF_USERID :NULLP,
:IN_RACF_GROUPID :NULLP,
:INOUT_IMS_LTERM :NULLP,
:INOUT_IMS_MODNAME :NULLP,
:IN_IMS_TRAN_NAME :NULLP,
:IN_IMS_DATA_IN ,
:OUT_IMS_DATA_OUT ,
:IN_OTMA_TPIPE_NAME :NULLP,
:IN_OTMA_DRU_NAME :NULLP,
:IN_OTMA_USER_DATA_IN :NULLP,
:OUT_OTMA_USER_DATA_OUT,
:OUT_STATUS_MESSAGE ,
:OUT_RETURN_CODE )
)endtext
)action
IN_DSNAIMS_FUNCTION = 'SENDRECV'
IN_DSNAIMS_2PC = 'N'
IN_XCF_GROUP_NAME = LEFT('IMSOTMA',8)
IN_XCF_IMS_NAME = LEFT('IMS6',16) /* IMS system */
IN_RACF_USERID = ''
IN_RACF_GROUPID = ''
INOUT_IMS_LTERM = ''
INOUT_IMS_MODNAME = ''
IN_IMS_TRAN_NAME = ''
IN_IMS_DATA_IN = LEFT('GETACC SALES',2000)
OUT_IMS_DATA_OUT = LEFT('',32000)
IN_OTMA_TPIPE_NAME = ''
IN_OTMA_DRU_NAME = ''
IN_OTMA_USER_DATA_IN = ''
OUT_OTMA_USER_DATA_OUT = LEFT('',1022)
OUT_STATUS_MESSAGE = LEFT('',120)
OUT_RETURN_CODE = 0
NULLP = -1
)action insql='sqa'
OUT_IMS_DATA_OUT
OUT_OTMA_USER_DATA_OUT
OUT_STATUS_MESSAGE
OUT_RETURN_CODE
)endaction
)endaction
Stored procedure SYSPROC.DSNAIMS is an IBM-provided procedure giving access to IMS from DB2: Any IMS transaction code or IMS system command may be issued in IN_IMS_DATA_IN, and if the stated function or command returns output, it will be presented in OUT_IMS_DATA_OUT.
SYSPROC.DSNACICS gives similar access to CICS.
A RXS program may contain up to 99 different SQL select call, and any number of other SQL calls. All calls to SQL can be active at the same moment - action blocks using SQL may be woven into each other in any pattern. (The precise rule is: action blocks are numbered from the top of the program. First 99 action blocks may use SQL select, the rest of the action blocks may not).
Isolation level in SQL is 'Cursor stability' If isolation level is to be changed, execute the following SQL statement from inside RXS:
Set current packageset = 'DSNREXxx'
here xx is RR for 'Repeatable read', RS for 'Read stability', CS for 'Cursor stability' and UR for 'Uncommitted read'. Isolation level may be changed at any time in a RXS program, and the stated isolation level will govern all succeeding SQL call in the RXS program.
Example 20.8:
)text out='sqa'
set current packageset = 'dsnrexur'
)endtext
)action insql='sqa'
)endaction
)text out='sqb'
select name from myqualif.mytable
where department = 'SALES'
)endtext
)action insql='sqb'
name
)endaction
This program will read all names from SALES department. Reading will be done under 'uncommitted read' that is without checking locks in DB2.
RXS accepts the 'normal' way of adding comments to a SQL-call: Any text inside SQL prefixed by '--' will not be interpreted.
Example 20.9:
)action out='q1'
"select account, name from myqualif.mytable"
"where department = :w_department -- killroy was here..."
"-- and position = :w_position"
"-- and salary > 13000 "
)endaction
)action
w_department = "'1448'"
)action insql='q1'
name" has account number "account
)endaction
)endaction