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
Compare a flat file with DB2 table.
Re: Compare a flat file with DB2 table.
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).
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).
Re: Compare a flat file with DB2 table.
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:
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.
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)
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.
Create an account or sign in to join the discussion
You need to be a member in order to post a reply
Create an account
Not a member? register to join our community
Members can start their own topics & subscribe to topics
It’s free and only takes a minute