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.
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 an empty string as the default value
Altibase treats empty strings ('') as NULL, meaning that no default value is assigned. For columns where the default value is an empty string and a NOT NULL constraint is present, refer to the Empty String Handling Options section. - 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 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() | ||
| Identity column | Identity | __SYS_table_name_column_name_SEQ.nextval | |
| DEFAULT ON NULL column | DEFAULT ON NULL 'test' | DEFAULT 'test' NOT NULL |
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, c12 INT GENERATED BY DEFAULT AS IDENTITY, c13 CHAR(5) DEFAULT ON NULL 'test' ); |
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(), C12 NUMBER (38, 0) DEFAULT __SYS_TESTTBL_4_DEFVAL_C12_SEQ.NEXTVAL NOT NULL, C13 CHAR (5) DEFAULT 'test' NOT NULL ); |
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() |
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' */ ); |
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() ); |
PostgreSQL to Altibase#
| Expression Type | Source(PostgreSQL) | Destination(Altibase) | Remarks |
|---|---|---|---|
| Function | current_role | USER_NAME() | |
| current_schema | USER_NAME() | ||
| current_user | USER_NAME() | ||
| session_user | USER_NAME() | ||
| user | USER_NAME() | ||
| ceiling(expression) | CEIL(number) | ||
| random() | RANDOM(0)/2147483647 | ||
| bit_length(string) | 8*OCTET_LENGTH(expr) | ||
| reverse(str) | REVERSE_STR(expr) | ||
| strpos(string, substring) | INSTR (expr, substring) | ||
| clock_timestamp() | SYSDATE | ||
| current_date | SYSDATE | ||
| current_time | SYSDATE | ||
| current_timestamp | SYSDATE | ||
| localtime | SYSDATE | ||
| localtimestamp | SYSDATE | ||
| now() | SYSDATE | ||
| statement_timestamp() | SYSDATE | ||
| transaction_timestamp() | SYSDATE |
The following is an example of the conversion.
| PostgreSQL CREATE TABLE Statement | Altibase CREATE TABLE Statement |
|---|---|
| CREATE TABLE testtbl_4_defval ( c1 VARCHAR(50) DEFAULT current_role, c2 VARCHAR(50) DEFAULT current_schema, c3 VARCHAR(50) DEFAULT current_user, c4 VARCHAR(50) DEFAULT session_user, c5 VARCHAR(50) DEFAULT user, c6 INTEGER DEFAULT ceiling(-95.3), c7 DOUBLE PRECISION DEFAULT random(), c8 INTEGER DEFAULT bit_length('abc'), c9 VARCHAR(50) DEFAULT reverse('reverse'), c10 INTEGER DEFAULT strpos('high', 'ig'), c11 timestamp with time zone DEFAULT clock_timestamp(), c12 date DEFAULT current_date, c13 time with time zone DEFAULT current_time, c14 timestamp with time zone DEFAULT current_timestamp, c15 time DEFAULT localtime, c16 timestamp DEFAULT localtimestamp, c17 timestamp with time zone DEFAULT now(), c18 timestamp with time zone DEFAULT transaction_timestamp() ); | CREATE TABLE TESTTBL_4_DEFVAL ( C1 VARCHAR (50) DEFAULT USER_NAME() ,C2 VARCHAR (50) DEFAULT USER_NAME() ,C3 VARCHAR (50) DEFAULT USER_NAME() ,C4 VARCHAR (50) DEFAULT USER_NAME() ,C5 VARCHAR (50) DEFAULT USER_NAME() ,C6 INTEGER DEFAULT CEIL('-95.3') ,C7 DOUBLE DEFAULT (RANDOM(0)/2147483647) ,C8 INTEGER DEFAULT 8*OCTET_LENGTH('abc') ,C9 VARCHAR (50) DEFAULT REVERSE_STR('reverse') ,C10 INTEGER DEFAULT INSTR('high', 'ig') ,C11 DATE DEFAULT SYSDATE ,C12 DATE DEFAULT SYSDATE ,C13 DATE DEFAULT SYSDATE ,C14 DATE DEFAULT SYSDATE ,C15 DATE DEFAULT SYSDATE ,C16 DATE DEFAULT SYSDATE ,C17 DATE DEFAULT SYSDATE ,C18 DATE DEFAULT SYSDATE ); |
Empty String as a Default Value#
Each database vendor handles empty strings as follows:
| Database Vendor | CHAR | VARCHAR |
|---|---|---|
| Oracle | NULL | NULL |
| MySQL | Empty String | Empty String |
| SQL Server | Fixed-length string | Empty String |
| PostgreSQL | Fixed-length string | Empty String |
| CUBRID | Fixed-length string | Empty String |
| Informix | Fixed-length string | Empty String |
Altibase, by default, processes empty strings as NULL. As a result, empty string data in the original database is processed as NULL during migration. If an empty string is set as the default value (DEFAULT ''), Altibase interprets this as DEFAULT NULL and removes the existing default value setting.
However, if there is a column where the default value is an empty string and a NOT NULL constraint is set, during migration to Altibase, the empty string is considered NULL, which will conflict with the NOT NULL constraint. To avoid potential data loss resulting from such conflicts, the Migration Center offers options to modify the default empty string value or adjust the NOT NULL constraint.
Altibase distinguishes between strings composed of fixed-length spaces and NULL from empty strings. Therefore, only the items marked as Empty String in the table above are affected by the empty string handling options during migration.
Empty String Handling Options#
Migration Center offers empty string handling options to prevent data loss. These options can be set via the menu Migration > Migration Options.
Object Options#
Empty string handling options that can be set in the Object Options are below:
By configuring the options below, users can adjust the CREATE statement generated when migrating a table with columns where the default value is an empty string and a NOT NULL constraint is set.
The empty string handling options in Object Options do not directly process empty string data. If users want to modify empty string data, users need to configure the empty string handling options in Data Options.
| Option | Description | Remarks |
|---|---|---|
| Replace Default Empty String | Specifies whether to replace the default value with a user-defined string in the CREATE statement generated by Altibase. |
|
| Replacement Default Value | Specifies the default value to replace the empty string. | Enabled when Replace Default Empty String is set to 'Yes'. |
| Remove Not Null | Removes the NOT NULL constraint in the CREATE statement generated by Altibase. |
These options can be applied individually or in combination, providing flexibility in empty string handling according to user requirements. For instance, if the column definition in the source database is C1 CHAR(10) DEFAULT '' NOT NULL, the generated statement will differ based on the selected option combinations as below.
| Replace Default Empty String | Replacement Default Value | Remove Not Null | Column Definition |
|---|---|---|---|
| Yes | EMPTY_STRING | Yes | C1 CHAR(10) DEFAULT 'EMPTY_STRING' |
| EMPTY_STRING | No | C1 CHAR(10) DEFAULT 'EMPTY_STRING' NOT NULL |
|
| No | N/A | Yes | C1 CHAR(10) |
| N/A | No | C1 CHAR(10) NOT NULL |
Data Options#
The empty string handling options that can be set in Data Options are below:
By configuring the options below, users can change empty strings discovered during data migration to a user-defined value.
| Option | Description | Remarks |
|---|---|---|
| Replace Empty Strings in Not Null | Specifies whether to replace empty strings in columns with NOT NULL constraints with a user-defined string. | |
| Replacement String | Specifies the string to replace the empty string. | Enabled when Replace Empty Strings in Not Null is set to 'Yes'. |
| Apply to Nullable Columns | Specifies whether to replace empty string data in columns without NOT NULL constraints with the string specified in Replacement String. | Enabled when Replace Empty Strings in Not Null is set to 'Yes'. |