ADABAS TIPS PROGRAMMERS - PowerPoint

Document Sample
ADABAS TIPS PROGRAMMERS - PowerPoint Powered By Docstoc
					ADABAS TIPS FOR PROGRAMMERS




                        Jim Poole
                                1
1. FAST UPDATING WITH READ PHYSICAL

   DBA SETS REUSE-DS = OFF


                               (EXCLUSIVE USE OF FILE)
 READ EMPLOYEES PHYSICAL
 IF YEAR > 99 THEN end-program-logic
 IF YEAR < 60 THEN YEAR = YEAR + 2000
              ELSE YEAR = YEAR + 1900
 UPDATE SAME
 LOOP




  DBA SETS REUSE-DS = ON
                                                    2
2. UPDATING INVERTED LISTS


          AVOID UPDATING INVERTED LIST WITH
             SUCCESSIVELY LOWER VALUES




                                              3
3. FIELDS IN FILE VIEW

         LIMIT VIEWS TO JUST DESIRED FIELDS



BUT:

       FIELD SEQUENCE DOES NOT MATTER


               Example (CPU TIME):

               FA,FB,FC = FC,FB,FA
                        = FB,FC,FA
                        = FA,FC,FB


                                              4
3. FIELDS IN FILE VIEW


 EXAMPLE 1



           CASE 1 SPECIFIC CASE 2 GENERIC
            20 FIELD VIEW   214 FIELD VIEW INCREASE
BUFFER LENGTH    115 BYTES       1718 BYTES
ELAPSED TIME        9.4 MIN.       14.7 MIN.   56%
USER CPU                 -----         -----   90%
DATABASE CPU             -----         -----   70%



                                               5
3. FIELDS IN FILE VIEW                                                                                                                                                                 EXAMPLE 2
                                RELATIVE CPU TIME TO DECOMPRESS X FIELDS
0.00007




0.00006




0.00005                                                                                                                                                                                                          60 Flds
                                                                                                                                                                               50 Flds
0.00004
                                                                                                                                                40 Flds
0.00003
                                                                                                              30 Flds
                                                                               20 Flds
                                             10 Flds
0.00002        5 Flds

0.00001




     0
          5

               5
                       5
                            5

                                    5
                                         0

                                                 0
                                                      0
                                                              0

                                                                   0
                                                                           0

                                                                                0
                                                                                        0
                                                                                             0

                                                                                                     0
                                                                                                          0

                                                                                                                  0
                                                                                                                       0
                                                                                                                               0

                                                                                                                                    0
                                                                                                                                            0

                                                                                                                                                 0
                                                                                                                                                         0
                                                                                                                                                              0

                                                                                                                                                                      0
                                                                                                                                                                           0

                                                                                                                                                                                   0
                                                                                                                                                                                        0
                                                                                                                                                                                                0

                                                                                                                                                                                                     0
                                                                                                                                                                                                             0

                                                                                                                                                                                                                  0
                                                                                                                                                                                                                          0
                                                                                                                                                                                                                               0

                                                                                                                                                                                                                                       0
     00

              00
                   00
                           00

                                00
                                        01

                                             01
                                                     01
                                                          01

                                                                  01
                                                                       02

                                                                               02
                                                                                    02
                                                                                            02

                                                                                                 02
                                                                                                         03

                                                                                                              03
                                                                                                                      03
                                                                                                                           03

                                                                                                                                   03
                                                                                                                                        04

                                                                                                                                                04
                                                                                                                                                     04
                                                                                                                                                             04

                                                                                                                                                                  04
                                                                                                                                                                          05

                                                                                                                                                                               05
                                                                                                                                                                                       05
                                                                                                                                                                                            05

                                                                                                                                                                                                    05
                                                                                                                                                                                                         06

                                                                                                                                                                                                                 06
                                                                                                                                                                                                                      06
                                                                                                                                                                                                                              06

                                                                                                                                                                                                                                   06
    C

          C
                   C
                        C

                                C
                                     C

                                             C
                                                  C
                                                          C

                                                               C
                                                                       C

                                                                            C
                                                                                    C
                                                                                         C

                                                                                                 C
                                                                                                      C

                                                                                                              C
                                                                                                                   C
                                                                                                                           C

                                                                                                                                C
                                                                                                                                        C

                                                                                                                                             C
                                                                                                                                                     C
                                                                                                                                                          C

                                                                                                                                                                  C
                                                                                                                                                                       C

                                                                                                                                                                               C
                                                                                                                                                                                    C
                                                                                                                                                                                            C

                                                                                                                                                                                                 C
                                                                                                                                                                                                         C

                                                                                                                                                                                                              C
                                                                                                                                                                                                                      C
                                                                                                                                                                                                                           C

                                                                                                                                                                                                                                   C
                                                                                                                                                                                                                                       6
! SELECT/VIEW FOR ONLY NEEDED FIELDS !
4. FILE DESIGN, FIELD SEQUENCE



        PUT FREQUENTLY USED FIELDS UP FRONT
             ( DECOMPRESS UNTIL LAST FIELD REQUESTED.)




                     FOR NEW PE’S
                      02,XA,1,A,NU
                      02,XN,1,P,NU
   ( CAN EXPAND LENGTH LATER.)




                                                   7
4. FILE DESIGN, FIELD SEQUENCE

                                           cmdtime DECOMPRESS 1 FIELD
                           RELATIVE CPU TIME TO vs field position                                                                                                                                                             Fld #
0.00008                                                                                                                                                                               Fld #                                   261
                                                                                                                                                                                      200
0.00007                                                                                                                                       Fld #
                                                                                                                                              152
0.00006

                                                                                                      Fld #
0.00005                                                   Fld #                                       100
                                                           49
0.00004
                      Fld #
0.00003
                       1

0.00002



0.00001



     0
          1

                  1

                          1

                                  1

                                          1

                                                  9

                                                          9

                                                                  9

                                                                          9

                                                                                  9

                                                                                          0

                                                                                                  0

                                                                                                          0

                                                                                                                  0

                                                                                                                          0

                                                                                                                                  2

                                                                                                                                          2

                                                                                                                                                  2

                                                                                                                                                          2

                                                                                                                                                                  2

                                                                                                                                                                          0

                                                                                                                                                                                  0

                                                                                                                                                                                          0

                                                                                                                                                                                                  0

                                                                                                                                                                                                          0

                                                                                                                                                                                                                  1

                                                                                                                                                                                                                          1

                                                                                                                                                                                                                                  1

                                                                                                                                                                                                                                          1

                                                                                                                                                                                                                                                  1
      00

              00

                      00

                              00

                                      00

                                              04

                                                      04

                                                              04

                                                                      04

                                                                              04

                                                                                      10

                                                                                              10

                                                                                                      10

                                                                                                              10

                                                                                                                      10

                                                                                                                              15

                                                                                                                                      15

                                                                                                                                              15

                                                                                                                                                      15

                                                                                                                                                              15

                                                                                                                                                                      20

                                                                                                                                                                              20

                                                                                                                                                                                      20

                                                                                                                                                                                              20

                                                                                                                                                                                                      20

                                                                                                                                                                                                              26

                                                                                                                                                                                                                      26

                                                                                                                                                                                                                              26

                                                                                                                                                                                                                                      26

                                                                                                                                                                                                                                              26
     C

              C

                      C

                              C

                                      C

                                              C

                                                      C

                                                              C

                                                                      C

                                                                              C

                                                                                      C

                                                                                              C

                                                                                                      C

                                                                                                              C

                                                                                                                      C

                                                                                                                              C

                                                                                                                                      C

                                                                                                                                              C

                                                                                                                                                      C

                                                                                                                                                              C

                                                                                                                                                                      C

                                                                                                                                                                              C

                                                                                                                                                                                      C

                                                                                                                                                                                              C

                                                                                                                                                                                                      C

                                                                                                                                                                                                              C

                                                                                                                                                                                                                      C

                                                                                                                                                                                                                              C

                                                                                                                                                                                                                                      C

                                                                                                                                                                                                                                              C
                                                                                                                                                                                                                                      8
! MOST FREQUENTLY USED FIELDS UP FRONT !
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




                                                       9
5. FILE DESIGN: NU,FI,dflt. Eg 1
       FI vs (NU,DEFAULT) COMPRESSION: TEST WHOLE FILE


 FILE 1 261 FIELDS   NUMBER OF COMPRESSED RECORDS 19,979
                     RAW DATA                  37,808,780   BYTES
   NU,DFLT,some FI   COMPRESSED DATA           10,400,702   BYTES
                     COMPRESSION RATE                72.9   %
                     LARGEST COMPRESSED RECORD        946   BYTES



 FILE 2 261 FIELDS   NUMBER OF COMPRESSED RECORDS 19,979
                     RAW DATA                  37,808,780   BYTES
   ALL FI EXCEPT:    COMPRESSED DATA           37,499,369   BYTES
       UNPACKED      COMPRESSION RATE                 1.3   %
                     LARGEST COMPRESSED RECORD      2,254   BYTES
       PE DEs


                                                            10
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


                                                    11
5. FILE DESIGN: NU,FI,dflt . Eg. 1
              FI vs (NU,DEFAULT) DECOMPRESSION: TEST WHOLE FILE
0.00008

                                                                                                                                                                                                                  60 Flds
0.00007
                                                                                                                                                                               50 Flds
0.00006
                                                                                                                                                40 Flds
                                                                                                                  30 Flds
0.00005
                                                                               20 Flds
                                             10 Flds
0.00004
                   5 Flds
0.00003                                                                                                                                                                                             KEY:

0.00002                                                                                                                                                                                                          NU,Dflt

0.00001                                                                                                                                                                                                          FIXED

     0
          5

               5
                       5
                            5

                                    5
                                         0

                                                 0
                                                      0
                                                              0

                                                                   0
                                                                           0

                                                                                0
                                                                                        0
                                                                                             0

                                                                                                     0
                                                                                                          0

                                                                                                                  0
                                                                                                                       0
                                                                                                                               0

                                                                                                                                    0
                                                                                                                                            0

                                                                                                                                                 0
                                                                                                                                                         0
                                                                                                                                                              0

                                                                                                                                                                      0
                                                                                                                                                                           0

                                                                                                                                                                                   0
                                                                                                                                                                                        0
                                                                                                                                                                                                0

                                                                                                                                                                                                     0
                                                                                                                                                                                                             0

                                                                                                                                                                                                                  0
                                                                                                                                                                                                                          0
                                                                                                                                                                                                                               0

                                                                                                                                                                                                                                       0
     00

              00
                   00
                           00

                                00
                                        01

                                             01
                                                     01
                                                          01

                                                                  01
                                                                       02

                                                                               02
                                                                                    02
                                                                                            02

                                                                                                 02
                                                                                                         03

                                                                                                              03
                                                                                                                      03
                                                                                                                           03

                                                                                                                                   03
                                                                                                                                        04

                                                                                                                                                04
                                                                                                                                                     04
                                                                                                                                                             04

                                                                                                                                                                  04
                                                                                                                                                                          05

                                                                                                                                                                               05
                                                                                                                                                                                       05
                                                                                                                                                                                            05

                                                                                                                                                                                                    05
                                                                                                                                                                                                         06

                                                                                                                                                                                                                 06
                                                                                                                                                                                                                      06
                                                                                                                                                                                                                              06

                                                                                                                                                                                                                                   06
    C

          C
                   C
                        C

                                C
                                     C

                                             C
                                                  C
                                                          C

                                                               C
                                                                       C

                                                                            C
                                                                                    C
                                                                                         C

                                                                                                 C
                                                                                                      C

                                                                                                              C
                                                                                                                   C
                                                                                                                           C

                                                                                                                                C
                                                                                                                                        C

                                                                                                                                             C
                                                                                                                                                     C
                                                                                                                                                          C

                                                                                                                                                                  C
                                                                                                                                                                       C

                                                                                                                                                                               C
                                                                                                                                                                                    C
                                                                                                                                                                                            C

                                                                                                                                                                                                 C
                                                                                                                                                                                                         C

                                                                                                                                                                                                              C
                                                                                                                                                                                                                      C
                                                                                                                                                                                                                           C

                                                                                                                                                                                                                                   C
                                                                                                                                                                                                                          12
                                                          ! FIXED IS MORE CPU !
5. FILE DESIGN: NU,FI,dflt. Eg 2
                                          field full

 FI vs NU vs DFLT DECOMPRESSION OF ONE 1b FLD. FIELD FULL
1.2




 1




0.8




0.6                                                                                  KEY:

                                                                                          NU
0.4


                                                                                          FIXED
0.2
                                                                                          DfLT

 0
      1   2   3   4   5   6   7   8   9    10          11   12   13   14   15   16   17   18   19    20
                                                                                                13
                  Test runs
5. FILE DESIGN: NU,FI,dflt. Eg 2
 FI vs NU vs DFLT DECOMPRESSION OF ONE 1b FLD. FIELD EMPTY
1.4




1.2




 1




0.8


                                                                             KEY:
0.6

                                                                                  NU
0.4
                                                                                  FIXED

0.2
                                                                                  DfLT

 0
      1   2   3   4   5   6   7   8   9   10   11   12   13   14   15   16   17   18   19   20

                                                                                       14
                  Test runs
5. FILE DESIGN: NU,FI,dflt. Eg 3
 FI vs NU vs DFLT COMPRESSION 20b FIELDS. FIELDS FULL
0.00007




0.00006




                                                                    40 Flds
0.00005




0.00004



                                           20 Flds                      KEY:
0.00003


                  5 Flds                                                     NU
0.00002
                                                                             FIXED
0.00001
                                                                             DfLT

     0
          1   2     3      4   5   6   7      8      9   10   11   12   13    14        15

                                                                                   15
5. FILE DESIGN: NU,FI,dflt . Eg 3
 FI vs NU vs DFLT COMPRESSION 20b FIELDS. FIELDS EMPTY
0.00007




0.00006




                                                                    40 Flds
0.00005




0.00004



                                           20 Flds                      KEY:
0.00003


                  5 Flds                                                     NU
0.00002
                                                                             FIXED

0.00001
                                                                             DfLT

     0
          1   2     3      4   5   6   7      8      9   10   11   12   13     14        15
                                                                                    16
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



NATURAL
                0010 OPTIONS GFID=OFF
OPTION:

                USE ON LOW-SHARE PROGRAMS
                (BATCH, etc)
          ( GFID IS DEFAULT ON ALL CATALOGUED PGMS)   17
7. DESCRIPTORs
  COST:               BEST IF:
  DISK SPACE          MORE UNIQUE/SELECTIVE
  UPDATE IO/CPU       SMALLER
                      LESS UPDATED


           DESCRIPTOR REMOVAL PROJECT:
                           BEFORE    AFTER    CHANGE
                           ------   ------    ------
          Number of DE's:    2120      1951      -8%
     Total DE bytes/recs:  41,248   34,671      -16%
    Total No. of Records:  231.9M   232.6M        0%
Used Data Storage Blocks:   10.3M    10.4M       +1%
       Used Index Blocks:   18.1M    13.4M      -26%

   Cmd time / update cmd:   .00110   .00087    +21%
   Duration / update cmd:   .01221   .00696    +43%

                                                  18
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


 CAUTION:
 THE SECOND TEST RUN IS GENERALLY FASTER THAN THE FIRST




                                                   19
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: COP1=R
NATURAL:      NATURAL SECURITY / WH=OFF
NATIVE SQL:   RETURN

CONSIDERATIONS:
BATCH JOBS
ONLINE USERS
BACKGROUND TASKS
REMOTE CALLS

                                                    20
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
ADABAS TIMES OUT (9-2) USER A




                                                21
11. ET TIMING
INFREQUENT                FREQUENT


HOLD CONTENTION
TIMEOUT RISKS (rsp 9-2)
                          EXCESSIVE COMMANDS
WORK WRAPAROUND
                          IO DELAY (DBA LFIOP / FAST DASD)
      (rsp 9-15,8)
DELAY DB BACKUP
DELAY NUC SHUTDOWN




            THE OLDE GUIDELINES
             ”LOGICAL UNIT OF WORK”
             TIME


                                                         22
12. FILE DESIGN: GROUPS

  EXAMPLE 1                       EXAMPLE 2
  01,AA,10,A.                     01,DG.
  01,BB,05,P.                      02,AA,10,A.
  01,CC,30,A.                      02,BB,05,P.
  01,DD,10,A.                      02,CC,30,A.
  01,EE,12,A,NU,DE.                02,DD,10,A.
  01,FF,30,A.                      02,EE,12,A,NU,DE.
                                   02,FF,30,A.
FB: AA,BB,CC,DD,EE,FF.          FB:          DG.

 GROUPS WOULD BE NICE BUT NATURAL DOESN’T USE THEM

 FB:   AA,010,A,BB,005,P,CC,030,A,DD,010,A,EE,012,A,FF,030,A.


 ADABAS NATIVE SQL:
                                                                23
13. PE ACCESS
 A. GROUP         B. ELEMENT             C. FIELD           D. ADABAS
   RANGE             RANGE                 LIST               GROUP
                                       02 JB-FLD   (1:1)
                                       02 JC-FLD   (1:1)
02 JA-GRP(1:5)   02   JA-GRP           02 JD-FLD   (1:1)   JA1-5.
   03 JB-FLD     03   JB-FLD   (1:5)   02 JE-FLD   (1:1)
   03 JC-FLD     03   JC-FLD   (1:5)   02 JF-FLD   (1:1)
   03 JD-FLD     03   JD-FLD   (1:5)   02 JN-FLD   (1:1)
   03 JE-FLD     03   JE-FLD   (1:5)   02 JO-FLD   (1:1)
   03 JF-FLD     03   JF-FLD   (1:5)   02 JG-FLD   (1:1)
   03 JN-FLD     03   JN-FLD   (1:5)   02 JH-FLD   (1:1)
   03 JO-FLD     03   JO-FLD   (1:5)   02 JI-FLD   (1:1)
   03 JG-FLD     03   JG-FLD   (1:5)   02 JJ-FLD   (1:1)
   03 JH-FLD     03   JH-FLD   (1:5)   02 JK-FLD   (1:1)
   03 JI-FLD     03   JI-FLD   (1:5)   02 JL-FLD   (1:1)
   03 JJ-FLD     03   JJ-FLD   (1:5)   02 JM-FLD   (1:1)
   03 JK-FLD     03   JK-FLD   (1:5)   02 JB-FLD   (2:2)
   03 JL-FLD     03   JL-FLD   (1:5)   02 JC-FLD   (2:2)
   03 JM-FLD     03   JM-FLD   (1:5)   02 JD-FLD   (2:2)
                                       : : : : :
                                       02 JL-FLD   (5:5)
                                                                    24
                                       02 JM-FLD   (5:5)
13. PE ACCESS: FB.            RB len = 345
A. GROUP     JB0001,002,A,JC0001,006,A,JD0001,009,A,JE0001,009,A,JF0001,009,A,...
             1,009,A,JH0001,001,A,JI0001,004,A,JJ0001,002,A,JK0001,004,A,...
  RANGE      ,A,JC0002,006,A,JD0002,009,A,JE0002,009,A,JF0002,009,A,JN0002,006...
   FBL=910   0002,001,A,JI0002,004,A,JJ0002,002,A,JK0002,004,A,JL0002,003,P,...
             006,A,JD0003,009,A,JE0003,009,A,JF0003,009,A,JN0003,006,A,...
             ,JI0003,004,A,JJ0003,002,A,JK0003,004,A,JL0003,003,P,JM0003,...
             04,009,A,JE0004,009,A,JF0004,009,A,JN0004,006,A,JO0004,002,A,...
             4,A,JJ0004,002,A,JK0004,004,A,JL0004,003,P,JM0004,003,P,JB0005,...
             E0005,009,A,JF0005,009,A,JN0005,006,A,JO0005,002,A,JG0005,009,...
             ,002,A,JK0005,004,A,JL0005,003,P,JM0005,003,P.

B. ELEMENT   JB0001-0005,002,A,JC0001-0005,006,A,JD0001-0005,009,A,JE0001-0005,
             009,A,JF0001-0005,009,A,JN0001-0005,006,A,JO0001-0005,002,A,
   RANGE     JG0001-0005,009,A,JH0001-0005,001,A,JI0001-0005,004,A,JJ0001-0005,
   FBL=252
             002,A,JK0001-0005,004,A,JL0001-0005,003,P,JM0001-0005,003,P.

 C. FIELD    JB0001,002,A,JC0001,006,A,JD0001,009,A,JE0001,009,A,JF0001,009,A,...
             1,009,A,JH0001,001,A,JI0001,004,A,JJ0001,002,A,JK0001,004,A,...
   LIST      ,A,JC0002,006,A,JD0002,009,A,JE0002,009,A,JF0002,009,A,JN0002,...
   FBL=910   0002,001,A,JI0002,004,A,JJ0002,002,A,JK0002,004,A,JL0002,003,P,...
             006,A,JD0003,009,A,JE0003,009,A,JF0003,009,A,JN0003,006,A,JO0003,...
             ,JI0003,004,A,JJ0003,002,A,JK0003,004,A,JL0003,003,P,JM0003,003,...
             04,009,A,JE0004,009,A,JF0004,009,A,JN0004,006,A,JO0004,002,A,...
             4,A,JJ0004,002,A,JK0004,004,A,JL0004,003,P,JM0004,003,P,JB0005,...
             E0005,009,A,JF0005,009,A,JN0005,006,A,JO0005,002,A,JG0005,009,A,...
             ,002,A,JK0005,004,A,JL0005,003,P,JM0005,003,P.
D. ADABAS JA1-5.                                                         25
  GROUP FBL=6
13. PE ACCESS: IFB.                    ADABAS FB-IFB TRANSLATION TIME
                                              ADABAS RELATIVE CPU




A. GROUP                                                                                                FBL = 910
  RANGE PGMA                                                                                            IFBL= 1740


B. ELEMENT                                                                                              FBL = 252
                                                                                                        IFBL= 1740
   RANGEPGMB

 C. FIELD                                                                                                FBL = 910
   LIST PGMC                                                                                             IFBL= 1740


D. ADABAS                                                                                               FBL =   10
  GROUPPGMD                                                                                             IFBL=   84

               0   0.0001   0.0002   0.0003          0.0004         0.0005   0.0006   0.0007   0.0008




                                                                                                                26
TEST: RAN EACH 25K TRANSLATIONS, FOR 2 RUNS
13. PE ACCESS: TEST.
       FACTORS:

        1. CALLS PER TRANSLATION         (eg. 50)
        2. ELEMENTS PER PE GROUP         (eg. 14)
        3. OCCURRENCES PER PE            (eg. 5)


A. GROUP
   RANGE
B. ELEMENT          FOR I = 1 TO 50,000
   RANGE            READ FILE BY ISN FROM 1 THRU 50       5 RUNS

  C. FIELD     X    LOOP
                    * RTN TO CLEAR OUT IFB/GCIDS      X   EACH
    LIST            LOOP

D. ADABAS
   GROUP


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




                        NATURAL RELATIVE CPU TIME
            4




A. GROUP
  RANGE 3


B. ELEMENT
   RANGE 2


 C. FIELD
            1
   LIST

                0   2     4     6           8              10   12   14   16


                                                                          28
13. PE ACCESS: ADA.CPU.

                      ADABAS RELATIVE CPU TIME FOR ALL CALLS
                                  ADABAS RELATIVE CPU FOR ALL




  A. GROUP
    RANGE A
        PGM




 B. ELEMENT
    RANGE B
        PGM




   C. FIELD
     LIST PGM C

  D. ADABAS
    GROUP D
         PGM



                                                                29
                  0         5      10     15     20     25      30
13. PE ACCESS: ADA.CPU.ADJUSTED
  ADABAS RELATIVE CPU TIME FOR READS (NO TRANSLATIONS)
             5




 A. GROUP
   RANGE 4

 B. ELEMENT
    RANGE 3

  C. FIELD
    LIST     2




 D. ADABAS
   GROUP 1

                 0    5       10    15      20         25

                                                  30
                     CONCLUSIONS?
14. MU/PE OCCURRANCES
BRING BACK LARGER EXPECTED NUMBER OFF OCCURRANCES
GET SAME IF MORE THAN C*VAR

READ DA-FILE
 Use MU-FIELD(1:5)
 IF C*MU-FIELD > 5
    THEN LOOP TO GET SAME THE REST



ASSUMING STANDARD DISTRIBUTION WHERE
  X IS MEAN OR AVERAGE
 O IS STANDARD DEVIATION

X        YIELDS 50% OF ALL OCCs
X + O    YIELDS 83% OF ALL OCCs
X + 2O   YIELDS 97.5% OF ALL OCCs
X + 3O   YIELDS 99.5% OF ALL OCCs
                                                31
15. EXCLUSIVE CONTROL FOR APPS
STANDARD: OPEN WITH EXU=fnr OR EXF=fnr


APPLY ADABAS SECURITY FILE=fnr, UPD / READ


DB-LEVEL:   DB READONLY
            SINGLE-USER-MODE
            SEPARATE SVC




                                             32
16. RC COMMANDS         ! ARE NOT EXPENSIVE !
RELEASE RESOURCES:
ISN LISTS (TBI), SEQ.COMMANDS (TBQ), FORMAT BUFFERS (LFP)
                                               MEAN
                                        MEAN    REL MEAN
NATURAL:                         CMD DUR       CPU     IO
LOOP,PGM-END,WHENEVER             RC .00010 .000005 0.00
                                 RI   .00013   .000009   0.00
                                 ET   .00016   .000010   0.00
                                 L2   .00017   .000032   0.03
DATABASE: NO IO       EXAMPLE    L9   .00022   .000043   0.02
  /CHAINED/ OFF UQ               L1   .00112   .000069   0.23
  TO TBI,TBQ,LFP                 L3   .00106   .000083   0.19
                                 L6   .00183   .000100   0.15
                                 OP   .00538   .000109   0.67
                                 S1   .00090   .000112   0.17
                                 L4   .00495   .000114   0.04
                                 A1   .00122   .000296   0.15
                                 E1   .00387   .000316   0.52
                                 N1   .00470   .000552   0.84
                                 S2   .00547   .001144   1.31
                     MEAN TIME TO DB: .00003 SECONDS     33
17. BEWARE WHERE


      ADABAS WORK
    FIND . . . . WITH …..
    READ …. STARTING FROM ….       ENDING AT
    HISTOGRAM …. STARTING FROM       THRU

                      WHERE
                   ACCEPT REJECT
                        IF
           NATURAL WORK

 IF IN DOUBT, DDLOG/CMD.LOG
                                          34
17. WHERE/ACCEPT/REJECT/IF
                    FIND EMP WITH       FIND EMP WITH
FIND EMP WITH
                       CITY=BARCELONA      CITY=BARCELONA
   CITY=BARCELONA
                    WHERE NAME=GARCIA      AND NAME=GARCIA
DISPLAY -----
                    DISPLAY -----       DISPLAY -----
LOOP
                    LOOP                LOOP

NO CMD ISN ISQ      NO CMD ISN ISQ      NO   CMD   ISN ISQ
 1 RC                1 RC                1   RC
 2 S1 400 27         2 S1 400 27         2   S1    403   4
 3 L1 403 27         3 L1 403 27         3   L1    406
 4 L1 406            4 L1 406            4   L1    430
 5 L1 410            5 L1 410            5   L1    479
: : : : : : :       : : : : : : :        6   RC
27 L1 487           27 L1 487            7   RC
28 L1 493           28 L1 493            8   RC
29 RC 493           29 RC 493
30 RC               30 RC
31 RC               31 RC
                                                         35
18. WHO’S HOLDING?
                             CM FNR ISN
                             L6 16 400
READ EMPLOYEE
     BY CITY = 'BARCELONA'   L6 16 403
        THRU 'BARCELONA'     A1 16 403
 ACCEPT IF NAME = 'GARCIA'   ET
 UPDATE SEX = SEX            L6 16 406
 END TRANSACTION             A1 16 406
LOOP                         ET
                             L6 16 410
                             L6 16 415
                             L6 16 416
                             L6 16 423
                             L6 16 427
                             L6 16 428
                             L6 16 430
                             A1 16 430
                             ET

                                          36
18. WHO’S HOLDING? Cont.      CM FNR ISN
                              L6 16 400
                              L6 16 403
READ EMPLOYEE
                              L1 16 403
      BY CITY = 'BARCELONA'
         THRU 'BARCELONA'     A1 16 403
IF NAME = 'GARCIA'            ET
  THEN DO                     L6 16 406
  GET EMPLOYEE *ISN           L1 16 406
  UPDATE SEX = SEX            A1 16 406
  END TRANSACTION             ET
  DOEND                       L6 16 410
LOOP                          L6 16 415
END
                              L6 16 416
                              L6 16 423
                              L6 16 427
                              L6 16 428
                              L6 16 430
                              L1 16 430
                              A1 16 430
                              ET           37
18. WHO’S HOLDING? Cont.          CM FNR ISN
                                  L3 16 400
                                  L3 16 403
0010 READ EMPLOYEE
                                  L4 16 403
0020      BY CITY = 'BARCELONA'
0030         THRU 'BARCELONA'     A1 16 403
0040 IF NAME = 'GARCIA'           ET   0   0
0050 THEN DO                      L3 16 406
0060 GET EMPLOYEE *ISN            L4 16 406
0070 UPDATE (0060) SEX = SEX      A1 16 406
0080 END TRANSACTION              ET   0   0
0090 DOEND                        L3 16 410
0100 LOOP                         L3 16 415
0110 END
                                  L3 16 416
                                  L3 16 423
                                  L3 16 427
                                  L3 16 428
                                  L3 16 430
                                  L4 16 430
                                  A1 16 430
                                  ET   0   0   38
19. NATURAL FIND TIPS      ( AS OF NAT 3.1.5)


 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.




                                                      39
20. EXPANDED FILES



      EXPANDED FILE         vs.        4-BYTE ISN FILE


  MANAGEABILITY             vs     PERFORMANCE

  SPACE SAVINGS             vs     SPACE SAVINGS
  (3B vs 4B ISN IN ASSO)           (NO DUPE DE-VALUE
                                    ACROSS FILES)




! UNLESS MANAGEABILITY IS AN ISSUE, USE 4-BYTE ISN FILES !
(vs EXPANDED FILES)
                                                         40
21. ADAREP COUNT / NOCOUNT
              USE NOCOUNT AS THE DEFAULT

FOR EXAMPLE    133 FILES
               230M RECORDS
         DBnnn 8391 BLOCKSIZE (A=4136b)




  ADAREP NOCOUNT                 ADAREP ‘COUNT’


   DDASSOR1    38 READS          DDASSOR1 187,036 READS

   .05 MINUTES ELAPSED            5.84 MINUTES ELAPSED
   .27 SECONDS CPU               34.81 SECONDS CPU




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


SOFT COUPLING:

 FIND VEHICLES-ADA WITH MAKE = 'RENAULT'
      AND COUPLED TO EMPLOYEE
      VIA PERSONNEL-ID = PERSONNEL-ID WITH NAME = 'JONES'
 DISPLAY PERSONNEL-ID MAKE MODEL COLOR
 LOOP



                                                    42
                                     ----IO----   22. LOGICAL COUPLING:
CM FNR ISN ISQ OP           CID      A D W
S1   16   205   10   ..   00200101   6   1   0 SB=AE,20,A.    VB=JONES
S1   18     0    0   ..   00300101   5   0   0 SB=AA,15,A,D,AD,20,A
L1   16   565    0   .N   00200101   0   1   0      VB=20007500 RENAULT
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                       43
S1   18     0    0   ..   00300101   0   0   0
                                           22. 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.
                     VB=RENAULT                  JONES




                                                                44
22. FILE COUPLING     LOGICAL VS SOFT



LOGICAL COUPLING:             SOFT COUPLING:




25 DATABASE CALLS             5 DATABASE CALLS
48 DATABASE IOs              50 DATABASE IOs
RELATIVE CPU TIME ABOUT =    RELATIVE CPU TIME ABOUT =




                                                    45
23. EOR ,DE ,empty fields
 REVIEW: DESCRIPTORS. SOURCE FIELD: NU vs (NOT-NU OR FI)

   NATURAL DDM
    DB Name       F Leng S D
    A1 A1-FIELD   A   10 N
    AX AX-FIELD   A   10 N D
       : :                        FIND w/ (AX , S1)
    S1 S1-SUPER   A   20 N S      READ by (AX , S1)
    * -- SOURCE FIELD(S)---     HISTOGRAM (AX , S1)
    * A1-FIELD(1-10)
    * AX-FIELD(1-10)
                  S=N,b,F

 ADABAS

         A1 AX                 DE=AX       DE=S1
   REC 1 X   X      …
                                REC 1        REC 1
   REC 2 X    b     …
   REC 3 X   X      …            REC 3        REC 3

                                                      46
23. EOR ,DE ,empty fields     EOR – END OF RECORD




   ADABAS
               ALL FIELDS DEFINED ACCORDING TO
    UTILITY
   UPDATES     COMPRESSION OPTIONS (NU,non-NU)
                  Non-NU TREATED AS non-NU



    USER  FIELDS NOT REFERENCED AT EOR ARE
  PROGRAM
  UPDATES NOT DEFINED
                    Non-NU TREATED AS NU




                                                    47
                                         DB Name    F Len S D
23. EOR ,DE ,empty fields                AA AA-FLD A    5 N
    EXAMPLE: NEW FILE                    BB BB-FLD A    5   D
                                         CC CC-FLD A    5   S
                                         SS SS-SUP A 10 N S
    ADACMP => ADALOD                     * - SOURCE FIELD(S)-
        AA = ‘AAAAA’                     *   AA-FLD(1-5)
                                         *   CC-FLD(1-5)
       Length    ISN     AA        BB     CC
NEW REC 0010 00000001 06C1C1C1C1C1 0240 0240



  FIND file BB-FLD = ‘   ‘    FIND file SS-SUP = ‘   ‘
  DISPLAY *ISN                DISPLAY *ISN


  RESULTS:                    RESULTS:
             1                           1

                                                         48
                                          DB Name    F Len S D
23. EOR ,DE ,empty fields                 AA AA-FLD A    5 N
    EXAMPLE: STORE NEW RECORD             BB BB-FLD A    5   D
                                          CC CC-FLD A    5   S
                                          SS SS-SUP A 10 N S
    STORE AA-FLD =‘AAAAA’                 * - SOURCE FIELD(S)-
                                          *   AA-FLD(1-5)
                                          *   CC-FLD(1-5)
       Length    ISN     AA          BB    CC
NEW REC 000C 00000002 06C1C1C1C1C1



  FIND file BB-FLD = ‘   ‘    FIND file SS-SUP = ‘    ‘
  DISPLAY *ISN                DISPLAY *ISN


  RESULTS:                    RESULTS:
             1                            1

                                                          49
                                          DB Name    F Len S D
23. EOR ,DE ,empty fields                 AA AA-FLD A    5 N
    EXAMPLE: STORE NEW RECORD             BB BB-FLD A    5   D
                                          CC CC-FLD A    5   S
                                          SS SS-SUP A 10 N S
    STORE AA-FLD = ‘AAAAA’                * - SOURCE FIELD(S)-
          BB-FLD = ‘     ‘                *   AA-FLD(1-5)
                                          *   CC-FLD(1-5)
       Length    ISN     AA        BB      CC
NEW REC 000E 00000003 06C1C1C1C1C1 0240



  FIND file BB-FLD = ‘   ‘    FIND file SS-SUP = ‘    ‘
  DISPLAY *ISN                DISPLAY *ISN

  RESULTS:
                              RESULTS:
             1
                                          1
             3

                                                          50
                                         DB Name    F Len S D
23. EOR ,DE ,empty fields                AA AA-FLD A    5 N
    EXAMPLE: STORE NEW RECORD            BB BB-FLD A    5   D
                                         CC CC-FLD A    5   S
                                         SS SS-SUP A 10 N S
    STORE AA-FLD = ‘AAAAA’               * - SOURCE FIELD(S)-
          CC-FLD = ‘     ‘               *   AA-FLD(1-5)
                                         *   CC-FLD(1-5)
       Length    ISN     AA        BB     CC
NEW REC 0010 00000004 06C1C1C1C1C1 0240 0240



  FIND file BB-FLD = ‘   ‘    FIND file SS-SUP = ‘   ‘
  DISPLAY *ISN                DISPLAY *ISN
  RESULTS:
                              RESULTS:
             1
                                         1
             3
                                         4
             4
                                                         51
                                           DB Name    F Len S D
23. EOR ,DE ,empty fields                  AA AA-FLD A    5 N
    EXAMPLE: UPDATE TO BLANKS              BB BB-FLD A    5   D
                                           CC CC-FLD A    5   S
                                           SS SS-SUP A 10 N S
    UPDATE BB = ‘             ’            * - SOURCE FIELD(S)-
           (ISN 2)                         *   AA-FLD(1-5)
                                           *   CC-FLD(1-5)
       Length    ISN     AA          BB     CC
NEW REC 000E 00000002 06C1C1C1C1C1 0240



  FIND file BB-FLD = ‘   ‘      FIND file SS-SUP = ‘   ‘
  DISPLAY *ISN                  DISPLAY *ISN
  RESULTS:
             1                  RESULTS:
             2                             1
             3                             4
             4
                                                           52
DAS ENDE




           53

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:99
posted:1/18/2009
language:English
pages:53
The Slasher The Slasher
About