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
 

 

Update on large number of rows

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

New User


Joined: 07 Jul 2006
Posts: 4
Location: Chennai

PostPosted: Tue Feb 20, 2007 4:35 pm    Post subject: Update on large number of rows
Reply with quote

I have a table Emp with the data's as below

Emp-id Emp-code
1111 A
2222 A
3333 B
4444 C
5555 B

There are 10,000 such records in this table .

Given as input to a Program is the Emp-id of 200 employees

What I need to do is to update the Emp-code of the 200 employees to code "A"

At present my idea is to put the UPDATE query in a LOOP that runs for 200 times.

UPDATE EMP
SET EMP_CODE = 'A'
WHERE EMP_ID = :EMP-ID

But this would naturally consume more CPU TIME. Mine is an Online module. Is there any way to minimize the CPU time?

Also since I'm going to hold the table for a longer time I may get -911 error. Any solution to reduce this error?

Please Help me out!!!!
Back to top
View user's profile Send private message

William Thompson

Global Moderator


Joined: 18 Nov 2006
Posts: 3158
Location: Tucson AZ

PostPosted: Tue Feb 20, 2007 4:40 pm    Post subject:
Reply with quote

Why are you doing batch work in an online module?
Have you considered using commits?
Back to top
View user's profile Send private message
Cathy123

New User


Joined: 07 Jul 2006
Posts: 4
Location: Chennai

PostPosted: Tue Feb 20, 2007 5:11 pm    Post subject: Re: Update on large number of rows
Reply with quote

William,

Thanks for ur immediate reply

This is not a Batch work. I've just provided EMP table as an example

In reality We have a Online trading screen ( Trading of securities)

In that screen, the user lists down the trades and sends that to the Back End for Processing

What we receive at the BACK END is the TRADE-ID ( Primary key)

So if the user lists down 200 trades, we get 200 TRADE-ID's
For these trades we need to update the TRADE-INDICATOR TO 'Y' in the table

Our TABLE

TRADE-ID TRADE-INDICATOR
1111 N
2222 N
3333 N
4444 N


Further, If i choose to do UPDATE in a loop 200 times, I will need to use a commit.
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts Updating Cursor row withour using FOR... chandan.inst DB2 15 Tue Nov 08, 2016 11:17 am


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