
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

EXEC SQL
CALL CIUSPPUR (ptype, preobj, plength, pcddct, prc, perrmsg );

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.
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.

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.
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. |
- For prc=8, it provides the following message: "Invalid type".
- For prc=12, it provides an SQL error message for the failed SQL statement.

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:

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
.

