HPlogo IMAGE/SQL Administration Guide: HP 3000 MPE/iX Computer Systems > Chapter 4 IMAGE/SQL Utility Commands

SPLIT
» 

Technical documentation

Complete book in PDF
» Feedback

 » Table of Contents

 » Glossary

 » Index

spacerspacerspacer
spacer
spacer
  		 
 

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

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.

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.



SET TURBODB
  		 


UPDATE TYPE  
Feedback to webmaster