Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Replace duplicate db2 rows using Load utility

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Tue Jan 26, 2010 3:40 am    Post subject: Replace duplicate db2 rows using Load utility
Reply with quote

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
Back to top
View user's profile Send private message

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Tue Jan 26, 2010 3:43 am    Post subject:
Reply with quote

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?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10210
Location: italy

PostPosted: Tue Jan 26, 2010 3:47 am    Post subject: Reply to: Replace duplicate db2 rows using Load utility
Reply with quote

moved where it belongs icon_cool.gif
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Tue Jan 26, 2010 3:49 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Wed Jan 27, 2010 12:26 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 1:44 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Wed Jan 27, 2010 1:50 am    Post subject: Reply to: Replace duplicate db2 rows using Load utility
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 2:11 am    Post subject:
Reply with quote

Hello,

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

How many rows are in these tables?

If the volume is not too large, the tables could be unloaded to qsam files, the "update" data merged with the unloaded data removing duplicates, and the output from the merges loaded back into the tables.
Back to top
View user's profile Send private message
deepak_munjal

New User


Joined: 30 May 2008
Posts: 43
Location: Mumbai

PostPosted: Wed Jan 27, 2010 2:20 am    Post subject: Reply to: Replace duplicate db2 rows using Load utility
Reply with quote

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.
.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jan 27, 2010 2:43 am    Post subject:
Reply with quote

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. . .
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts TAPE VERIFY utility?? MrExtraordinare JCL & VSAM 3 Fri Dec 09, 2016 10:57 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us