HP 3000 Manuals HP 3000 Manuals
Task 5:Splitting Mapped Columns
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:
PART_ID_CODE
PART_VERSION_NO
PART_NOTES
* Determine what SQL data type(s) can be mapped to each
TurboIMAGE/XL data type identified in step 2; refer to Table 2-6
. When alternative 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 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
* Refer to Table 2-6 for alternative data type mapping
information.
* 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.