HP 3000 Manuals HP 3000 Manuals
SPLIT
Divides a mapped column into two or more smaller mapped columns.
Syntax
SP[LIT] MapTable.MapCol
INTO NewColSpec [,...]
Parameters
MapTable is the name of the mapped table containing the mapped
column.
MapCol is the name of the mapped column to be split into smaller
units.
NewColSpec is the specification of the new mapped column. Repeat this
parameter for each new mapped column. The following syntax
is used for the new mapped columns:
NewMapCol:SourceType[:MappedType]
NewMapCol is the new mapped column name. This name can
be up to 20 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.
(Note that lowercase letters are
automatically converted to uppercase
letters.)
SourceType is the TurboIMAGE/XL type the new mapped
column would have if it were a data item in a
TurboIMAGE/XL database.
MappedType is the new mapped column's SQL type. Refer
to Table 2-6 for default and alternative
data type mappings. If omitted, default type
mapping is supplied.
Prerequisites
* SET SQLDBE issued.
* SET TURBODB issued.
* DBA authority.
* Database attached.
Description
Use the SPLIT command to divide mapped columns into smaller units. This
is sometimes necessary because one data item can be used to contain
information about several logically discrete units.
When a TurboIMAGE/XL database becomes part of a DBEnvironment, it is no
longer necessary to keep logically separate information in one mapped
column. The SPLIT command can be used to divide mapped columns of this
kind into several smaller more logically discrete mapped columns.
Use the following guidelines when using the SPLIT command:
1. To use this command, you must first determine what each new mapped
column's data type would be if it were a source field in a
TurboIMAGE/XL database.
2. Each new mapped column must appear in the SPLIT command in the
same order in which it is stored in the original source field.
3. Only certain data type conversions are allowed from TurboIMAGE/XL
to SQL. Refer to Table 2-6 for recommended and alternative
data type conversions.
4. The total length of the proposed new source fields must match the
length of the original source field or an error message is issued.
It is desirable to split mapped columns before IMAGE/SQL users access the
attached database because when a mapped column is split, all user-created
views containing the mapped column are dropped.
[REV BEG]
When a mapped column is split, the definition for the mapped table to
which this mapped column belongs is removed from the system catalog of
the DBEnvironment. This also results in removal of definitions for hash,
B-Tree, and third-party indices for the table. The new definition with
additional columns for the mapped table is reentered in the system
catalog of the DBEnvironment. In addition, new definitions for indices
are also reentered in the system catalog of the DBEnvironment.
(Definitions may not be the same as before.)
If you split a mapped column which represents a TurboIMAGE key item or
search item, the definitions for hash or B-Tree (if they exist) indices
on the mapped column are not entered in the system catalog. For an
example, if a key item is split but the related search item of the detail
data set is not, definitions for hash and implicit B-Tree indices on the
mapped search item will be entered in the SQL catalog. However,
definitions for unique hash index and B-Tree index on the mapped key item
will not be entered.
Although the definitions for third-party indices are reentered in the
system catalog, it is recommended that when you define the third-party
index, you define the index on the whole item, and not substring
especially starting from byte offset other than 1. For information on
third-party indices, refer to your vendor's documentation.
[REV END]
Example
The mapped column INVENTORY.PART_INFO is of type CHAR(60) and contains
the following units of information about parts in the order shown:
* A code identifying the part.
* The version number of the part.
* Brief listing of any special considerations regarding the part.
To split this mapped column into its logical units, issue the following
command:
____________________________________________________________
| |
| >>SPLIT INVENTORY.PART_INFO INTO PART_ID_CODE:X4,& |
| PART_VERSION_NO:I1,&|
| PART_NOTES:X54 |
| |
| Updated information in table INVENTORY. |
| >> |
____________________________________________________________
In the above example, INVENTORY.PART_INFO is split into three mapped
columns. No alternative data type mapping exists for the specified data
types. Therefore, it is not necessary to specify the :MappedType
parameter because in this case the mapped types default to the following
types:
* PART_ID_CODE of type CHAR(4)
* PART_VERSION_NO of type SMALLINT
* PART_NOTES of type CHAR(54)
The combined length of these three mapped columns equals the total length
of the original mapped column, INVENTORY.PART_INFO.