IBM Mainframe Forum

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.

Why not try the manuals? They have sample queries.

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))

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.

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

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
--------------------------------- ------------------------------ -------------------------------------

So in this case... he have to load the the table and then run a program to update the database???

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

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