MVSFORUMS.com

Nirav721 · Beginner Joined: 07 Feb 2008 Posts: 4 Topics: 1

I am wondering if there is a way to update all columns for a particular row excluding the key without having to actually specify all column names within the SET clause? The data would be coming in from a flat file into cobol db2 program...I would like to be able to keep performance in perspective too...

Or, is it better to do delete/insert vs. updating all columns for a particular row within a table?

Any advise would be great!

Thanks

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

Nirav721,

Try the below SQL.

Nirav721 · Beginner Joined: 07 Feb 2008 Posts: 4 Topics: 1

vkphani,

Thanks for the reply. But, I am looking for a way to avoid writing out the column names since we have some tables with upto 300 columns.....? Would you know anyway to do that?

Thanks

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

The UPDATE-Statement forces the use of all the column namnes. The good news is, that you can generate this column list using BMC catalog manager or QMF (look into the manuals for the DRAW commmand).

regards
Christian

Nirav721 · Beginner Joined: 07 Feb 2008 Posts: 4 Topics: 1

Thanks Chris. I will look into it. I am curious though...not that we would ever do something like this because of a huge performance hit, but would it be possible to declare temporary table, in which we insert into data being read (i m hoping we can just specify space without actually getting into gritty details about each field name....). And then having update table set row = (select * from declaredtab where key = key) ??

Just curious on this part..if it is doable or not?

Thanks!

kolusu · Posted: Mon Feb 11, 2008 11:01 am Post subject:

Nirav721,

Why not simply Load the table with the flat file data? You can either do a LOAD REPLACE or LOAD RESUME.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Nirav721 · Beginner Joined: 07 Feb 2008 Posts: 4 Topics: 1

Hi,

The reason why we do not want to load replace is because this module is part of batch process that will be called on per-transaction basis...This module will just get the address of the place where data is...we would like the updates to take place as soon as transaction processes rather than waiting until everything processes and then applying the changes to database....

Thanks