DBSTATS, Batch Performance Monitor for CA-IDMS Applications from HSL

DBSTATS

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.


                                                                                
                                                                                                                                         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

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!

                                                                                
                                                                                                                                         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

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.

How many sets are missing owner pointers? Programmers sitting at a member record do an obtain owner, thinking there are owner pointers. In a test environment, with a small set the problem goes undetected. In production, with a long set CA-IDMS walks all the members back to the owners. Run times are excesive and everyone thinks it's just a volume problem.
Solution - Let's buy a bigger computer!
Index sets need to be re-built on a periodical basis. If you miss one performance really suffers.
Does a programmer continue to walk a set to a member record when they should save the DBKEY and advoid needless I/O?
Do you have a bad page size? Are record overflowing? Why are you only getting 120 records per page? With your database design you thought you would get about 250 records per page.
Maybe your performance problems are just volume related. BUT HOW DO YOU KNOW FOR SURE?

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."

CUSTOMIZED REPORTING FEATURES

Users may customize the reports with a powerful and flexible selection feature. User specified criteria includes:

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.)

For each call, the following values are available for reporting:

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

For each call, the following values are available for reporting in totals, average, maximum, and percentage of totals:

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).

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.

AND FINALLY -

WE OFFER NO DISCOUNTS.
EVERYONE PAYS THE SAME PRICE.

We have been selling CA-IDMS software for over 20 years. We think our prices are fair, and are not ashamed of publishing them on the web.

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