HP 3000 Manuals HP 3000 Manuals
Splitting Mapped Columns: Task 5
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:
1. Confirm that the TurboIMAGE/XL database containing the source
field is attached to a DBEnvironment.
2. 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. A part identification code (the first 4 bytes of PART-INFO).
b. The version number of the part (the next 2 bytes of
PART-INFO).
c. Brief notes about the part (the last 54 bytes of PART-INFO).
Each unit of information would correspond to the following
TurboIMAGE/XL data types:
* The part identification code would be X4 (4 bytes).
* The version number of the part would be I1 (2 bytes).
* The notes about the part would be X54 (54 bytes).
3. 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.)
4. Decide what to name the new mapped columns (see Task Reference).
For example, the new mapped columns in the mapped table INVENTORY
will be named:
PART_ID_CODE
PART_VERSION_NO
PART_NOTES
5. Determine what ALLBASE/SQL data type(s) can be mapped to each
TurboIMAGE/XL data type identified in step 2; refer to Table 2-5
(see Task Reference). When alternate data type mapping
possibilities exist, decide which mapping best reflects the format
of the data.
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
ALLBASE/SQL data type. Note that the ampersand (&) is used to continue
to the next line (see Task Reference). 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 ALLBASE/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
ALLBASE/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
* Refer to Table 2-5 (task 4) for alternate data type mapping
information.
* ALLBASE/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 (task 5) before Turbo CONNECT
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.