MVSFORUMS.com

[abhinav343]

Hi,

I am comparing a flat file with a DB2 table. Taking a key from flat file and searching it into DB2 table.

I want to list all the DB2 records which doesnt have a mathcing key in file or you can say extra records in DB2 table.

Also, I know there can be a way comparing the flat file and DB2 unload file using SORT and get the differences but could you please suggest me other options?

Thanks,

Abhinav

[kolusu]

abhinav343,

If you dont want to unload and do a compare. Assuming that you don't have duplicates on the key values in the table you can try something like this

1. sort the flat file on ascending sequence of the key.
2. read the file in the program
3. for every key open a cursor on the db2 table with table key <= file key
4. fetch the cursor and compare the key . if equal ignore , else write output
5. repeat step 4 till end of cursor. once reached end of cursor close the cursor
6. perform the same steps for all the records in the file.

Kolusu

[abhinav343]

[jsharon1248]

You could also use a DECLARE'd GLOBAL TEMPORARY TABLE. Issue the command to create the table, insert your records, and then issue a query with a NOT EXISTS.

[haatvedt]

Abhinav,

unless the table is very small, it would be much faster and more cost efficient to use a high performance unload utility to unload the table. I would recommend using an "order by" clause to put the unload into the same sequence as the sequential file. Then a simple COBOL match program could be used.

On of the questions I ask myself is "is this a cost effective solution to what the customer wants ?". There is always a cost to running a process and as such you want your process to be cost effective.
_________________
Chuck Haatvedt

email --> clastnameatcharterdotnet

(replace lastname, at, dot with appropriate
characters)