Replace duplicate db2 rows using Load utility

deepak_munjal · New User Joined: 30 May 2008 Posts: 43 Location: Mumbai

Hi,...

I have a file which i need to load in a table in such a way that---
>Data inserted if duplicate not found. (We can use resume option in Load card)
>Duplicate records should be override by the load i.e. should update the existing duplicate record.<Means if we find a same primary key row in the table we should update with the new data from file>.

I need to do it using load card...
Appreciate your help on this..

<Sorry.. i have posted this in ICETOOL forum>

Thanks,
Deepak

Escapa · Posted: Tue Jan 26, 2010 3:43 am

First thing is you have posted it to wrong forum.

Secondly you need to tell which utility you are using to load data in table

Also what about rows which are there only in the table but not in the file. Should they be kept or deleted?

enrico-sorichetti · Posted: Tue Jan 26, 2010 3:47 am

moved where it belongs

deepak_munjal · New User Joined: 30 May 2008 Posts: 43 Location: Mumbai

Its a BMC'S LOAD PLUS FOR DB2 LOAD
Utility used-- D2BTLD0E

Thanks enrico!

Records should be kept in the table.
No replace.... ONLY DUPLICATE RECORDS SHOULD BE REPLACED.

Escapa · Posted: Wed Jan 27, 2010 12:26 am

Best way is to look for BMC LOAD utility manual...

Since not all shops have this you need to wait for somebody who has worked on this to get the answer.

dick scherrer · Posted: Wed Jan 27, 2010 1:44 am

Hello,

Or implement some other solution. . .

Many systems have the same requirement and do not have the BMC tools available yet they still get this done with minimal effort.

deepak_munjal · New User Joined: 30 May 2008 Posts: 43 Location: Mumbai

Other solution i can't look because this I need to do with 7 load jobs.

7 load jobs are already being implemented long back using Resume option without taking care of REPLACE DUPLICATE ROWS.

Dick- Can you please let us know if you have any other way to implemente this without using a cobol program ?

It would be apprectiated if any one from moderator group can able to look into this and provide the option in load card with which we can achieve our goal.

dick scherrer · Posted: Wed Jan 27, 2010 2:11 am

Hello,

deepak_munjal · New User Joined: 30 May 2008 Posts: 43 Location: Mumbai

Thanks Dick!
Good idea.. but i can't use this as the number of records is more than a million in each table to process for daily run.
Do we have any other utiltiy used for load the data apart with which we can able to achieve this ?

I m looking to complete this in one JCL JOB without using COBOL program.
.

dick scherrer · Posted: Wed Jan 27, 2010 2:43 am

Hello,

Rather than the concern of processing a million rows, i'd suggest looking at how long it takes to unload and reload these . . .

We regularly do this with a (small for us) 2.7 million row table and it takes only a few minutes. Several things i work with are measured in tera-bytes and no, these would not be good candidates for a daily unload/reload. . .

What % of the data is updated? If it is only a thousand rows (or some other small number), it makes no sense to exclude a simple coded program. The wasted system resources would be required "forever" to save a day or less actual coding. . .