i have 2 millions records in DB2 table, i want to UPDATE

soumyaranjan007 · New User Joined: 30 Aug 2006 Posts: 30 Location: mumbai

Hi Gurues,

I have 2 millions record in one DB2 Table. i want to update those 2 millions records. could any body pass the queries for that.

we have tried with

UPDATE <TABLE NAME> SET <column name> = '35';

But this queries got abend due to full of buffer space.

Could any body aware of this issue, please help me out to sort out issue
ASAP.

Regards
Somu

guptae · Posted: Tue Nov 07, 2006 1:15 pm

Hi soumyaranjan007,

Another option is download the table in to flat file update value for that coloum & again load it in to table.

soumyaranjan007 · New User Joined: 30 Aug 2006 Posts: 30 Location: mumbai

Hi Ekta,

Actually we are planning to modify the table structure ( one new column will be add). we want to update that column for older data.

I really appreciate your view but uploading the data from table and load back to table after modifying data is not a fusible way for ous because of authority restrictions.

But we are able to solve this issue.

I am highlighting that steps need to take care before update the huge data below.

1. We lock the table in EXCLUSIVE mode first.
2. Update the column

Regards
Somu

dbzTHEdinosauer · Posted: Tue Nov 07, 2006 6:46 pm

that many rows... I suggest either:

update current cursor, with COMMITs every 1000 rows or so
if you must spufi, then multiple UPDATEs where <condition-1>AND<condition-2>
- condition 1 = range of unique key
- condition-2 = <column Not Equal '35'>

a MASS UPDATE means that you are sucking the complete table into a recovery copy; you are duplicating the table in DB2's work space. You have to update sections (rows), then commit those rows, then UPDATE some more.

vijikesavan · Posted: Fri Nov 10, 2006 2:43 am

Yes. Frequent commit will help in this case.

vijikesavan · Posted: Fri Nov 10, 2006 2:47 am

Also you can use "for update of" in the cursor and "where current of" in the update"...if exclusive control of the table is not a problem.