HP 3000 Manuals HP 3000 Manuals
ATTACH
Attaches a TurboIMAGE/XL database to an SQL DBEnvironment.
Syntax
AT[TACH] [WITH OWNER=OwnerName]
Parameters
OwnerName specifies an owner for all SQL objects that need to be
created for the attached TurboIMAGE/XL database. This name
can be up to 17 bytes in length and can be made up of any
combination of letters (A to Z), decimal digits (0 to 9),
$, #, @, or _ (underscore). However, the first character
cannot be a decimal digit or an underscore. Lowercase
letters are automatically converted to uppercase letters.
Note that group and account names are not included in the
OwnerName parameter.
If this parameter is omitted, the owner name defaults to
the name of the TurboIMAGE/XL database defined in the most
recent SET TURBODB command.
Prerequisites
* SET SQLDBE issued.
* SET TURBODB issued.
* DBA authority.
* Database detached.
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:
-------------------------------------------------------------------------------------------
| |
| File Created File Name |
| |
-------------------------------------------------------------------------------------------
| |
| DBEConFile DBEnvironmentName |
| |
| DBEFile DBEnvironmentNameFL |
| |
| LOG file DBEnvironmentNameLG |
| |
| ATCINFO file DBEnvironmentNameCR |
| |
-------------------------------------------------------------------------------------------
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.[REV BEG]
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.
[REV END]
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 |
| >> |
_________________________________________________________________________________