Appendix D: Mapping Default Values#
The default values for Altibase table columns are mostly compatible with the default values for the source database.
However, the default value policy can differ among heterogeneous databases. In the event of such exceptional situations, Migration Center converts source database values according to the Altibase policy.
This appendix provides a mapping table for default values according to which Migration Center converts source database default values to comply with Altibase.
Mapping Table for Default Values#
Prior to migrating data, Migration Center creates a table identical to the source database table in the migration destination database. Migration Center first creates a CREATE TABLE statement with the equivalent attributes as the source database table. To set the same default values for both source table columns and target table columns, Migration Center converts the default values in the following mapping tables and specifies them in the CREATE TABLE statement, when creating the statement. Default values excluded from the table are specified in the statement without being changed.
Note: Some default values that are not changed can be incompatible with source and destination databases. If necessary, you must manually alter the default value for the CREATE TABLE statement in the DDL Editing window at the Reconcile Step.
Default Value Mapping Policy#
Default values of most of the original database are compatible with the target database without any modifications. However, Migration Center converts source database default values according to the policy of the target database system for the following exceptions.
- The CHARACTER data type with a zero length string as the default value: Altibase identifies zero length strings as NULL; therefore, the default value is not specified.
- The DATE data type with a string expression as the default value: Since the default format for the DATE data type differs among source databases, Migration Center specifies a comment which includes the DEFAULT keyword in the CREATE TABLE statement, instead of the default value. If necessary, the user must manually set the default value later on, by referring to the comment. However, if the source database is the one among the MySQL, TimesTen or CUBRID, Migration Center automatically converts default values as shown below.
- The default value specified with a function: A function which is listed in the following table is converted accordingly, only if the function is exclusively specified as the default value in the source database. Other functions or expressions of a complex form are converted without being changed. If necessary, the user must manually change them later on.
Oracle Database to Altibase#
| Expression Type | Source(Oracle) | Destination(Altibase) | Remarks |
|---|---|---|---|
| String for CHARACTER Data Type | " | ||
| String for DATE Data Type | '97/04/21' | /* DEFAULT '97/04/21' */ | |
| Function | DBTIMEZONE | DB_TIMEZONE() | Supported for Altibase 6.3.1.0.0 or above. |
| SYS_GUID() | SYS_GUID_STR() | Supported for Altibase 6.3.1.0.0 or above. | |
| UID | USER_ID() | ||
| USER | USER_NAME() |
The following is an example of the conversion.
| Oracle CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 INT DEFAULT 123, c2 VARCHAR(50) DEFAULT 'test', c3 INT DEFAULT NULL, c4 CHAR(10) DEFAULT '', c5 INT DEFAULT SQRT(144) + 72, c6 DATE DEFAULT '97/04/21', c7 DATE DEFAULT TO_DATE('1999-12-01', 'YYYY-MM-DD'), c8 VARCHAR(100) DEFAULT DBTIMEZONE, c9 VARCHAR(100) DEFAULT SYS_GUID(), c10 VARCHAR(100) DEFAULT UID, c11 VARCHAR(100) DEFAULT USER ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 NUMBER (38, 0) DEFAULT 123, C2 VARCHAR (50) DEFAULT 'test', C3 NUMBER (38, 0), C4 CHAR (10), C5 NUMBER (38, 0) DEFAULT SQRT(144) + 72, C6 DATE /* DEFAULT '97/04/21' */, C7 DATE DEFAULT TO_DATE('1999-12-01', 'YYYY-MM-DD'), C8 VARCHAR (100) DEFAULT DB_TIMEZONE(), C9 VARCHAR (100) DEFAULT SYS_GUID_STR(), C10 VARCHAR (100) DEFAULT USER_ID(), C11 VARCHAR (100) DEFAULT USER_NAME() ); |
MS SQL Server to Altibase#
| Expression Type | Source (MS SQL Server) | Destination (Altibase) | Remarks |
|---|---|---|---|
| String for CHARACTER Data Type | " | ||
| String for DATE Data Type | 'December 5, 1985' | /* DEFAULT 'December 5, 1985' */ | |
| Function | GETDATE90 | SYSDATE | |
| CURRENT_TIMESTAMP | |||
| LEN( str_expression ) | LENGTH( str_expression ) |
The following is an example of the conversion.
| MS SQL CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 BIT DEFAULT 0, c2 INT DEFAULT 2 + 3, c3 VARCHAR(50) DEFAULT 'test', c4 INT DEFAULT NULL, c5 NCHAR(10) DEFAULT '', c6 FLOAT DEFAULT sqrt(12 * 12), c7 DATE DEFAULT 'December 5, 1985', c8 DATE DEFAULT getdate(), c9 DATETIME DEFAULT CURRENT_TIMESTAMP, c10 INT DEFAULT len('test'), ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 CHAR (1) DEFAULT (0), C2 INTEGER DEFAULT (2)+(3), C3 VARCHAR (50) DEFAULT 'test', C4 INTEGER, C5 NCHAR (10), C6 VARCHAR (310) DEFAULT sqrt((12)*(12)), C7 DATE /* DEFAULT 'December 5, 1985' */, C8 DATE DEFAULT SYSDATE, C9 DATE DEFAULT SYSDATE, C10 INTEGER DEFAULT LENGTH('test') ); |
MySQL to Altibase#
| Expression Type | Source (MySQL) | Destination (Altibase) | Remarks |
|---|---|---|---|
| String for CHARACTER Data Type | " | ||
| String for DATE Data Type | '1989-04-28' | TO_DATE('1989-04-28', 'YYYY-MM-DD') | |
| '1989-04-28 12:31:29' | TO_DATE('1989-04-28 12:31:29', 'YYYY-MM-DD HH:MI:SS') | ||
| '0000-00-00 00:00:00' | /* DEFAULT '0000-00-00 00:00:00' */ | If the default value for the DATE data type is omitted, MySQL automatically specifies it as '0000-00-00 00:00:00'. However, since this value cannot be input to the DATE type for Altibase, a comment is left. | |
| Function | CURRENT_TIMESTAMP | SYSDATE | |
| CURRENT_TIMESTAMP() | |||
| NOW() | |||
| LOCALTIME | |||
| LOCALTIME() | |||
| LOCALTIMESETAMP | |||
| LOCALTIMESETAMP() |
Note: If the first column of a table is of the TIMESTAMP data type, MySQL automatically specifies CURRENT_TIMESTAMP as the default value, even if the user omits it. In this case, the default value is converted to SYSDATE. Please refer to the following example.
The following is an example of the conversion.
| MySQL CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 TIMESTAMP NOT NULL, c2 INT DEFAULT 123, c3 VARCHAR(50) DEFAULT 'test', c4 INT DEFAULT NULL, c5 CHAR(10) DEFAULT '', c6 DATE DEFAULT '1989-04-28', c7 DATETIME DEFAULT '1989-04-28 12:31:29', c8 TIMESTAMP DEFAULT '1989-04-28 12:31:29' NOT NULL, c9 TIMESTAMP NOT NULL ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 DATE DEFAULT SYSDATE NOT NULL, C2 INTEGER DEFAULT 123, C3 CLOB DEFAULT 'test', C4 INTEGER, C5 CHAR (10), C6 DATE DEFAULT TO_DATE('1989-04-28', 'YYYY-MM-DD'), C7 DATE DEFAULT TO_DATE('1989-04-28 12:31:29', 'YYYY-MM-DD HH:MI:SS'), C8 DATE DEFAULT TO_DATE('1989-04-28 12:31:29', 'YYYY-MM-DD HH:MI:SS') NOT NULL, C9 DATE /* DEFAULT '0000-00-00 00:00:00' */ NOT NULL ); |
Informix 11.5 to Altibase#
| Expression Type | Source (Informix) | Destination (Altibase) | Remarks |
|---|---|---|---|
| String for CHARACTER Data Type | " | ||
| String for DATE Data Type | '2007-03-06' | /* DEFAULT '2007-03-06' */ | |
| Function | CURRENT | SYSDATE | |
| TODAY | SYSDATE |
The following is an example of the conversion.
| Informix CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 INTEGER DEFAULT 123, c2 BOOLEAN DEFAULT 't', c3 CHAR(100) DEFAULT 'test', c4 INTEGER DEFAULT null, c5 CHAR(10) DEFAULT '', c6 DATETIME YEAR TO DAY DEFAULT DATETIME(07-3-6) YEAR TO DAY, c7 DATETIME DAY TO HOUR DEFAULT CURRENT DAY TO HOUR, c8 DATE DEFAULT TODAY ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 INTEGER DEFAULT 123, C2 CHAR (1) DEFAULT 't', C3 CHAR (100) DEFAULT 'test', C4 INTEGER, C5 CHAR (10), C6 DATE /* DEFAULT '2007-03-06' */, C7 DATE DEFAULT SYSDATE, C8 DATE DEFAULT SYSDATE ); |
TimesTen to Altibase#
| Expression Type | Source(TimesTen) | Destination(Altibase) | Remarks |
|---|---|---|---|
| String for DATE Data Type | '1989-04-28' | TO_DATE('1989-04-28', 'YYYY-MM-DD') | |
| '1989-04-28 12:31:29' | TO_DATE('1989-04-28 12:31:29', 'YYYY-MM-DD HH:MI:SS') | ||
| '12:31:29' | TO_DATE('12:31:29', 'HH:MI:SS') | ||
| Function | UID | USER_ID | |
| USER | USER_NAME |
The following is an example of the conversion.
| TimesTen CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 INT DEFAULT 123, c2 VARCHAR2(50) DEFAULT 'test', c3 INT DEFAULT NULL, c4 DATE DEFAULT '1999-12-01', c5 TIMESTAMP DEFAULT '1999-12-01 11:30:21', c6 TIME DEFAULT '11:30:21', c7 VARCHAR(100) DEFAULT UID, c8 VARCHAR(100) DEFAULT USER ); |
CREATE TABLE TESTTBL_4_DEFVAL ( c1 INT DEFAULT 123, c2 VARCHAR2(50) DEFAULT 'test', c3 INT DEFAULT NULL, c4 DATE DEFAULT TO_DATE('1999-12-01', 'YYYY-MM-DD'), c5 TIMESTAMP DEFAULT TO_DATE('1999-12-01 11:30:21', 'YYYY-MM-DD HH:MI:SS), c6 TIME DEFAULT TO_DATE('11:30:21', 'HH:MI:SS'), c7 VARCHAR(100) DEFAULT UID, c8 VARCHAR(100) DEFAULT USER ); |
CUBRID to Altibase#
| Expression Type | Source(CUBRID) | Destination(Altibase) | Remarks |
|---|---|---|---|
| Function | USER | USER_ID() | |
| CURRENT_USER | USER_NAME() |
The following is an example of the conversion.
| CUBRID CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 INTEGER DEFAULT 123, c2 CHARACTER VARYING (50) DEFAULT 'test', c3 INTEGER, c4 CHARACTER VARYING (100) DEFAULT 'USER', c5 CHARACTER VARYING (100) DEFAULT 'CURRENT_USER', c6 CHARACTER VARYING(100) DEFAULT ' ', c7 DATE DEFAULT DATE'2008-10-31', c8 TIME DEFAULT TIME'1:15', c9 TIMESTAMP DEFAULT TIMESTAMP'10/31', c10 DATETIME DEFAULT DATETIME'01:15:45 PM 2008-10-31' ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 INTEGER DEFAULT 123, C2 VARCHAR (50) DEFAULT 'test', C3 INTEGER, C4 VARCHAR (100) DEFAULT USER_ID(), C5 VARCHAR (100) DEFAULT USER_ID(), C6 VARCHAR (100) DEFAULT ' ', C7 DATE /* DEFAULT '10/31/2008' */, C8 DATE /* DEFAULT '01:15:00 AM' */, C9 DATE /* DEFAULT '12:00:00 AM 10/31/2016' */, C10 DATE /* DEFAULT '01:15:45.000 PM 10/31/2008' */ ); |
Altibase to Oracle#
| Expression Type | Source(Altibase) | Destination(Oracle) | Remarks |
|---|---|---|---|
| String for Character type | " | ||
| Function | DB_TIMEZONE() | DBTIMEZONE | Supported in Altibase 6.3.1.0.0 or above |
| SYS_GUID_STR() | SYS_GUID() | Supported in Altibase 6.3.1.0.0 or above | |
| USER_ID() | UID | ||
| USER_NAME() | USER |
The following is an example of the conversion.
| Altibase Table Creation SQL statements | Oracle Table Creation SQL statements |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 INT DEFAULT 123, c2 VARCHAR(50) DEFAULT 'test', c3 INT DEFAULT NULL, c4 CHAR(10) DEFAULT '', c5 INT DEFAULT SQRT(144) + 72, c6 DATE DEFAULT TO_DATE('1999-12-01 PM', 'YYYY-MM-DD AM'), c7 VARCHAR(100) DEFAULT DB_TIMEZONE(), c8 VARCHAR(100) DEFAULT SYS_GUID_STR(), c9 VARCHAR(100) DEFAULT USER_ID(), c10 VARCHAR(100) DEFAULT USER_NAME() ); |
CREATE TABLE TESTTBL_4_DEFVAL ( C1 NUMBER (10) DEFAULT 123 , C2 VARCHAR2 (50) DEFAULT 'test' , C3 NUMBER (10) , C4 CHAR (10) , C5 NUMBER (10) DEFAULT SQRT(144) + 72 , C6 TIMESTAMP DEFAULT TO_DATE('1999-12-01 PM', 'YYYY-MM-DD AM') , C7 VARCHAR2 (100) DEFAULT DBTIMEZONE , C8 VARCHAR2 (100) DEFAULT SYS_GUID() , C9 VARCHAR2 (100) DEFAULT UID , C10 VARCHAR2 (100) DEFAULT USER ); |
Tibero to Altibase#
| Expression Type | Source(Tibero) | Destination(Altibase) | Remarks |
|---|---|---|---|
| String for CHARACTER Data Type | " | ||
| String for DATE Data Type | '97/04/21' | /* DEFAULT '97/04/21' */ | |
| Function | DBTIMEZONE | DB_TIMEZONE() | Supported in Altibase 6.3.1.0.0 or above. |
| SYS_GUID_GUID() | SYS_GUID_STR() | Supported in Altibase 6.3.1.0.0 or above. | |
| UID | USER_ID() | ||
| USER | USER_NAME() |
The following is an example of the conversion.
| Tibero CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval( c1 INT DEFAULT 123, c2 VARCHAR(50) DEFAULT 'test', c3 INT DEFAULT NULL, c4 CHAR(10) DEFAULT '', c5 INT DEFAULT QRT(144) + 72, c6 DATE DEFAULT '97/04/21', c7 DATE DEFAULT TO_DATE('1999-12-01', 'YYYY-MM-DD'), c8 VARCHAR(100) DEFAULT DBTIMEZONE, c9 VARCHAR(100) DEFAULT SYS_GUID(), c10 VARCHAR(100) DEFAULT UID, c11 VARCHAR(100) DEFAULT USER ); |
CREATE TABLE TESTTBL_4_DEFVAL( C1 NUMBER (38, 0) DEFAULT 123, C2 VARCHAR (50) DEFAULT 'test', C3 NUMBER (38, 0), C4 CHAR (10), C5 NUMBER (38, 0) DEFAULT SQRT(144) + 72, C6 DATE /* DEFAULT '97/04/21' */, C7 DATE DEFAULT TO_DATE('1999-12-01', 'YYYY-MM-DD'), C8 VARCHAR (100) DEFAULT DB_TIMEZONE(), C9 VARCHAR (100) DEFAULT SYS_GUID_STR(), C10 VARCHAR (100) DEFAULT USER_ID(), C11 VARCHAR (100) DEFAULT USER_NAME() ); |