Compare a flat file with DB2 table.

[Kannan P]

Hi,

I have ps file
Record 1
Record 2

Db2 table
Record 1
Record 2
Record 3
My req : I need to find record 3 record and delete it

[zum13]

Hello.

I'm going to assume that there is some sort of unique key available in both the input file and table which would mean you could write a program to do standard collation processing to identify the rows you need to remove. The file would need to be in the correct key sequence order as would the input cursor. You can then collate the two together and remove any rows that are returned on the cursor but not on the input file (see the "WHERE CURRENT OF cursor-name" clause of the "DELETE" statement).

[Kannan P]

Thanks. Can we not do it using JCL only with some utility?

[zum13]

OK, there are a couple of options.

You can load the key data from your input file into another table in the database and use the "NOT EXISTS" clause in a predicate to identify the non-matching rows in this sort of a way:

Code: Select all

SELECT A.KEY
FROM   ORIG_TABLE A
WHERE  NOT EXISTS
       (SELECT '1'
        FROM   FILE_TABLE B
        WHERE  B.KEY = A.KEY)

That will give you a list of keys that you can then turn into "DELETE" statements to be run against the original database table using either SPUFI or the DSNTEP2 sample program. The above "SELECT" can also be changed to a "DELETE" to do the task all in one, but do the "SELECT" first to confirm the outcome before doing that.

Alternatively, you can unload the table using the DB2 UNLOAD utility which will give you a file containing the table's data. Extract the key information from this file and your input file then use DFSORT's "ICETOOL" utility to collate the two files together (see the "SPLICE" operator: https://www.ibm.com/docs/en/zos/3.1.0?t ... e-operator). From that you can identify the records where there are no matches and, as with the other method, turn them into a set of "DELETE" statements.