Changing the Default Data Type
In most cases, the default data types are sufficient. If you want to change the data type, however, use a relational database query tool to edit the sql_type column in the DATAITEMS client control table, or put the SQL statements in user scripts as explained in Customizing with User Scripts.
Caution: When changing the default data type, make sure that you choose a correct data type or the data may not be correctly stored in the relational database.
Most of these relational database data types can be changed using data table customization user scripts. For example, if a DMSII item has a data type of NUMBER(4) that gets mapped to a short int, you can change the sql_type to a long int. By knowing that this item will be changed to a NUMBER(5) in an imminent DMSII reorganization, you can eliminate the need to reclone the data set after the reorganization.
Value for sql_type |
Generic Data Type |
Microsoft SQL Server Data Type |
Oracle Data Type |
DB2 Data Type |
0 |
bit |
bit |
number(1) |
smallint |
1 |
char |
char |
char |
char |
2 |
varchar |
varchar |
varchar2 |
varchar |
3 |
byte |
tinyint |
number(3) |
smallint |
4 |
short int |
smallint |
number(5) |
smallint |
5 |
long int |
int |
number(10) |
int |
6 |
float |
float |
float |
float |
7 |
text |
text |
clob |
varchar |
8 |
binary |
binary |
raw |
char |
9 |
varbinary |
varbinary |
raw |
varchar |
10 |
datetime |
datetime |
date |
timestamp |
11 |
packed BCD |
dec |
number |
decimal |
12 |
smalldatetime |
smalldatetime |
date |
timestamp |
13 |
numeric date |
int |
number(10) |
int |
14 |
unsigned long |
binary(4) |
raw(4) |
char for bit data |
15 |
timestamp |
timestamp |
N/A |
N/A |
16 |
serial |
{int | dec(n)} identity |
N/A |
N/A |
17 |
numeric time |
int |
number(6) |
int |
ticks |
int |
number(6) or number(10) |
N/A |
|
18 |
int64 |
bigint |
NA |
NA |
19 |
date |
date |
NA |
NA |
20 |
datetime2 |
datetime2 |
NA |
NA |
21 |
time |
time |
NA |
NA |
 |