How to update DB2 table using Utility



IBM's flagship relational database management system

Re: How to update DB2 table using Utility

Postby ritikasingh » Wed Apr 27, 2011 9:01 pm

Please advice me : If i load the input data in a table , it is possible to write a sql such that it insert and update the output table as per the select query return code .Please provide the sample query.
ritikasingh
 
Posts: 39
Joined: Wed Sep 01, 2010 9:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Re: How to update DB2 table using Utility

 

Re: How to update DB2 table using Utility

Postby NicC » Wed Apr 27, 2011 11:49 pm

Why not try the manuals? They have sample queries.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: How to update DB2 table using Utility

Postby GuyC » Thu Apr 28, 2011 4:59 pm

Merge from subselect is not (yet) available on z/OS.
you'll have to write 2 SQLs, something like (not tested):
update tab1 set (a,b,c) = (select a,b,c from tab2 where tab1.pk = tab2.pk) where exists (select a,b,c from tab2 where tab1.pk = tab2.pk)
INSERT into tab1(a,b,c) (select a,b,c from tab2 where not exists (select 1 from tab1 where tab1.pk = tab2.pk))
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: How to update DB2 table using Utility

Postby ritikasingh » Thu Apr 28, 2011 11:27 pm

Thanks for replying.Sorry i am not clear with the solution because i see we are using exists & not exists both in same query.
My understanding as per manualis EXISTS is very effective as it save cpu time, now as we are dealing with millions of records
i want some solution like this

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)

But the values for the table will be in a flat file dataset.
ritikasingh
 
Posts: 39
Joined: Wed Sep 01, 2010 9:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to update DB2 table using Utility

Postby GuyC » Fri Apr 29, 2011 2:04 pm

update tab1 set (a,b,c) = (select a,b,c from tab2 where tab1.pk = tab2.pk) where exists (select a,b,c from tab2 where tab1.pk = tab2.pk)
INSERT into tab1(a,b,c) (select a,b,c from tab2 where not exists (select 1 from tab1 where tab1.pk = tab2.pk))

Are 2 SQL statements, not 1
I thought with
If i load the input data in a table , it is possible to write a sql such that it insert and update the output table as per the select query return code

you meant loading the flat file in an empty table2
and then based on table2, insert/update table1
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: How to update DB2 table using Utility

Postby ritikasingh » Fri May 13, 2011 10:41 pm

Yes..I successfully loaded the tables..Thanks for the reply.
I need one more advice on reconcillation..
As number of tables i am loading with DB2 utility ad Day o batch are 52.
So is it possible to create a report using ICETOO/DFSSORT etc having information :
Number of records received Number of records loaded Number of records discarded etc
--------------------------------- ------------------------------ -------------------------------------
ritikasingh
 
Posts: 39
Joined: Wed Sep 01, 2010 9:12 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to update DB2 table using Utility

Postby Alison Oliveira » Mon Jan 23, 2012 10:38 pm

So in this case... he have to load the the table and then run a program to update the database???
Alison Oliveira
 
Posts: 37
Joined: Fri Jan 20, 2012 9:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: How to update DB2 table using Utility

Postby sinmani » Wed Apr 04, 2012 4:01 pm

To Load a table afresh we use Load replace option.

What it will do is delete old records and insert all the new ones you are loading.

If you want to kep the original records and load new ones use LOAD RESUME.

Hope this will solve ur problem
-----------------------------------------
As long as you think you are a student, you are doing well.
The day you consider yourself as the master of the game..........well
sinmani
 
Posts: 93
Joined: Thu Mar 22, 2012 10:02 am
Has thanked: 14 times
Been thanked: 0 time

Re: How to update DB2 table using Utility

Postby pmartyn » Wed Apr 24, 2013 4:23 am

I know this is an old post but I am writing this so you will have the information i case someone else comeswith the same question.
The answer was to use LOAD RESUME.

There are three possible LOAD scenarios for a tablespace:
(1) tablespace is empty and you have all new data in an external file
(2) tablespace is not empty; you want to empty it first, then do (1)
(3) tablespace is not empty; you have additional data to be appended to it

It si important to note that DB2 uses Linear VSAM files as its foundation. Option 1 or 2 above will perform a delete and define, then load.
RESUME NO is the default; it makes sure you don't do stupid things.

RESUME YES tells DB2 you want to resume, i.e., continue with, the data already present in the table, i.e. you go for appending (scenario 3).
It is important to note that the new data is 'appended' to existing structure. This can be attributed to the underlying structure is a linear VSAM file and DB2 is smart enough to handle the keys(etc.)

pm
pmartyn
 
Posts: 42
Joined: Thu Feb 28, 2013 7:11 pm
Has thanked: 5 times
Been thanked: 3 times

Previous

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post