MVSFORUMS.com

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

Hi ,

We need to change all 'X' to 'Y' in around 50 DB2 tables( which has around 30000 records each).

I know we can write SQL which will do the job. Wanted to know If there is any other ways of achieving this keeping in mind the performance and also time taken to do this job.

DB2 techies please help !

- Martin

shekar123 · Posted: Mon Jun 19, 2006 2:49 am Post subject:

Martin,

Is it that you are trying to update a column from char 'X' to 'Y' in 50 db2 tables ? Assuming there is a column called GROUP CHAR(1) in a table A and has value 'X' for some records.We need to change for all records the column to 'Y'.

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

shekar123,

Here's what I want: -

1) replace all occurance of 'X' to 'Y' in all the 50 db2 tables.

Could you please give the sql for the same?

Thanks,
Martin

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

Well, you really have to code 50 SQL UPDATE statements.

If you would talk about more tables, say 500, I would suggest to generate the UPDATE statement via SELECT statements on the DB2 catalog. But with 50 Updates at hand, I wouldn't bother.

regards
Christian

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

CZerfas,

But the problem is the amount of data that needs to be changed in all the 50 tables... ( which has around 30000 records each).

- Martin

kolusu · Posted: Tue Jun 20, 2006 8:01 am Post subject:

Martin,

If there is a primary key for the tables, you can split the update statement.

Update table set coln = 'Y' where primary_key <= 'value 1';

Update table set coln = 'Y' where primary_key > 'value 1' and primary_key <= 'value2'

...

Hope this helps....

Cheers

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

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

Kolusu,

We tried it But the time taken for completion is huge. Your approach will work only for 1 table at a time. I want to do a mass change in all the 50 tables at once.

Can any one give the syntax for using solution suggested by shekar123?

- Martin

kolusu · Posted: Tue Jun 20, 2006 1:56 pm Post subject:

shekar123 · Posted: Tue Jun 20, 2006 3:09 pm Post subject:

Martin,

Code all the update statements in a sysin control card and run the job.Try this code:

1.Change Proper DB2 Subsytem you are using
2.Give the proper dataset name for the RUNLIB.LOAD dataset which will have DSNTEP2 IBM Module