Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

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

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
soumyaranjan007

New User


Joined: 30 Aug 2006
Posts: 30
Location: mumbai

PostPosted: Tue Nov 07, 2006 11:43 am    Post subject: i have 2 millions records in DB2 table, i want to UPDATE
Reply with quote

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
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Nov 07, 2006 1:15 pm    Post subject:
Reply with quote

Hi soumyaranjan007,

Another option is download the table in to flat file update value for that coloum & again load it in to table.
Back to top
View user's profile Send private message
soumyaranjan007

New User


Joined: 30 Aug 2006
Posts: 30
Location: mumbai

PostPosted: Tue Nov 07, 2006 6:08 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Tue Nov 07, 2006 6:46 pm    Post subject: Re: i have 2 millions records in DB2 table, i want to UPDATE
Reply with quote

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.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Nov 10, 2006 2:43 am    Post subject: Re: i have 2 millions records in DB2 table, i want to UPDATE
Reply with quote

Yes. Frequent commit will help in this case.
Back to top
View user's profile Send private message
vijikesavan

Active User


Joined: 04 Oct 2006
Posts: 118
Location: NJ, USA

PostPosted: Fri Nov 10, 2006 2:47 am    Post subject: Re: i have 2 millions records in DB2 table, i want to UPDATE
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us