Identifying data to delete

The CICS® IA data that you collect can change, which can lead to redundant records being stored in the database. You can delete data from the database in a number of ways. You can use SQL queries to help you to delete the unwanted data from your database tables, which improves performance.

The CICS IA data is loaded into the DB2® tables from the VSAM files. You must ensure that you clear the VSAM files and the DB2 tables. For development and test regions, it is prudent to empty the files and tables at CICS IA startup.

The supplied a sample job, hlq.SCIUSAMP.CICS(CIUPRUNE), is used to delete data from the DB2 tables. You can use this job to run sample supplied SQL that is provided in hlq.SCIUSQL.CICS. These sample SQL jobs begin with the prefix CIUP; for example, CIUPCICS deletes data from the CIU_CICS_DATA table. The sample jobs use the LAST_RUN date. The query first displays the records before the selected LAST_RUN date:

Figure 1. Records before the selected LAST_RUN date.
--
-- Show rows that are older then specified timestamp
SELECT APPLID, TRANSID, PROGRAM, FUNCTION, TYPE, OBJECT
FROM CIU_CICS_DATA READONLY
WHERE LAST_RUN<='2012-01-01-00.00.00.000000';
-- Uncomment this statement when you want to delete rows
--DELETE FROM CIU_CICS_DATA
--
-- WHERE LAST_RUN<='2012-01-01-00.00.00.000000';
COMMIT;

An alternative method involves deleting records based on the program version. You do this by querying on the program, the program length, and the first used timestamp. Program and program length are part of the unique key that is used the data is stored in the CICS IA dependency tables.

When a program changes, the program length probably changes too. CICS IA uses this information as a crude method for a programming version. The EXEC commands for a changed program contain a FIRST_RUN timestamp, which is used to identify the latest program. You can then delete all of the commands that are reported for previous programs.

If you want to use the method that involves deleting records based on the program version, you must first examine the data to look for programs with different program lengths. In the following figure, program DB900001 has two different program lengths. From the FIRST_RUN timestamp, you can see that program length 2180 corresponds to the latest program.

Figure 2. Query to show program versions and FIRST_RUN timestamps.
SELECT DISTINCT APPLID, PROGRAM, PROGLEN,
MAX(CAST(FIRST_RUN AS CHAR(26)))
FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
GROUP BY APPLID, PROGRAM , PROGLEN;
---------+---------+---------+---------+---------+---------+---------+--
APPLID PROGRAM PROGLEN
---------+---------+---------+---------+---------+---------+---------+--
IYCYZC37 DB900001 000021E8 2007-10-30-11.31.33.000000
IYCYZC37 DB900001 00002180 2007-11-22-11.23.57.000000
IYCYZC37 DB910001 000032A0 2007-11-22-11.23.57.000000
IYCYZC37 DB910001 000033E0 2007-10-30-11.31.33.000000
IYCYZC37 DB930001 00002000 2007-11-22-11.23.57.000000
IYCYZC37 DB930001 00002050 2007-10-30-11.31.33.000000
IYCYZC37 DB940001 00002698 2007-11-22-11.23.57.000000
IYCYZC37 DB940001 00002720 2007-10-30-11.31.33.000000
IYCYZC37 EMSTESTA 00002110 2008-04-03-12.38.48.000000
IYCYZC37 EMSTESTB 00001500 2008-04-03-12.38.48.000000

You can now find all of the latest programs in region IYCYZC37. The query in the following figure shows the latest FIRST_RUN timestamp. In this query, the output is concatenated so it can be used later as an SQL subquery.

Figure 3. Query to show all distinct programs by latest FIRST_RUN timestamp.
SELECT DISTINCT APPLID||PROGRAM||MAX(CAST(FIRST_RUN AS CHAR(26)))
FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
GROUP BY APPLID, PROGRAM ;
---------+---------+---------+---------+---------+---------+------
---------+---------+---------+---------+---------+---------+------
IYCYZC37DB9000012007-11-22-11.23.57.000000
IYCYZC37DB9100012007-11-22-11.23.57.000000
IYCYZC37DB9300012007-11-22-11.23.57.000000
IYCYZC37DB9400012007-11-22-11.23.57.000000
IYCYZC37EMSTESTA2008-04-03-12.38.48.000000
IYCYZC37EMSTESTB2008-04-03-12.38.48.000000
IYCYZC37EMSTESTS2008-04-03-12.38.45.000000
IYCYZC37FC0100012007-11-19-15.09.18.000000
IYCYZC37FC0200012007-11-19-15.09.17.000000
You can now get the program length for the latest programs. The query in the following figure shows the program and program length for the most recently used programs. The output is concatenated so it can be used later as an SQL subquery.
Figure 4. Query to show latest program and program lengths.
SELECT DISTINCT APPLID||PROGRAM||PROGLEN
FROM CIU_CICS_DATA , (
SELECT DISTINCT APPLID AS A, PROGRAM AS P,
MAX(CAST(FIRST_RUN AS CHAR(26))) AS S
FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
GROUP BY APPLID, PROGRAM) X
WHERE CAST(FIRST_RUN AS CHAR(26)) = X.S;
---------+---------+---------+---------+---------
---------+---------+---------+---------+---------
IYCYZC37DB90000100002180
IYCYZC37DB910001000032A0
IYCYZC37DB93000100002000
IYCYZC37DB94000100002698
IYCYZC37EMSTESTA00002110
IYCYZC37EMSTESTB00001500
IYCYZC37EMSTESTS00003380
IYCYZC37FC01000100000AD0
IYCYZC37FC02000100001558

You can now display the records for all of the commands that are collected from earlier versions of the programs, which are displayed in the query in Figure 5. You can now choose to delete these records, as shown in Figure 6.

Figure 5. Query showing commands to be deleted.
SELECT * FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
AND APPLID||PROGRAM||PROGLEN NOT IN
(SELECT DISTINCT APPLID||PROGRAM||PROGLEN
FROM CIU_CICS_DATA , (
SELECT DISTINCT APPLID AS A, PROGRAM AS P,
MAX(CAST(FIRST_RUN AS CHAR(26))) AS S
FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
GROUP BY APPLID, PROGRAM) X
WHERE CAST(FIRST_RUN AS CHAR(26)) = X.S)
---------+---------+---------+---------+---------+---------+---------+--
APPLID HOMESYSID TRANSID PROGRAM FUNCTION TYPE OBJECT
---------+---------+---------+---------+---------+---------+---------+--
IYCYZC37 TS07 DB90 DB900001 RECEIVE MAP S1
IYCYZC37 TS07 DB90 DB900001 RECV MAP MAPSET CBMS01
IYCYZC37 TS07 DB90 DB900001 SEND MAP S1
IYCYZC37 TS07 DB90 DB900001 SEND MAP S1
IYCYZC37 TS07 DB90 DB900001 SEND MAP MAPSET CBMS01
IYCYZC37 TS07 DB90 DB900001 SEND MAP MAPSET CBMS01
IYCYZC37 TS07 DB91 DB910001 RECEIVE MAP S2

You must repeat this delete action for the other tables: CIU_DB2_DATA, CIU_IMS_DATA, and CIU_MQ_DATA.

Figure 6. Query to delete commands from the CICS table for old commands.
---------+---------+---------+---------+---------+---------+---------+
DELETE FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
AND APPLID||PROGRAM||PROGLEN NOT IN
(SELECT DISTINCT APPLID||PROGRAM||PROGLEN
FROM CIU_CICS_DATA , (
SELECT DISTINCT APPLID AS A, PROGRAM AS P,
MAX(CAST(FIRST_RUN AS CHAR(26))) AS S
FROM CIU_CICS_DATA
WHERE APPLID='IYCYZC37'
GROUP BY APPLID, PROGRAM) X
WHERE CAST(FIRST_RUN AS CHAR(26)) = X.S)
---------+---------+---------+---------+---------+---------+---------+
DSNE615I NUMBER OF ROWS AFFECTED IS 24
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
---------+---------+---------+---------+---------+---------+---------+

You can also use these queries as the basis for writing your own delete functions.

When the data from these tables is deleted, you must reload the CIU_RESOURCE table that is used by the CICS IA plug-in by running the sample job SCIUSAMP.CICS(CIURESLD).