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

You can call the CIUSPAFF procedure with the following SQL CALL statement:
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.

The following table lists all CIUSPAFF parameters.
Table 1. CIUSPAFF parameters
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:

When you have defined the qtype value, you can configure the qarg1 and qarg2 parameters. The following table provides a list of matching values for the selected query type.
Table 2. Available qarg1 and qarg2 values
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
Table 3 describes qarg1 and qarg2 value types and lengths.
Table 3. qarg1 and qarg2 values in detail
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)

The rc parameter contains value of the CIUSPAFF return code. Possible rc values are listed in the following table.
Table 4. rc values
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.
errmsg contains message text that describes the error or warning:
  • 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.
The sqlcode parameter values depend on the return code and can be found in the following table.
Table 5. sqlcode values
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

The CIUSPAFF stored procedure returns result sets. The number and structure of the result sets is dependent on the input parameters values. A result set is a set of rows that are associated with a cursor opened in the stored procedure and returned to caller program. A result set is effectively a table. You can access the data that is returned in result set by running an SQL ASSOCIATE LOCATORS, followed by an SQL ALLOCATE cursor, and then the SQL FETCH loop as shown in the COBOL example for CIUSPAFF stored procedure The following subsections describe result sets that are returned by the CIUSPAFF procedure.
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:
qtype = 'BLD'	
qarg1 = applid
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.
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:
qtype = 'RGN'					
qarg1 = applid					
qarg2 = affinity group ID mask
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.
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:
qtype = 'PGM'					
qarg1 = program name			
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 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:
qtype = 'GRP'					
qarg1 = transaction ID			
qarg2 = affinity group ID mask
If you want to build affinity groups for all CICS TS regions, set qarg1 to %%%%%%%%.
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:
qtype = 'TRN'					
qarg1 = applid					
qarg2 = affinity group ID
If you want to build affinity groups for all CICS TS regions set qarg1 to %%%%%%%%.
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

Below you can see an example of CISPAFF invocation from COBOL program:
       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                                                         
           .

Concept Concept

Feedback


Timestamp icon Last updated: Friday, 8 February 2013


http://pic.dhe.ibm.com/infocenter/cicsts/v5r1/topic//ciuugsp04.html