EVERYTHING YOU WANT TO KNOW ABOUT Sx

1
EVERYTHING YOU WANT TO KNOW ABOUT Sx ( BUT WERE
AFRAID TO ASK ) PART I
Jim Poole
2
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 . . .
3
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
4
BASICS OF ADABAS COMMAND PROCESSING
5
BASICS OF ADABAS COMMAND PROCESSING (CONT.)
PART 2
REC
NI
PART 3
AC
6
BASIC ADABAS CALL/COMMAND (CMD)
CB (CONTROL BLOCK) Eg. CMD / FNR / DB / RSP.CD
/ LENGTHS /
ISN / ISNQ / CID / options FB
(FORMAT BUFFER) FIELDS TO RETURN/UPDATE
(FORMATs) RB (RECORD BUFFER) FIELD VALUES SB
(SEARCH BUFFER) DESCRIPTORS, RELATIONSHIPS,(FOR
MATs) VB (VALUE BUFFER) DESCRIPTOR VALUES IB
(ISN BUFFER) ISNS (USE IS OBSCURE)
7
BASICS OF ADABAS COMMAND PROCESSING (CONT.)
NATURAL FIND (combo of) S1/4 - Create ISN
list based on search criteria (SB/VB) S2
- Same as S1 but with sort based on DEs in CB
S8 - Combine of ISN lists based on
Command IDs in CB (similar to
S1) AND / OR / NOT S9 - Sort of ISN
list based on DEs in CB. (similar to S2)
L1/4 - Return records based on ISNs in ISN
list. NATURAL READ LOGICAL L3/6 NATURAL
HISTOGRAM L9 NATURAL RELEASE /END.LOOP RC
8
BASICS OF ADABAS COMMAND PROCESSING (CONT.)
THE BASIC Sx. RETURNS ISQ - ISN
QUANTITY (NUMBER OF RECS ON LIST).
ISN - OF 1ST RECORD MEETING
CRITERIA. (opt) 1ST REC - (opt)
S4, PUT 1ST RECORD ON HOLD,
(opt) COPT1R FOR RSP.CD.
145 (opt) ISN LIST -
RECORDS MEETING CRITERIA, USES CID.

9
BASICS OF ADABAS COMMAND PROCESSING (CONT.)
COMMAND ID (CID) 4b, non-zero, non-blank,
Labels ISN List REQUIRED FOR
L1/4 GET NEXT PROCESSING
RETAIN SET-NAME COPT1H
USE IN
SUBSEQUENT Sx COMMANDS NOT REQUIRED
ISQ
1ST RECORD
OPTION,COP1/2I RELEASE PREVIOUS CID
Eg.
10

• 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

11
DETERMINING THE SEARCH ALGORITHM
COMPLEX 2 or more DEs, with more than 2
Values SIMPLE anything else
1. ADALINK BASED ON PERIOD IN SB ( gt 9)
SBST. (simple)
SBST,D,NM. (simple)
SBST,D,NM,GT. (complex)
SBST,GT. (simple)
? SBST,20,A,GT. ?
? SBST,20,A .?
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 S2,S8,S9 COMMANDS ARE FLAGGED
COMPLEX.
12
DETERMINING THE SEARCH ALGORITHM
(cont.) ONCE IN A THREAD SB/FDT ANALYSIS
ALG. DESCRIPTION SEARCH
BUFFER EXAMPLES
NOTE a ALG. 5/6 WILL RECEIVED RSP 68 IF ADARUN
NONDESYES. b (CID), SET-NAME FROM
PREVIOUS FIND, TREATED AS 1 DE
13

• 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

14
SBNM.

• READ FCB-UI-MI-NI.
• MOVE NI ISNs TO FINAL LIST.
• (ALREADY SORTED)

COMMENTS 1. ! VERY FAST, EFFICIENT !
R.I.L. RESULTANT ISN LIST
15
SBNM,GT.
16
PROCESSING THE SEARCH ALGORITHMS ALG. 2 cont.

• 1 DE , MANY VALUES LWP/LS
  USED, NO WORK-2 USED

• READ FCB-UI-MI.
• DO MI-ESTIMATION. IF ISNs gt LS/8, THEN
  ALGORITHM 4.
• 
  (EG. LS40,000, EST. gt 5000, THEN ALG. 4)
• READ NI, MOVE ISNS TO ½ LS.
• SORT ISNs TO SECOND ½ LS.
• MOVE ISNs TO FINAL LIST.

COMMENTS 1. ISNs ARE ALWAYS SORTED (NOTE SEQ.
VS. L3/6) 2. ! DE OR DE IS FAIRLY EFFICIENT
! 3. DE-THRU-DE OR DE-RANGE DEPENDS ON NUMBER OF
ISNS
17
PROCESSING THE SEARCH ALGORITHMS ALG.3

• 1 DE 1 VAL., 1 DE 1 VA., . . . NO LWP/LS
  USED, NO WORK-2 USED

SBNM,D,ST,D,CT.
1. READ FCB-UI-MI-NI FOR _at_ DE-VALUE (UP TO 5
DEs) 2. READ ALONG ISN LISTS IN BLOCKS. 3. MOVE
ISNs IN COMMON TO RESULTANT ISN LIST.
NI(NM)
SMITH(20) 2,4,6,8,9,10,. . .
NI(ST)
UTAH(15) 3,4,5,8,10,. . .
PROVO(33) 1,4,5,6,10,. . .
NI(CT)
4,10, . . .
R.I.L.
COMMENTS 1. ! VERY EFFICIENT !
18
PROCESSING THE SEARCH ALGORITHMS ALG.4
gt 2 DEs, gt 2 VALUES USE
LWP/LS, MAY USE WORK-2
SBNM,D,ST,GT.
MI
NI
NI
UI
MI
NI
NI
NI
LWP
W-2
WORK
LS
WORK-2

• ISNs
• ISN BIT STRINGS

W-2
W-2
W-2
R.I.L.
19
PROCESSING THE SEARCH ALGORITHMS ALG.4 cont.
ALG.4 ANY NON 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. LS200K for 15M recs) 2. ! SEQUENCE QUERY
FROM LEAST TO MOST LIKELY ! 3. SOME ARE GOOD,
SOME ARE BAD, IT DEPENDS
AGEgt75 AND NAMESNODGRASS
SEXF AND HEIGHTgt2 (meters)
4. ! EVALUATE EACH QUERY BASED ON CURRENT
FUTURE DATA ! ! BEST WHEN EACH CRITERIA IS
LIMITING AND RESULT IS SMALL !
20
PROCESSING THE SEARCH ALGORITHMS ALG.5
ALL NON-DEs
USE LWP/LS, MAY USE WORK-2
SBWT.
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.
21
PROCESSING THE SEARCH ALGORITHMS ALG.6
DEs AND NON-DEs MAY USE LWP/LS,
MAY USE WORK-3
SBNM,D,WT.
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.
ALG.1,2,3,4,7
22
PROCESSING THE SEARCH ALGORITHMS ALG.6 CONT.

• COMMENTS
• GOOD FOR ONE-TIME SEARCHES.
• 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
23
PROCESSING THE SEARCH ALGORITHMS ALG.7
2 OR MORE DEs, 1 VALUE EACH, OR
USE WORK-3
SBNM,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 V5.3
COMMENTS 1. FAIRLY EFFICIENT.
24

• 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

25
DETERMINING THE SORT ALGORITHM
ADABAS SORTS ARE IN MEMORY ONLY LIMITED
BY ADARUN LS (20K-200K) ELSE RESPONSE
CODE 1
2 TYPES OF SORT COMMANDS
S2 - CONTAINS ALG 1-7 SEARCH TO CREATE R.I.L.
SORT ON 1-3 DEs SORT
ASCENDING/DESCENDING

S9 - 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.
26
DETERMINING THE SORT ALGORITHM cont.
IF ( ISNQ ) 2 ( 4 LEN.DE1 LEN.DE2
LEN.DE3 ) gt (ADARUN LS) THEN ALGORITHM 1
ELSE ALGORITHM 2
EXAMPLE. LS100,000 LENGTH.DE21 ISNQ 2 (
4 21) gt? 100,000 ISNQ 50 gt?
100,000 ISNQ gt? 20,000
! NOTE THAT MANY SORTS ARE ALG.2, NOT ALG.1 !
27
PROCESSING THE SORT ALGORITHMS ALG.2
SBNM. CB ADDS1CT

28
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
L1/4s ! FOR SMALL LISTS, VERY EFFICIENT ! ! FOR
LARGE LISTS, LOTSA AC/DS IO ! COMPARE S1
SBNM,GT. S2 SBNM,GT.
CB.ADDS1NM
29
PROCESSING THE SORT ALGORITHMS ALG.1
SBNM. CB ADDS1CT
Cntr
30
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 ISNS
FOUND EARLY. -IF 1 ISN HAS HIGHEST SORT-DE VALUE,
ENTIRE NI WILL BE READ !
EG. ISNQ10, CB.ADDS1SS (SUPER-DE), ISN-X
HAS SS ZZ NI/UI IS 1,000,000
BLOCKS ! 1 MILLION BLOCKS WILL BE READ
!
31
PROCESSING THE SORT ALGORITHMS ALG.1 cont
? WHEN RESPONSE CODE 1 ? 1. VERIFY ADARUN LS
WITH DPARM
Eg. LS20,000 ACTUAL19,968
LS100,000 ACTUAL999,840
2. VERIFY TOPISN OF FILE (NOT ISN QTY). gtlt 65,536
?
4. EXAMPLE ADARUN LS120,000 S2 1 DE
ISNQ10,000 S2 2 DE ISNQ 7,500 S2 3
DE ISNQ 6,000
32

• 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

33
STORAGE OF THE RESULTANT LIST
FUNCTION OF CID / ADARUN NSISN / COP1H
(RETAIN SET)
USRA CID1 ISQ1
USRA CID0 ISQ100
Pt. III
34
STORAGE OF THE RESULTANT LIST PROCESSING THE
RESULTANT LIST
FIRST RECORD RETURNED IF FBL / RBL
SPECIFIED. THAT IS FORMAT-BUFFER-LENGTH gt
1 COMMON TO MOST FINDs S1, S2, S4, S8 ,S9
35
PROCESSING THE RESULTANT LIST L1/4 GET NEXT
PROCESSING
TBI LI
WORK
FIND Sx L1 L1 L1 . . . LOOP
RC
1,4,5,6,..
55,56,. . .
1. Sx CREATES RESULTANT ISN LIST, RETURNS
ISNQ AND 1ST RECORD. 2. EACH L1 ACCESSES TBI
ELEMENT FOR NEXT ISN, READS AC-DS FOR
RECORD. IF TBI ELEMENT EXHAUSTED, WORK-3
BLOCK IO. 3. LOOP ISSUES AN RC.
36
ADABAS RESPONSE CODES FOR Sx PROCESSING
RSP.CODE
DATABASE
PROGRAM
37
QUICK NOTE ON READ LOGICAL AND HISTOGRAM
PROCESSING
READ LOGICAL L3/6 HISTOGRAM L9
READ LOGICAL L3 L3 L3 . .
. LOOP RC
TBQ ADARUN LQ
UI
USR-CID-PTR
MI
USR-CID-PTR
NI
AC
HISTOGRAM L9 L9 L9 . .
. LOOP RC
DS-RECS
1.INITIAL CALL CREATES TBQ ELEMENT. 2.EACH CALL
ACCESSES TBQ FOR PTR, UI-MI-NI (L9)
UI-MI-NI-AC-DS (L3/6) 3.LAST CALL RECEIVES
RSP.CD. 3 4.END.LOOP ISSUES RC.
38
ADABAS V7.4 DIRTY READ CHECK
UNDER V7.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.
L3,L6, some S1/4
39
ADABAS V7.4 DIRTY READ CHECK

1. REQUIRED FOR ADABAS CLUSTER SERVICES
2. INTEGRITY CHECK FOR NON-CLUSTER DATABASES
3. SEE ADABAS SPEC ZAP AY743109 TO DISABLE

40
ADABAS V7.4 DIRTY READ CHECK
DIRTY READ CHECK MADE FOR ALL L3/L6 (READ LOGICAL)
DIRTY READ CHECK MADE FOR Sx ONLY IF

• CMD S1 OR S4
• AND
• FBLgt1 (RETURN 1ST REC)
• AND
• 3. SB 1 Descr
• OR
• SB 1 Descr thru 1 Descr

41
ADABAS V7.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 SBDE. FB / DE.
? SCAN OF RECORD UNTIL ?
42
DAS ENDE