|
View previous topic :: View next topic
|
| Author |
Message |
Rjadeja
New User
Joined: 18 Feb 2008
Posts: 9
Location: Mumbai
|
|
|
|
My program is doing mass update on table using one of the field of table due to which uncommitted reads happening and error logs are getting generated. Can any one please help me how to avod this.
I need to change program in such a way that it will take checkpoint after updtion of certain number of recrods.
1) How sholud I do this ? Do I need to declare cusror for update.
2) How to keep count of number of records updated.
3) How to take check point after certain muber of records updated.
Please help me its urgent.
Thanks,
Raghu |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello and welcome to the forum,
Sorry, we don't do urgent. . .
We also do not propose adding checkpoint/recovery for a single process - c/r is an environmental decision, not a single-process decision.
If you do not already have c/r directions for your system, suggest you run a backup before this begins, issue checkpoints periodically, and completely restore if there is an abend. |
|
| Back to top |
|
|
Rjadeja
New User
Joined: 18 Feb 2008
Posts: 9
Location: Mumbai
|
|
|
|
I want to explain you the situation
Lets say my Update query have 1000 records.
Can any way I can split it in to update for every 100 records and take check point after that. |
|
| Back to top |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| Are you certain that you are not confusing "checkpoint" and "commit"? |
|
| Back to top |
|
|
Rjadeja
New User
Joined: 18 Feb 2008
Posts: 9
Location: Mumbai
|
|
|
|
I want commit as well as check point.
Comit so as to avoid UR and check point if something goes wrong and job abend. I can restart the job from last check point. |
|
| Back to top |
|
|
Rjadeja
New User
Joined: 18 Feb 2008
Posts: 9
Location: Mumbai
|
|
|
|
I will explain you sitution in above case.
I have one table with all data. I want to update it by qaulifyting with one of the coloumn. Currently program has direct UPDATE function using where condition in that cloumn. However due to large data qaulifes for this condition DB2 logs are getting generated. I need to avoid. I am thinknig that is thier any way I can commit work after certain nmuber of records so that Uncommited read will not happen and logs will not be generated |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
I believe most of us completely understand your situation.
| Quote: |
| However due to large data qaulifes for this condition DB2 logs are getting generated. |
What is the content of these logs - post a few examples.
If other processes are permitted to read these rows while the update process to them is still running, and returning unpredictable content, talk with your dba about having the non-update tasks use a "dirty read".
Regardless, you do not want/need to try to implement checkpoint/restart for this.
Something else to consider is to implement some scheduling rule(s) that will run this update when the system is less in use. . .
Another consideration could be looking at the update task and reduce the time/resources needed to accomplish the task. |
|
| Back to top |
|
|
ovreddy
Active User
.jpg)
Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.
|
|
|
|
Hi,
I understood your problem is your program is holding locks on large number of uncommitted rows as well as causing to fill DB2 logs as this data is not COMMITTEd.
You can do the following
1) Define a Cursor for UPDATE WITH HOLD using the same WHERE clause as in singleton UPDATE statement.
2) Fetch from cursor then UPDATE... CURRENT OF <<CURSOR>>. If Update is successful then increment COMMIT-CNT variable by 1.
3) IF COMMIT-CNT >= MAX-COMMIT-CNT then issue a COMMIT and reset COMMIT-CNT to zero. MAX-COMMIT-CNT variable should have maximum records to be updated in a single commit. This might be 1000.
4) Repeat FETCH ---- UPDATE then at end of the cursor issue one more COMMIT and CLOSE the cursor.
Each COMMIT will clean up your DB2 logs. Let me know if you have any questions.
Thanks,
Reddy |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Slow down and try to understand what exactly do you want.
Before you start with check-point-restart, You need to know what is check-point restart -- Checkpoint/Restart allows you quickly insert COMMITs and CHECKPOINTS into your DB2 batch job streams – in some instances with- out even editing your programs. These COMMITs will often speed up DB2 batch programs and also free up DB2 resources for other production jobs that are running at the same time. After COMMITS are added to your DB2 batch programs, Checkpoint/Restart allows you to quickly restart any abended production job from the point of failure. Checkpoint/Restart will reestablish all positioning and resources within the problem program, including COBOL Working Storage and QSAM and VSAM file access; and then completes any remaining application processing rather than rerunning the whole DB2 program from the beginning.
Checkpoint Restart also provides a powerful Variable COMMIT Frequency Option that enables you to dynamically tune DB2 batch jobs by changing their COMMIT frequency as they run. Database Administrators can now dramatically speed up any jobs that need to be completed as soon as possible prior to the online DB2 applications being restarted.
Here is one supporting link: publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.admin/fastrest.htm
If you are intrested in BMC, here is a link to look at: www.bmc.com/products/proddocview/0,2832,19052_19429_22984_2005,00.html |
|
| Back to top |
|
|
|
|
