One data set field is sometimes used in TurboIMAGE/XL databases to
hold several related units of data.
This task describes how to easily access these individual data units by
dividing them into separate mapped columns.
Getting Ready
Before splitting a mapped column, some preparation is necessary:
Confirm that the TurboIMAGE/XL database containing the source field is attached to a
DBEnvironment.
Determine what TurboIMAGE/XL data type and length each individual unit would be
assigned if it were to be defined as an individual data item in the TurboIMAGE/XL database
schema.
For example,
PART-INFO, a large field in the INVENTORY
data set, contains several units
of information about a particular part.
The DISPLAY MAP command shows how it is mapped at attach time:
>>DISPLAY MAP FOR SALES INVENTORY.PART_INFO
TurboIMAGE/XL DB : SALES.SERED.ATC
DBEnvironment : PARTSDBE.SERED.ATC
Owner Name : SALES
MAPPED SOURCE MAPPED SOURCE MAPPED
TABLE FIELD COLUMN TYPE TYPE NOTES
------------ ---------------- --------------- --------- --------- -----
INVENTORY
PART-INFO PART_INFO X60 CHAR(60)
>>
|
Specifically, PART-INFO contains:
A part identification code (the first 4 bytes of PART-INFO).
The version number of the part (the next 2 bytes of PART-INFO).
Brief notes about the part (the last 54 bytes of PART-INFO).
Each unit of information corresponds to the following
TurboIMAGE/XL data types:
The part identification code is X4 (4 bytes).
The version number of the part is I1 (2 bytes).
The notes about the part is X54 (54 bytes).
Make sure that the sum of the lengths of the data units calculated in step 2
matches the length of the original field, in this case
PART-INFO (60 bytes). (Here 4 + 2 + 54 = 60, so the lengths correspond.)
Decide what to name the new mapped columns.
For example, the new mapped columns in the mapped table INVENTORY are named:
Performing the Task
The SPLIT command requires you to enter the name of the
new mapped column, its equivalent TurboIMAGE/XL data type, and, optionally, its SQL
data type. Note that the
ampersand (&) is used to continue to the next line.
Be sure to list the new mapped columns in the
order in which they are stored in the original source field:
For example:
>>SPLIT INVENTORY.PART_INFO INTO PART_ID_CODE:X4:CHAR(4),&
PART_VERSION_NO:I1:SMALLINT,&
PART_NOTES:X54:CHAR(54)
Updated information in table INVENTORY.
|
Here the mapped column PART_INFO in the mapped table INVENTORY is being split
into three new mapped columns:
PART_ID_CODE of type CHAR(4)
PART_VERSION_NO of type SMALLINT
PART_NOTES of type CHAR(54)
Note that for clarity, the SQL data types are explicitly specified in the above
example. However, because they represent default data type mapping, it is not
mandatory to explicitly specify these SQL data types.
The DISPLAY MAP command shows the newly split columns:
>>DISPLAY MAP FOR SALES INVENTORY
TurboIMAGE/XL DB : SALES.SERED.ATC
DBEnvironment : PARTSDBE.SERED.ATC
Owner Name : SALES
MAPPED SOURCE MAPPED SOURCE MAPPED
TABLE FIELD COLUMN TYPE TYPE NOTES
------------ --------------- ------------------ ------- ----------- -----
INVENTORY
PRODUCT# PRODUCT# U8 CHAR(8)
. . . .
. . . .
. . . .
LOCATION-BIN LOCATION_BIN Z2 DECIMAL(2,0) I
new ⇒ PART-INFO PART_ID_CODE X4 CHAR(4) S
new ⇒ PART-INFO PART_VERSION_NO I1 SMALLINT S
new ⇒ PART-INFO PART_NOTES X54 CHAR(54) S
NOTES:
I: Imprecise(float)/Incompatible(others) mapping between source and
mapped data types
S: Source field has been split.
>>
|
The S in the NOTES section indicates that the source field, PART-INFO, has been
split into separate mapped columns.
Task Reference
SQL names 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 (_). The first character cannot be an underscore or a decimal
digit.
It is desirable to split mapped columns before IMAGE/SQL users
access
the attached database. This is because when a mapped column is split, any
user-created views containing the mapped column to be split are dropped.