Implications of running batch and online concurrently

[login to unmask email]

Implications of running batch and online concurrently
We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?



Philip Gunning

Re: Implications of running batch and online concurrently
(in response to Thomas_Abbott@HESC.COM)
Tom, You really need to take a hard look at the transaction mix, commit
frequency, locking strategy, use of uncomitted read where prudent and
possible, and the most important area is how the applications group
originally wrote the application. Are there logical steps in the process
that takes locking into consideration, IE; do the applications always
access the objects in a certain order to avoid contention or not? The
answer is probably no. So you will have some contention. The degree of
contention will be determined by the transaction mix at your shop. It is
possible to do it but if the homework isn't done up-front, there can be
major problems. HTH Phil

Philip K. Gunning
DB2 DBA
IBM Certified Solutions Expert -- DB2 UDB
IBM Certified Solutions Expert -- CICS TS
Assoc List Owner

-----Original Message-----
From: [login to unmask email] [SMTP:[login to unmask email]
Sent: Thursday, January 06, 2000 10:45 AM
To: [login to unmask email]
Subject: Implications of running batch and online concurrently

We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?








Craig Snoeyenbos

Re: Implications of running batch and online concurrently
(in response to Philip Gunning)
You should start by restating the problem.

/Purist Mode - in an OLTP environment there are no batch jobs, only
non-terminal transactions that run concurrently with other transactions
-/Purist Mode off

In real life the minimal change is to remove the lock table statements and
have the batch jobs take regular commits to avoid lockouts. Since values of
"regular" are hard to guesstimate, a table with jobname, time and commit
frequency allows you to tune without recompiles.

You'll have to analyze the effects of an abend and rollback to last commit
point based on your application needs. Lots of possible solutions.

Craig Snoeyenbos

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 10:45 AM
To: [login to unmask email]
Subject: Implications of running batch and online concurrently


We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?








RICK (SWBT) DAVIS

Re: Implications of running batch and online concurrently
(in response to Craig Snoeyenbos)
Craig,
One issue that hasn't been noted thus far is that some batch
process(es) may actually need ISOLATION(RR) and/or LOCK several TABLEs
within a unit of work.

HTH,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: Snoeyenbos, Craig [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 10:18 AM
To: [login to unmask email]
Subject: Re: Implications of running batch and online concurrently


You should start by restating the problem.

/Purist Mode - in an OLTP environment there are no batch jobs, only
non-terminal transactions that run concurrently with other transactions
-/Purist Mode off

In real life the minimal change is to remove the lock table statements and
have the batch jobs take regular commits to avoid lockouts. Since values of
"regular" are hard to guesstimate, a table with jobname, time and commit
frequency allows you to tune without recompiles.

You'll have to analyze the effects of an abend and rollback to last commit
point based on your application needs. Lots of possible solutions.

Craig Snoeyenbos

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 10:45 AM
To: [login to unmask email]
Subject: Implications of running batch and online concurrently


We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?













Craig Snoeyenbos

Re: Implications of running batch and online concurrently
(in response to RICK (SWBT) DAVIS)
Rick,

You're right. The original question referred to batch update jobs, which I
assumed to be old style VSAM master file update logic. The lock table
and/or RR requirements would imply a balance and audit type process, which
is incompatible with any updates. Some application designer somewhere has
undoubtably added updates to a balance and audit run. It's going to take
more than DBA work to fit that into a 24/7 environment.

Craig

-----Original Message-----
From: DAVIS, RICK (SBCSI) [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 11:48 AM
To: [login to unmask email]
Subject: Re: Implications of running batch and online concurrently


Craig,
One issue that hasn't been noted thus far is that some batch
process(es) may actually need ISOLATION(RR) and/or LOCK several TABLEs
within a unit of work.

HTH,
Rick Davis
"This e-mail and any files transmitted with it are the property of SBC,
are confidential, and are intended solely for the use of the individual
or entity to whom this e-mail is addressed. If you are not one of the
named recipient(s) or otherwise have reason to believe that you have
received this message in error, please notify the sender at 314-235-6854
and delete this message immediately from your computer. Any other use,
retention, dissemination, forwarding, printing, or copying of this
e-mail is strictly prohibited."



-----Original Message-----
From: Snoeyenbos, Craig [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 10:18 AM
To: [login to unmask email]
Subject: Re: Implications of running batch and online concurrently


You should start by restating the problem.

/Purist Mode - in an OLTP environment there are no batch jobs, only
non-terminal transactions that run concurrently with other transactions
-/Purist Mode off

In real life the minimal change is to remove the lock table statements and
have the batch jobs take regular commits to avoid lockouts. Since values of
"regular" are hard to guesstimate, a table with jobname, time and commit
frequency allows you to tune without recompiles.

You'll have to analyze the effects of an abend and rollback to last commit
point based on your application needs. Lots of possible solutions.

Craig Snoeyenbos

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, January 06, 2000 10:45 AM
To: [login to unmask email]
Subject: Implications of running batch and online concurrently


We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?


















HARBRY ARIZA

Re: Implications of running batch and online concurrently
(in response to Craig Snoeyenbos)
Thomas:

I would like to suggest review some points before you start to process
online and batch concurrently.
-If you are on V4 or later , use index type 2 .
- Plan or Package definitions. If you want to improve your concurrency you
have bind your aplications chosing the options that can help you to get it
like ISOLATION (CS) , ACQUIRE(USE), and RELEASE(COMMIT). Specifying
Isolation level CURSOR STABILITY allow your application lock the page long
enough meantime the cursor is getting another page. Acquire(use) (default
for packages) and Release(commit) allow your application free resources more
frecuently but it depend on the commit frecuency.This combination provides
the greatest concurrency.
-Commits Fecuently, allow your aplications avoid the amount of Deadlocks
too.
-Try to set up a bufferpools big enough (if you can afford it) to satisfy
a big number of the getpages request ( high hit bpool ratio).
-One important thing that you have to check is the update activity on your
db2 subsystem. If it is high to have to specify a big OUTBUFFER to write all
the updates occurred on your system and set WRTHRSH good enough to keep your
OUTBUFFER available to news updates.
And the LOGLOAD has to be checked too .

Those are some points and I hope it helps you.

>From: [login to unmask email]
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Implications of running batch and online concurrently
>Date: Thu, 6 Jan 2000 10:45:26 -0500
>
>We are a shop which runs CICS transactions during the day and batch jobs at
>night after online update has been programmatically disabled. We are
>investigating the possibilities of running batch and online update
>transactions concurrently.
> I am interested in methods used to allow this. Our batch programs
>currently take table locks and commit at the end of processing. Our
>tables are defined with page-level locks. What sort of
>programmatic(locking, commits, rollbacks, restarts, timestamp
>considerations on read/update) and/or database changes would be required ?
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



[login to unmask email]

Re: Implications of running batch and online concurrently
(in response to HARBRY ARIZA)
A few considerations:

Ensure your batch programs can correctly handle timeouts and deadlocks only
failing when n contention problems are hit
Ensure your batch programs take frequent commits (aim for a few seconds
below your timeout value)
Watch out for lock escalation
Amend the batch programs so that no table locks are taken
If v4 or above amend all indexes to type 2 if not already done
Consider isolation UR for batch reports
Only move to row locking for problematic tables, consider amending maxrows
first

Jim.


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: 06 January 2000 15:45
To: [login to unmask email]
Subject: Implications of running batch and online concurrently


We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?








Roland Chua

Re: Implications of running batch and online concurrently
(in response to Jim.Leask@RS-COMPONENTS.COM)
Hi,

Just to add in one more consideration:
Ensure that your batch programs can be restart from the point of failure or
checkpoint. If your batch programs read records from input file, you need
to bypass those already processed records. Similarly, you need to take care
of the output file too.






Jim Leask <[login to unmask email]> on 07/01/2000 04:02:30 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: ROLAND CHUA CHONG KEE/DATACEN/IT/SGX)
Subject: Re: Implications of running batch and online concurrently




A few considerations:

Ensure your batch programs can correctly handle timeouts and deadlocks only
failing when n contention problems are hit
Ensure your batch programs take frequent commits (aim for a few seconds
below your timeout value)
Watch out for lock escalation
Amend the batch programs so that no table locks are taken
If v4 or above amend all indexes to type 2 if not already done
Consider isolation UR for batch reports
Only move to row locking for problematic tables, consider amending maxrows
first

Jim.


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: 06 January 2000 15:45
To: [login to unmask email]
Subject: Implications of running batch and online concurrently


We are a shop which runs CICS transactions during the day and batch jobs at
night after online update has been programmatically disabled. We are
investigating the possibilities of running batch and online update
transactions concurrently.
I am interested in methods used to allow this. Our batch programs
currently take table locks and commit at the end of processing. Our
tables are defined with page-level locks. What sort of
programmatic(locking, commits, rollbacks, restarts, timestamp
considerations on read/update) and/or database changes would be required ?



the