HP 3000 Manuals HP 3000 Manuals
UPDATE TYPE
Updates data type mapping information.
Syntax
{SourceType IN {* }}
U[PDATE] TYPE { {MappedTable}} [TO NewMappedType]
{ }
{IN MappedTable.Col }
Parameters
SourceType is a TurboIMAGE/XL data item type whose data type
mapping information you want to update. Use either
this parameter or the MappedTable.Col parameter.
* (asterisk) indicates that you want to update data type mapping for
a source data type in all mapped tables where it
occurs. Use either this option or specify the
individual table to be updated.
MappedTable is the name of a mapped table containing a data type
whose mapping you want to update. Use either this
parameter or the asterisk (*) option.
MappedTable.Col is the name of a column in a specific mapped table
whose data type mapping you want to update. Use either
this parameter or the SourceType parameter.
NewMappedType is the new data type you want to assign. If this
parameter is omitted, default IMAGE/SQL utility data
type mapping is used. (See Table 2-6 for allowed
data type mappings.)
Prerequisites
* SET SQLDBE issued.
* SET TURBODB issued.
* DBA authority.
* Database attached.
Description
Use the UPDATE TYPE command to update the data type mapping information
in one of the following situations:
* For all occurrences of a specified source data type in the
database.
* For all occurrences of the source data type in a specified mapped
table.
* For one occurrence of the source data type in a particular column
of a specified table.
To return the TurboIMAGE/XL data type or mapped column to default
IMAGE/SQL utility data type mapping values, omit the TO NewMappedType
parameter.
Update data type mapping before IMAGE/SQL users access the database
because when a mapped column is updated, all user-created views
containing these mapped columns are dropped.[REV BEG] When a mapped
column type is updated, 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 the removal of definitions for hash,
B-Tree, and third-party indices for the table. The new definition 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.
[REV END]
UPDATE TYPE stores the new data type mapping information in the ATCINFO
file (DBEnvironmentNameCR). The DISPLAY MAP command reflects the updated
type information. Data type updates are restricted to those data types
that have alternative data type mapping.[REV BEG] The UPDATE TYPE command
only affects the DBEnvironment named in the SET SQLDBE statement.[REV
END]
Table 2-2 in Chapter 2 summarizes IMAGE/SQL data type mapping
defaults and alternatives.[REV BEG]
NOTE Updating a TurboIMAGE field X16 or K8 to an SQL DATE type means
that data entered through SQL is not readable via pure
TurboIMAGE/XL applications. You need to use an API routine as
described in Appendix F, "Date/Time API."
[REV END]
Example
The first example shows how to convert fields in a data base to a decimal
value with a varying number of decimal places. You can map type J2 to
decimal type (10,n) where 0<=n<10.
* Use this command to remap all J2 fields in a data base to 0
decimal places:
_____________________________________________
| |
| >>UPDATE TYPE J2 IN * TO DECIMAL(10,0)|
_____________________________________________
* Use this command to remap all J2 fields in a data set to 2 decimal
places:
______________________________________________________
| |
| >>UPDATE TYPE J2 IN tablename TO DECIMAL(10,2)|
______________________________________________________
* Use this command to remap one particular field in a data set to 1
decimal place:
______________________________________________________
| |
| >>UPDATE TYPE IN table.column TO DECIMAL(10,1)|
______________________________________________________
In this second example, the UPDATE TYPE command is used to specify the
alternative mapping ALLBASE/SQL CHAR(4). This is a byte-by-byte transfer
(R2 is 4 bytes long). No data conversion is performed.
______________________________________________
| |
| >>UPDATE TYPE R2 IN CUSTOMER TO CHAR(4)|
| Updated information in table CUSTOMER. |
| >> |
______________________________________________
After the data type update, the DISPLAY MAP command reflects the 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) |
| CUSTOMER# CUSTOMER# 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 |
| >> |
| |
_________________________________________________________________________________
In the above example, the NOTES column indicates that the data type
mapping for CREDIT_RATING, the only mapped column whose source data type
is R2, has been updated. The I indicates that the updated data type
mapping is incompatible with the original data type.