10.3: IMAGE/SQL with TurboIMAGE/XL Enhancements

	MPE documents
	Complete PDF
	Table of Contents
	Index
⇓ Page Bottom

⇑ Page Top

Overview

Highlights

• Dynamic data set expansion for master sets (MDX or DDXM)
• Performance enhancement for databases with third-party indices
• 80 GB data sets
• Predicate locks when updating manual masters
• DBSCHEMA and P type with odd sub-item length
• B-Tree indices enhancement
• New 16-bit and 32-bit ODBC driver, ODBCLink/SE, bundled with IMAGE/SQL
• Support for third-party composite indices in IMAGE/SQL
• Scalability for DBPUT, DBDELETE, and DBUPDATE (CIU on)
• Dynamic detail data set expansion (DDX) fix

Manuals Bundled with IMAGE/SQL

• HP IMAGE/SQL Administration Guide
• Getting Started With HP IMAGE/SQL
• TurboIMAGE/XL Database Management System Reference Manual
  (Revised August 1997)
• ALLBASE/SQL Database Administration Guide
• ALLBASE/SQL Reference Manual (36216-90001)
• ALLBASE/SQL Message Manual
• ALLBASE/SQL Performance and Monitoring Guidelines
• ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL
• ODBCLink/SE Reference Manual

Updating IMAGE/SQL

TurboIMAGE/XL Enhancements

• Dynamic data set expansion for masters (MDX or DDXM)
• 80 GB data sets
• Performance Enhancement for databases with third-party indices
• DBSCHEMA and P type with odd sub-item length
• B-Tree indices
• Scalability for DBPUT, DBDELETE, and DBUPDATE (CIU on)

Dynamic Data Set Expansion for Masters

• maximum capacity
  is a required parameter and is a maximum number of entries the data set can contain. It must be less than or equal to 2³¹-1 (2,147,483,647). The maximum capacity is adjusted by TurboIMAGE/XL to represent a multiple of the blocking factor.
• initial capacity
  is a required parameter and is the initial, primary, or hashing capacity for the data set. It is the number of entries for which space will be allocated and initialized when the data set is created. More importantly, the initial capacity, and not the current capacity (initial capacity with expansions included), will be used in the hashing algorithm to calculate the primary address of the new entry being added. If initial capacity is very low, there can be frequent expansions which could result into both long synonym chains which degrade the performance of some TurboIMAGE intrinsics and disk fragmentation. Therefore, this number should be derived after giving consideration to the expected volume of current data, anticipated growth, and synonym chain lengths. It should be such that a minimum number of expansions are necessitated. The initial capacity must be between 1 and 2³¹ -1 inclusive but must be less than or equal to the maximum capacity. If it is not specified, or if it is either zero or equal to the maximum capacity, dynamic capacity expansion is not enabled for the data set, and maximum capacity is used for the data set file creation and primary address calculation. The initial capacity is adjusted to represent a multiple of the blocking factor.
• increment
  is an optional parameter and is either the number of entries or the percentage of the initial capacity by which the data set needs to be expanded each time. If a percentage is used, the percent sign (%) must follow the incremental amount. The increment is adjusted (reduced) for the last expansion so that it does not exceed the maximum capacity. This increment parameter can only be used if a valid initial capacity parameter is also specified. This number must be 1 to 100 inclusive for percent, or 1 to 2³¹ -1 (2,147,483,647) inclusive for number of entries. If it is very low, there can be frequent expansions leading to severe disk fragmentation and performance degradation. If it is very high, DBPUT that triggers the expansion may take long time to complete which could impact other database users.
  If the increment is not specified for the data set, or is zero, but the initial capacity is greater than zero and not equal to the maximum capacity, the increment for each expansion defaults to ten percent (10%) of the initial capacity for the data set. If the initial capacity is equal to the maximum capacity, or the initial capacity is zero, it indicates that the data set cannot be expanded and increment is ignored.

 {CAPACITY: | C:} maximum capacity [(blocking factor)]
   [,initial capacity [,increment]];
  

Dynamic Expansion and Placement of a New Entry

1. If the primary address in the original area is not occupied, the new entry resides there. The new entry also becomes a synonym chain head with a count of one.
2. If the primary address is already occupied by a primary entry which hashed to the same address, TurboIMAGE/XL scans a few blocks (predetermined based on internal values, also known as quick search) in the close proximity of the primary address to find a free entry (empty entry). If found, the new entry resides at this secondary address in the original area. Otherwise, search for a free entry is directed towards the expansion area. At this point, if the set has not been expanded at all and can be expanded, expansion will take place and the new entry will reside at the first entry address in the new expanded area.
   If prior expansion has already taken place, the pointer to the delete chain head and the last entry used in the file for the expansion area are interrogated to determine the secondary address in the expansion area for the new entry. When there is room, the new entry is assigned a secondary address in the expanded area, and the new entry becomes the last entry in the synonym chain. If the expanded area is full and can be expanded, it is further expanded to accommodate the new entry.
   When there is no room in the expanded area and cannot be expanded further, however, there is room in the original area based on the free entry count, the original area is scanned once again (long search) to find a secondary address for the new entry. If a free entry is found, the new entry resides there, and it becomes the last entry in the synonym chain. Otherwise, the set is full and DBPUT will fail.
3. If the primary address is occupied by a secondary entry, the secondary entry is relocated to another secondary address in either the original area or the expansion area. A free entry is searched as described in item 2 above. The new entry (primary) becomes a synonym chain head with a count of one. TurboIMAGE/XL performs synonym chain maintenance for the secondary entry which relocates.

Intrinsics and utilities

• DBINFO modes 205 and 208 (new for MDX) give detailed information pertaining to dynamic expansion. The buffer layout for mode 205 remains the same as that of DDX except that it is relevant to MDX. The buffer layout for new DBINFO mode 208 is in the following table.

  Buffer

  must be at least a 64-byte record and returns the following (each element is a 32-bit word):

  Element	Contents
  1	Primary (hashing) capacity for masters, 0 for details
  2	Current capacity, including expansions
  3	Maximum capacity
  4	Expansion threshold: -1 ... 100 percentage -1 this value means expansion is not triggered by percentage, or it is a non-expandable set.
  5	Delete chain free head (0 for non-expanded masters)
  6	High-water mark (0 for non-expanded masters)
  7	Expansion threshold: -1 ... 2 billion blocks -1 this value means expansion not triggered by traversing 3 blocks without success, or it is a non-expandable set.
  8...16	Reserved; 0 is returned

  DBINFO mode 208 returns information about internals which will be meaningful to only a few customers.
• DBUTIL SHOW database ALL command shows the presence of MDX. The SHOW database CAPACITY command gives detailed information pertaining to capacity parameters and identifies sets enabled for dynamic expansion.
  For more information, refer to the sixth edition of the TurboIMAGE/XL Database Management System Reference Manual.

NOTE: Select master data sets which need dynamic expansion based on anticipated growth and specify the capacity parameters (in particular the initial capacity) very carefully. Specifying low initial (primary/hashing) capacity and increment leading to frequent expansions may result into long synonym chains, which degrade the performance of some TurboIMAGE intrinsics, and disk fragmentation. Select initial capacity such that expansions will be minimal.

80 GB Data Sets

Performance Enhancement for Databases with Third-party Indices (TPI)

• DBFIND and DBGET when the set does not have TPI and the trace facility, if applicable, is turned off.
• Serial DBGET (modes 2 and 3) for a set with TPI when the mode need not be promoted to other mode (possibly a chained mode) by the third-party software.
• Chained DBGET (modes 5 and 6) for a DBFIND of an IMAGE search item or a B-Tree item for a set with TPI.

DBSCHEMA and P Type with Odd Sub-Item Length

B-Tree Indices

Key Features of B-Tree Indices

1. B-Tree index can be created only on the key item of the master data set.
2. B-Tree searches using the search items of all of its corresponding detail paths can be done.
3. You may create B-Tree indices for zero, one, or more master data sets.
4. DBSCHEMA has a new option, INDEXED, for the SET specification.
   New syntax

   
    NAME:setname,{M[ANUAL] | A[UTOMATIC]}[/INDEXED]
    [(read class list)/(write class list)][,device class];
           

5. DBUTIL has new commands and options.
   • New ADDINDEX, DROPINDEX, and REBUILDINDEX commands (to be used for one, more than one, or all masters).
     Syntax

     
      {ADDI[NDEX] | DROPI[NDEX] | REBUILDI[NDEX]}
      database name[/maintword] FOR {ALL | setnamelist | setnumlist}
                   

     Parameters

     setnamelist

     is the list setname[,...]

     setnumlist

     is the list setnum[,...]

     ALL

     means all master data sets for the database.

   • The SET command has a new BTREEMODE1 option to set DBFIND mode 1 access ON or OFF for a B-Tree wildcard search for X and U types. The default is OFF. The ON setting allows you to use B-Tree indices for generic search without making application changes. Also, it allows you to define your own database-wide wildcard character.
     New Syntax

     
      SET database name[/maintword]
      BTREEMODE1={ON | OFF}[,[WILDCARD=]c]
                   

     where c is any printable ASCII character, and the default character is @.
   • The SHOW command has a new INDEX, INDEXES, or INDICES option.
     New Syntax

     
      SHOW database name[/maintword]
      {INDEX | INDEXES| INDICES}
                   

   • CREATE, ERASE, PURGE, SECURE, RELEASE, and SHOW commands include B-Tree index files in their operation.
   • The MOVE command does not allow moving the index file.
6. DBCONTROL has these modes pertaining to B-Tree indices:

   13

   is for B-Tree index file control. That is, to ADD, DROP, REBUILD, ATTACH, or DETACH a B-Tree index file. This mode requires PRIVILEGED mode, and hence, must be used carefully as improper use may damage your system.

   14

   is used to obtain and control database-wide B-Tree information. This mode requires PRIVILEGED mode, and hence, must be used carefully as improper use may damage your system.

   15

   sets BTREEMODE1 ON, and optionally allows the wildcard character to be set for the current DBOPEN.

   16

   sets BTREEMODE1 OFF for the current DBOPEN.

7. DBINFO has these modes pertaining to B-Tree indices:

   209

   informs whether or not a B-Tree index exists for a master.

   113

   gives BTREEMODE1 setting and the wildcard character for the database as well as current DBOPEN.

8. DBFIND has these features pertaining to B-Tree indices:
   • Can be used for details as well as masters to specify B-Tree index searches.
   • Introduces a new fixed-format structured argument.
   • DBFIND modes with added functionality pertaining to B-Tree indices:

   1

   can continue to work as it does in releases prior to the release of B-Tree index feature (C.07.00), despite the presence of a B-Tree index, or it can be used for a B-Tree search if BTREEMODE1 is ON. Uses the classic mode 1 argument format.

   4

   used for B-Tree index searches on numeric as well as ASCII types and, for details, it returns accurate chain (super-chain, that is, multiple qualified chains) counts. Requires a new structured argument.

   10

   allows you to simulate DBFIND mode 1 as if there were no B-Tree index. This is the same as TPI mode 10. Requires the classic mode 1 argument format.

   21

   is the same as B-Tree index search using DBFIND mode 1, except it is a faster version and does not return accurate chain counts. Requires the classic mode 1 argument format.

   24

   is the same as B-Tree index search using DBFIND mode 4, except it is a faster version and does not return accurate chain counts. Requires a new structured argument.

   Allows wildcard search, as well as range search (<, <=, >, >=,"PK" for partial key, or [] for between).
9. DBGET modes 5 and 6 can be used for B-Tree index retrieval for masters or details. Super-chains are traversed for detail data sets.
10. KSAM/iX files are used for B-Tree index files. These are KSAM related key points:
    • There is one KSAM/iX privileged file with a filecode of -412 for each B-Tree index file. The size limit for this B-Tree index file is 4 GB. A jumbo master (larger than 4 GB) can have a B-Tree index file provided the B-Tree index file remains within its 4 GB limit.
    • KSAM/iX B-Tree index file is named using the POSIX file format with the "idx" extension (lowercase). For example:

      
       /ACTSALES/GRPSALES/ORDERS03.idx
                    

    • The KSAM file has the Native Language Support language specified to match the language of the database, if the key is an ASCII type.
11. Third-party index can coexist with B-Tree index, that is, on the same item of the data set.

Quick Start for Using B-Tree Indices

1. Identify the masters with ASCII key item that can benefit from B-Tree indices.
2. Create B-Tree indices using either of these methods:
   1. Use the INDEXED option of DBSCHEMA for new databases.
   2. Use the ADDINDEX command of DBUTIL for existing databases.
3. Set the BTREEMODE1 option ON using DBUTIL as follows:

   
    :Run DBUTIL.PUB.SYS
   
    >SET database name [/maintword] BTREEMODE1=ON
           

Scalability

 EN[ABLE] database name[/maintword] FOR DSEM

 DI[SABLE] database name[/maintword] FOR DSEM
  

Dynamic Detail Dataset Expansion (DDX) Fix

Problem Description

 |          |
 |          |
 |          |
 |          |
 |----------|----->  Old MPE EOF as well as IMAGE EOF (e.g., 1)
 |          |
 |INCREMENT |        New MPE EOF (e.g., 51) after expansion by
 |----------|----->  INCREMENT equivalent to 50 MPE records.
 |          |----->  New entries placed from this point on are lost.
 |          |
 |----------|----->  New IMAGE EOF (e.g., 101) as calculated by the
                     adjustment to CAPACITY of set, following the
                     expansion.
  

• While an actual DDX is being performed by DBPUT for one process, a second process (user) accessing the same data set for the first time causes execution of IMAGE's open data set module. In a narrow timing window, the result will be incorrect extraneous adjustment of the CAPACITY by adding the INCREMENT! Specifically, the CAPACITY has been incremented twice, while the MPE EOF has been correctly incremented — only once.

1. Use the FORM SETS command of QUERY, which gives the Current Capacity (CC) and Blocking Factor (BF) of each data set.

   
    :query
    HP32216D.03.11  QUERY/3000  TUE, NOV  4, 1997,  3:20 PM
    COPYRIGHT HEWLETT-PACKARD CO. 1976
   
    >b=dbusa PASSWORD = >>
    MODE = >>1
    >form sets
   
    DATA BASE: DBUSA                        TUE, NOV  4, 1997,  3:20 PM
   
    DATA BASE LANGUAGE ATTRIBUTE: NATIVE-3000
   
                     ITEM   CURRENT   ENTRY    ENTRY   BLOCKING
    SETS:      TYPE  COUNT  CAPACITY  COUNT    LENGTH  FACTOR
   
       PEPU01  D     3      1010      11       12      10
           

2. Calculate the IMAGE EOF of a DDX data set using the following formula:

   
    (CC + (BF-1))/BF
           

   For example:

   
    IMAGE EOF = (1010 + (10-1))/10 = 101
           

3. Verify this calculated EOF with the MPE EOF given by the command :LISTF dbnamenn,2 where dbnamenn is the file name of that DDX data set.

   
    :listf dbusa01,2
   
    ACCOUNT=  TESTACCT    GROUP=  DDX
   
    FILENAME  CODE  -----------LOGICAL RECORD-----------  ----SPACE----
                     SIZE  TYP        EOF     LIMIT R/B  SECTORS #X MX
   
    DBUSA01   PRIV   256W  FB         51       200   1    112   2  4
           

4. If the two EOFs do not match, there is a problem.
   In the example, MPE EOF of 51 and IMAGE EOF of 101 do not match — the problem exists.
   The SHOW DBUSA CAPACITY command of DBUTIL gives capacity parms:

   
                       No. of  %Max -----------Capacity------------- Dyn
    Data Set Name Type Entries Cap Maximum Current Initial Increment Exp:
   
    PEPU01        D    11      1   2000    1010    10      500       YES
   
           

   Based on the above formula for calculating MPE records, the initial capacity was 1 MPE record ( (10+9)/10), and the increment was 50 MPE records ( (500+9)/10). The correct physical EOF should be 51 (1+50). However, IMAGE EOF as shown by QUERY is 101 (1+50+50). This establishes that the IMAGE EOF was adjusted twice.

• Until you install the patch, avoid opening a data set from one process while another is expanding it!

1. Preallocate and pre-expand DDX data sets which are nearly-full by adding, in exclusive mode, many new dummy entries. Add more than your actual processing will add. Then delete them before your real processing begins.
2. Open all DDX-enabled data sets needed from each process which accesses them before allowing any process to add data to any of these sets.
3. Access the database exclusively, if possible, until the expansion has completed.
4. Disable DDX on any nearly-full data set, using an HP or third-party tool.

Important Information

1. The default for Critical Item Update option was changed from DISALLOWED to ALLOWED starting with Express 3 for 5.5.
2. A jumbo data set cannot be enabled for dynamic expansion.
3. PURGE and ERASE commands of DBUTIL are enhanced to prompt the user in session mode only (not batch) for confirmation (starting with version C.07.07).
4. If you want the TPI performance enhancement, you also need to get the third-party indexing software which supports this enhancement.

IMAGE/SQL Enhancements

• Predicate level lock when updating masters
• Support for B-Tree indices
• Support for third-party composite indices
• New ODBC driver, ODBCLink/SE

Predicate Locks when Updating Manual Masters

Benefits

 UPDATE Music.Albums set RecordingCo='ABC Music Ltd.' where Medium='ca';
  

 :DBUTIL

 HP30391C.07.04 TurboIMAGE/XL: DBUTIL (C)COPYRIGHT HEWLETT-PACKARD COMPANY 1987

 >>show Music locks
    For database MUSIC
                                                   PIN/    PROGRAM
       LOCKED ENTITY  /  ( - waiting process )     PATH    NAME   JOBNUM

 ALBUMS: MEDIUM = ca . . . . . . . . . . . . . . . 41/1    ISQL    #S18
  

 UPDATE Music.Albums set RecordingCo='California Music' where
 Medium='cd';
  

 :dbutil

 HP30391C.07.04 TurboIMAGE/XL:  DBUTIL (C) COPYRIGHT HEWLETT-PACKARD
 COMPANY 1987

 >> show Music locks
    For database MUSIC
                                                   PIN/    PROGRAM
       LOCKED ENTITY  /  ( - waiting process )     PATH    NAME   JOBNUM

 ALBUMS: MEDIUM = cd . . . . . . . . . . . . .     56/1    ISQL    #S21
 ALBUMS: MEDIUM = ca . . . . . . . . . . . . .     41/1    ISQL    #S18
  

Impact on current IMAGE/SQL applications

Support for B-Tree Indices

Third-Party Composite Indices

Important Information

1. If you have third-party indexing (TPI) enabled for the database and want to create B-Tree indices, obtain the correct version of the third-party software which recognizes this coexistence. Otherwise, you will get run-time errors returned by third-party software for new modes of DBFIND, DBINFO, and other utility commands.
2. The Optimizer derives an index scan based on the current statistics for the data set. Hence, it may select an index on the column not used in the SQL statement.

ODBCLink/SE