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 “IMAGE/SQL Data Type Mapping Defaults and Alternatives”. 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 “IMAGE/SQL Data Type Mapping Defaults and Alternatives” 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.

Task 5: Splitting Mapped Columns

Technical documentation

» Table of Contents

» Glossary

» Index