HP 3000 Manuals HP 3000 Manuals
Task 4:Updating IMAGE/SQL Utility Data Type Mapping Information
This task describes how to select alternative IMAGE/SQL utility data type
mapping.
Getting Ready
To select alternative data type mapping, you may first want to examine
the default mapping done by the IMAGE/SQL utility. For example, the
following default data type mapping information is stored in the ATCINFO
file (DBEnvironmentNameCR) for the mapped table CUSTOMER:
_________________________________________________________________________________
| |
| >>DISPLAY MAP CUSTOMER |
| |
| TurboIMAGE/XL DB : SALES.SERED.ATC |
| DBEnvironment : PARTSDBE.SERED.ATC |
| Owner Name : SALES |
| |
| MAPPED(SOURCE) SOURCE MAPPED SOURCE MAPPED |
| TABLE FIELD COLUMN TYPE TYPE NOTES|
| ------------ ---------------- ------------------ ------- ----------- -----|
| |
| |
| CUSTOMER (CUSTOMER) |
| ACCOUNT ACCOUNT J2 INTEGER |
| LAST-NAME LAST_NAME X16 CHAR(16) |
| FIRST-NAME FIRST_NAME X10 CHAR(10) |
| INITIAL INITIAL U2 CHAR(2) |
| STREET STREET X26 CHAR(26) |
| CITY CITY X12 CHAR(12) |
| STATE STATE X2 CHAR(2) |
| ZIP ZIP X6 CHAR(6) |
| CREDIT-RATING CREDIT_RATING R2 FLOAT I |
| |
| NOTES: |
| I: Imprecise(float)/Incompatible(others) mapping between source and |
| mapped data types |
| >> |
_________________________________________________________________________________
By default, R2, the source data type of CREDIT_RATING, is mapped to a
FLOAT. The I in the NOTES section indicates that this mapping may be
imprecise because of differences in numeric storage between a 32-bit 3000
real (R2) and a 64-bit IEEE real (FLOAT).
Performing the Task
To change default data type mapping, use the UPDATE TYPE command. For
example:
____________________________________________________________
| |
| >>UPDATE TYPE R2 IN CUSTOMER.CREDIT_RATING TO CHAR(4)|
| Updated information in table CUSTOMER. |
| >> |
____________________________________________________________
In this example, CHAR(4) is specified as the data type mapping for
CUSTOMER.CREDIT_RATING. The DISPLAY MAP command now reflects this change:
_________________________________________________________________________________
| |
| >>DISPLAY MAP CUSTOMER |
| |
| TurboIMAGE/XL DB : SALES.SERED.ATC |
| DBEnvironment : PARTSDBE.SERED.ATC |
| Owner Name : SALES |
| |
| MAPPED(SOURCE) SOURCE MAPPED SOURCE MAPPED |
| TABLE FIELD COLUMN TYPE TYPE NOTES|
| ------------ ---------------- ------------------ ------- ----------- -----|
| |
| |
| CUSTOMER (CUSTOMER) |
| ACCOUNT ACCOUNT J2 INTEGER |
| LAST-NAME LAST_NAME X16 CHAR(16) |
| FIRST-NAME FIRST_NAME X10 CHAR(10) |
| INITIAL INITIAL U2 CHAR(2) |
| STREET STREET X26 CHAR(26) |
| CITY CITY X12 CHAR(12) |
| STATE STATE X2 CHAR(2) |
| ZIP ZIP X6 CHAR(6) |
| CREDIT-RATING CREDIT_RATING R2 CHAR(4) IU |
| |
| NOTES: |
| I: Imprecise(float)/Incompatible(others) mapping between source and |
| mapped data types |
| U: Source field has been updated |
| >> |
_________________________________________________________________________________
The U in the NOTES section indicates that the data type mapping for this
source field has been updated. The I indicates that the new mapping is
incompatible with the source data type because numerical operations
cannot be performed on character data.
Task Reference
* When the data type of a mapped column is updated, all user-created
views based on IMAGE/SQL utility views containing the updated
mapped column are dropped. Therefore, it is desirable to update
data types before IMAGE/SQL users access the attached database.
* Table 2-6 summarizes IMAGE/SQL data type mapping defaults and
alternatives. The following abbreviations and variables are used
in Table 2-6 :
MSB most significant bit.
b number of bytes needed for storage.
n number of occurrences of the associated SQL type (the
TurboIMAGE/XL sub-item length).
Table 2-6. IMAGE/SQL Data Type Mapping Defaults and Alternatives
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Source | Bits | SQL Type | Comments on | SQL Type | Comments on |
| Type | | Default | Default | Alternative | Alternative |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Un | 8*n | char(n) | | | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Xn | 8*n | char(n) | | | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Zn* | 8*n | decimal(n,0) | default when n <= 15 | char(n)** | default when n > 15 |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Pn* | 4*n | decimal(n- | default when n <= 16 | char(n/2)** | default when n > 16 |
| | | 1,0) | | | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| I1, J1 | 16 | smallint | | decimal (5,n) | convert to decimal |
| | | | | | (5,n) where 0<=n<5 |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| I2, J2 | 32 | integer | | decimal | convert to decimal |
| | | | | (10,n) | (10,n) where 0<=n<10 |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| I3, J3 | 48 | decimal(15,0) | value converted to | char(6)** | 8 bits binary data |
| | | | packed decimal | | stored in each char |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| I4, J4* | 64 | char(20)** | value converted from | decimal(15,0) | value converted to |
| | | | binary to printable | *** | packed decimal |
| | | | ASCII; zero-filled; | | |
| | | | sign included at run | | |
| | | | time | | |
| | | | | | |
- -
| | | | | | |
| | | | | char(8)** | 8 bits binary data |
| | | | | | stored in each char |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| K1* | 16 | integer | no loss of magnitude | smallint*** | MSB taken as sign |
| | | | | | |
-----------------------------------------------------------------------------------------------------
Table 2-6. IMAGE/SQL Data Type Mapping Defaults and Alternatives (cont.)
-----------------------------------------------------------------------------------------------------
| | | | | | |
| Source | Bits | SQL Type | Comments on | SQL Type | Comments on |
| Type | | Default | Default | Alternative | Alternative |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| K2* | 32 | integer*** | MSB taken as sign | float | short IEEE converted |
| | | | | | to long IEEE |
| | | | | | |
- -
| | | | | | |
| | | | | decimal(15,0) | value converted to |
| | | | | | packed decimal |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| K3 | 48 | decimal(15,0) | value converted to | char(6)** | 8 bits binary data |
| | | | packed decimal | | stored in each char |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| K4* | 64 | char(20)** | value converted from | float | long IEEE assumed |
| | | | binary to printable | | |
| | | | ASCII; zero-filled; | | |
| | | | no sign at run time | | |
| | | | | | |
- -
| | | | | | |
| | | | | decimal(15,0) | value converted to |
| | | | | *** | packed decimal |
| | | | | | |
- -
| | | | | | |
| | | | | char(8)** | 8 bits binary data |
| | | | | | stored in each char |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| R2* | 32 | float | value converted from | char(4)** | 8 bits binary data |
| | | | short HP 3000 real | | stored in each char; |
| | | | to long IEEE real at | | no IEEE conversion |
| | | | run time | | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| R4* | 64 | float | value converted from | char(8)** | 8 bits binary data |
| | | | HP 3000 real to long | | stored in each char; |
| | | | IEEE real at run | | no IEEE conversion |
| | | | time | | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| X16 | 108 | char(16) | | DATE | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| X16 | 108 | char(16) | | DATETIME | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| X16 | 108 | char(16) | | INTERVAL | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | | |
| X16 | 108 | char(16) | | TIME | |
| | | | | | |
-----------------------------------------------------------------------------------------------------
| | | | | |
| all other data | char(b)** | 8 bits binary data | | |
| types* | | stored in each char | | |
| | | | | |
-----------------------------------------------------------------------------------------------------
| |
| * 100% mapping to an SQL type is not available. |
| |
| ** Caution: When this type is mapped to char, the numeric meaning is lost in |
| sorting, expressions, and aggregate functions. |
| |
| *** Potential to under/overflow the available range. A run-time error results if the |
| data value is outside the range of the SQL type. In this case, you may want to |
| store the data in an alternative type. |
| |
-----------------------------------------------------------------------------------------------------