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