ADABAS TIPS PROGRAMMERS

1
ADABAS TIPS FOR PROGRAMMERS
Jim Poole
2
1. FAST UPDATING WITH READ PHYSICAL
3
2. UPDATING INVERTED LISTS
AVOID UPDATING INVERTED LIST WITH SUCCESSIVELY
LOWER VALUES
4
3. FIELDS IN FILE VIEW
LIMIT VIEWS TO JUST DESIRED FIELDS
5
3. FIELDS IN FILE VIEW
EXAMPLE 1
INCREASE
BUFFER LENGTH 115 BYTES
1718 BYTES ELAPSED TIME
9.4 MIN. 14.7 MIN.
56 USER CPU
-----
----- 90 DATABASE CPU
-----
----- 70
6
3. FIELDS IN FILE VIEW
EXAMPLE 2
RELATIVE CPU TIME TO DECOMPRESS X FIELDS
60 Flds
50 Flds
40 Flds
30 Flds
20 Flds
10 Flds
5 Flds
! SELECT/VIEW FOR ONLY NEEDED FIELDS !
7
4. FILE DESIGN, FIELD SEQUENCE
8
4. FILE DESIGN, FIELD SEQUENCE
RELATIVE CPU TIME TO DECOMPRESS 1 FIELD
Fld 261
Fld 200
Fld 152
Fld 100
Fld 49
Fld 1
! MOST FREQUENTLY USED FIELDS UP FRONT !
9
5. FILE DESIGN NU,FI,dflt

• NU NULL SUPPRESS UNLESS DE ISSUES OR SOURCE FOR
  DE
• DEFAULT- FOR ALL ELSE
• -FI FIXED. SELDOM NULL, ALWAYS FULL (SAVE
  LENGTH BYTE)
• SAVE SPACE ON 1 BYTE FIELDS
• ONLY IF NEVER EVER EVER GOING TO CHANGE
  LENGTH
• Eg. YY

FI vs NU . . . . . . .
10
5. FILE DESIGN NU,FI,dflt. Eg 1
FI vs (NU,DEFAULT) COMPRESSION TEST WHOLE
FILE
11
5. FILE DESIGN NU,FI,dflt. Eg. 1
FI vs (NU,DEFAULT) COMPRESSION TEST WHOLE
FILE
RUN PGM A
IO RECORD CREATE GLOBAL IFBs FILE 1 / FILE
2 5, 10, 20, 30, 40, 50, 60 FIELDS
RUN PGM B 5X
LOOP 30K TIMES READ RECORD FOR FILE 1 /
FILE 2 5, 10, 20, 30, 40, 50, 60 FIELDS
REPORT RELATIVE CPU TIME
12
5. FILE DESIGN NU,FI,dflt . Eg. 1
FI vs (NU,DEFAULT) DECOMPRESSION TEST WHOLE
FILE
60 Flds
50 Flds
40 Flds
30 Flds
20 Flds
10 Flds
5 Flds
! FIXED IS MORE CPU !
13
5. FILE DESIGN NU,FI,dflt. Eg 2
FI vs NU vs DFLT DECOMPRESSION OF ONE 1b FLD.
FIELD FULL
KEY NU FIXED DfLT
Test runs
14
5. FILE DESIGN NU,FI,dflt. Eg 2
FI vs NU vs DFLT DECOMPRESSION OF ONE 1b FLD.
FIELD EMPTY
KEY NU FIXED DfLT
Test runs
15
5. FILE DESIGN NU,FI,dflt. Eg 3
FI vs NU vs DFLT COMPRESSION 20b FIELDS. FIELDS
FULL
40 Flds
KEY NU FIXED DfLT
20 Flds
5 Flds
16
5. FILE DESIGN NU,FI,dflt . Eg 3
FI vs NU vs DFLT COMPRESSION 20b FIELDS. FIELDS
EMPTY
40 Flds
KEY NU FIXED DfLT
20 Flds
5 Flds
17
6. CIDs GFIDs
USE CID TO SAVE ON FORMAT TRANSLATIONS
RELEASE CIDs (RC) WHEN DONE
USE GFIDs FOR COMMON VIEWS
DO NOT USE GFIDs FOR VIEWS RARELY USED
18
7. DESCRIPTORs
COST
BEST IF
DISK SPACE UPDATE IO/CPU
MORE UNIQUE/SELECTIVE SMALLER LESS UPDATED
19
8. TESTING
STANDARDS, GUIDELINES AND GOLDEN RULES MAY ONLY
BE GOOD UNTIL THE NEXT RELEASE OF ADABAS OR
NATURAL
DIRECT CALLS DDLOG APAS/INSIGHT
(REVIEW,TRIM,CLOG) CPU vs IO vs THRUPUT
20
9. HOLD CONTENTION
EXAMPLE (NO RETURN LOGIC)
USER-A HOLDS ISN 1 USER-B REQUESTS HOLD ISN
1 USER-B WAITS UNTIL USER-A DOES ET,BT,CL,TIMEOUT
CODE RETURN OPTION WHERE POSSIBLE (RETURN RSP.CD
145) DIRECT CALLS COP1R NATURAL
NATURAL SECURITY / WHOFF NATIVE SQL
RETURN
21
10. DEADLY EMBRACE LOGIC
ADABAS DEADLY EMBRACE LOGIC (NO RETURN LOGIC)
USER A HOLDS ISN 1 USER B HOLDS ISN 2 USER A
REQUESTS HOLD ISN 2 USER B REQUESTS HOLD ISN
1 -USER B RECEIVES RSP.CD. 145-9, THEN 9-3
(TIMED-OUT)
22
11. ET TIMING
INFREQUENT
FREQUENT
HOLD CONTENTION TIMEOUT RISKS (rsp 9-2) WORK
WRAPAROUND (rsp 9-15,8) DELAY DB
BACKUP DELAY NUC SHUTDOWN
EXCESSIVE COMMANDS IO DELAY (DBA LFIOP / FAST
DASD)
23
12. FILE DESIGN GROUPS
EXAMPLE 1
01,AA,10,A. 01,BB,05,P. 01,CC,30,A. 01,DD,10,A. 01
,EE,12,A,NU,DE. 01,FF,30,A.
AA,BB,CC,DD,EE,FF.
FB
ADABAS NATIVE SQL
24
13. PE ACCESS
B. ELEMENT RANGE

• GROUP
• RANGE

C. FIELD LIST
D. ADABAS GROUP
02 JB-FLD (11) 02 JC-FLD (11) 02 JD-FLD
(11) 02 JE-FLD (11) 02 JF-FLD (11) 02 JN-FLD
(11) 02 JO-FLD (11) 02 JG-FLD (11) 02 JH-FLD
(11) 02 JI-FLD (11) 02 JJ-FLD (11) 02 JK-FLD
(11) 02 JL-FLD (11) 02 JM-FLD (11) 02 JB-FLD
(22) 02 JC-FLD (22) 02 JD-FLD (22)
02 JL-FLD (55) 02 JM-FLD (55)
02 JA-GRP(15) 03 JB-FLD 03 JC-FLD 03
JD-FLD 03 JE-FLD 03 JF-FLD 03 JN-FLD
03 JO-FLD 03 JG-FLD 03 JH-FLD 03 JI-FLD
03 JJ-FLD 03 JK-FLD 03 JL-FLD 03 JM-FLD
02 JA-GRP 03 JB-FLD (15) 03 JC-FLD (15) 03
JD-FLD (15) 03 JE-FLD (15) 03 JF-FLD (15) 03
JN-FLD (15) 03 JO-FLD (15) 03 JG-FLD (15) 03
JH-FLD (15) 03 JI-FLD (15) 03 JJ-FLD (15) 03
JK-FLD (15) 03 JL-FLD (15) 03 JM-FLD (15)
JA1-5.
25
13. PE ACCESS FB.
RB len 345
26
13. PE ACCESS IFB.
ADABAS FB-IFB TRANSLATION TIME
FBL 910 IFBL 1740
FBL 252 IFBL 1740
FBL 910 IFBL 1740
FBL 10 IFBL 84
TEST RAN EACH 25K TRANSLATIONS, FOR 2 RUNS
27
13. PE ACCESS TEST.
FACTORS

• CALLS PER TRANSLATION (eg. 50)
• ELEMENTS PER PE GROUP (eg. 14)
• OCCURRENCES PER PE (eg. 5)

NOTE ALL BLOCKS IN BUFFER POOL, NO IO
28
13. PE ACCESS NAT.CPU.
NATURAL RELATIVE CPU TIME

• GROUP
• RANGE

B. ELEMENT RANGE
C. FIELD LIST
29
13. PE ACCESS ADA.CPU.
ADABAS RELATIVE CPU TIME FOR ALL CALLS

• GROUP
• RANGE

B. ELEMENT RANGE
C. FIELD LIST
D. ADABAS GROUP
30
13. PE ACCESS ADA.CPU.ADJUSTED
ADABAS RELATIVE CPU TIME FOR READS (NO
TRANSLATIONS)

• GROUP
• RANGE

B. ELEMENT RANGE
C. FIELD LIST
D. ADABAS GROUP
CONCLUSIONS?
31
14. EXCLUSIVE CONTROL FOR APPS
STANDARD OPEN WITH EXUfnr OR EXFfnr
APPLY ADABAS SECURITY FILEfnr, UPD / READ
DB-LEVEL DB READONLY
SINGLE-USER-MODE SEPARATE
SVC
32
15. RC COMMANDS
! ARE NOT EXPENSIVE !
RELEASE RESOURCES ISN LISTS (TBI),
SEQ.COMMANDS (TBQ), FORMAT BUFFERS (LFP)
NATURAL LOOP,PGM-END,WHENEVER
DATABASE NO IO /CHAINED/ OFF UQ TO
TBI,TBQ,LFP
33
16. BEWARE WHERE
ADABAS WORK
FIND . . . . WITH .. READ . STARTING FROM
. HISTOGRAM . STARTING FROM
ENDING AT THRU
WHERE ACCEPT REJECT IF
NATURAL WORK
IF IN DOUBT, DDLOG/CMD.LOG
34
16. WHERE/ACCEPT/REJECT/IF
35
17. NATURAL FIND TIPS
( AS OF NAT 4)
a. SEQUENCE QUERY FROM LEAST LIKELY TO MOST LIKELY
b. DBA SHOULD SET ADARUN NSISN FOR FREQUENT ISN
QTY
c. USE DE NAME ONCE AND ONLY ONCE.
d. AVOID PARENS WHEREVER POSSIBLE.
e. TO RETURN 1 RECORD, 1 DE, 1 VALUE, USE READ
(1)
36
18. ADAREP COUNT / NOCOUNT
USE NOCOUNT AS THE DEFAULT
133 FILES 230M RECORDS 8391 BLOCKSIZE
(A4136b)
DBnnn
37
19. FILE COUPLING
READ ONE FILE BASED ON 2ND FILE
LOGICAL COUPLING
FIND EMPLOYEE WITH NAME 'JONES' FIND
VEHICLES-ADA PERSONNEL-ID PERSONNEL-ID(0020)
AND MAKE 'RENAULT' DISPLAY
PERSONNEL-ID MAKE MODEL COLOR LOOP LOOP
38
19. LOGICAL COUPLING
----IO----
CM FNR ISN ISQ OP CID A D W
S1 16 205 10 .. 00200101 6 1 0 S1 18 0
0 .. 00300101 5 0 0 L1 16 565 0 .N
00200101 0 1 0 S1 18 0 0 .. 00300101 0
0 0 L1 16 573 0 .N 00200101 0 0 0 S1 18
60 1 .. 00300101 3 1 0 RC 0 0 0 SI
00300101 0 0 0 L1 16 691 0 .N 00200101 0
1 0 S1 18 70 1 .. 00300101 0 0 0 RC 0
0 0 SI 00300101 0 0 0 L1 16 789 0 .N
00200101 0 1 0 S1 18 0 0 .. 00300101 0
0 0 L1 16 792 0 .N 00200101 0 0 0 S1 18
0 0 .. 00300101 0 0 0 L1 16 800 0 .N
00200101 0 0 0 S1 18 0 0 .. 00300101 0
0 0 L1 16 817 0 .N 00200101 0 1 0 S1 18
0 0 .. 00300101 0 0 0 L1 16 960 0 .N
00200101 1 1 0 S1 18 80 1 .. 00300101 0
1 0 RC 0 0 0 SI 00300101 0 0 0 L1 16
999 0 .N 00200101 0 1 0 S1 18 0 0 ..
00300101 0 0 0
SBAE,20,A.
VBJONES
SBAA,15,A,D,AD,20,A
VB20007500 RENAULT
39
19. SOFT COUPLING
----IO----
CM FNR ISN ISQ OP CID A D W
S1 18 60 3 .. 00200101 15 8 2 L1 18 70
0 .N 00200101 0 0 0 L1 18 80 0 .N
00200101 0 1 0
SB(18,AA,16,AA)/18/AD,20,A,D,/16/AE,20,A.
VBRENAULT JONES
40
19. FILE COUPLING
LOGICAL VS SOFT
LOGICAL COUPLING
SOFT COUPLING
5 DATABASE CALLS 50 DATABASE IOs RELATIVE CPU
TIME ABOUT
25 DATABASE CALLS 48 DATABASE IOs RELATIVE CPU
TIME ABOUT
41
20. EOR ,DE ,empty fields
REVIEW DESCRIPTORS. SOURCE FIELD NU vs
(NOT-NU OR FI)
FIND w/ (AX , S1) READ by (AX , S1) HISTOGRAM
(AX , S1)
42
20. EOR ,DE ,empty fields
EOR END OF RECORD
ADABAS UTILITY UPDATES
ALL FIELDS DEFINED ACCORDING TO COMPRESSION
OPTIONS (NU,non-NU)
Non-NU TREATED AS non-NU
USER PROGRAM UPDATES
FIELDS NOT REFERENCED AT EOR ARE NOT DEFINED
Non-NU TREATED AS NU
43
20. EOR ,DE ,empty fields
DB Name F Len S D AA AA-FLD A 5 N BB
BB-FLD A 5 D CC CC-FLD A 5 S SS SS-SUP
A 10 N S - SOURCE FIELD(S)- AA-FLD(1-5)
CC-FLD(1-5)
EXAMPLE NEW FILE
ADACMP gt ADALOD AA AAAAA
FIND file BB-FLD DISPLAY ISN
FIND file SS-SUP DISPLAY ISN
RESULTS 1
RESULTS 1
44
20. EOR ,DE ,empty fields
DB Name F Len S D AA AA-FLD A 5 N BB
BB-FLD A 5 D CC CC-FLD A 5 S SS SS-SUP
A 10 N S - SOURCE FIELD(S)- AA-FLD(1-5)
CC-FLD(1-5)
EXAMPLE STORE NEW RECORD
STORE AA-FLD AAAAA
45
20. EOR ,DE ,empty fields
DB Name F Len S D AA AA-FLD A 5 N BB
BB-FLD A 5 D CC CC-FLD A 5 S SS SS-SUP
A 10 N S - SOURCE FIELD(S)- AA-FLD(1-5)
CC-FLD(1-5)
EXAMPLE STORE NEW RECORD
STORE AA-FLD AAAAA BB-FLD
46
20. EOR ,DE ,empty fields
DB Name F Len S D AA AA-FLD A 5 N BB
BB-FLD A 5 D CC CC-FLD A 5 S SS SS-SUP
A 10 N S - SOURCE FIELD(S)- AA-FLD(1-5)
CC-FLD(1-5)
EXAMPLE STORE NEW RECORD
STORE AA-FLD AAAAA CC-FLD
47
20. EOR ,DE ,empty fields
DB Name F Len S D AA AA-FLD A 5 N BB
BB-FLD A 5 D CC CC-FLD A 5 S SS SS-SUP
A 10 N S - SOURCE FIELD(S)- AA-FLD(1-5)
CC-FLD(1-5)
EXAMPLE UPDATE TO BLANKS
UPDATE BB (ISN 2)
48
21. RELEASE ISN DATA INTEGRITY
NON-NATURAL
Adabas Command Reference Manual (RI command)
"Programs using ET logic should not release
records with the RI command if any updating has
been performed during the current transaction,
since this could result in a loss of data
integrity. ET users should release ISNs with
the ET or CL commands."
Adabas Native SQL Reference Manual (RELEASE ISN
stmt)
"If you are using ET logic, do not use this
statement to release a record that has been
updated during your current session."
49
21. RELEASE ISN DATA INTEGRITY
Example
(ACTIONS ON ISN 7, SAME FILE)
3.RI ISN 7
50
DAS ENDE