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
 
Check Point Logic in DB2

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

New User


Joined: 18 Feb 2008
Posts: 9
Location: Mumbai

PostPosted: Thu Oct 07, 2010 12:45 am    Post subject: Check Point Logic in DB2
Reply with quote

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

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 07, 2010 12:52 am    Post subject:
Reply with quote

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

New User


Joined: 18 Feb 2008
Posts: 9
Location: Mumbai

PostPosted: Thu Oct 07, 2010 1:32 am    Post subject:
Reply with quote

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

Global Moderator


Joined: 03 Oct 2009
Posts: 1777
Location: Bloomington, IL

PostPosted: Thu Oct 07, 2010 1:43 am    Post subject:
Reply with quote

Are you certain that you are not confusing "checkpoint" and "commit"?
Back to top
View user's profile Send private message
Rjadeja

New User


Joined: 18 Feb 2008
Posts: 9
Location: Mumbai

PostPosted: Thu Oct 07, 2010 2:49 am    Post subject:
Reply with quote

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

New User


Joined: 18 Feb 2008
Posts: 9
Location: Mumbai

PostPosted: Thu Oct 07, 2010 3:05 am    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Oct 07, 2010 7:58 am    Post subject:
Reply with quote

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

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Fri Oct 08, 2010 1:12 am    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Oct 08, 2010 3:08 pm    Post subject:
Reply with quote

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: http://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: http://www.bmc.com/products/proddocview/0,2832,19052_19429_22984_2005,00.html
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 ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm
No new posts Check Binary Values for symbols gandikk CA Products 3 Tue Mar 21, 2017 5:11 am
No new posts Need help in building a logic Benchwarmer All Other Mainframe Topics 4 Wed Feb 22, 2017 2:49 am
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 7 Fri Nov 18, 2016 1:48 pm

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