MVSFORUMS.com

ut89 · Beginner Joined: 26 Mar 2003 Posts: 4 Topics: 2

I am looking at CICS reports that give a tally of trnasactions on a particular day, response times, cpu usage etc. Some days the response times are a lot faster. It looks like if their is a slow transaction then whole region is being slowed down. What could be the reasons?
Some days 15 out of 4000 transactions take more than 10 sec to complete and some days 80 out of 4800 transactions take more than 10 sec to complete. When I drill down, I find that DB2 updates/selects are taking long times in sometimes. What can be the reasons?
ut

warp5 · Posted: Fri Apr 04, 2003 12:53 am Post subject:

DB2 deadlocks could be a reason, you can look at your system log to see if there are deadlocks.
If your DB2 (or CPU) is overworked then all your other programs that want to use DB2 will be slow too. We sometimes have transactions that use 10,000 or more getpages and then the whole system suffers, transactions waiting to process back up, and it can take a long time to process this backup. Take a look at those long transactions - how many getpages do they have? How about batch running at the same time, or Spufi's?

ut89 · Beginner Joined: 26 Mar 2003 Posts: 4 Topics: 2

We have batch job scheduled every 30 mins and that for sure appears to be one of the reasons for long db2 transactions. How do I look for db2 logs, do I need dba privileges to do that.

Thanks,
ut

Glenn · Beginner Joined: 23 Mar 2003 Posts: 56 Topics: 3

You will wish to analyze all the transactions for poorly written statements or statements that should be in a different process - especially these 80 transactions you mention.

These statements could access a large portion of the database through a tablespace scan or indexspace scan (and thus causing deadlocks as warp5 mentions) or try and reengineer the process if it's not possible to make the statements more efficient - maybe push transactions out to update in batch at the end of the day in a window where there's not much transaction activity?

EXPLAIN is your best friend when it comes to DB2 and performance...you might do well to become acquainted.

ut89 · Beginner Joined: 26 Mar 2003 Posts: 4 Topics: 2

we have a batch job scheduled every 30 mins and I guess it uses tablescan/ index scan and locks the table. During that time (observed 1 min) the normal transactions hitting are getting delayed. I know having such batch programs is not a good practice but can't change that.
My issue is that in situations like this should cause db2 deadlocks and in turn CICS program abends. But that is not happening even if the transaction gets delayed as long as 2 minutes.
Another issue is this are web applications and use CTG and EAB record and CICS program to retrieve/update DB2. Are the CTG pipes quasi-conversational or not? I don't see any exceptions on the java programs on the web.
First step will be to look into the batch program and find out some efficient way. Delayed transactions on their own are OK with the batch process.
Thanks,
ut

Glenn · Beginner Joined: 23 Mar 2003 Posts: 56 Topics: 3

Yes you can look at the batch program (a good possibility). Write it so the DB2 statement does not access the whole table. If you are using a mass insert, delete or update, break it up so only a single row is accessed at any one time and the rest of the table is unlocked.

What I mean by mass insert/update/delete is one statement which will act on multiple rows in the database. Doing this will increase the likelihood of locks happening on the whole table. You can break it up and issue multiple statements and you can even update or delete based on a cursor.