Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 Merging 2 records at multiple rows wi... Bijesh DFSORT/ICETOOL 2 Wed Dec 06, 2017 1:50 am
No new posts Can we combine the rows with same key... V S Amarendra Reddy DB2 12 Fri Dec 01, 2017 10:29 pm
No new posts Increment number for every group of r... srajendran2 DFSORT/ICETOOL 7 Thu Nov 09, 2017 10:31 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Conditional highlighting of rows in I... packerm CLIST & REXX 2 Thu Sep 21, 2017 6:50 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us