EVERYTHING YOU WANT TO KNOW ABOUT Sx BUT

EVERYTHING YOU WANT TO KNOW ABOUT Sx ( BUT WERE AFRAID TO ASK ) PART I Jim Poole 1

NASTY STATEMENTS "Never use the Find Sorted By" "Never use a non descriptor search" "Read logical is always faster than a Find" "Histogram is always faster than a Find" "Never use Finds" "Never. . . , Always. . . " THE HOW, WHY, WHAT. . . 2

OUTLINE -BASICS OF ADABAS COMMAND PROCESSING A. DETERMINING THE SEARCH ALGORITHM B. PROCESSING THE SEARCH ALGORITHMS C. DETERMINING THE SORT ALGORITHM D. PROCESSING THE SORT ALGORITHMS E. STORAGE OF THE RESULTANT LIST -PROCESSING THE RESULTANT LIST -QUICK NOTE ON READ LOGICAL AND HISTOGRAM PROCESSING -WHAT NATURAL DOES -WHAT ADABAS DOES 3

BASICS OF ADABAS COMMAND PROCESSING PROGRAM ADALINK • CALL ADABAS • DETERMINE DBID • DETERMINE CMD TYPE • CREATE ADABAS USERID SVC/ ROUTER • DB ID TABLE ADABAS • BUILD CQE • MOVE UB TO NAB 4

BASICS OF ADABAS COMMAND PROCESSING CMD. Q. NC=20 -200 TBQ THREADS USR-CID-INFO LQ=20 -400 K CQE SELECT LI=20 -100 K WORK POOL LS= USER. Q NU=200 -3000 ? Timeout ? ? Rsp. Code? Usr Resources TBI USR-CID-INFO NT=5 -30 (CONT. ) LWP=1 -5 M ASSO DATA WORK FCB UI REC PART 2 MI NI PART 3 AC BLKSIZE=2544 BLKSIZE=5064 BLKSIZE=5724 5

BASIC ADABAS CALL/COMMAND (CMD) CB (CONTROL BLOCK) – Eg. CMD / FNR / DB / RSP. CD / LENGTHS / ISNQ / CID / options FB (FORMAT BUFFER) – FIELDS TO RETURN/UPDATE (+FORMATs) RB (RECORD BUFFER) – FIELD VALUES SB (SEARCH BUFFER) – DESCRIPTORS, RELATIONSHIPS, (+FORMATs) VB (VALUE BUFFER) – DESCRIPTOR VALUES IB (ISN BUFFER) – ISN’S (USE IS OBSCURE) 6

BASICS OF ADABAS COMMAND PROCESSING (CONT. ) NATURAL FIND (combo of): S 1/4 - Create ISN list based on search criteria (SB/VB) S 2 - Same as S 1 but with sort based on DE’s in CB S 8 - Combine of ISN lists based on Command ID’s in CB (similar to S 1): AND / OR / NOT S 9 - Sort of ISN list based on DE’s in CB. (similar to S 2) L 1/4 - Return records based on ISN’s in ISN list. NATURAL READ LOGICAL: L 3/6 NATURAL HISTOGRAM: L 9 NATURAL RELEASE /END. LOOP: RC 7

BASICS OF ADABAS COMMAND PROCESSING (CONT. ) THE BASIC Sx. RETURNS: ISQ ISN - ISN QUANTITY (NUMBER OF RECS ON LIST). - OF 1 ST RECORD MEETING CRITERIA. (opt) 1 ST REC - (opt) S 4, PUT 1 ST RECORD ON HOLD, (opt) COPT 1=R FOR RSP. CD. 145 (opt) ISN LIST - RECORDS MEETING CRITERIA, USES CID. 8

BASICS OF ADABAS COMMAND PROCESSING COMMAND ID (CID): 4 b, non-zero, non-blank, REQUIRED FOR: (CONT. ) Labels ISN List L 1/4 GET NEXT PROCESSING RETAIN ‘SET-NAME’ COPT 1=H USE IN SUBSEQUENT Sx COMMANDS NOT REQUIRED: ISQ 1 ST RECORD OPTION, COP 1/2=I Eg. RELEASE PREVIOUS CID NATURAL CID: 12200101 1220 Statement no. 01 Pgm. Level no. 01 counter 9

• A. DETERMINING THE SEARCH ALGORITHM • B. PROCESSING THE SEARCH ALGORITHMS • C. DETERMINING THE SORT ALGORITHM • D. PROCESSING THE SORT ALGORITHMS • E. STORAGE OF THE RESULTANT LIST 10

DETERMINING THE SEARCH ALGORITHM COMPLEX: 2 or more DEs, with more than 2 Values SIMPLE: anything else 1. ADALINK: BASED ON PERIOD IN SB ( > 9) SB=ST. (simple) SB=ST, D, NM, GT. (complex) SB=ST, GT. (simple) ? SB=ST, 20, A. ? ? SB=ST, 20, A, GT. ? 2. COMPLEX COMMANDS ARE NOT SELECTED FROM CQ IF: a. LESS THAN 50% OF WORK-2 IS AVAILABLE. ! SUPERSIZE WORK-2 ! b. LESS THAN LS= AVAILABLE IN WORK POOL. ! SIZE LWP/LS RATIO ! c. AT LEAST ONE Sx IS SUSPENDED WAITING ON LWP SPACE. ! SIZE LWP/LS RATIO ! 3. FINAL SEARCH ALGORITHM IS DETERMINED ONCE COMMAND IS IN AN ADABAS THREAD. . . 4. NOTE THAT ALL S 2, S 8, S 9 COMMANDS ARE FLAGGED COMPLEX. 11

DETERMINING THE SEARCH ALGORITHM ONCE IN A THREAD: ALG. DESCRIPTION (cont. ) SB/FDT ANALYSIS 1 1 DESCRIPTOR 1 VALUE SEARCH BUFFER EXAMPLES NM. CT 1. NM, 20, A. NM, EQ. 2 1 DESCRIPTOR MANY VALUES NM, GE. NM, S, NM. 7 n DEs, 1 VALUE, OR NM, R, ST. 3 2 -5 DESCRIPTORS 1 VALUE EACH, AND NM, D, ST. 4 2 OR MORE DEs MANY VALUES 5 ALL NON-DEs 6 MIX OF NON-DEs AND DE’S NM, LT. NM, NE. NM, S, NM, N, NM. NM, 20, A, GT. NM, O, NM. NM, D, ST, 10, A, D, CT. NM, D, ST, GT. NM, S, NM, D, ST, O, ST. NM, D, ST, Y, ST, O, ST, Y, ST, S, ST. WT, S, WT. WT, D, HT, GT. (WT and HT are not Descriptors) NM, D, WT. NOTE a: ALG. 5/6 WILL RECEIVED RSP 68 IF ADARUN NONDES=YES. b: (CID), SET-NAME FROM PREVIOUS FIND, TREATED AS 1 DE 12

• A. DETERMINING THE SEARCH ALGORITHM • B. PROCESSING THE SEARCH ALGORITHMS • C. DETERMINING THE SORT ALGORITHM • D. PROCESSING THE SORT ALGORITHMS • E. STORAGE OF THE RESULTANT LIST 13

PROCESSING THE SEARCH ALGORITHMS: ALG. 1 • 1 DE , 1 VALUE NO LWP/LS USED, NO WORK-2 USED SB=NM. VALUE QTY ISNs NI … SMITH (6) 2, 6, 8, 12, 20 R. I. L. * … 1. 2. READ FCB-UI-MI-NI. MOVE NI ISNs TO FINAL LIST. (ALREADY SORTED) 2, 6, 8, 12, 20 COMMENTS: 1. ! VERY FAST, EFFICIENT ! 14 *R. I. L. : RESULTANT ISN LIST

PROCESSING THE SEARCH ALGORITHMS: ALG. 2 • 1 DE , MANY VALUES SB=NM, GT. LWP/LS USED, NO WORK-2 USED UI MI. . . GLAN GRUN HURT INGO NI. . GLAN(3)20, 40, 50 GLEN(2)12, 24 GRAN(600) 2, 9, . . . HURT(600)14, 15, . . . HUSH(2)3, 16 INGO(800) 22, 42, . . . GRUN(1)23 HUN(500) 5, 27. . . LWP= R. I. L. LS= 20, 40, 50 12, 24 2, 9, . . . 23 5, 27, . . . 14, 15. . 3, 16 22, 42, . . 2, 3, 5, 9, 12, 14, 15, 16, 20, 22, 23, 24, 27, 40, 42, 50, . . . 15

PROCESSING THE SEARCH ALGORITHMS: ALG. 2 cont. • 1 DE , MANY VALUES 1. 2. 3. 4. 5. LWP/LS USED, NO WORK-2 USED READ FCB-UI-MI. DO MI-ESTIMATION. IF ISNs > LS/8, THEN ALGORITHM 4. (EG. LS=40, 000, EST. > 5000, THEN ALG. 4) READ NI, MOVE ISN’S TO ½ LS. SORT ISNs TO SECOND ½ LS. MOVE ISNs TO FINAL LIST. COMMENTS: 1. ISNs ARE ALWAYS SORTED (NOTE SEQ. VS. L 3/6) 2. ! DE OR DE IS FAIRLY EFFICIENT ! 3. DE-THRU-DE OR DE-RANGE DEPENDS ON NUMBER OF ISNS 16

PROCESSING THE SEARCH ALGORITHMS: ALG. 3 • 1 DE : 1 VAL. , 1 DE : 1 VA. , . . . NO LWP/LS USED, NO WORK-2 USED SB=NM, D, ST, D, CT. NI(NM) SMITH(20) 2, 4, 6, 8, 9, 10, . . . NI(ST) UTAH(15) 3, 4, 5, 8, 10, . . . NI(CT) PROVO(33) 1, 4, 5, 6, 10, . . . R. I. L. 4, 10, . . . 1. READ FCB-UI-MI-NI FOR @ DE-VALUE (UP TO 5 DEs) 2. READ ALONG ISN LISTS IN BLOCKS. 3. MOVE ISNs IN COMMON TO RESULTANT ISN LIST. COMMENTS 1. ! VERY EFFICIENT ! 17

PROCESSING THE SEARCH ALGORITHMS: ALG. 4 >= 2 DEs, > 2 VALUES USE LWP/LS, MAY USE WORK-2 SB=NM, D, ST, GT. MI NI NI UI NI W-2 LWP= LS= -ISNs W-2 -ISN BIT STRINGS W-2 WORK-2 R. I. L. 18

PROCESSING THE SEARCH ALGORITHMS: ALG. 4 cont. ALG. 4: ANY NON-DE SB THAT IS NOT ALG. 1, 2, 3, 7 1. ALLOCATE LS: MIN(LS, TOPISN/8 + TOPISN/128) 2. READ FCB-UI-MI FOR EACH PART OF QUERY ( D IS SEPARATOR) 3. SEQUENCE EACH PART IN SMALLEST TO LARGEST ORDER (MI EST. ) 4. READ NI FOR EACH PART, CREATE BIT STRINGS (8 ISNs / BYTE) 5. MERGE BIT STRINGS 6. IO TO WORK-2 IF NEEDED. 7. CONVERT BIT-STRING BACK TO ISNs, CREATE RESULT ISN LIST. COMMENTS 1. SIZE LS TO ASSIST COMPLEX SEARCHES (eg. LS=200 K for 15 M recs) 2. ! SEQUENCE QUERY FROM LEAST TO MOST LIKELY ! 3. SOME ARE GOOD, SOME ARE BAD, IT DEPENDS: AGE>75 AND NAME=‘SNODGRASS’ SEX=F AND HEIGHT>2 (meters) 4. ! EVALUATE EACH QUERY BASED ON CURRENT & FUTURE DATA ! ! BEST WHEN EACH CRITERIA IS LIMITING AND RESULT IS SMALL ! 19

PROCESSING THE SEARCH ALGORITHMS: ALG. 5 ALL NON-DEs USE LWP/LS, MAY USE WORK-2 SB=WT. DS DS REC 1 REC 2 REC 3. . . RECn RECm RECl. . . DS RECp RECq RECr. . . : : : LWP= LS= ISN BIT STRINGS ISNs WT=? WORK 2 1. READ ALL RECS IN DS, EVALUATE AGAINST CRITERIA. 2. CREATE BIT STRING OF VALID ISNs IN LS/LWP. 3. IF NEEDED SPOOL TO WORK-2. 4. CONVERT BIT STRING TO ISNs. 5. WRITE TO RESULTANT ISN LIST. R. I. L. COMMENTS ! SOLVES A VERY SMALL CLASS OF PROBLEMS ! 20

PROCESSING THE SEARCH ALGORITHMS: ALG. 6 DEs AND NON-DEs SB=NM, D, WT. MAY USE LWP/LS, MAY USE WORK-3 WORK 3 ALG. 1, 2, 3, 4, 7 1 5 12 15 : : AC DS WT=? AC : : : DS : : : WT=? : : : R. I. L. 1. SEPARATE SB BETWEEN DE/NON-DE CRITERIA. 2. DO ALG. 1, 2, 3, 4, 7 ON DE CRITERIA. 3. WRITE TEMP ISN LIST TO WORK-3 4. READ WORK-3 ISN-LIST, FOR EACH: a. READ REC: AC-DS b. EVALUATE AGAINST NON-DE CRIT. 5. IF CRITERIA MET, WRITE ISN TO FINAL RESULTANT ISN LIST. 5, 15, . . . 21

PROCESSING THE SEARCH ALGORITHMS: ALG. 6 CONT. COMMENTS 1. GOOD FOR ONE-TIME SEARCHES. 2. GOOD IF INITIAL TEMP ISN LIST IS SMALL (RECS STILL IN LBP=), AND NON-DE CRITERIA FURTHER RESTRICTS LIST. NATURAL: NON-DE MARKED AS N IN DDM FOR DE FIELD 22

PROCESSING THE SEARCH ALGORITHMS: ALG. 7 2 OR MORE DEs, 1 VALUE EACH, OR USE WORK-3 SB=NM, R, CT. 1. READ FCB-UI-MI-NI FOR EACH DE. 2. WRITE ISNs TO WORK-3. (? ) 3. MERGE ISNS, CREATE RESULTANT ISN LIST. ADA V 5. 3 COMMENTS: 1. FAIRLY EFFICIENT. 23

• A. DETERMINING THE SEARCH ALGORITHM • B. PROCESSING THE SEARCH ALGORITHMS • C. DETERMINING THE SORT ALGORITHM • D. PROCESSING THE SORT ALGORITHMS • E. STORAGE OF THE RESULTANT LIST 24

DETERMINING THE SORT ALGORITHM ADABAS SORTS ARE IN MEMORY ONLY: LIMITED BY ADARUN LS= (20 K-200 K) ELSE RESPONSE CODE 1 2 TYPES OF SORT COMMANDS S 2 - CONTAINS ALG 1 -7 SEARCH TO CREATE R. I. L. SORT ON 1 -3 DEs SORT ASCENDING/DESCENDING S 9 - R. I. L. PASSED IN IB(RARE) OR BY COMMAND-ID SORT ON 1 -3 DEs SORT ASCENDING ONLY SORT ALGORITHMS: 1, 2 TRY 2 FIRST, 1 SECOND. 25

DETERMINING THE SORT ALGORITHM cont. IF ( ISNQ ) * 2 * ( 4 + LEN. DE 1 + LEN. DE 2 + LEN. DE 3 ) > (ADARUN LS=) THEN ALGORITHM 1 ELSE ALGORITHM 2 EXAMPLE. LS=100, 000 LENGTH. DE=21 ISNQ * 2 ( 4 + 21) >? 100, 000 ISNQ * 50 >? 100, 000 ISNQ >? 20, 000 ! NOTE THAT MANY SORTS ARE ALG. 2, NOT ALG. 1 ! 26

PROCESSING THE SORT ALGORITHMS: ALG. 2 SB=NM. CB ADDS 1=CT R. I. L. 1 AC 2 AC 8 AC 9. . . AC : : : LWP= DS 1 -PARIS LS= DS 2 -BUTTE DS 8 -AKRON DS 9 -BUTTE : : : 1 -PARIS 2 -BUTTE 8 -AKRON 9 -BUTTE. . 8 -AKRON 2 -BUTTE 9 -BUTTE 1 -PARIS. . R. I. L. 8, 2, 9, 1, . . 27

PROCESSING THE SORT ALGORITHMS: ALG. 2 CONT. 1. START WITH THE RESULTANT ISN LIST FROM THE SEARCH. 2. FOR EACH ISN ON THE LIST READ AC-DS FOR THE RECORD. 3. MOVE THE ISN-DE. VALUE PAIR TO ½ OF LS=. 4. AFTER ALL PAIRS MOVED TO LS=, SORT INTO SECOND ½ OF LS=. 5. CREATE FINAL RESULTANT ISN LIST. COMMENTS: WORST CASE IO: ISNQ * (AC + DS) IF ISNQ SMALL, THEN AC/DS REMAIN IN BUFFER POOL FOR L 1/4’s ! FOR SMALL LISTS, VERY EFFICIENT ! ! FOR LARGE LISTS, LOTSA AC/DS IO ! COMPARE: S 1 SB=NM, GT. S 2 SB=NM, GT. CB. ADDS 1=NM 28

PROCESSING THE SORT ALGORITHMS: ALG. 1 SB=NM. CB ADDS 1=CT UI R. I. L. 1 2 8 9. . . MI NI. . . AKRON-8. . . Cntr: 1 LWP= ISN 1 2 LS= 8 9 : : cntr 3 2 1 2 R. I. L. BUTTE-2, 9, . . . 2 PARIS-1. . . 3 8 -1, 2 -2, 9 -2, 1 -3, . . . 8, 2, 9, 1, . . 29

PROCESSING THE SORT ALGORITHMS: ALG. 1 cont 1. START WITH THE RESULTANT ISN LIST FROM THE SEARCH. MOVE R. I. L. INTO ½ LS=. 2. READ THE UI-MI-NI INDEX FOR EACH SORT-DE, KEEP A COUNTER FOR HITS. 3. AS EACH ISN IS FOUND IN THE NI, UPDATE LS=, ADD 1 TO COUNTER. 4. ONCE ALL ISNs FOUND, SORT INTO SECOND ½ OF LS=. 5. CREATE FINAL RESULTANT ISN LIST. COMMENTS: -NI SCAN WILL BE FASTER IF ALL ISN’S FOUND EARLY. -IF 1 ISN HAS HIGHEST SORT-DE VALUE, ENTIRE NI WILL BE READ ! EG. ISNQ=10, CB. ADDS 1=SS (SUPER-DE), ISN-X HAS SS= ‘ZZ*’ NI/UI IS 1, 000 BLOCKS ! 1 MILLION BLOCKS WILL BE READ ! 30

PROCESSING THE SORT ALGORITHMS: ALG. 1 cont ? WHEN RESPONSE CODE 1 ? 1. VERIFY ADARUN LS= WITH DPARM Eg. LS=20, 000 LS=100, 000 ACTUAL=19, 968 ACTUAL=999, 840 2. VERIFY TOPISN OF FILE (NOT ISN QTY). >< 65, 536 ? 3. MAX ISNQ THAT CAN BE SORTED BEFORE RSP. CD. 1: 65536 1 DE LS/12 2 DE 3 DE LS/16 LS/20 TOPISN >= 65536 LS/14 LS/20 TOPISN < LS/26 4. EXAMPLE: ADARUN LS=120, 000 S 2 1 DE: ISNQ=10, 000 S 2 2 DE: ISNQ= 7, 500 S 2 3 DE: ISNQ= 6, 000 31

• A. DETERMINING THE SEARCH ALGORITHM • B. PROCESSING THE SEARCH ALGORITHMS • C. DETERMINING THE SORT ALGORITHM • D. PROCESSING THE SORT ALGORITHMS • E. STORAGE OF THE RESULTANT LIST 32

STORAGE OF THE RESULTANT LIST FUNCTION OF: CID / ADARUN NSISN= / COP 1=H (RETAIN SET) WORK USRA CID=1 ISQ=1 USRA CID=0 ISQ=100 TBI, ADARUN LI= NSISN=51 USRB CID=1 ISQ=7 USRB-CID 1 … 1, 2, 3, 4, 5, 6, 7. USRB CID=2 ISQ=100 USRB-CID 2 … 1, 2, 3, . . . , 51 USRC CID=1 ISQ=100 C 1=H USRC-CID 1 … 1, 2, 3, . . . , 51 Pt. III 52, 53, . . . , 100 1, 2, 3, . . . , 100 USRD CID=1 ISQ=1 C 1=H USRD-CID 1 … 1. 33

STORAGE OF THE RESULTANT LIST PROCESSING THE RESULTANT LIST FIRST RECORD RETURNED IF FBL / RBL SPECIFIED. THAT IS FORMAT-BUFFER-LENGTH > 1 COMMON TO MOST FINDs : S 1, S 2, S 4, S 8 , S 9 34

PROCESSING THE RESULTANT LIST L 1/4 GET NEXT PROCESSING FIND Sx L 1 L 1. . . LOOP RC TBI LI= … 1, 4, 5, 6, . . WORK 55, 56, . . . 1. Sx CREATES RESULTANT ISN LIST, RETURNS ISNQ AND 1 ST RECORD. 2. EACH L 1 ACCESSES TBI ELEMENT FOR NEXT ISN, READS AC-DS FOR RECORD. IF TBI ELEMENT EXHAUSTED, WORK-3 BLOCK IO. 3. LOOP ISSUES AN RC. 35

ADABAS RESPONSE CODES FOR Sx PROCESSING RSP. CODE DATABASE PROGRAM 68 – NON-DESCR NOT ALLOWED NONDES= 70 – TBI FULL NSISN= LI= RELEASE set-name 73 – WORK 3 FULL WORKSIZE CACHE WORK 3 RELEASE set-name MANAGE ISNQTY 74 – WORK 2 FULL LWKP 2= CACHE WORK 2 REVIEW COMPLEX SEARCHES 46 – TOO MANY CIDs PER USER 7 – Sx TIME EXCEEDED NQCID= TLSCMD= RELEASE set-name REVIEW BAD SEARCHES 36

QUICK NOTE ON READ LOGICAL AND HISTOGRAM PROCESSING READ LOGICAL: L 3/6 READ LOGICAL L 3 : . . . LOOP RC HISTOGRAM L 9 : . . . LOOP RC HISTOGRAM: L 9 TBQ ADARUN LQ= UI USR-CID-PTR MI USR-CID-PTR NI AC DS-RECS 1. INITIAL CALL CREATES TBQ ELEMENT. 2. EACH CALL ACCESSES TBQ FOR PTR, UI-MI-NI (L 9) UI-MI-NI-AC-DS (L 3/6) 3. LAST CALL RECEIVES RSP. CD. 3 4. END. LOOP ISSUES RC. 37

ADABAS V 7. 4 DIRTY READ CHECK “ UNDER V 7. 4 SOME CMDS, THE VALUE IN THE INDEX IS CHECKED AGAINST THE VALUE IN DATA STORAGE. IF THEY DO NOT MATCH, A RC 175 IS RETURNED. ” L 3, L 6, some S 1/4 PER MESSAGES AND CODES MANUAL (NEW TO 7. 4): Response 175 Explanation: An inconsistency was detected between the index and Data Storage. Action: Run the "check" utilities (especially ADAICK and ADAVAL) against the file, and contact your Software AG technical support representative. 38

ADABAS V 7. 4 DIRTY READ CHECK 1. REQUIRED FOR ADABAS CLUSTER SERVICES 2. INTEGRITY CHECK FOR NON-CLUSTER DATABASES 3. SEE ADABAS SPEC ZAP AY 743109 TO DISABLE 39

ADABAS V 7. 4 DIRTY READ CHECK MADE FOR ALL L 3/L 6 (READ LOGICAL) DIRTY READ CHECK MADE FOR Sx ONLY IF: 1. CMD = S 1 OR S 4 AND 2. FBL>1 (RETURN 1 ST REC) AND 3. SB= 1 Descr OR SB= 1 Descr thru 1 Descr USE OF CID/GFID NOT A FACTOR USE OF IBL NOT A FACTOR 40

ADABAS V 7. 4 DIRTY READ CHECK LOGIC: 1. IF ELEMENTARY DE, DATA STORAGE RECORD SCANNED UNTIL DE-FIELD FOUND. 2. IF SUB/SUPER/HYPER DE, ENTIRE RECORD SCANNED (LOCATE ALL SOURCE FIELDS). 3. CONSIDER: SB=DE. FB /= DE. ? SCAN OF RECORD UNTIL ? 41

DAS ENDE 42