CIUSPAFF Stored Procedure
With the help of this CICS® IA External Interface, you can access saved affinity data directly from your application.
What is the CIUSPAFF Stored Procedure?
CIUSPAFF is a DB2® Stored Procedure that acts as affinity data update and query interface. It can be called from a user application with the SQL CALL statement to get the collected affinity data from CICS IA interdependency database.
Syntax
EXEC SQL
CALL CIUSPAFF (qtype, qarg1, qarg2, rc, sqlcode, errmsg);
Procedure parameters
There are several input parameters that manage CIUSPAFF processing and several output parameters that inform about the process completion and errors, if any.
Parameter name | input/output | Type | Description |
---|---|---|---|
qtype | INPUT | CHAR(3) | Query type |
qarg1 | INPUT | VARCHAR(8) | First query argument |
qarg2 | INPUT | CHAR(10) | Second query argument |
rc | OUTPUT | INTEGER | Return code |
sqlcode | OUTPUT | INTEGER | SQLCODE |
errmsg | OUTPUT | VARCHAR(300) | Error message text |
CIUSPAFF INPUT parameters (qtype, qarg1, qarg2)
The qtype parameter defines which cursors the CIUSPAFF program opens on either CIU_AFF_GRP_DATA or CIU_AFF_CMD_DATA tables and return them as result sets, except for the BLD query. There are five qtype values, or query types, you can use: BLD, RGN, PGM, TRN, and GRP.
qarg1 and qarg2 are query arguments that you must specify for each query type.
When you configure the qtype parameter, consider the following points:
- The BLD query is the only query interface that does not provide any information. Instead, it updates the tables CIU_AFF_GRP_DATA and CIU_AFF_CMD_DATA for all affinity group types and the supplied region, if it is listed in CIU_REGION_INFO table. The CICS region APPLID must be specified in qarg1. If wildcard mask is specified instead of the APPLID value, the CIU_AFF_GRP_DATA and CIU_AFF_CMD_DATA tables for all regions that are listed in the CIU_REGION_INFO table are processed. No result set is returned.
- The RGN query fetches information about affinity groups of the specified type for the supplied region from the CIU_AFF_GRP_DATA table. The CICS region APPLID must be specified in the qarg1 parameter. The affinity group type must be specified in qarg2 as affinity group ID MASK.
- The PGM query fetches information about affinity groups of the specified type that contain the specified program. The PROGRAM NAME must be specified in qarg1. The affinity group type must be specified in qarg2 as affinity group ID MASK.
- The TRN query fetches information about affinity groups of the specified type that contain the specified transaction. The TRANSACTION ID must be specified in qarg1. The affinity group type must be specified in qarg2 as affinity group ID MASK.
- The GRP query fetches information about all commands of the specified affinity group ID in the supplied region from the CIU_AFF_CMD_DATA table.
qtype value | qarg1 value | qarg2 value |
---|---|---|
BLD | APPLID | N/A |
RGN | APPLID | Group ID MASK |
PGM | PROGRAM NAME | Group ID MASK |
TRN | TRANSACTION ID | Group ID MASK |
GRP | APPLID | Group ID |
Query argument value | Length | Description |
---|---|---|
APPLID | 8 | CICS TS region APPLID. |
PROGRAM NAME | ≤8 | CICS application program name, wildcard characters % allowed. |
TRANSACTION ID | 4 | CICS application transaction ID, wildcard characters % allowed. |
Group ID | 10 | Affinity group ID. |
Group ID MASK | 10 | Group mask format is ‘PP.%%%%%%%’, where PP is affinity group prefix (one of the following CW, CA, EQ, GM, GU, G4, G6, LD, LF, LU, RW, TS, CO, DI, EN, EX, IN, PE, RE, WA, CR, CS, UN) and ‘%’ is a wildcard character. |
CIUSPAFF OUTPUT parameters (rc, sqlcode, errmsg)
Return code | Description |
---|---|
0 | CIUSPAFF procedure that completed successfully. |
4 | CIUSPAFF procedure that completed successfully, but one or more SQL warning conditions were received during SQL statements execution. |
8 | CIUSPAFF procedure failed because of a critical error caused by incorrect parameter values. |
12 | CIUSPAFF procedure failed because of a disaster error caused by SQL Exception conditions during SQL statement execution. |
- For rc=4, it provides the last SQL warning message out of all SQL warnings that occurred during CIUSPAFF run time.
- For rc=8, it provides the invalid parameter
value that caused the error. The incorrect parameter can also be found
in SQLSTATE (SQLCA):
- 99150: Invalid qtype value specified
- 99155: Invalid qarg1 value specified
- 99160: Invalid qarg2 value specified
- For rc =12, it provides SQL error message for the failed SQL statement.
Return code | sqlcode value |
---|---|
0 | 0 |
4 | Shows SQLCODE for the last statement that caused SQL warning condition. |
8 | 0 |
12 | Shows SQLCODE of the failed SQL statement. |
Returned result sets
- Build affinity groups tables
- To build affinity groups tables, CIU_AFF_GRP_DATA and CIU_AFF_CMD_DATA,
you must set the input parameters as follows:
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.qtype = 'BLD' qarg1 = applid
- The qarg2 input parameter is ignored and can be set to null value.
- This call returns no result sets, it processes data from the CIU_AFF_EVENTS table and creates new, or updates the existing affinity groups data in the CIU_AFF_GRP_DATA and CIU_AFF_CMD_DATA tables.
- List affinity groups of the specified type for the specified CICS TS region
- To receive a list of existing affinity groups of the specified
type for the specified CICS TS
region, you must set the input parameters as follows:
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.qtype = 'RGN' qarg1 = applid qarg2 = affinity group ID mask
- This call returns one result set with APPLID, TRANGROUP, AFFTYPE, GROUPTYPE, AFFINITY, AFFWORSENED, LIFETIME, LIFEWORSENED, RECOVERY, RESOURCE, RESLENGTH, and TYPE columns from the CIU_AFF_GRP_DATA table.
- List affinity groups of the specified type that contain the specified program
- To receive a list of existing affinity groups of the specified
type, and which contains a specified program, you must set the input
parameters as follows:
This call returns one result set with APPLID, TRANGROUP, AFFTYPE, GROUPTYPE, AFFINITY, AFFWORSENED, LIFETIME, LIFEWORSENED, RECOVERY, RESOURCE, and TYPE columns from the CIU_AFF_GRP_DATA.qtype = 'PGM' qarg1 = program name qarg2 = affinity group ID mask
- List affinity groups of the specified type that contains the specified transaction
- To receive a list of existing affinity groups of the specified
type, and which contains the specified transaction ID, you must set
the input parameters as follows:
If you want to build affinity groups for all CICS TS regions, set qarg1 to %%%%%%%%.qtype = 'GRP' qarg1 = transaction ID qarg2 = affinity group ID mask
- This call returns one result set with APPLID, TRANGROUP, AFFTYPE, GROUPTYPE, AFFINITY, AFFWORSENED, LIFETIME, LIFEWORSENED, RECOVERY, RESOURCE, and TYPE columns from the CIU_AFF_GRP_DATA.
- List commands for the specified affinity group in the specified region
- To receive a list of commands for the existing affinity group,
you must set the input parameters as follows:
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.qtype = 'TRN' qarg1 = applid qarg2 = affinity group ID
- This call returns one result set with APPLID, TRANSID, PROGRAM, OFFSET, COMMAND, RESTYPE, AFFGROUP, TERMINAL, BTS, LINK3270, and USAGE columns from the CIU_AFF_CMD_DATA.
CIUSPAFF invocation
IDENTIFICATION DIVISION.
PROGRAM-ID. CALLSP
…
DATA DIVISION.
…
01 WS-SP-QTYPE PIC X(3).
01 WS-SP-QARG1 PIC X(8).
01 WS-SP-QARG2 PIC X(10).
01 WS-SP-ERRMSG PIC X(300).
01 WS-SP-RETCODE PIC S9(9) BINARY.
01 WS-SP-SQLCODE PIC S9(9) BINARY.
…
PROCEDURE DIVISION.
…
C01-CALL-CIUSPAFF SECTION.
C01-START.
MOVE ‘RGN’ TO WS-SP-QTYPE
MOVE ‘IYDZZ420’ TO WS-SP-QARG1
MOVE ‘GU.%%%%%%%’ TO WS-SP-QARG2
MOVE SPACES TO WS-SP-ERRMSG
MOVE ZEROS TO WS-SP-RETCODE
WS-SP-SQLCODE
EXEC SQL
CALL CIUSPAFF(:WS-SP-QTYPE,
:WS-SP-QARG1,
:WS-SP-QARG2,
:WS-SP-RETCODE,
:WS-SP-SQLCODE,
:WS-SP-ERRMSG)
END-EXEC
*************************************************************
* Check if SQL CALL statement completed successfully. *
* If not – perform corresponding action. *
*************************************************************
IF SQLCODE NOT = 0 AND
SQLCODE NOT = +466 THEN
…
END-IF
*************************************************************
* Check if CIUSPAFF completed successfully. *
* Act depending on CIUSPAFF RETCODE value. *
*************************************************************
IF WS-SP-RETCODE NOT = 0 THEN
EVALUATE WS-SP-RETCODE
WHEN ‘04’
* Process CIUSPAFF warning *
…
WHEN ‘08’
* Process CIUSPAFF critical error *
…
WHEN ‘12’
* Process CIUSPAFF disaster error *
…
END-EVALUATE
*************************************************************
* Check if CIUSPAFF returns the result set and take *
* corresponding action. *
*************************************************************
IF SQLCODE = +466 THEN
…
END-IF
…
C01-EXIT.
EXIT
.