About Ispirer Systems

Ispirer Home Page Database Migration Application Conversion Downloads


Sqlways.ini File - Section [DATA]

This article describes sqlways.ini file's [DATA] section and the options it contains.

Option name Description
COLUMN_DELIMITER Column delimiter.
LINE_DELIMITER Line delimiter.
DECIMAL_POINT Decimal point character.
DATETIME_FORMAT This option specifies the format of the datetime datatypes in text files.
Datetime data consists of valid date and time combinations. Depending on the database, this could be DATE data type in Oracle, TIMESTAMP data type in IBM DB2 and DATETIME data type in Microsoft SQL Server and Sybase.
A datetime format can be composed of one or more datetime format elements as listed below.
* YYYY - 4-digit year
* YY - Last 2 digits of a year
* MM - 2-digit numeric abbreviation of a month (01-12)
* MON - 3-symbol abbreviated name of a month (JAN, FEB etc)
* DD - 2-digit day of a month (01-31)
* HH - 2-digit hour of a day (01-12)
* HH12 - 2-digit hour of a day (01-12)
* HH24 - 2-digit hour of a day (00-23)
* MI - 2-digit minute (00-59)
* SS - 2-digit second (00-59)
* F - Fraction of second. The number of F symbols represents the precision. For example, FFF for milliseconds accuracy and FFFFFF for microseconds accuracy.
For example, to get datetime values like 21-DEC-2002 21-21-00 set the datetime format to DD-MON-YYYY HH24-MI-SS.
If no value is specified in this option, the default datetime format is used. By default, ODBC drivers convert datetime values using the ISO format YYYY-MM-DD HH24:MI:SS.FFF (the FFF part depends on the database).
DATE_FORMAT This option specifies the format of the date datatypes in text files.
A date is a three-part value (year, month and day). Not all databases have a data type that allows you to keep date values without a time part. For example, such data type exists in IBM DB2 and MySQL (the DATE data type).
A date format can be composed of one or more date format elements as listed below.
* YYYY - 4-digit year
* YY - Last 2 digits of a year
* MM - 2-digit numeric abbreviation of a month (01-12)
* MON - 3-symbol abbreviated name of a month (JAN, FEB etc)
* DD - 2-digit day of a month (01-31)
For example, to get date values like 21-DEC-2002, set the datetime format to DD-MON-YYYY.
If no value is specified in this option, the default date format is used. By default, ODBC drivers convert date values using the ISO format YYYY-MM-DD.
TIME_FORMAT This option specifies the format of the time datatypes in text files.
Time is a three-part value (hour, minute and second). Not all databases have a data type that allows you to keep time values without a date part. For example, such data type exists in IBM DB2 and MySQL (the TIME data type).
A time format can be composed of one or more time format elements as listed below.
* HH - 2-digit hour of a day (01-12)
* HH12 - 2-digit hour of a day (01-12)
* HH24 - 2-digit hour of a day (00-23)
* MI - 2-digit minute (00-59)
* SS - 2-digit second (00-59)
For example, to get time values like 21-21-00 set the time format to HH24-MI-SS.
If no value is specified in this option, the default time format is used. By default, ODBC drivers convert time values using the ISO format HH24:MI:SS.
DATETIME_FRACTION Specifies that the datetime columns can contain fraction part in a text file. Datetime data consist of valid date and time combinations. Time can contain a fraction part. For example, IBM DB2 TIMESTAMP data type can contain microseconds, Microsoft SQL Server and Sybase DATETIME can keep milliseconds.
Possible values - Yes, No. Yes means that datetime values can contain the fraction part in the text files, No means that if the fraction part exists, it is not saved in a text file.
The default value is Yes unless the target database is Oracle.
If the target database is Oracle and the datetime datatypes are converted to Oracle DATE data type, the default value is No. Oracle DATE data type doesn't support fractional seconds.
If the target database is Oracle and the datetime datatypes are converted to Oracle TIMESTAMP data type, the default value is Yes. Oracle TIMESTAMP data type supports fractional seconds.
Note. This option is ignored if any value is specified in the option DATETIME_FORMAT (see above).
REPLACE_NEWLINE Specifies a character string that replaces newline characters in the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix.
Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays Wizard saves them in the export files. Using this option you can specify a string that will replace newline characters.
For example, to replace newlines with zzz, set REPLACE_NEWLINE=zzz, to replace newlines with a blank, set REPLACE_NEWLINE=“ ”.
To specify the hexadecimal value of a character use the following syntax: 0xhh. For example, 0x2c specifies a comma character. Non printing characters like tab, carriage return and new line can be represented by \t, \r and \n accordingly. To specify a backslash character use '\\'. You can combine different character representations to specify the string. For example, REPLACE_NEWLINE=¦¦¦0x2c0x2c¦¦¦
When you need to remove newlines from the data, use the REMOVE_NEWLINE option. The REPLACE_NEWLINE option is ignored when the REMOVE_NEWLINE option is set to Yes (see below).
In most cases when you need to save newline characters in the data you have to change the line delimiter using the /LDEL option. This leads to proper processing of rows by import/load utilities.
REMOVE_NEWLINE If Yes is specified, newline characters are removed from the data. Newline characters are 0x0D0A or \r\n for Windows and 0x0A or \n for Unix.
Character columns like CHAR and VARCHAR e.g. can contain newline characters in the data. By default, SQLWays Wizard saves them in the export files. Using this option, you can specify the removal of newline characters from the data. The default value is No. Possible values - Yes, No.
If you need to replace newline characters with another string, use the REPLACE_NEWLINE option (see above).
In most cases when you need to save newline characters in the data, you have to change the line delimiter using the /LDEL option. This leads to proper processing of rows by import/load utilities.
ODBC_FLOAT_CONVERSION If Yes is specified, numeric data (REAL, FLOAT, DOUBLE datatypes) is converted to text representation by ODBC driver. Otherwise, SQLWays Wizard performs the conversion. The default value is Yes. Possible values - Yes, No.
LOBS_INFILE Places LOB data inside the text file. Possible values - Yes, No.
LOBS_INFILE_LENGTH This option specifies the maximum length of LOB data. Can be used only when option LOBS_INFILE is enabled. Default value is 65535.
LOBS_DIR_TAB If Yes is specified, SQLWays Wizard creates a sub-directory in the LOB directory for each table and writes LOB data for each table to the LOB sub-directories. The name of the sub-directory is the table name. A file is created for each LOB value, and file name is the table name with the extension equal to the sequence number of the LOB value.
If No is specified, SQLWays Wizard does not create LOB sub-directories for each table and writes all LOB files to the LOB directory.
The default value is Yes. Possible values - Yes, No.
FIXFMT_COLUMN_GAPS This option specifies the number of blanks that are placed between columns for FIX (fixed length) output format. The default value is 1.
DATAFILE_PART_SIZE By default this option specifies that the text file with exported table is not divided into parts and it is not possible to define the maximum possible file size for the file in the edit box.
The option can be defined. 2G value (two gigabytes) is specified by default. When this limit is exceeded SQLWays Wizard divides the text file into parts. Each file contains the whole number of rows. SQLWays Wizard creates the following files: table_name.txt, table_name2.txt, … table_nameN.txt.
Note: The minimal part size must exceed the maximum row size (without LOB data) multiplied by the prefetch row count.
CORRECT_24H_TIME This option changes the time value from “24:00:00” to the time specified in the option for TIME columns when migrating from DB2 database to other databases (for example Oracle and MSSQL Server). If this option is set to Yes, 23:59:59 will be used in the target database.
Default value for this option is NO. Possible values - Yes, No, exact time value (for example, 23:45:47).
CORRECT_24H_TIMESTAMP This option changes the time value from “24:00:00” to the time specified in the option for TIMESTAMP columns when migrating from DB2 database to other databases (for example Oracle and MSSQL Server). If this option is set to Yes, will be used 23:59:59.999999 in target database.
Default value for this option is NO. Possible values - Yes, No, exact time value (for example, 23:45:47).
CASESENSITIVE_DATA_IN_FK_COL By default, MSSQL Server is case-insensitive and processes the values of string columns that are included in foreign keys, in case-insensitive mode. This behavior is unacceptable in Oracle. Thus, this option allows you to extract data from the referenced columns in a specific case. If this option is set to Upper, the data will be extracted in uppercase. If Lower is specified, it will be extracted in lowercase. If this option is empty or not set, the data will be extracted as is. Possible values are: Upper, Lower, (empty). The default value is (empty).
BOOLEAN_REPRESENTATION This option controls the way boolean values will be converted in the target database. Possible values “Y/N”, “T/F” or “1/0”. If you set “Y/N”, “Y” or “N” values will be used; if you set “T/F”, “T” or “F” values will be used; if this option is set to “1/0”, then “1” and “0” will be used for boolean values.
IFMX_ROWID_IN_BLOBFILE_NAME This option adds ROWID to the file name where blob data will be placed after the Export process. If this option is set to “Yes”, ROWID number will be added to the file name. If this option is set to “No” or empty, ROWID number will not be added.
This option can be applied only for Informix as a source database. Possible values - “Yes”, “No” or empty. Default value - “No” or empty.
IFMX_UPDATE_STATISTICS If this option is set to “Yes”, SQLWays Wizard tool will run UPDATE STATISTICS in Informix database before performing the Export process. This will allow to get actual information from Informix database.
If this option is set to “No”, UPDATE STATISTICS will not be executed. This could speed up the export process.
This option can be applied only for Informix as a source database. Possible values - “Yes”, “No”. Default value - “No”.
CUT_NUMERIC_VALUE If this option is set to “Yes”, during the data export process SQLWays will check the number of digits to the right of the decimal point for numeric values and compare it with the scale allowed for numeric data types in table definition. If the number of digits exceeds the allowed scale, SQLWays Wizard will cut them. This feature was implemented for Firebird and InterBase to MS SQL Server directions.
Possible values - “Yes”, “No” or Empty.
Default value - “No”.
FIX_SPACE_FOR_SIGN This option controls the allocation of space for sign in data file when target of the migration is Oracle.
The option is available only if OUTFORMAT=FIX.
If it is set to “Yes”, the tool increases POSITION parameter for TINYINT, SMALLINT, INTEGER and BIGINT data types to be able to store sign. “No” option value generates POSITION in accordance with original data type length.
Possible values - “Yes”, “No” or Empty.
Default value - “Yes”.
RESOLVE_DELIMITER_CONFLICTS This option controls if resolving delimiter conflicts should be done during data export. It is helpful when source data contains the same symbols as used as delimiter. If delimiter conflicts are not resolved, target database will not be able to import data correctly. If resolving delimiter conflicts option is set to “Yes”, additional files with conflict data will be created. In these files specific delimiters are used to demarcate each row and each column.
Possible values - “Yes”, “No” or Empty.
Default value - “Yes”.
COLUMN_NAMES_FIRST_ROW The option controls whether columns names should be added before data in the first row of data file.
Possible values - “Yes”, “No” or Empty.
Default value - “No”.
COBOLFORMAT The option defines the format of cobol code as source. If this option is set to “No” - parser technology will exclude from analysis first 6 characters and all the characters that follow the 74th character in a row. If this option is set to “Free” - parser will try to read the cobol code as is, without removing any characters. If this option is set to “ANSI” - parser technology will not read all the characters that follow the 74th character in a row.
Possible values - “No”, “Free”, “ANSI”.
Default value - “ANSI”.
CONVERT_EMPTY_STRINGS_TO_NULL If this option is set to “Yes”, empty strings with default values for character columns will be converted to NULLs. When target database is Oracle, you need to leave empty strings as is, as in Oracle they are equal to NULL value. If this options is set to “No”, default values will be left as is.
Possible values - “Yes”, “No” or Empty.
Default value - “No”.
UUID_CONVERSION This option controls the case of the characters in the GUID columns.
Possible values - “LOWER”, “UPPER”.
Default value - “LOWER”.
DB2_APP_CTL_HEAP_SZ This option sets the APP_CTL_HEAP_SZ parameter in DB2 database when running the export of data from DB2. In this option you need to specify the number of KBs.
DATE_TIME_DATA_CONCAT_MAPPING In this option you can specify the path to the txt file with the date and time mapping.
IBMRPG_VERSION This option defines the version of RPG formatting. For more information about the specifics of the RPG formatting, please contact our support team:support@ispirer.com.
Possible values - “ILE”, “400”.
Default value - “ILE”.
SKIP_EXPORT_IF_DATAFILES_EXIST If this option is set to “Yes”, SQLWays Wizard will firstly check whether the .txt file with data and with the same name as table that is specified for conversion exists in the export directory. Then the tool will skip the data extraction for that table. If there is no .txt file with the same name as table name, the tool will extract the data for that table.
Possible values - “Yes”, “No” or Empty.
Default value - “No”.
COBOL_TAB_WIDTH This option removes the TAB character with the appropriate number of spaces, which should be defined in this option.
Default value - 4.
DECIMALPOINT This option defines the decimal point that should be used by SQLWays Wizard tool for numeric data types.
REVERSE_BLOCK_NAMES If this option is set to “Yes”, block names that start with numbers will be changed and numbers will be placed at the end of the block names. If this option is set to “No” - names will be left as is.
Possible values - “Yes”, “No” or Empty.
Default value - “No” or Empty.
COMP_FORMATTING This option defines the conversion of COMP clause for items. If it is set to “Yes” COMP clause will be converted. If it is set to “No” - COMP clause will be removed from conversion when migrating from Cobol to Oracle.
Possible values - “No”,“Yes” or Empty.
Default value - “Yes” or Empty.
CHAR_DATA_CASE This option defines the case of string data. If this option is set to “LOWER”, data will be converted to a lower case. If it is set to “UPPER”, data will be converted to upper case. If this option is not set, data will be migrated as is.
Possible values - “Upper”, “LOWER”, Empty.
Default value - Empty.
TRIM_TRAILING_BLANKS_IN_CHAR If this option is set to “Yes”, trailing blanks are removed from the string data. If this option is not set, data will be migrated as it is.
Default value - “No” or Empty.
DEP_ON_DATA_CUT This option controls the usage of data cut conditions for referenced tables. If this option is set to “Yes”, data cut conditions will be used for table for which this condition was specified and for tables that depend on this table. If this option is set to “No”, data cut conditions will be used only for table where such conditions were specified.
Possible values - “No”, “Yes” or Empty.
Default value - “Yes” or Empty.
USE_MULTIPROCESS_EXPORT This option defines whether the tables will be converted in multiple processes or in one process. If this option is set to Yes, then will be used multiprocess conversion. If this option is set to No, then conversion will be done in one process.
Possible values - “Yes”, “No” or Empty.
Default value - “Yes” or Empty.

Example:

[DATA]
COLUMN_DELIMITER=","
REMOVE_NEWLINE=No
CORRECT_24H_TIME=23:45:47


If you have any questions about the supported options or any features that you may need in your migration project, please contact our technical team: support@ispirer.com