Start of change

CIUSPPUR Stored Procedure

When you use the CIUSPPUR stored procedure you can access saved dependency data directly from your application.

What is the CIUSPPUR Stored Procedure?

CIUSPPUR is a DB2® Stored Procedure that acts as a dependency data query interface that can be called from a user application with the SQL CALL statement. CIUSPPUR optimizes the size of the CICS® IA® DB2 tables when they contain a large amount of unnecessary TSQ or ENQ related dependency records that have equal prefixes.

Syntax

You can call the CIUSPPUR procedure with the following SQL CALL statement:Start of change
EXEC SQL 
CALL CIUSPPUR (ptype, preobj, plength, pcddct, prc, perrmsg ); 
End of change

Procedure parameters

There are several input parameters that help you to manage CIUSPPUR processing and several output parameters that inform about the process completion and errors, if any.

This table lists all CIUSPPUR parameters.
Table 1. CIUSPPUR parameters
Parameter name Input/Output Type Description
ptype INPUT CHAR(3) Type of object.
preobj INPUT <=CHAR(100) Prefix of OBJECT fields.
plength INPUT INTEGER Length of preobj parameter.
pcddct OUTPUT INTEGER Count of deleted rows in the CIU_CICS_DATA table.
prc OUTPUT INTEGER Return code.
perrmsg OUTPUT CHAR(300) Error message text.

CIUSPPUR INPUT parameters (ptype, preobj, plength)

The ptype parameter specifies the resource type, and has two values: TSQ or ENQ. The ptype parameter defines which cursors the CIUSPPUR program opens.

The preobj parameter is a prefix of the similar TSQ or ENQ resource names in the CIU_CICS_DATA table for which you want to make a DB2 compression.

The plength parameter specifies the prefix length. The valid values of the plength parameter are in the range from 1 - 100, specified automatically by a call from the JCL- COBOL programs.

Start of change

CIUSPPUR OUTPUT parameters (pcddct, prc, perrmsg)

The pcddct parameter contains the number of deleted rows in the CIU_CICS_DATA table with the duplicate combination of type, function, program, offset, proglen and prefix of resource.

The prc parameter contains the value of the CIUSPPUR return code. Possible prc values are listed in this table:
Table 2. prc values
Return code Description
0 Procedure has completed successfully.
8 Procedure failed because of a critical error caused by an incorrect parameter value.
12 Procedure failed because of a disaster error caused by SQL Exception conditions during SQL statement execution.
The perrmsg parameter contains message text that describes the error or warning:
  • For prc=8, it provides the following message: "Invalid type".
  • For prc=12, it provides an SQL error message for the failed SQL statement.
End of change

Returned result sets

Procedure CIUSPPUR does not return result sets because it directly modifies the contents of database tables.

CIUSPPUR invocation

An example of a COBOL program, which calls the CIUSPPUR stored procedure:

Start of change
IDENTIFICATION DIVISION.
       PROGRAM-ID.    CIUDBPUR
       EJECT
       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SOURCE-COMPUTER.         IBM3090.
       OBJECT-COMPUTER.         IBM3090.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
       DATA DIVISION.
       FILE SECTION.
       EJECT
       WORKING-STORAGE SECTION.
      ************************************************************
      *
      *   WORKING VARIABLES
      *
      ************************************************************
       01  WS-VARIABLES.
           03  WS-IN-TYPE               PIC X(3).
           03  WS-IN-PREOBJ             PIC X(100).
           03  WS-IN-PREOBJ-LEN         PIC S9(8) BINARY.
           03  WV-PTYPE                 PIC X(3).
           03  WV-PREOBJ                PIC X(100).
           03  WV-LENGTH                PIC S9(8) BINARY.
           03  WV-PCDDCT                PIC S9(8) BINARY.
           03  WV-ERRMSG                PIC X(300).
           03  WV-RC                    PIC S9(8) BINARY.
      *
       01  WS-PARSED-PARAMETERS.
           03  WS-LENGTH                PIC S9(8) COMP.
           03  WS-PARSED-COMMA-COUNT    PIC S999  COMP  VALUE 0.
           03  WS-INPUT-PARM            PIC X(100).
      * 'type='
           03  WS-PARSED-P1.
             05  WS-PARSED-P1-VALUE     PIC X(4)  VALUE SPACES.
             05  WS-PARSED-P1-LENGTH    PIC S999  COMP  VALUE 0.
             05  WS-PARSED-DELIMITER1   PIC X     VALUE SPACES.
      * 'ENQ,' or 'TSQ,'
           03  WS-PARSED-P2.
             05  WS-PARSED-P2-VALUE     PIC X(3) VALUE SPACES.
             05  WS-PARSED-P2-LENGTH    PIC S999  COMP  VALUE 0.
             05  WS-PARSED-DELIMITER2   PIC X     VALUE SPACES.
           03  WS-PARSED-P3.
      * 'prefix='
             05  WS-PARSED-P3-VALUE     PIC X(6) VALUE SPACES.
             05  WS-PARSED-P3-LENGTH    PIC S999  COMP  VALUE 0.
             05  WS-PARSED-DELIMITER3   PIC X     VALUE SPACES.
           03  WS-PARSED-P4.
      * 'XXXXXXXXXXXXXXXX..XX' - prefix
             05  WS-PARSED-P4-VALUE     PIC X(100) VALUE SPACES.
             05  WS-PARSED-P4-LENGTH    PIC S999  COMP  VALUE 0.
             05  WS-PARSED-DELIMITER4   PIC X     VALUE SPACES.
      ************************************************************
      *
      *   CONSTANT VALUES
      *
      ************************************************************
       01  WC-CONSTANTS.
           03  WC-COMMA                 PIC X      VALUE ','.
           03  WC-EQUATION              PIC X      VALUE '='.

      ************************************************************
      *
      *   MESSAGE AREA
      *
      ************************************************************

       01  WM-MESSAGES.
           03  WM-MESSAGE-0.
               05  WM-MSGNUMB PIC S9(8) BINARY VALUE 9999.
               05  WM-SUBNUMB1 PIC S9(8) BINARY VALUE 1.

       EJECT
      ************************************************************
      *
      *    DB2 COMMUNICATIONS AREA
      *
      ************************************************************

      ***  SQL COMMUNICATIONS AREA

           EXEC SQL
                INCLUDE SQLCA
           END-EXEC.

       01  WK-END                       PIC X(45) VALUE
           '*** END OF WORKING STORAGE FOR CIUDBPUR ***'.

       EJECT

       LINKAGE SECTION.
       01  PARAMETER.
           05  P-LENGTH                PIC S999 COMP.
           05  INPUT-PARMS             PIC X(116).

       PROCEDURE DIVISION USING PARAMETER.
       VALIDATE-PARAMETERS SECTION.

      *** CHECK LENGTH OF INPUT STRING
           IF ((P-LENGTH < 17) OR (P-LENGTH > LENGTH OF INPUT-PARMS))
           THEN
             MOVE 6062  TO WM-MSGNUMB
             MOVE P-LENGTH TO WS-LENGTH
             CALL 'CIUCMSG' USING WM-MSGNUMB
               WM-SUBNUMB1 INPUT-PARMS WS-LENGTH
             MOVE 8 TO RETURN-CODE
             STOP RUN
           END-IF.

      *** COUNT NUMBER OF PARAMETERS (1 comma => 2 parameters)
           INSPECT INPUT-PARMS(1:P-LENGTH)
                   TALLYING WS-PARSED-COMMA-COUNT
                   FOR ALL WC-COMMA.

      *** PARSE PARAMETERS INTO STRUCTURE
           UNSTRING INPUT-PARMS(1:P-LENGTH)
           DELIMITED BY WC-COMMA OR WC-EQUATION
           INTO WS-PARSED-P1-VALUE DELIMITER IN WS-PARSED-DELIMITER1
                COUNT IN WS-PARSED-P1-LENGTH
                WS-PARSED-P2-VALUE DELIMITER IN WS-PARSED-DELIMITER2
                COUNT IN WS-PARSED-P2-LENGTH
                WS-PARSED-P3-VALUE DELIMITER IN WS-PARSED-DELIMITER3
                COUNT IN WS-PARSED-P3-LENGTH
                WS-PARSED-P4-VALUE DELIMITER IN WS-PARSED-DELIMITER4
                COUNT IN WS-PARSED-P4-LENGTH
           ON OVERFLOW
                   MOVE 6062 TO WM-MSGNUMB
                   MOVE P-LENGTH TO WS-LENGTH
                   CALL 'CIUCMSG' USING WM-MSGNUMB
                     WM-SUBNUMB1 INPUT-PARMS WS-LENGTH
                   MOVE 8 TO RETURN-CODE
                   STOP RUN
           END-UNSTRING.

      *** CHECK PARAMETERS
           EVALUATE WS-PARSED-COMMA-COUNT
             WHEN 0
               MOVE 6062 TO WM-MSGNUMB
               MOVE P-LENGTH TO WS-LENGTH
               CALL 'CIUCMSG' USING WM-MSGNUMB
               WM-SUBNUMB1 INPUT-PARMS WS-LENGTH
               MOVE 8 TO RETURN-CODE
               STOP RUN
             WHEN 1
               IF (WS-PARSED-P1-VALUE(1:4) NOT = 'TYPE') OR
                  (WS-PARSED-DELIMITER1 NOT =  WC-EQUATION) OR
                  (WS-PARSED-P1-LENGTH NOT = 4)
               THEN
                   MOVE 6062 TO WM-MSGNUMB
                   MOVE WS-PARSED-P1-LENGTH TO WS-LENGTH
                   MOVE WS-PARSED-P1-VALUE TO WS-INPUT-PARM
                   CALL 'CIUCMSG' USING WM-MSGNUMB
                     WM-SUBNUMB1 WS-INPUT-PARM WS-LENGTH
                   MOVE 8 TO RETURN-CODE
                   STOP RUN
               END-IF
               IF ((WS-PARSED-P2-VALUE(1:3) NOT = 'TSQ') AND
                   (WS-PARSED-P2-VALUE(1:3) NOT = 'ENQ')) OR
                   (WS-PARSED-DELIMITER2 NOT = WC-COMMA ) OR
                   (WS-PARSED-P2-LENGTH NOT = 3)
               THEN
                   MOVE 6062 TO WM-MSGNUMB
                   MOVE WS-PARSED-P2-LENGTH TO WS-LENGTH
                   MOVE WS-PARSED-P2-VALUE TO WS-INPUT-PARM
                   CALL 'CIUCMSG' USING WM-MSGNUMB
                     WM-SUBNUMB1 WS-INPUT-PARM WS-LENGTH
                   MOVE 8 TO RETURN-CODE
                   STOP RUN
               END-IF
               MOVE WS-PARSED-P2-VALUE(1:3) TO WS-IN-TYPE
               IF (WS-PARSED-P3-VALUE(1:6) NOT = 'PREFIX') OR
                  (WS-PARSED-DELIMITER3 NOT =WC-EQUATION ) OR
                  (WS-PARSED-P3-LENGTH NOT = 6)
               THEN
                   MOVE 6062 TO WM-MSGNUMB
                   MOVE WS-PARSED-P3-LENGTH TO WS-LENGTH
                   MOVE WS-PARSED-P3-VALUE TO WS-INPUT-PARM
                   CALL 'CIUCMSG' USING WM-MSGNUMB
                     WM-SUBNUMB1 WS-INPUT-PARM WS-LENGTH
                   MOVE 8 TO RETURN-CODE
                   STOP RUN
               END-IF
               IF (WS-PARSED-P4-LENGTH = 0 OR WS-PARSED-P4-LENGTH >
                   LENGTH OF WS-IN-PREOBJ)
               THEN
                   MOVE 6062 TO WM-MSGNUMB
                   MOVE WS-PARSED-P4-LENGTH TO WS-LENGTH
                   MOVE WS-PARSED-P4-VALUE TO WS-INPUT-PARM
                   CALL 'CIUCMSG' USING WM-MSGNUMB
                     WM-SUBNUMB1 WS-INPUT-PARM WS-LENGTH
                   MOVE 8 TO RETURN-CODE
                   STOP RUN
               END-IF
               MOVE WS-PARSED-P4-VALUE(1:LENGTH OF WS-IN-PREOBJ)
                    TO WS-IN-PREOBJ
               MOVE WS-PARSED-P4-LENGTH TO WS-IN-PREOBJ-LEN
             WHEN OTHER
               MOVE 6062 TO WM-MSGNUMB
               MOVE P-LENGTH TO WS-LENGTH
               CALL 'CIUCMSG' USING WM-MSGNUMB
                 WM-SUBNUMB1 INPUT-PARMS WS-LENGTH
               MOVE 8 TO RETURN-CODE
               STOP RUN
           END-EVALUATE.

       EJECT

       A-MAIN SECTION.

           PERFORM B-INITIALIZE
           PERFORM C-MAIN-PROCESS
           PERFORM D-TERMINATE
           .
       A-EXIT.
           STOP RUN.
       EJECT

       B-INITIALIZE SECTION.
      ************************************************************
      *  THIS SECTION OPENS THE FILES
      ************************************************************
       B-INIT.

           MOVE SPACES          TO WV-ERRMSG
                                   
           MOVE ZEROS           TO WV-RC
                                   WV-PCDDCT
       B-EXIT.
           EXIT
           .
       EJECT

       C-MAIN-PROCESS SECTION.
      ************************************************************
       C-START.
           MOVE WS-IN-TYPE TO WV-PTYPE
           MOVE WS-IN-PREOBJ TO WV-PREOBJ
           MOVE WS-IN-PREOBJ-LEN TO WV-LENGTH

               EXEC SQL CALL CIUSPPUR(:WV-PTYPE,
                                      :WV-PREOBJ,
                                      :WV-LENGTH,
                                      :WV-PCDDCT,
                                      :WV-RC,
                                      :WV-ERRMSG)
               END-EXEC
           .
       C-EXIT.
           EXIT
           .
       EJECT

       D-TERMINATE SECTION.
      ************************************************************
      *  THIS SECTION CLOSES THE FILES AND OUTPUTS DETAILS.
      ************************************************************
       D-START.

           IF WV-RC = 0 THEN
               DISPLAY WV-ERRMSG
               DISPLAY 'DELETE REPORT FOR 'WV-PTYPE' TYPE and '
               WV-PREOBJ' prefix:'
               DISPLAY 'The number of deleted rows in the '
               DISPLAY 'CIU_CICS_DATA table =  ' WV-PCDDCT
               DISPLAY 'CIU_RESOURCE table should be updated.'
           ELSE
               MOVE WV-RC TO RETURN-CODE
               DISPLAY WV-ERRMSG
           END-IF
           .
       D-EXIT.
           EXIT
           .
       EJECT
           .                             
End of change
End of change