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

1 / 42

# EVERYTHING YOU WANT TO KNOW ABOUT Sx BUT WERE AFRAID TO ASK PART I - PowerPoint PPT Presentation

EVERYTHING YOU WANT TO KNOW ABOUT Sx ( BUT WERE AFRAID TO ASK ) PART I. Jim Poole. NASTY STATEMENTS. &quot;Never use the Find Sorted By&quot; &quot;Never use a non descriptor search&quot; &quot;Read logical is always faster than a Find&quot; &quot;Histogram is always faster than a Find&quot; &quot;Never use Finds&quot;

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'EVERYTHING YOU WANT TO KNOW ABOUT Sx BUT WERE AFRAID TO ASK PART I' - aron

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

EVERYTHING YOU WANT TO KNOW ABOUT Sx

( BUT WERE AFRAID TO ASK )

PART I

Jim Poole

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 . . .

OUTLINE

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

PROGRAM

• DETERMINE DBID
• DETERMINE CMD TYPE

SVC/

ROUTER

• DB ID TABLE

• BUILD CQE
• MOVE UB TO NAB

CMD.Q.

NC=20-200

NT=5-30

TBQ

WORK POOL

CQE SELECT

USR-CID-INFO

LS=

LQ=20-400K

TBI

LWP=1-5M

USER.Q

USR-CID-INFO

NU=200-3000

LI=20-100K

? Timeout ?

? Rsp.Code?

Usr Resources

ASSO

DATA

WORK

FCB

UI

MI

BLKSIZE=5064

BLKSIZE=5724

BLKSIZE=2544

BASICS OF ADABAS COMMAND PROCESSING (CONT.)

PART 2

REC

NI

PART 3

AC

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,(+FORMATs)

VB (VALUE BUFFER) – DESCRIPTOR VALUES

IB (ISN BUFFER) – ISN’S (USE IS OBSCURE)

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 DE’s in CB

S8 - Combine of ISN lists based on Command ID’s in CB

(similar to S1): AND / OR / NOT

S9 - Sort of ISN list based on DE’s in CB. (similar to S2)

L1/4 - Return records based on ISN’s in ISN list.

NATURAL HISTOGRAM: L9

NATURAL RELEASE /END.LOOP: RC

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) COPT1=R FOR RSP.CD. 145

(opt) ISN LIST - RECORDS MEETING CRITERIA, USES CID.

NATURAL CID:

12200101

1220

Statement no.

01

Pgm. Level no.

01

counter

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’ COPT1=H

USE IN SUBSEQUENT Sx COMMANDS

NOT REQUIRED: ISQ

1ST RECORD

OPTION,COP1/2=I RELEASE PREVIOUS CID

Eg.

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

DETERMINING THE SEARCH ALGORITHM

COMPLEX: 2 or more DEs, with more than 2 Values

SIMPLE: anything else

SB=ST. (simple)

SB=ST,D,NM. (simple)

SB=ST,D,NM,GT. (complex)

SB=ST,GT. (simple)

? SB=ST,20,A,GT. ?

? SB=ST,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

4. NOTE THAT ALL S2,S8,S9 COMMANDS ARE FLAGGED COMPLEX.

1

1 DESCRIPTOR

1 VALUE

NM. CT1.

NM,20,A. NM,EQ.

2

1 DESCRIPTOR

MANY VALUES

NM,GE. NM,LT. NM,NE. NM,20,A,GT.

NM,S,NM. NM,S,NM,N,NM. NM,O,NM.

7

n DEs, 1 VALUE, OR

NM,R,ST.

3

2-5 DESCRIPTORS

1 VALUE EACH, AND

NM,D,ST. NM,D,ST,10,A,D,CT.

4

2 OR MORE DEs

MANY VALUES

NM,D,ST,GT. NM,S,NM,D,ST,O,ST.

NM,D,ST,Y,ST,O,ST,Y,ST,S,ST.

5

ALL NON-DEs

WT. WT,S,WT. WT,D,HT,GT.

(WT and HT are not Descriptors)

6

MIX OF NON-DEs

AND DE’S

NM,D,WT.

DETERMINING THE SEARCH ALGORITHM (cont.)

ALG. DESCRIPTION SEARCH BUFFER EXAMPLES

b: (CID), SET-NAME FROM PREVIOUS FIND, TREATED AS 1 DE

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 SEARCH ALGORITHMS: ALG.1

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

VALUE QTY ISNs

SMITH

(6)

2,6,8,12,20

NI

R.I.L.*

2,6,8,12,20

SB=NM.

• MOVE NI ISNs TO FINAL LIST.

1. !VERY FAST, EFFICIENT !

*R.I.L.: RESULTANT ISN LIST

PROCESSING THE SEARCH ALGORITHMS: ALG.2

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

UI

MI

NI

. . .GLAN GRAN GRUN

HURT INGO

. .GLAN(3)20,40,50 GLEN(2)12,24

HURT(600)14,15, . . . HUSH(2)3,16

GRAN(600) 2,9, . . .

INGO(800) 22,42, . . .

GRUN(1)23 HUN(500) 5,27 . . .

LWP=

20,40,50 12,24

2,9, . . . 23

5,27,. . . 14,15 . .

3,16 22,42,..

LS=

2,3,5,9,12,14,15,

16,20,22,23,24,

27,40,42,50,. . .

2,3,5,9,12,14,15,16,20,22,23,24,27,40,42,50, . . .

R.I.L.

SB=NM,GT.

PROCESSING THE SEARCH ALGORITHMS: ALG. 2 cont.

• 1 DE , MANY VALUES LWP/LS USED, NO WORK-2 USED
• 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.

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

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.

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.

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)

R.I.L.

4,10, . . .

1. ! VERY EFFICIENT !

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

MI

NI

NI

NI

LWP=

W-2

WORK

LS=

WORK-2

• ISNs
• ISN BIT STRINGS

W-2

W-2

W-2

R.I.L.

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.

1. SIZE LS TO ASSIST COMPLEX SEARCHES (eg. LS=200K for 15M 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 !

DS

DS

DS

REC1 REC2 REC3 . . .

RECn RECm RECl . . .

RECp RECq RECr . . .

WT=?

: : :

WORK

LWP=

LS=

ISN BIT STRINGS

ISNs

WORK

2

R.I.L.

! SOLVES A VERY SMALL CLASS OF PROBLEMS !

PROCESSING THE SEARCH ALGORITHMS: ALG.5

ALL NON-DEs USE LWP/LS, MAY USE WORK-2

SB=WT.

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.

WORK

WORK

3

1

5

12

15

:

:

AC

AC

AC

DS

DS

DS

WT=?

WT=?

WT=?

: : : : : : : : :

R.I.L.

5,15,. . .

PROCESSING THE SEARCH ALGORITHMS: ALG.6

DEs AND NON-DEs MAY USE LWP/LS, MAY USE WORK-3

SB=NM,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

b. EVALUATE AGAINST NON-DE CRIT.

5.IF CRITERIA MET, WRITE ISN TO FINAL

RESULTANT ISN LIST.

ALG.1,2,3,4,7

PROCESSING THE SEARCH ALGORITHMS: ALG.6 CONT.

• 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

PROCESSING THE SEARCH ALGORITHMS: ALG.7

2 OR MORE DEs, 1 VALUE EACH, OR USE WORK-3

SB=NM,R,CT.

2.WRITE ISNs TO WORK-3. (?)

3.MERGE ISNS, CREATE RESULTANT

ISN LIST.

1. FAIRLY EFFICIENT.

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

DETERMINING THE SORT ALGORITHM

ADABAS SORTS ARE IN MEMORY ONLY:

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.

DETERMINING THE SORT ALGORITHM cont.

IF

( ISNQ ) * 2 * ( 4 + LEN.DE1 + LEN.DE2 + LEN.DE3 ) > (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 !

LWP=

DS

DS

DS

DS

AC

AC

AC

AC

2-BUTTE

1-PARIS

9-BUTTE

8-AKRON

LS=

1-PARIS

2-BUTTE

8-AKRON

9-BUTTE . . . .

R.I.L.

1

2

8

9

.

.

.

8-AKRON

2-BUTTE

9-BUTTE

1-PARIS . . . .

: : : : : :

R.I.L.

8,2,9,1,. . . .

PROCESSING THE SORT ALGORITHMS: ALG.2

PROCESSING THE SORT ALGORITHMS: ALG.2 CONT.

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.

WORST CASE IO: ISNQ * (AC + DS)

IF ISNQ SMALL, THEN AC/DS REMAIN IN BUFFER POOL FOR L1/4’s

! FOR SMALL LISTS, VERY EFFICIENT !

! FOR LARGE LISTS, LOTSA AC/DS IO !

COMPARE: S1 SB=NM,GT.

UI

R.I.L.

MI

1

2

8

9

.

.

.

NI

. . .AKRON-8 . . . BUTTE-2,9, . . . PARIS-1 . . .

1

2

3

LWP=

ISNcntr

1

2

8

9

::

3

8-1, 2-2, 9-2,

1-3, . . .

2

LS=

1

2

R.I.L.

8,2,9,1,. . . .

PROCESSING THE SORT ALGORITHMS: ALG.1

Cntr:

PROCESSING THE SORT ALGORITHMS: ALG.1 cont

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.

-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.ADDS1=SS (SUPER-DE), ISN-X HAS SS= ‘ZZ*’

NI/UI IS 1,000,000 BLOCKS

! 1 MILLION BLOCKS WILL BE READ !

3. MAX ISNQ THAT CAN BE SORTED BEFORE RSP.CD. 1:

1 DE2 DE3 DE

TOPISN < 65536 LS/12 LS/16 LS/20

TOPISN >= 65536 LS/14 LS/20 LS/26

PROCESSING THE SORT ALGORITHMS: ALG.1 cont

? WHEN RESPONSE CODE 1 ?

1. VERIFY ADARUN LS= WITH DPARM

Eg. LS=20,000 ACTUAL=19,968

LS=100,000 ACTUAL=999,840

2. VERIFY TOPISN OF FILE (NOT ISN QTY). >< 65,536 ?

S2 1 DE: ISNQ=10,000

S2 2 DE: ISNQ= 7,500

S2 3 DE: ISNQ= 6,000

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

WORK

NSISN=51

USRB-CID1

USRB CID=1 ISQ=7

…1,2,3,4,5,6,7.

USRB CID=2 ISQ=100

USRB-CID2

…1,2,3,. . .,51

52,53,. . .,100

USRC CID=1 ISQ=100 C1=H

USRC-CID1

…1,2,3,. . .,51

1,2,3,. . .,100

…1.

USRD CID=1 ISQ=1 C1=H

USRD-CID1

STORAGE OF THE RESULTANT LIST

FUNCTION OF: CID / ADARUN NSISN= / COP1=H (RETAIN SET)

USRA CID=1 ISQ=1

USRA CID=0 ISQ=100

Pt. III

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 : S1, S2, S4, S8 ,S9

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,

IF TBI ELEMENT EXHAUSTED, WORK-3 BLOCK IO.

3. LOOP ISSUES AN RC.

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

LWKP2=

CACHE WORK 2

REVIEW COMPLEX

SEARCHES

46 – TOO MANY CIDs

PER USER

NQCID=

RELEASE set-name

7 – Sx TIME EXCEEDED

TLSCMD=

SEARCHES

ADABAS RESPONSE CODES FOR Sx PROCESSING

RSP.CODE

DATABASE

PROGRAM

QUICK NOTE ON READ LOGICAL AND HISTOGRAM PROCESSING

L3 :

L3

L3 : . . .

LOOP

RC

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)

4.END.LOOP ISSUES RC.

Response 175

Explanation: Aninconsistencywas detected between

the index and Data Storage.

Action: Run the "check" utilities (especially ADAICK and

technical support representative.

PER MESSAGES AND CODES MANUAL (NEW TO 7.4):

“ 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

• REQUIRED FOR ADABAS CLUSTER SERVICES
• INTEGRITY CHECK FOR NON-CLUSTER DATABASES
• SEE ADABAS SPEC ZAP AY743109 TO DISABLE

USE OF CID/GFID NOT A FACTOR

USE OF IBL NOT A FACTOR

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

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 ?