Page 2 of 2

Re: How to update DB2 table using Utility

PostPosted: Wed Apr 27, 2011 9:01 pm
by ritikasingh
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.

Re: How to update DB2 table using Utility

PostPosted: Wed Apr 27, 2011 11:49 pm
by NicC
Why not try the manuals? They have sample queries.

Re: How to update DB2 table using Utility

PostPosted: Thu Apr 28, 2011 4:59 pm
by GuyC
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))

Re: How to update DB2 table using Utility

PostPosted: Thu Apr 28, 2011 11:27 pm
by ritikasingh
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.

Re: How to update DB2 table using Utility

PostPosted: Fri Apr 29, 2011 2:04 pm
by GuyC
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

Re: How to update DB2 table using Utility

PostPosted: Fri May 13, 2011 10:41 pm
by ritikasingh
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
--------------------------------- ------------------------------ -------------------------------------

Re: How to update DB2 table using Utility

PostPosted: Mon Jan 23, 2012 10:38 pm
by Alison Oliveira
So in this case... he have to load the the table and then run a program to update the database???

Re: How to update DB2 table using Utility

PostPosted: Wed Apr 04, 2012 4:01 pm
by sinmani
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

Re: How to update DB2 table using Utility

PostPosted: Wed Apr 24, 2013 4:23 am
by pmartyn
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