Granular Transcations - OLTP's

By the time you login to see what the transaction does - It's out of your sight !, may be that's why they named it as granular. Going technical - If you have a shop that is composed of CICS - DB2 hub, you as a DBA or developer should always ensure that these transactions execute for less than a minute ! OLTP's usually have a short interval zone - they creep in and creep out within 5 - 15 minutes of time frame differences.  In my experience with OLTP's ( On Line Transaction Processing ), I have seen few guys having a '' slight '' more run time duration than what it should be. So what's the big deal again ?

If your OLTP program is not well " tuned " then it can cause an overlap with the next program which is set within the interval zone. Believe it or not - After a long analysis I managed to find out in my account there is just a 3 second breather between transactions. A slight delay would trigger a chain of '' escalations " in the system and your subsystem master file will be loaded with them.

Usually OLTP's will not have a complex code or a logic in them, well most of the cases. But if you manage to see any complex SQL - try tuning it. It will be fun working with these little guys.

Here is my experience in tuning an OLTP -

The application developers used to intimate me that a particular OLTP which runs twice every hour, 24 x 7 is consuming lot of time and is causing delays especially during the month end processing ( when the load is high on the systems ). Here is how the transaction looked like

why would I bother on an application program consuming 85  seconds of application duration and 28 seconds of DB2 CPU ?  ? But wait - you sum the application and DB2 duration of the OLTP for a day and then for a month, the math would say - 1 hour of application time every day which would pile up to 30 hours every month with a DB2 duration of 704 mins/month . WOW Big Numbers !

I looked at the stats for the tables - It looked good. My aim now was looking for any access path issues. I did find one for the below query -

SELECT * FROM PRDNVW00
WHERE
COL_2 = :HV1 AND
COL_3 = 'Y'     AND
COL_4 LIKE 'SUCCESS%'

The access path for the above query was MATCHCOLS 0, ACCESSTYPE I , The index was defined on COL_1, COL_2 and COL_3 of the table. But the WHERE clause had COL_2 and COl_3. I tried my luck with the developers asking them to introduce COL_1 in the query and they managed to do that ( this is not easy, they had to check their business logic and impact ). Everything went as planned and the OLTP run time reduced drastically due to the query change -

SELECT * FROM PRDNVW00
WHERE
COL_1 = :HV0 AND
COL_2 = :HV1 AND
COL_3 = 'Y'     AND
COL_4 LIKE 'SUCCESS%'

Here is the run of the tuned OLTP program post the query change -

The OLTP now consumes 7 mins of application time every day which will pile up to 3.5 hours every month with a DB2 duration of 14 mins/month. HUGE SAVINGS and a great amount of breather time created between these transactions