A Batch Performance Monitor for CA-IDMS Applications.
PRODUCT BRIEF
DBSTATS is designed to assist the CA-IDMS professional in tuning batch applications. CA-IDMS statistics are gathered at the individual call level. By accumulating statistics at the DML level exact program statements causing batch performance problems can be easily identified.
DBSTATS is activated by simply adding a DD statement to the CDMSLIB (or STEPLIB/JOBLIB) concatenation. It is not necessary to modify the application or to re-compile or re-link the program. The application can be written in any language and executed under CV or in local mode. Upon completion of the program or upon application abort, the DBSTATS reports are produced.
REPORTING FEATURES
Two types of DBSTATS reports are available: summary or trace. The summary report has one line for each different DML statement executed. It shows the accumulated time and database activity for all executions of the statement. The trace report shows the time and database activity for each execution of a DML statement. Trace reports can be turned on or off and show the results of each database call (i.e. return codes and DBKEYS).
Lets examine a DBSTATS report to see how easily a performance problem can be identified. We modified one of our HSL programs for this example. Please keep in mind we are professional programmers. Don't try this at home.
In this example, program HSLBC009 ran about 10 minutes. Look at DML statement #53. SCROLL TO THE RIGHT. Ninety one percent (91%) of the time was spent on that statement! It's a committ. We are over committing. (editors note - I have NEVER been accused of over committing!) How much time are your commits taking? How do you know? In this example, the commit is killing us.
Check out this next DBSTATS report. We increased the commit level and now the commits are only using 1% of the resources. The SAME batch job now runs in two minutes. An 80% reduction. Just imagine if this little program had been running for hours!
Ok, that one was easy. It should also be pointed out that DBSTATS does not print the line "Here it is!!!!!". Your job is safe for now.
We have seen a number of similar problems.
TOTAL % OF
CALLING DML CALLS CALLS PAGES READ PAGES WRITTEN PAGES REQUESTED RECORDS REQUESTED RECORDS CURRENT WALL CLOCK BASE
PROGRAM SEQ # OK ERROR TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TIME TIME DML COMMAND
-------- ----- -------- -------- -------- ----- --- ------ ---- --- --------- ------ ----- --------- ------ ----- -------- ---- --- ------------- ----- -----------------------------------------------------
HSLBC009 111 8419 1 0 0.0 0 0 0.0 0 8420 1.0 1 8420 1.0 1 8420 1.0 1 :04.3616 0.7 FIND NEXT HSL-0220 WITHIN SET-0210-0220-1
HSLBC009 112 1 0 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0011 0.0 FIND CURRENT HSL-0210
HSLBC009 113 1 0 1 1.0 1 2 2.0 2 9 9.0 9 9 9.0 9 0 0.0 0 :00.0160 0.0 ERASE HSL-0210
HSLBC009 108 0 1 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0003 0.0 OBTAIN PRIOR HSL-0210 WITHIN SET-0190-0210
HSLBC009 103 1 0 0 0.0 0 2 2.0 2 8 8.0 8 7 7.0 7 1 1.0 1 :00.0007 0.0 STORE HSL-0210
HSLBC009 2 1 0 0 0.0 0 0 0.0 0 4 4.0 4 4 4.0 4 0 0.0 0 :00.0004 0.0 MODIFY HSL-0210
HSLBC009 46 4 0 1 0.3 1 1 0.3 1 32 8.0 8 28 7.0 7 4 1.0 1 :00.0156 0.0 STORE HSL-0220
Here it is!!! Now scroll to the right and check out the time. . .
HSLBC009 53 8416 0 0 0.0 0 0 0.0 0 0 0.0 0 0 0.0 0 0 0.0 0 9:04.7508 91.3 COMMIT
HSLBC009 54 84 0 0 0.0 0 0 0.0 0 587 7.0 7 586 7.0 7 0 0.0 0 :00.2682 0.0 CONNECT HSL-0220 TO SET-0210-0220-2
HSLBC009 50 1 0 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0280 0.0 FIND CURRENT HSL-0210
HSLBC009 51 1 0 0 0.0 0 1 1.0 1 4 4.0 4 4 4.0 4 0 0.0 0 :00.0007 0.0 MODIFY HSL-0210
TOTAL % OF
CALLING DML CALLS CALLS PAGES READ PAGES WRITTEN PAGES REQUESTED RECORDS REQUESTED RECORDS CURRENT WALL CLOCK BASE
PROGRAM SEQ # OK ERROR TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TOTAL AVG MAX TIME TIME DML COMMAND
-------- ----- -------- -------- -------- ----- --- ------ ---- --- --------- ------ ----- --------- ------ ----- -------- ---- --- ------------- ----- -----------------------------------------------------
HSLBC009 111 8419 1 0 0.0 0 0 0.0 0 8420 1.0 1 8420 1.0 1 8420 1.0 1 :04.5776 3.4 FIND NEXT HSL-0220 WITHIN SET-0210-0220-1
HSLBC009 112 1 0 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0003 0.0 FIND CURRENT HSL-0210
HSLBC009 113 1 0 1 1.0 1 2 2.0 2 9 9.0 9 9 9.0 9 0 0.0 0 :00.0252 0.0 ERASE HSL-0210
HSLBC009 108 0 1 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0003 0.0 OBTAIN PRIOR HSL-0210 WITHIN SET-0190-0210
HSLBC009 103 1 0 0 0.0 0 2 2.0 2 8 8.0 8 7 7.0 7 1 1.0 1 :00.0010 0.0 STORE HSL-0210
HSLBC009 2 1 0 0 0.0 0 0 0.0 0 4 4.0 4 4 4.0 4 0 0.0 0 :00.0003 0.0 MODIFY HSL-0210
HSLBC009 46 4 0 1 0.3 1 1 0.3 1 32 8.0 8 28 7.0 7 4 1.0 1 :00.0570 0.0 STORE HSL-0220
Same program, Identical test - - LOOK AT THE RESULTS - WOW!!!
HSLBC009 53 84 0 0 0.0 0 0 0.0 0 587 7.0 7 586 7.0 7 0 0.0 0 :01.3034 1.0 COMMIT
HSLBC009 54 16 0 0 0.0 0 0 0.0 0 0 0.0 0 0 0.0 0 0 0.0 0 :02.8684 2.1 CONNECT HSL-0220 TO SET-0210-0220-2
HSLBC009 50 1 0 0 0.0 0 0 0.0 0 1 1.0 1 1 1.0 1 1 1.0 1 :00.0004 0.0 FIND CURRENT HSL-0210
HSLBC009 51 1 0 0 0.0 0 0 0.0 0 4 4.0 4 4 4.0 4 0 0.0 0 :00.0006 0.0 MODIFY HSL-0210
These are just some of the problems and issues that DBSTATS can help resolve. Performance issues can be due to either database design, programming, or volume. Sometimes it's a combination of one or more factors. To improve performance you must first determine exactly where the bottleneck is located. Attempting to fix a problem when you don't know what's broken is like "trying to catch a bullet in the dark with a pair of pliers."
Solution - Let's buy a bigger computer!
CUSTOMIZED REPORTING FEATURES
Users may customize the reports with a powerful and flexible selection feature. User specified criteria includes:
For each call, the following values are available for reporting:
For each call, the following values are available for reporting in totals, average, maximum, and percentage of totals:
Click here for DBSTATS Release 3.0 PTF's (12.X compatible).
PRICING INFORMATION
HSL offers a site license for DBSTATS. A site license is $16,000 (US). A site is defined as a building where the computer is located. DBSTATS may be run on more than one computer at the eligible site. There are no additional costs if the computer is upgraded.
We do not permit the software to be transferred, re-assigned, or moved. In the event of a disaster, or disaster/recovery testing, the software may be temporarily transferred to a backup site at no additional cost.
We do not license service bureaus. However customers may use the software at a service bureau.
There is an annual required maintenance/renewal fee. This fee is due 12 months from the execution of a license. The fee is 15% of the then current license fee. The current annual maintenance/renewal fee is $2,400 (US). Increases in the annual maintenance/renewal fee are capped at a maximum of 10% per year.
You may terminate a license at any time with 30 days prior written notice. We do not refund any license or maintenance/renewal fees.
fields to be printed
display pictures (number of digits, editing, etc.)
interval between blank lines
report title and headings
report page length and width
logical pages per physical page
initial printer record (i.e. DJDE for XEROX 9700)
sort sequence for report (i.e. DML sequence, total time, etc.)
program name issuing call
DML sequence number
DML syntax
number of times a statement is executed
number of times a statement has a zero or non-zero return code
elaspsed time
pages read
pages written
pages requested
CALC records stored ---- overflow & no overflow
VIA records stored ---- overflow & no overflow
records requested
records made current
fragments stored
record locks requested
number of SR8 splits
number of index levels spawned
SR7s (stored & erased)
SR8s (stored & erased)
index B-Tree searches
levels searched
orphans adopted
Click here for DBSTATS Release 2.02 PTF's (10.X compatible).
Back to HSL.
Hybrid Systems Ltd., Inc.
200 University Park Drive
Edwardsville, IL 62294
US 1-800-779-2802
Canada 1-618-692-4719
E-mail: HSL