ATTACH

Description

Use the ATTACH command to attach a TurboIMAGE/XL database to a DBEnvironment. This command can only be used after the database name and the DBEnvironment name have been specified with SET commands.

You need exclusive access to the DBEnvironment when using the ATTACH command, because it is required for the first ATTACH command. Use the ISQL SELECT command to find out if it is being accessed by users.

   :RUN ISQL.PUB.SYS

   >isql=> SELECT * FROM SYSTEM.USER;

   >isql=> exit;

When a database is attached to a DBEnvironment, only the DBC is defined as an IMAGE/SQL user and default data type mapping is performed. Once attached, IMAGE/SQL utility commands can be used to update this default information. Refer to the ADD USER, UPDATE USER, UPDATE TYPE, and SPLIT commands for more information.

A database already attached to a specific DBEnvironment cannot be reattached. If you attempt to do this, an error message is issued. If you want to reattach it, you must perform a DETACH first and then ATTACH it.

To attach databases with the same database name but in different groups and accounts to the same DBEnvironment, you must use the OwnerName parameter to specify a different owner name for all but the first such database you attach.

If the DBEnvironment does not exist, IMAGE/SQL displays this message:

   DBE does not exist, do you want to create one? [Y/N] :

If you reply 'Y', a DBEnvironment and DBE files are automatically created for you. The files created are:

For example, if you issue the command

   SET SQLDBE MYDBE

and the DBEnvironment MYDBE does not exist, these files are created: MYDBE, MYDBEFL, MYDBELG, and MYDBECR.

The size of DBEFile is 5000 pages and LOG file is 10000 pages.

ATTACH triggers entering definitions for hash indices on all TurboIMAGE/XL master keys and detail search items in the system catalog of the DBEnvironment specified by the SET SQLDBE statement. All master keys, except P and Z data types, are entered as having unique hash indices. All detail search items, as well as master P and Z key types, are entered as having non-unique hash indices. With hash indices, performance gain can be observed only when the mapped column of the key or search item in an SQL statement employs an equality operator. That is, the SQL Optimizer will only derive an index scan on this mapped column if the operator used with it is "=". For other operators (such as > or <) used with this mapped column, the Optimizer will not choose the hash index scan on this mapped column.

The name of the hash index on the key item is derived by suffixing the mapped column name of the key item with '_A1' for the automatic master and '_M1' for the manual master. For the detail data set, '_Dn' is appended to the mapped column name of the search item where n is the path number. The definitions for hash indices can be seen in the views, SYSTEM.IMAGEKEY and CATALOG.IMAGEKEY, of the system catalog of the DBEnvironment. For an example, the SQL statement, Select * from SYSTEM.IMAGEKEY, after connecting to the DBEnvironment will display all hash indices.

If B-Tree indices are created on the key items of the selected master data sets using DBUTIL or DBSCHEMA, IMAGE/SQL enters definitions for the B-Tree indices in the system catalog of the DBEnvironment. Although the B-Tree index can only be created on the key item, the definitions for B-Tree indices on all of its related search items of the detail data sets are also entered in the SQL catalog. The B-Tree index on the key item of the master set is perceived as an EXPLICIT index and that on the related search item as IMPLICIT (virtual). For an example, if a B-Tree index is created on the key item which has paths to 16 detail data sets, a definition for an index for all 16 data sets will be entered. That is, this will result into 17 (1 for master and 16 for detail data sets ) definitions. The index on the key item of the master, except for P and Z types, is entered as 'unique' index. Other definitions, by default, are non-unique. The index name for the key items is derived by suffixing its mapped column name with '_B1'. For the related search item, its mapped column is suffixed with '_Vn' where n is the path number. With B-Tree indices, you can use operators such as '<=', and '> ' which are permissible in ALLBASE/SQL. The B-Tree indices can be seen in the views, SYSTEM.INDEX and CATALOG.INDEX, of the DBEnvironment.

If the key item or its related search item is split using the SPLIT command of IMAGESQL, definitions for both hash index and B-Tree index on the split item will not be entered. For an example, a key item on which a B-Tree index exists, is split, but its related search item in the detail set is not. Definitions for unique hash index and unique B-Tree index on this split key item will not be entered. However, definitions for non-unique hash index as well as non-unique B-Tree index on the related search item will be entered.

If the database is enabled for third-party indices (TPI), ATTACH enters definitions for all TPIs, excluding keyword indices and the ones for which the third-party does not provide information. The TPIs are entered as unique or non-unique based on the index configuration and information provided by the third-party for this index.

While keys, search items, and B-Tree indices are registered in the specified DBEnvironment, third-party indices are registered in all attached DBEnvironments.

Multiple index definitions on the same column can coexist and the SQL optimizer derives the optimal access plan based on the statistics present in the system catalog. In other words, the key or search item of the set can have a maximum of three index definitions. One will be a hash index definition entered automatically at ATTACH time, another can be a B-Tree index definition, and the third can be a third-party index definition. It is recommended that both B-Tree index and third-party index be not created on the same item as it will have an unnecessary impact on the performance (Optimizer calculates cost for each index).

The Optimizer decides which index to use and the proper order of operations to ensure that the most efficient path is used.

Example

In the following example, SALES is attached to PARTSDBE. The accompanying message summarizes the mapping that took place during the attach.

   >>SET TURBODB SALES

   >>SET SQLDBE PARTSDBE

   >>ATTACH

   Split 1 compound source field(s) (ATCWARN 32063).

   Mapped 15 source table/source field name(s) (ATCWARN 32062).

   Mapped 1 incompatible source type(s) (ATCWARN 32061).

   >>

To see the specific mapping for each data set and field, use the DISPLAY MAP command. In the following example, the display notes that the data type mapping performed for the mapped column CREDIT_RATING is imprecise. Also noted is the splitting of the compound source field OTHER_VENDORS into three mapped columns.

  >>DISPLAY MAP



  TurboIMAGE/XL DB : SALES.SERED.ATC

  DBEnvironment    : PARTSDBE.SERED.ATC

  Owner Name       : SALES



  MAPPED(SOURCE)   SOURCE             MAPPED       SOURCE     MAPPED

     TABLE         FIELD              COLUMN        TYPE       TYPE    NOTES

  ------------ ---------------- ------------------ ------- ----------- -----

  

  DATE_MASTER (DATE-MASTER)

               DATE             DATE               X6      CHAR(6)

  

  CUSTOMER (CUSTOMER)

               CUSTOMER#        CUSTOMER#          J2      INTEGER

               LAST-NAME        LAST_NAME          X16     CHAR(16)

               FIRST-NAME       FIRST_NAME         X10     CHAR(10)

               INITIAL          INITIAL            U2      CHAR(2)

               STREET           STREET             X26     CHAR(26)

               CITY             CITY               X12     CHAR(12)

               STATE            STATE              X2      CHAR(2)

               ZIP              ZIP                X6      CHAR(6)

               CREDIT-RATING    CREDIT_RATING      R2      FLOAT       I



  PRODUCT (PRODUCT)

               PRODUCT#         PRODUCT#           U8      CHAR(8)

               PRODUCT-DESCRIPT PRODUCT-DESCRIPT   X20     CHAR(20)





  VENDOR (VENDOR)

               VENDOR           VENDOR             X16     CHAR(16)

               STREET           STREET             X26     CHAR(26)

               CITY             CITY               X12     CHAR(12)

               STATE            STATE              X2      CHAR(2)



  INVENTORY (INVENTORY)

               PRODUCT#         PRODUCT#           U8      CHAR(8)

               ON-HAND-QTY      ON_HAND_QTY        J2      INTEGER

               VENDOR           VENDOR             X16     CHAR(16)

               OTHER-VENDORS    OTHER_VENDORS_1    X16     CHAR(16)     S

               OTHER-VENDORS    OTHER_VENDORS_2    X16     CHAR(16)     S

               OTHER-VENDORS    OTHER_VENDORS_3    X16     CHAR(16)     S

               UNIT-COST        UNIT_COST          P8      DECIMAL(7,0)

               LAST-SHIP-DATE   LAST_SHIP_DATE     X6      CHAR(6)

               LOCATION-BIN     LOCATION_BIN       Z2      DECIMAL(2,0)

               PART-INFO        PART_INFO          X60     CHAR(60)



  SALES (SALES)

               CUSTOMER#        CUSTOMER#          J2      INTEGER

               PRODUCT#         PRODUCT#           U8      CHAR(8)

               QUANTITY         QUANTITY           I1      SMALLINT

               PRICE            PRICE              J2      INTEGER

               TAX              TAX                J2      INTEGER

               TOTAL            TOTAL              J2      INTEGER

               PURCHASED-DATE   PURCHASED_DATE     X6      CHAR(6)

               DELIVERED-DATE   DELIVERED_DATE     X6      CHAR(6)



  NOTES:

    I: Imprecise(float)/Incompatible(others) mapping between source and

       mapped data types

    S: Source field has been split

  >>