SPEEDING UP THAT REAL

1
SPEEDING UP THAT REAL BATCH OF A JOB
Jim Poole
2

• CONTENTS
• REPORTS USED.
• CHANGE OF FILE PROCESSING.
• MULTI-FETCH
• TRACK-LEVEL IO
• ADDITIONAL THOUGHTS
• APPENDIX BASIC ADABAS DIRECT CALLS

3
A. REPORTS USED.
1. OPERATING SYSTEM VIEW EPILOG
2. SUMMARY REPORTS BY FILE BY COMMAND BY . . . .
3. DETAIL REPORTS COMMAND BY COMMAND
4. DATABASE FILE REPORT ADAREP
4
A. REPORTS USED OPERATING SYSTEM VIEW
? ADABAS PERCENT VERSUS OTHER PERCENT ?
TOOLS OMEGAMON, EPILOG, STROBE, SMF/RMF, etc.
5
A. REPORTS USED OPERATING SYSTEM VIEW
Eg. EPILOG (OUR TARGET JOB)
-------------------------------------------------
---------------------------- JOB jobname
JES NUMBER 30156 JOB STEPS
1 / 2 JOB CLASS A ACCT NO
0000099999 INPUT QUEUE .00 S FROM
2213 ON 08/06/01 TO 0951 ON 08/07/01
ELAP 1138 H PRD2 -------------------------
--------------------------------------------------
-- WAIT_REASON_____________TIME______0___1___2
___3___4___5___6___7___8___9___0 USING CPU
1416 M 2.0. . . . . . .
. . . . ECB WAIT 1119 H
97.3-----------------------gtgtgtgtgtgt
. JOB ELAPSED TIME 1138 H
----------------
--------------------------------------------------
-----------
NOTE ECB WAIT SUM.OF.DURATION
SUM.OF.ENQTIME (almost)
6
A. REPORTS USED SUMMARY REPORTS
THE ADABAS VIEW COMMAND LOGGING, CLOGS
TOOLS APAS/INSIGHT, REVIEW, TRIM, HOMEGROWN.
7
A. REPORTS USED SUMMARY REPORTS
BASIC SUMMARY REPORT, DEFINITIONS
DURATION ELAPSED TIME IN ADABAS.
FROM CQ SELECTION THRU THREAD EXECUTION.
CMDTIME TIME IN ADABAS THREAD MINUS IO WAITS
AND THREAD OVERLAPS ( CPU
LIKE)
TOTAL-IO ASSOIO DATAIO WORKIO
(MINUS WRITE IO IF ADARUN LFIOP USED)
(NOT MINUS BLOCKS READ FROM
ADABAS CACHE)
ENQTIME TIME FROM USER TASK TO ADABAS
(UNIQUE TO APAS/INSIGHT)
8
A. REPORTS USED SUMMARY REPORTS
BASIC SUMMARY REPORT, EG. 1
OUR JOB
C SUM SUM
SUM FIL M CMD DURAT CMD
TOT NUM D COUNT SECS
TIME IO --- -- ------------- ----------
---------- --------- 0 CL 1
0.000 0.000 0 0 RC 2
0.000 0.000 0 133 L3
19,728,425 39,225.718 1,335.562 7,318,467
------------- ---------- ---------- ---------
19,728,428 39,225.718 1,335.562 7,318,467
SEE APPENDIX FOR CMD TO NATURAL/SQL EQUIVALENTS.
! NOTE WRITE IO NOT SHOWN IF ADARUN LFIOP USED,
(USE LFIOP)
! NOTE ACTUAL IO MAY BE LESS IF CACHE USED
9
A. REPORTS USED SUMMARY REPORTS
BASIC SUMMARY REPORT, EG. 2
NOT OUR JOB
C SUM SUM
SUM FIL M CMD DURAT CMD
TOT NUM D COUNT SECS TIME
IO --- -- ---------- ---------- ---------
--------- 0 CL 12 0.001
0.000 0 RC 189,384 10.813
1.002 0 RE 1 0.000
0.000 0 14 L3 42 0.406
0.005 40 L9 36 0.004
0.002 1 15 L3 57
0.044 0.008 6 L9 24
0.003 0.002 0 S1 6
0.002 0.001 0 19 L3 402
0.680 0.020 86 L9 6
0.001 0.001 0 S1 3
0.001 0.001 0 31 L3
142,845 865.726 13.731 121,061 48 L3
1 0.032 0.001 4 S1
55,767 875.512 11.437 95,785 133 L3
16,276,311 57,755.623 1,175.272 9,336,796 134
S1 53,118 749.678 11.489 109,449 136
L3 50,065 369.387 6.583 60,711
137 S1 27,638 299.991 6.061
44,528 ---------- ---------- ---------
--------- 16,795,718 60,927.902
1,225.618 9,768,467
? WORST DURATION ?
? WORST CPU TIME ?
10
A. REPORTS USED SUMMARY REPORTS
BASIC SUMMARY REPORT, EG. 3
NOT OUR JOB
C SUM SUM
SUM FIL M CMD DURAT CMD
TOT NUM D COUNT SECS TIME
IO --- -- ---------- -----------
--------- ---------- 0 CL 2
0.000 0.000 0 RC 18,458,400
1,692.233 105.662 0 21 L3
1,320,324 275.315 139.834 2,468 48
L3 12,772,189 127,881.400 1,135.245 9,058,787
86 L3 1,699,256 2,696.412 174.910
231,830 108 L3 137,770 375.070 16.147
29,030 134 L3 18,575,906 24,339.890
1,633.992 3,118,124 136 L3 3,112,323
10,154.249 289.019 1,370,030 137 L3
3,241,708 3,387.474 258.485 407,801 195
L3 149,615 103.501 10.946 11,461
--- -- ---------- ----------- ---------
---------- 59,467,493 170,905.540
3,764.239 14,229,531
11
A. REPORTS USED SUMMARY REPORTS
JOB SUMMARIZE (SUBTOTON)
MIN(L3-SEQUENCE) (HD'L3','SEQ') MEAN(ISNQ)
(PFN3.1) COUNT (PFN6)
SUM(DURATION) (PFN4.3) SUM(CMD-TIME)
(PFN3.3) MEAN(ASSO-IO) (PFN2.3)
MEAN(DATA-IO) (PFN1.2) MEAN(WORK-IO)
(PFN1.2) MEAN(REC-LENGTH-DEC) (PFN4.1)
BY NAT-PROGRAM BY CID (PFH8
SUBTOTOFF) BY FILE (PFN3 SUBTOTOFF)
BY CMD (SUBTOTOFF) WHERE
JOBNAME 'jobname' INSIGHT-LINES400
PRINT TO ADHOC1
FANCY SUMMARY REPORT
12
A. REPORTS USED SUMMARY REPORTS
BASIC SUMMARY REPORT
NOT OUR JOB
C SUM SUM
SUM FIL M CMD DURAT CMD
TOT NUM D COUNT SECS TIME
IO --- -- ---------- ----------
--------- ------------- 0 RC 1,146
0.557 0.004 0 39 L1 169
0.985 0.017 148 S1
1,288 2.915 0.176 487 108 L3
1,288 2.250 0.132 970
---------- ---------- --------- -------------
3,891 6.707 0.329
1,605
13
A. REPORTS USED SUMMARY REPORTS
EXTRACT REPORT COMMAND BY COMMAND DETAIL
EXT EXTRACT (ML10000) TIME CID
(PFH8) FILE (PFN3)
CMD ISNQ (PFN3 HD'ISN','QTY')
REC-LENGTH-DEC (PFN5) DURATION
(PFN1.5) CMD-TIME (PFN1.5)
ISN (PFN8) ASSO-IO
(PFN3) DATA-IO (PFN3)
WORK-IO (PFN3) WHERE JOBNAME(6)
'jobname' INSIGHT-LINES 300 TITLE '
JOB EXTRACT ' PRINT TO ADHOC1
OTHER NIFTY INFO
DATE PROGRAM NAT-PROGRAM FBL RBL
L3-SEQUENCE ADDITIONS-1 COPT1
COPT2 RESPONSE-CODE SUB-CODE
14
A. REPORTS USED SUMMARY REPORTS
EXTRACT REPORT EG 1.
OUR JOB
C REC
FIL M ISN LEN DURAT CMD ASSO
DATA WORK TIME CID NUM D QTY DECMP
SECS TIME ISN IO IO IO ------
-------- --- -- --- ----- ------- -------
---------- --- ---- ---- 210559 20400101 133 L3
0 77 0.02878 0.00072 1,077,296 3 1
0 210559 20400101 133 L3 0 77 0.01061
0.00018 1,141,038 1 1 0 210559 20400101
133 L3 0 77 0.01128 0.00016 1,248,484 1
1 0 210559 20400101 133 L3 0 77 0.04034
0.00014 1,265,866 1 1 0 210559 20400101
133 L3 0 77 0.02984 0.00014 12,380,548 1
1 0 210559 20400101 133 L3 0 77 0.01810
0.00010 12,380,549 0 1 0

15
A. REPORTS USED SUMMARY REPORTS

• DATABASE FILE REPORT ADAREP
• NEED TO ANSWER
• NUMBER OF DATA STORAGE BLOCKS.
• NUMER OF INDEX BLOCKS (NI/MI/UI)
• AVERAGE RECORDS PER BLOCK.
• AVERAGE RECORDS PER TRACK.

• OTHER NICETIES
• REUSEISN 2. REUSEDS
• 3. USERISN 4. PADDING ASSO/DATA
• FORWARD INDEX COMPRESSION
• EXPANDED FILE (VS. 4 BYTE ISN)

16
A. REPORTS USED SUMMARY REPORTS
DATABASE FILE REPORT ADAREP EXAMPLE
FILE 133
(BIG-UGLY-FILE ) 2001-08-15
101158 TOP-ISN
25,982,683 HIGHEST INDEX
LEVEL 4 MAX-ISN EXPECTED 30,000,437
PADDING FACTOR ASSO 3 RECORDS LOADED
25,982,683 PADDING FACTOR DATA 1 MIN-ISN
1 LENGTH OF CLIENT NR
0 LIST I DEV BLOCK I SPACE ALLOC.I
FROM TO I UNUSED SPACE I TYPE I TYPE
LNGTH I BLOCKS CYLI RABN RABN I BLOCKS
CYL I -----I------------I--------------I--------
----------I---------------I AC I 8391 4136 I
21771 120I 1439604 1461374I I
NI I 8391 4136 I 800000 4444I 13689443
14489442I 432318 2401I UI I 8391 4136 I
3000 16I 6144106 6147105I 1598
8I DSST I 8391 4136 I 176 0I 1443
1618I I I I
I I I DS I
8391 10796 I 724886 9665I 566842 1291727I
95614 1274I -----I------------I--------------I---
---------------I---------------I
17
A. REPORTS USED SUMMARY REPORTS
DATABASE FILE REPORT ADAREP EXAMPLE
NOTE RECORDS LOADED IS 25,982,683
LIST I DEV BLOCK I SPACE ALLOC.I FROM
TO I UNUSED SPACE I TYPE I TYPE LNGTH I BLOCKS
CYLI RABN RABN I BLOCKS CYL
I -----I------------I--------------I--------------
----I---------------I AC I 8391 4136 I 21771
120I 1439604 1461374I I NI I
8391 4136 I 800000 4444I 13689443 14489442I
432318 2401I UI I 8391 4136 I 3000 16I
6144106 6147105I 1598 8I DSST I 8391
4136 I 176 0I 1443 1618I
I I I I
I I DS I 8391 10796 I
724886 9665I 566842 1291727I 95614 1274I
IF DS BLKSIZE 5064 (DEFAULT), THEN 10 BLOCKS
PER TRACK
18
EXAMPLE. BATCH JOB XYZ PRIMARY ACCESS READ
LOGICAL ( L3 ) OF FILE 133 JOB RUN TIME
9.5 TO 20 HOURS
12.5 HOURS AVERAGE
DATA FROM PRODUCTION RUN
C SUM SUM
SUM FIL M CMD DURAT CMD
TOT NUM D COUNT SECS
TIME IO --- -- ------------- ----------
---------- --------- 0 RC 6,232
0.289 0.043 0 133 L3 19,722,196
39,225.429 1,335.519 7,318,467
------------- ---------- ---------- ---------
19,728,428 39,225.718 1,335.562 7,318,467
19
NOTE FOR COMPARISONS, ONLY DEAL WITH FILE READ,
NO RC,CL
PRODUCTION
FIL CM CMD COUNT ENQTIME DURATION CMDTIME
TOTALIO 133 L3 19,722,196 39,225.4
1,335.5 7,318,467
FILE DETAIL 26,982,683 RECORDS
629,272 USED DATA STORAGE BLOCKS
369,084 USED INDEX
BLOCKS
ABOUT 73 OF THE FILE IS READ
BATCH JOB IO (7.3M) IS OVER 11 TIMES THE USED
DS BLOCKS !
20
B. CHANGE OF FILE PROCESSING.
RULE IF TOTAL-IO gt gt gt USED DS,
USE READ PHYSICAL (L2) WITH SORT.
THREE STEP APPROACH 1.
READ PHYSICAL (L2) ? 2.
SORT BY KEY ? 3. MAIN
PROGRAM READ WORK FILE
21
B. CHANGE OF FILE PROCESSING. THE RESULTS
CM CMD COUNT ENQTIME DURATION CMDTIME
TOTALIO
L3 19,722,196 39,225.4 1,335.5
7,318,467
PROD
SORT ON KEY 900 SECONDS (SYNCSORT,DISK-TO-DIS
K)
TOTAL 3944 SECONDS 65 MINUTES 44 SECONDS !
22
C. MULTI-FETCH
PREFETCH vs. MULTIFETCH ( PREFNREC ONLY
WITH MLF)
23
C. MULTI-FETCH
PGM
24
C. MULTI-FETCH. CAUTIONS.
MULTIFETCH CAN TAKE RUN TIMES OF HOURS DOWN TO
MINUTES.
MULTIFETCH CAN TAKE RUN TIMES OF MINUTES UP TO
HOURS.
MULTIFETCH JOBS CAN LOCK UP THE DATABASE.
GOAL MAKE BATCH RUN FASTER BUT DO NOT KILL THE
REST OF THE DATABASE!
GUIDELINES (OUR SHOP)
1. MLF JOBS SHOULD RUN OFF-HOURS.
2. NO MORE THAN 2 TO 3 MLF JOBS CONCURRENTLY.
3. IDEAL MLF JOBS ARE NON-UPDATE AND READ
PHYSICAL.
25
C. MULTI-FETCH.
GUIDELINES (OUR SHOP) cont.
3. EACH JOB SHOULD BE PARMED INDIVIDUALLY. ( !
NO PROCS ! )
4. IF JOB CHANGES, PARMS MUST BE REVIEWED.
5. SIZE PARMS TO LIMIT 7 TO 9 IO PER
COMMAND.
6. USE PARMS TO INCLUDE ONLY FILES FOR MLF
SEQUENCE. (Changed with Adabas V7.4)
PREFIFIL / PREFICMD
7. RECORDS PER COMMAND SHOULD BE LESS THAN
RECORDS PER LOOP ( Lx PER RC).
26
C. MULTI-FETCH. OOPS, FORGETTING TO PREFIFIL
PREFETCH with INCLUDE
FIL CMD SUM SUM NUM CM CNT DURAT
IO --- -- --- ------ ---- 0 CL 3 0.000
0 OP 3 0.013 3 RC 55 0.011
0 14 L3 4 0.006 4 L9 8 0.004
1 15 L3 15 0.087 8 L9 2 0.014
1 S1 2 0.057 3 19 L3 141 1.229
80 L9 2 0.001 0 S1 3 0.027
1 108 L2 3 0.072 5 --- ------
---- 241 1.521 106
FUSER
FSEC
FNAT
DATA
27
C. MULTI-FETCH. OOPS, USING A GENERALIZED
APPROACH.
PROGRAM
0200 READ SOURCE-FILE 0300 READ CHECK BY XKEY
SKEY(0200) THRU SKEY(0200)
PREFETCHYES (dflt) PREFIFIL241 PREFICMDL2
PREFETCHYES PREFIFIL241,242 PREFICMDL2,L3
28
C. MULTI-FETCH. PARAMETERS.
SUPPLIED TO // DDCARD INPUT
PREFETCHYES
! ASSUME USE OF MLF PREFNRECn
29
C. MULTI-FETCH. PARAMETERS.
PRENRECX

• LESS THAN AVERAGE RECORDS PER LOOP ( Lxs / RCs
  )

2. IF IN DOUBT, PREFNREC 10
3. IF READ PHYSICAL (L2), PREFNREC RECORDS /
TRACK
4. IF NOT L2, PREFNREC 8 ( COUNT / TOTAL.IO
)
30
C. MULTI-FETCH. BACK TO OUR EXAMPLE.
SEQUENCE IS READ PHYSICAL ( L2 ) RECORDS PER
TRACK 206
31
C. MULTI-FETCH. BACK TO OUR EXAMPLE. THE RESULTS.
CM CMD COUNT ENQTIME DURATION CMDTIME
TOTALIO
L3 19,722,196 39,225.4 1,335.5
7,318,467
PROD
32
D. TRACK-LEVEL IO
1. WORKS BEST ON READ PHYSICAL (L2) JOBS AND
UTILITIES.
2. CAN IMPROVE DATA IO BY 101 ! ( 51 FOR
8391 DEVICES )
3. DEPENDS ON DASD TYPE AN IO METHOD (Eg. EMC)
4. DEPENDS ON DATABASE LOAD.
5. THE DBA MUST BE INVOLVED.
33
D. TRACK-LEVEL IO
ENSURE ADEQUATE DB REGION
PRE-SETUP DB CYCLE
ADARUN CACHEYES ADARUN CBUFNO10 ADARUN
CEXCLUDEPHYS/LOGI/HIST/FIND
RUN JOB
34
D. TRACK-LEVEL IO. THE RESULTS.
CM CMD COUNT ENQTIME DURATION CMDTIME
TOTALIO
L3 19,722,196 39,225.4 1,335.5
7,318,467
PROD
L2 25,982,684 111.4 3,044.5 764.6
629,272
L2
L2 126,133 1.8 1,541.4 522.2
629,272
L2.MLF
35
E. OTHER THOUGHTS.
ADACMP DECOMPRESS INFILE,FORMAT SELECT
FIELDS, ENTIRE FILE. NO UPDATES TO FILE DURING.
SELECTION PROCESS NEEDED FOR DESIRED
RECORDS. SORT NEEDED. ADACMP TIME 1335
SECONDS
ADASTRIP SELECT FIELDS, SELECT RECORDS.
NO UPDATES TO FILE DURING. FIELDS IN FDT
SEQUENCE. SORT NEEDED. ADASTRIP TIME
844 SECONDS
ADAULD SELCRIT/SELVAL ! DO NOT USE !
DOES ADABAS FIND (Sx) TO CREATE ISN LIST
OF RECORDS. ADAULD TIME FOREVER
36
E. OTHER THOUGHTS. SUMMARY OF IMPROVEMENTS.
APPROACH
TIME1 NOTES
1 STEP PROGRAM
READ LOGICAL (PROD)
654 MIN
1 TIME INCLUDES APPROACH SORT , 900 secs.
(IF SORT REQUIRED)
2 TIME DOES NOT INCLUDE SELECT STEP.
FINAL TESTING NOTES BEWARE OF THE BUFFER POOL
DEMON.
37
APPENDIX LIST OF ADABAS COMMANDS/CALLS AND
THEIR NATURAL/SQL EQUIVALENT.
The following is a list of direct call commands
against ADABAS with the NATURAL ADABAS SQL
commands that cause them. Where there are 2
commands separated by a / (such as L1/L4) the
second performs the same function as the first
with the addition of putting the record on hold.
When NATURAL compiles a program, the compiler
determines if there is an update on records
referred to by the original command and then
produces the direct call with appropriate hold
option.

Direct NATURAL Equivalent
ADABAS SQL Equivalent Call
Command



OP (Implicit
OPEN at Signon) CONNECT

CL (Implicit CLOSE at
DBCLOSE at Signoff)




38
APPENDIX

COMMAND NATURAL Equivalent
ADABAS SQL Equivalent


L1/L4 GET READ
ISN (CID)READ BY ISN
READ ISN with the (And
Records from FIND) SEQUENCE OPTION

L2/L5 READ PHYSICAL
READ PHYSICAL CID (default READ)


L3/L6 READ LOGICAL READ
LOGICAL CID
Read by Descriptor
values.

L9 HISTOGRAM
HISTOGRAM CID




39
APPENDIX

COMMAND NATURAL Equivalent
ADABAS SQL Equivalent


S1/S4 FIND FIND
(CID)

S2
FIND... SORTED BY FIND with ORDER BY
(CID)
Clause
S9
(Sorted By) SORT
(CID)

S8 (Compare
ISN Lists) COMPARE
(CID)


40
APPENDIX

COMMAND NATURAL Equivalent
ADABAS SQL Equivalent


A1 (A4)UPDATE UPDATE
(CID)

E1
(E4)DELETE DELETE

N1 STORE
INSERT (CID)


N2 STORE USING NUMBER
INSERT.. WHERE ISNisn (CID)


ET END TRANSACTION COMMIT
WORK
BT BACKOUT
TRANSACTION ROLLBACK WORK



41
APPENDIX

COMMAND NATURAL Equivalent
ADABAS SQL Equivalent


RC (CID)RELEASE RELEASE
Close-Loop
CLOSE


RE GET TRANSACTION DATA READ
USERDATA
LF
(SYSDDM Utility / PREDICT) ????

RI RELEASE
RELEASE ISN
HI
(Put ISN on hold) HOLD ISN

C1/C5 ( None )
????
User-Defined Checkpints

S5 FIND COUPLED
????