Start of change

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 invoke 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 enable you to manage CIUSPAFF processing and several output parameters that inform about the process completion and errors, if any.

The table below lists all CIUSPAFF parameters. You can find more information on each of them later in this section.
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 should open 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 you must specify for each query type.

When configuring qtype, consider the following:

Upon defining the qtype value, you should also configure the qarg1 and qarg2 parameters. Table 2 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, 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 table below.
Table 4. rc values
Return code Description
0 CIUSPAFF procedure completed successfully.
4 CIUSPAFF procedure completed successfully, but one or more SQL warning conditions were received during SQL statements execution.
8 CIUSPAFF procedure failed due to a critical error caused by incorrect parameter values.
12 CIUSPAFF procedure failed due to 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 qarg1value 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 table below.
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.

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                                                         
           .
End of change