Checkpoint Restart in DB2 Part - I

>> Click here for Part II <<

Before getting into what is check point restart and how to implement it,you need to understand when and why you should go for it.


Suppose, a batch program that basically reads an input file and posts the updates/inserts/deletes to DB2 tables in the database was abended before the end of the job because of some reasons; Is it possible to tell - How many input records were processed? Were any of the updates committed to the database or can the job be started from the beginning?

Assume that COMMIT logic was not coded for large batch jobs that process millions of records. If an ABEND occurs all database updates will be rolled back and the job can be resubmitted from the beginning. If an ABEND occurs near the end of the process, the rollback of all the updates is performed. Also, DB2 will maintain a large number of locks for a long period of time, reducing concurrency in the system. In fact, the program may ABEND if it tries to acquire more than the installation-defined maximum number of locks.

This involves setting up a batch-restart control table (CHECKPOINT_RESTART in our case) to store the last input record processed and other control information. The restart control table can also be used as an instrumentation table to control the execution, commit frequency, locking protocol and termination of batch

One of the problems with restart is synchronizing DB2 tables and output files(If your program updates some output file). DB2 will rollback all work on DB2 tables to the last commit point; but for output files we have to delete all the records up to the last commit point. (One option to do this would be via a global temporary table, FILE_POSITION_GTT, See FILE REPOSITIONING section for further details.).

COMMIT Function

The COMMIT statement ends a unit of recovery and commits the relational database changes that were made in that unit of recovery. If relational databases are the only recoverable resources used by the application process, COMMIT also ends the unit of work. The unit of recovery in which the statement is executed is ended and a new unit of recovery is effectively started for the process. All changes made by ALTER, COMMENT ON, CREATE, DELETE, DROP, EXPLAIN, GRANT, INSERT, LABEL ON SQL connections are ended when any of the following apply:

► The connection is in the release pending state
► The connection is not in the release pending state but it is a remote connection and:

  • The DISCONNECT(AUTOMATIC) bind option is in effect, or
  • The DISCONNECT (CONDITIONAL) bind option is in effect and an open WITH HOLD cursor is not associated with the connection.
For existing connections,

► All open cursors that were declared with the WITH HOLD option are preserved, along with any 
SELECT statements that were prepared for those cursors.

► All other prepared statements are destroyed unless dynamic caching is enabled.

► If dynamic caching is enabled, then all prepared SELECT, INSERT, UPDATE 
and DELETE statements
 that are bound with KEEPDYNAMIC (YES) are kept past the commit.
 Prepared statements cannot be kept past a commit if:

      * SQL RELEASE has been issued for that site, or
      * Bind option DISCONNECT(AUTOMATIC) was used, or 
      * Bind option DISCONNECT (CONDITIONAL) was used and there are no hold cursors.

All implicitly acquired locks are released, except for those required for the cursors that were
  • A place to store the details (CHECKPOINT-COMMIT record) pertaining to the current execution of the program, like various counts (number of inserts/deletes/updates/selects), number of processed, processing dates, and other details which are needed in the program after a RESTART. 
  • A reliable FILE RE-POSITIONING logic with minimal changes to the existing PROCJCL.
  • Flexibility, to modify the commit frequency without changing the program code.
Where we can store this CHECKPOINT-COMMIT record?

We can store the CHECKPOINT-COMMIT record, COMMIT-FREQUENCY and other relevant
information in a DB2 table .


At restart, all records written to the output file since the last commit will have to be removed. To accomplish this, FILE_POSITION_GTT global temporary table is used. SQL statements that use global temporary tables can run faster because:

• DB2 does not log changes to global temporary tables
• Global temporary tables do not experience lock contention
• DB2 creates an instance of the temp table OPEN/SELECT/INSERT/DELETE stmts. only occur:

• An instance of a temporary table exists at the current server until one of the following actions occur:

• The remove server connection under which the instance was created terminates
     For ROLLBACK stmt, DB2 deletes the instance of the temporary table.
     For COMMIT stmt, DB2 deletes the instance of the temporary table unless cursor
     for accessing temporary table is defined WITH HOLD and is open.
 •  The application process ends.

In next part we will discuss step by step process of check point restart installation along with sample code.



Anonymous said...

Hi Nitin I think this site is very useful for DB2 learners.. Is there any contact details for you? My number is 0 9880285679 Jayaraman Sekar

Nitin Gandhi said...

Hi Jayaraman,

Thanks for your feedback.

You can reach me @

Neha A. said...

Nitin, the step by step explanation provided by you simply rocks.... Thanks a ton !!!

rahul said...

This is really nice. Thanks for your hard work. It is really appreciable.

Anonymous said...

Hey There. I found your weblog the use of msn. This is a really well written article.
I will be sure to bookmark it and return to learn extra of your useful information.
Thanks for the post. I will definitely comeback.
Here is my blog ...

Anonymous said...

I really like what you guys tend to be up too.
This kind of clever work and exposure! Keep up the very good works
guys I've included you guys to our blogroll.
Feel free to visit my site : money online niche

Anonymous said...

What's up everyone, it's my first pay a quick visit at this website,
and piece of writing is really fruitful for me, keep up posting these types of content.
Here is my web-site :: online insurance quotes

Anonymous said...

This is really interesting, You're a very skilled blogger. I have joined your feed and look forward to seeking more of your great post. Also, I've shared your web site
in my social networks!
Feel free to visit my page quit smoking today

Anonymous said...

Good web site you have here.. It's difficult to find high quality writing like yours these days. I honestly appreciate people like you! Take care!!

Here is my site innovative internet marketing

Anonymous said...

It is really a nice and helpful piece of info. I'm happy that you just shared this helpful info with us. Please stay us up to date like this. Thanks for sharing.

My web-site
my webpage ::

Anonymous said...

Excellent beat ! I would like to apprentice while you amend your web site, how can i subscribe for a blog website?
The account aided me a acceptable deal. I had been tiny bit acquainted of
this your broadcast provided bright clear idea

Check out my web blog online shopping portals

Anonymous said...

I really like your blog.. very nice colors &
theme. Did you make this website yourself or did you hire someone to do it for you?

Plz reply as I'm looking to design my own blog and would like to know where u got this from. kudos

my site ... Coq10 supplements

Anonymous said...

Hello, always i used to check weblog posts here in the early hours in the morning, since i
love to find out more and more.

my web site;

Anonymous said...

In fact no matter if someone doesn't understand afterward its up to other people that they will help, so here it takes place.

Feel free to visit my weblog ... smoking hypnosis rates

Anonymous said...

It's going to be end of mine day, except before ending I am reading this great post to improve my knowledge.

my site; bmi chart female
Also see my website: bmi for women chart

Anonymous said...

Every weekend i used to pay a visit this website, because i
want enjoyment, since this this website conations truly fastidious funny data too.

Feel free to visit my web site ... ways to lose weight

Anonymous said...

Exceptional post but I was wanting to know if you could write
a litte more on this topic? I'd be very grateful if you could elaborate a little bit more. Kudos!

My webpage: bmi chart for men

Anonymous said...

I also incorporate some guidelines on what equipment is needed to put together a
essential home fitness center.

my weblog - adjustable dumbbells sets

Anonymous said...

The truth is, even professionals typically use this twice-a-week training process by themselves, precisely doing work certain muscular tissues and muscle groups on two days and dealing sure other
teams on two other times.

my homepage: Skip Navigation

Anonymous said...

For a few, this is not a big deal.

Here is my homepage ... simply click the next internet page

Anonymous said...

Behind the scenes in each and every relationship, there are actually lots of minimal complications that exist among the men and
women in them.

Feel free to surf to my page ... bowflex select tech dumbbells

Anonymous said...

Whatsoever you need to do, or you should not do,
using your previous somekeyword, if finding a different
one particular will induce you to work with it,
or to work out extra, it's truly worth it!

Take a look at my web page adjustable weights

Anonymous said...

For those who have read about isometric exercising
than you prefer me can be drained of hearing the expression -- "NO Pain, NO Obtain!

Feel free to surf to my page Seguir para a frente de Navegação

Anonymous said...

Being a end result numerous new work out systems and exercising products are currently being advertised heavily
specifically all over January of every 12 months.

Here is my website: click through the following website page

Anonymous said...

It can be tall so forget about putting it inside the interior of
the household when you have very low ceilings or inside your basement.

My web site ... adjustable dumbbell set

Anonymous said...

It needs to be noted that you should under no circumstances
start off any sort of instruction with no initially consulting
a physician with regards to the exercise amount of both
you and your boy or girl if schooling with each other.

Here is my web site ... adjustable weights

Post a Comment