MULTIFETCH IN AND OUT OF NATURAL

1
MULTI-FETCHIN AND OUTOF NATURAL
Jim Poole
2
CONTENTS

• WHATS NOT COVERED
• OVERALL
• SYNTAX
• HOW IT WORKS
• NATURAL vs ADAMLF
• UPDATING
• HOW MUCH TO MULTI-FETCH
• CONCERNS, ISSUES

3
WHATS NOT COVERED
PREFETCH
ADAMLF Exclude PREFXFIL/ PREFXCMD
HISTOGRAM L9s
ADAMLF NOT USING PREFNREC
DIRECT CALLS PROGRAMS
4
OVERALL
?WHAT IS MULTI-FETCH ?
RETURN MULTIPLE RECORDS ON ONE ADABAS CALL/CMD
5
OVERALL
TWO APPROACHES TO MULTI-FETCH
1. BATCH
PROGRAM
ADAMLF ADARUN PREF
ADALNK
NATMLF
ADAMLF
NOTE PGM MLF HAS PRECEDENCE
6
SYNTAX ADAMLF
JCL //DDCARD (ALL PREFACED BY ADARUN)

• PREFETCHYES / NO
• PREFSBL130000 MLF BUFFER SIZE/SEQUENCE
• PREFTBLnPREFSBL n MLF SEQUENCES)
• PREFNREC MAX RECS TO MLF
• Code ICMD/IFIL pairs for cmd/fnr to MLF
• ICMD cmds separated by /
• IFIL fnrs separated by ,
• PREFICMDcmd/cmd/cmd
• PREFIFILfnr,fnr,fnr

!! ALWAYS CODE PREFNREC !!
7
SYNTAX ADAMLF EXAMPLES
PREFETCHYES,PREFSBL130000,PREFTBL130000 PREFNRE
C20,PREFICMDL2,PREFIFIL241
FNR 241, ALL L2/L5 WILL MLF UP TO 20 RECORDS PER
L2/L5
8
SYNTAX NATMLF
AVAILABLE WITH NATURAL 4
READ/FIND MULTI-FETCH factor view-name . . .
9
SYNTAX NATMLF RULES
1. READ LOGICAL. WORKS ON BOTH ASCENDING,
DESCENDING.
2. DOES NOT WORK IF DYNAMIC SEQUENCE CHANGES
. . . IN DYNAMIC . . . SEQUENCE . . .
3. ONLY WORKS FOR BROWSE LOOPS (NO RECORD
HOLDS), NO UPDATES.
3a. IF UPDATE,DELETE THEN MLF TURNED OFF (no
error)
4. MLF BUFFER RELEASED WITH REPOSITIONING OR
LOOP-END
5. MLF BUFFER RELEASED AT END OF LOOP (RC CMD)
10
SYNTAX NATMLF MFF REDUCED BY

• READ/FIND (NNN) . . .
• IF MFF gt NNN THEN NNN USED
• IF MFF lt NNN THEN MFF USED (extra records
  may be read)

B. RECORD BUFFER LENGTH (32K). More later
C. ISNQ (NUMBER) FOR FIND STATEMENTS. More later
11
SYNTAX NATMLF. MULFETCH BUFFER
NTDS MULFETCH,nn Or GLOBALS DS(MULFETCH,nn)
nn 0 1024 KILOBYTES OF MEMORY 0
MULTI-FETCH DISABLED / 0 (NATURAL 4.1)
SIZE AS NEEDED (UP TO MAX BUFFER
LENGTH)
12
HOW IT WORKS. DIRECT CALLS
ADABAS NUCLEUS
CB CONTROL BLOCK
CMD L2/L5, L3/L6 L1/L4 where
COP2I or N ILL MAX RECS TO RETURN COP1M or
O CID NON-ZERO,NON-BLANK
FB FORMAT BUFFER
RB RECORD BUFFER
DECOMPRESSED RECORDS
NOTE MAX BUFFER LENGTH 32,768 (ADABAS 7)
NOTE V8 ACBX USES MLF ABD vs IB FOR HEADERS
13
HOW IT WORKS. DIRECT CALLS,eg.
1 EMP VIEW OF EMPLOYEES 2 FIRST-NAME
2 NAME 2 MIDDLE-NAME
READ MULTI-FETCH 10 EMP BY NAME
14
HOW IT WORKS. MAX MFF, PREFNREC
ADABAS V7
MAX AS DETERMINED BY IB
IBL32768 / 16 2048
15
HOW IT WORKS. ET/BT
ADABAS NUCLEUS
CB CONTROL BLOCK
CMD ET, BT COP1 P ILL COUNT OF 6b IB
ENTRIES
IB ISN BUFFER
6 BYTE ENTRIES 2B FILE NUMBER 4B
ISNs TO BE PUT BACK ON HOLD
NOTE ONLY AVAILABLE FOR DIRECT CALL
PROGRAMMING COP1M RELEASE ONLY
ISNS IN IB IB 4b COUNT OF
ELEMENTS n8b ELEMENTS (4b FNR,
4b ISN)
16
HOW IT WORKS. CA-APAS
CB ISNLL MAX MFF RECORDS REQUESTED
CA-APAS
ISN-LOWER-LIMIT ( ISNLL ) equals
0 ISNLL-REPORTED shows MFF
requested ISN-QUANTITY ( ISNQ ) shows
RECS in RB Note ISNQ may not equal
ISNLL-REPORTED
17
CPU COSTS OFMULTI-FETCH
5 RUNS EACH MFF 0 MFF 10 MFF 25 MFF
50 MFF 100 MFF 1000 AVERAGES SHOWN
NO ADABAS DELAY NO IO, NO OTHER USERS NO PGM
DELAY.
18
NATURAL (NATMLF) vs ADAMLF
19
NATMLF PROCESSES
NORMAL CALLS TO ADABAS
ADABAS NUCLEUS
ADALNK
PROGRAM
PROGRAM READS BUFFER
SAVE ON PGM-TO-ADA TIME CQ-THREAD TIME SOME PGM
TIME SOME ADA TIME
PROGRAM READS BUFFER
20
ADAMLF PROCESSES
ASYNC CALLS TO ADABAS
ADABAS NUCLEUS
ADALNK
ADAMLF
PROGRAM
½ PREFSBL ½
WHILE PROGRAM PROCESS BUFFER, ADABAS FILLS NEXT
BUFFER
21
ADAMLF PREFSBL USAGE
PREFSBL 1 / 2 1 / 2
1 / 4
IB
IB
3 / 4
RB
RB
22
ADAMLF WARM UP
NOT USED IN NATMLF
DETERMINE RBL ( PREFSBL / 2 ) X (3/4)
MAX RECORDS RETURNED ON EACH MLF ADAMLF CALL
1ST CALL - ONE RECORD RETURNED (NO MLF) 2ND
CALL - NUMBER OF RECORDS THAT CAN FIT IN 1/8
RBL 3RD CALL - NUMBER OF RECORDS THAT CAN FIT
IN 1/8 RBL 4TH CALL - NUMBER OF RECORDS THAT
CAN FIT IN 1/2 RBL 5TH CALL - NUMBER OF
RECORDS THAT CAN FIT IN RBL 5n CALL -
NUMBER OF RECORDS THAT CAN FIT IN RBL
23
ADAMLF WARM UP Example
PREFSBL 65K, RBL ( 65K / 2 ) X (3/4) 24,573
PREFNREC 200
NOTE CA-APAS ISNQ RECS MLFD
24
ADAMLF vs NATURAL MLF (overall)
ADAMLF
NATURAL/PROGRAM MLF
1. ASYNC PROCESSING SYNC PROCESSING
2. BATCH ONLY
ONLINE OR BATCH
3. NO PROGRAM CHANGES CHANGED
WITHIN PROGRAM
4. PREFNREC FOR ALL SEQs VARY FOR _at_
ACCESS LOOP
5. WARMS UP
WHAT YOU CODE, YOU GET
6. CAN DO UPDATES ! NO
! UPDATING
7. CONTROL VIA //DDCARD CONTROL
WITHIN PROGRAM (PROCS, PDS MBRs, ETC.)
25
ADAMLF vs NATURAL MLF (Performance)
PERFORMANCE (PGM ELAPSED TIME)

• MULTI-FETCH SAVES ON
• PATHLENGTH
• SOME ADABAS RESPONSE
• SOME PGM TIME TO PROCESS CALLS

ADAMLF IMPROVES AS PGM DELAY INCREASES
26
ADAMLF vs NATURAL MLF (Performance)

• IN THE LAB NATMLF vs ADAMLF
• MFF100
• 800,000 RECORDS
• READ PHYSICAL
• MEASURE PGM ELAPSED TIME
• PGM CPU TIME
• ADABAS RESPONSE TIME
  (DURATION)
• ADABAS RELATIVE CPU TIME
  (CMDTIME)
• SCENARIOS
• NO DELAY
• ADABAS NO IO, NO OTHER USERS
• NATURAL READ LOOP ONLY
• B. ADABAS DELAY (IO)
• C. ADABAS/PGM DELAY (LOTSA MOVE STATEMENTS)
• FOR EACH SCENARIO, MULTIPLE RUNS NATMLF,ADAMLF

RESULTS
27
ADAMLF vs NATURAL MLF (Performance)
A. NO DELAY
28
ADAMLF vs NATURAL MLF (Performance)
B. ADABAS DELAY
29
ADAMLF vs NATURAL MLF (Performance)
C. DELAY PGM/ADA
30
MULTI-FETCH AND UPDATING( ADAMLF )
31
ADAMLF. UPDATES. ET
FOR EXAMPLE
REVIEW
RD1. READ TEST PHYSICAL IF AM-FIELD 'AA'
THEN AM-FIELD 'BB' ELSE AM-FIELD 'AA'
END-IF UPDATE (RD1.) ETCNT ETCNT 1 IF
ETCNT ETLIMIT END TRANSACTION ETCNT
0 END-IF END-READ /(RD1.) END TRANSACTION
Lx CB COP1O ET CB COP1P IB 6b fnr/isn
to put back on HQ
PREFNREC100 PREFIFIL241,PREFICMDL2
RESULTS
32
ADAMLF. UPDATES. ET
ETLIMIT 10
CM COP1 ILL IBL L5 O 1 16,247 A1
0 0 L5 O 100 16,247 L5
O 100 16,247 9 A1'S ET P
191 1,146 10 A1'S ET P 181
1,086 10 A1'S ET P 101 606 10
A1'S L5 O 100 16,247
RC S 0 0 RI
0 16,247 RI 0 16,247

? 1ST CALL
? UPDATE 1ST RECORD
? 1ST MLF CALL, 100 RECs
? 2nd ASYNC MLF CALL, 100 RECs
? 9 MORE UPDATES
? 191 RECS BACK ON HOLD
? 10 MORE UPDATES
? 181 RECS BACK ON HOLD
? 3rd ASYNC MLF CALL, 100 RECs
? RECS ON HOLD AT LOOP END
33
ADAMLF. UPDATES. ET
ETLIMIT PREFNREC 100
(NOTE A1s NOT ON REPORT)
CM COP1 ILL IBL L5 O 1 16,247 L5 O
100 16,247 L5 O 100 16,247 ET P 0
606 L5 O 100 16,247 ET P 0 606 L5
O 100 16,247 ET P 0 606 L5 O 100
16,247 ET P 0 606
RC S 0 0

• EVERY ET PUTS 101 RECS
• BACK ON HOLD

!! CANNOT SYNC ETS WITH PREFNREC !!

• EOF, NO RIs

MIN ADARUN NISNHQ2 X PREFNREC
34
ADAMLF. UPDATES. ET COSTS
ADABAS AVG CPU TIME / ET
NREC 0 25
50 100 200
35
ADAMLF. UPDATES. ET COSTS
ADABAS SUM CPU TIME ETs
NREC 0 25
50 100 200
ET COSTS INCREASE AS PREFNREC INCREASES
36
ADAMLF. UPDATES. UPD/DELETE A1/E1
RECORD BUFFER
CURRENT RECORD
RECORD 1 RECORD 2 RECORD 3 RECORD 4 RECORD
5
FOR EVERY UPDATE PREFSBL BUFFER SCANNED TO SEE IF
ISN IN YET-TO-BE-READ RECORDS ( NOT CURRENT
RECORD )
INCREASE IN PROGRAM CPU/ELAPSED TIME
NO INCREASE IN ADABAS TIMES (NEITHER CPU NOR
DURATION)
? MEASURE CPU COSTS ?
TEST PROGRAM
37
ADAMLF. UPDATES. A1/E1 COSTS
DEFINE DATA LOCAL
1 CPU-PGM-ALL (I4) INIT lt0000gt / TIME
IN 10 MS UNITS 1 CPU-UPD-ALL (I4) INIT lt0000gt
/ TIME IN 10 MS FOR UPDS 1 CPU-UPD (I4)
/ CPU TIME COUNTER 1 TEST VIEW
OF SOME-FILE

END-DEFINE
ST. SETTIME
ASSIGN CPU-PGM-ALL
CPU-TIME RD1. READ
TEST PHYSICAL

ASSIGN CPU-UPD CPU-TIME /
UPDATE (RD1.)
ASSIGN CPU-UPD
CPU-TIME - CPU-UPD ASSIGN
CPU-UPD-ALL CPU-UPD-ALL CPU-UPD

END-READ /(RD1.)
ASSIGN CPU-PGM-ALL CPU-TIME
- CPU-PGM-ALL WRITE 'DA-TIME'
TIME TIMD(ST.) (EM999999'.'9) WRITE
'DA-PGM-CPU' CPU-PGM-ALL
WRITE 'DA-UPD-CPU' CPU-UPD-ALL
VARY PREFNREC
RESULTS
38
ADAMLF. UPDATES. A1/E1 COSTS
SUM PROGRAM CPU TIMEs
TOTAL
TOTAL
TOTAL
TOTAL
A1
A1
A1
A1
ET
ET
ET
ET
NREC ----- 0 ----- ---- 100 ----
---- 400 ---- ---- 800 ----
UPDATE COSTS INCREASE AS PREFNREC INCREASES
39
ADAMLF. UPDATES. A1/E1 VIA REREAD
FOR EVERY READ, REREAD (L4) THEN UPDATE
NO INCREASE IN ET TIMES
NO INCREASE IN ADABAS TIMES
MAJOR INCREASE IN PROGRAM TIMES
MEASURE CPU COSTS
RESULTS
40
ADAMLF. UPDATES. A1/E1 VIA REREAD
SUM PROGRAM CPU TIMEs
TOTAL
TOTAL
TOTAL
TOTAL
A1
A1
A1
A1
NREC ----- 0 ----- ---- 100 ----
---- 400 ---- ---- 800 ----
!! DONT REREAD RECORD AND UPDATE !!
41
ADAMLF. UPDATES. OUTSIDE OF LOOP
RB
CURRENT RECORD
FOR EXAMPLE 1. MLF READ 2. UPDATE/DELETE
YET-TO-BE-READ-RECORD 3. IF THAT RECORD IS IN
PREFSBL, IT WILL BE REREAD BY ADAMLF (L1/L4)
RECORD 1 RECORD 2 RECORD 3
FOR EXAMPLE READ MLF LOGICAL GET
isn-x/UPDATE/REC (isn-x not yet read )
!! CAN CAUSE OUT-OF-SEQUENCE FOR READ LOGICAL !!
42
SIZING MFF
43
SIZE MFF/PREFNREC. UPPER LIMIT
1. 2048
(IBL32K/16 ADABAS V7)
2. 32K / (RECORD.SIZE) (ADABAS V7)
3. ISN-QUANTITY FOR FIND (Sx) STATEMENTS
4. RECORDS PER LOOP (COUNTER)
44
SIZE MFF/PREFNREC. VARY VALUE IF
45
SIZE MFF/PREFNREC.
1. (PERSONAL PHILOSOPHY) GOAL OF 7 TO 10
IOS PER COMMAND. READ PHYSICAL, USE ADAREP,
RECS/BLOCK X 10 READ LOGICAL, DETERMINE
AVERAGE IO/CMD
2. J / D (ROUND UP
TO INTEGER) J ELAPSED TIME OF JOB (IDEALLY
READ SEQUENCE) D SUM OF ADABAS DURATION
FOR SEQUENCE
3. START SMALL (5,10,20, . . . ) MONITOR
CPU, ELAPSED TIMES. ! DO NOT INCREASE IF
ELAPSED TIME DOES NOT INCREASE !
ANALYSIS REPORT for 1, 2
46
SIZE MFF/PREFNREC.
CA-APAS
MCIMLF SUMMARIZE(SUBTOTOFF) MIN(TIME)
MAX(TIME) COUNT (PFN7)
SUM(TOTAL-IO) (PFN7) SUM(DURATION) (PFN4.2)
SUM(ENQ-TIME) (PFN4.2) SUM(CMD-TIME)
(PFN4.2) MEAN(RBL) (PFN3.1) WHERE
FILE 241 AND CMD 'L2 AND CID
X'02000101' AND NAT-PROGRAM 'TPGM' AND
JOBNAME 'THISJOB PRINT TO ADHOC1
INSIGHT-LINES600
ANALYSIS REPORT for 1, 2
TO ENSURE ONLY 1 SEQUENCE IS MEASURED
QUALIFY SEQUENCE
FOR EXAMPLE
47
SIZE MFF/PREFNREC. Eg. Method 1,2
SUM SUM SUM
SUM MIN MAX CMD TOT DURAT
ENQTIM CMD MEAN TIME TIME COUNT
IO SECS SECS TIME RBL EXAMPLE 1 154931
163551 1,103,425 179,865 530.76 939.96 70.14
184.0 EXAMPLE 2 160926 161841 4,460 6,604
61.07 0.02 1.11 204.0
48
SIZE MFF/PREFNREC. AGAIN 3
3. START SMALL (5,10,20, . . . ) MONITOR
CPU, ELAPSED TIMES. ! DO NOT INCREASE IF
ELAPSED TIME DOES NOT INCREASE !
PERFORMANCE (PGM ELAPSED TIME)
ADABAS RESPONSE (CPU,IO,OVERLAP)
PGM PROCESS ADABAS CALLS
PATHLENGTH TO/FROM PGM-ADABAS
PGM DELAY (CPU,IO,ETC.)
EXAMPLES
49
SIZE MFF/PREFNREC.
MINIMAL PGM DELAY MINIMAL
PATHLENGTH
PGM ELAPSED TIME
STOP
NREC - 0 - - 25 - - 50 -
- 100 - 200 - - 400 -
50
SIZE MFF/PREFNREC.
PGM DELAY Lotsa MOVE stmts
MINIMAL PATHLENGTH
PGM ELAPSED TIME
PATHLENGTH
PROGRAM TIME
ADABAS TIME
STOP
NREC 0 - 5 - - 10 - -
20 - - 30 - - 60 - - 400 -
51
SIZE MFF/PREFNREC.
PGM DELAY Lotsa MOVE stmts
PATHLENGTH Different LPAR
PGM ELAPSED TIME
STOP
NREC - 0 - - 5 - - 10 -
- 20 - - 30 - - 60 - - 400 -
52
CONCERNS / ISSUES
53
CONCERNS. ADARUNs
ADARUN
IF TOO SMALL
LU65K
152
NAB
254,255
NISNHQ 2 X PREFNREC
47
NH
9 sub 1
NT
performance
NC
151
54
CONCERNS. LOOP LIMITS
EXAMPLE
RD1. READ FILE-X RD2. READ MFF FILE-Y
THEDE A THRU A LOOP LOOP
COUNTER(RD2.)10 to 15
IF MFF 10, 0 TO 5 EXTRA RECORDS READ EACH
TIME
IF MFF 100, 90 TO 85 EXTRA RECORDS READ EACH
TIME
55
CONCERNS. LOOP EXITS
ESCAPE . . . READ view WITH REPOSITION BY . .
.
56
CONCERNS. RECORD SIZE
EXAMPLE RECORD EXTRACTION JOB
LRECL 10,000 (eg. PE, MU, LA, etc) MAX MFF
3
USE OF COMMON VIEWS VIA PROGRAM, LDA, GDA. EG.
LDA DEFINES 2000 BYTES OF DATA EG. PROGRAM WITH
LDA USES ONLY 50 BYTES OF DATA MAX MFF 16
WITH LDA MAX MFF 640 WITHOUT LDA
NOTE IF VIEW SIZE CHANGES MFF LIMIT CHANGES
57
CONCERNS. GENERALIZATIONS
AVOID

• COMMON PROCS
• COMMON ADARUN PDS MEMBERS
• USING ADAMLF PREFXFIL,PREFXCMD, EXCLUDES
• COMMON VIEWS,LDAS,GDAS (LARGER RECS)

58
CONCERNS. MADIO
MADIO LIMITS THE NUMBER OF ADABAS CALLS
READ (9000) MULTI-FETCH 100 DA-FILE . . .
! DOES NOT FAIL !
59
CONCERNS. DB PERFORMANCE
HIGH IO MULT-FETCH CALLS CAN IMPACT OTHER DB
USERS. ADAMLF (ASYNC) HIGH IO MLF CALLS CAN
IMPACT OTHER USERS.
NOTE NATURAL NUCLEUS USES MLF AGAINST SYSTEM
FILES FNAT YES FUSER
YES FDIC - NOT AT THIS TIME
FSEC - NOT AT THIS TIME ? ADD
ON PRODUCTS ? ? FASTPATH ?
SAMPLE PERF REPORT
60
CONCERNS. DB PERFORMANCE
CA-APAS
PART 1
DECLARE FETCH (A3) VALOF IF
COPT1 'M' RESULTIS 'YES' ELSEIF
COPT1 'P' RESULTIS 'YES' ELSEIF COPT1
'O' RESULTIS 'YES' ELSE
RESULTIS ' ' IFEND
61
CONCERNS. DB PERFORMANCE
CA-APAS
PART 2
MCIMLFS SUMMARIZE(SUBTOTON GTTNO)
COUNT
MEAN(ENQ-TIME) (PFN2.5)
MEAN(DURATION) (PFN2.5)
MEAN(CMD-TIME) (PFN1.5)
MEAN(TOTAL-IO) (PFN2.2)
MEAN(ECBS) (PFN2.2)
MEAN(DATA-IO) (PFN1.2)
MAX(FETCH) (HD'MLF?') BY
JOBNAME WHERE
OMIT FUSER/FNAT FILES FILE NE
14 AND FILE NE 19 INTERVAL 15 MINUTES
PRINT TO SYSOUTX
INSIGHT-LINES 400
MLF
62
CONCERNS. DB PERFORMANCE
CA-APAS
OPTIONs
DRILL DOWN OPTIONS

• WHERE
• JOBNAME 'X'
• TP-TRANS-NAME NATX
• CID X02000101
• BY
• BY TP-TRANS-NAME
• BY NATURAL-LOGON
• BY NAT-PROGRAM
• MAX(ISNLL-REPORTED) (PFN3) CAUTION

SAMPLE PERF REPORT
63
CONCERNS. DB PERFORMANCE
DOCTORD REPORT
MEAN
MEAN MEAN MEAN MEAN MEAN CMD
ENQTIME DURAT CMD TOT PST DATA JOB
NAME COUNT SECS SECS TIME IO ECB
IO MLF CICS00BH 27,097 0.00002 0.00032 0.00012
0.06 5.29 0.03 CICS000A 29,512 0.00002 0.00031
0.00014 0.03 5.52 0.01 CICS000B 15,820 0.00003
0.00082 0.00048 0.16 5.36 0.32 YES CICS000C
8,594 0.00002 0.00031 0.00015 0.03 5.29 0.01
CICS000D 9,252 0.00003 0.00035 0.00014 0.05
5.36 0.02 CICS000I 1,525 0.00071 0.00035
0.00008 0.05 5.70 0.02 JOB00001 3,516 0.01245
0.00046 0.00007 0.10 5.27 0.04 JOB00004 7,079
0.00002 0.00339 0.00020 0.83 5.46 0.62 JOB00004
8,030 0.00001 0.01434 0.00893 5.23 5.88 4.55 YES
64
ADABAS 2006, V8 NOTES

• FOR DIRECT CALLS (NEW ACBX)
• MULTI-FETCH BUFFER REPLACES ISN-BUFFER FOR
  HEADERS
• 32K LIMIT REMOVED FROM RECORD BUFFER.
• FOR EXAMPLE

65
DAS ENDE