This is an old revision of the document!



Ispirer Home Page Ispirer SQLWays Product Page Request SQLWays

Sqlways.ini File - Section [DATA]

This article describes sqlways.ini file [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 the text files.
Datetime data consists of valid date and time combinations. Depending on the database, this can 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 year
* MM - 2-digit numeric abbreviation of month (01-12)
* MON - 3-symbol abbreviated name of month (JAN, FEB etc)
* DD - 2-digit day of month (01-31)
* HH - 2-digit hour of day (01-12)
* HH12 - 2-digit hour of day (01-12)
* HH24 - 2-digit hour of 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 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 database).
DATE_FORMAT This option specifies the format of the date datatypes in the 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 year
* MM - 2-digit numeric abbreviation of month (01-12)
* MON - 3-symbol abbreviated name of month (JAN, FEB etc)
* DD - 2-digit day of month (01-31)
For example, to get date values like 21-DEC-2002, set the datetime format 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 the text files.
A 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 day (01-12)
* HH12 - 2-digit hour of day (01-12)
* HH24 - 2-digit hour of 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 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 the fraction part in the 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 the 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 the Oracle DATE data type, the default value is No. The Oracle DATE data type doesn't support fractional seconds.
If the target database is Oracle and the datetime datatypes are converted to the Oracle TIMESTAMP data type, the default value is Yes. The Oracle TIMESTAMP data type supports fractional seconds.
Note. This option is ignored if any value is specified in the option DATETIME_FORMAT (see earlier).
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 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 makes possible properly processing 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 saves them in the export files. Using this option, you can specify that newline characters be removed from the data. The default is No. Possible values - Yes, No.
If you need to replace newline characters with another string, use the REPLACE_NEWLINE option (see earlier).
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 makes possible properly processing rows by import/load utilities.
ODBC_FLOAT_CONVERSION If Yes is specified, numeric data (REAL, FLOAT, DOUBLE datatypes) are converted to the text representation by ODBC driver. Otherwise, SQLWays performs the conversion. The default is Yes. Possible values - Yes, No.
LOBS_INFILE Place the LOB data inside the text file. Possible values - Yes, No.
LOBS_INFILE_LENGTH This option specifies maximum length of the LOB data. Can be used only when option LOBS_INFILE is switched on. Default value is 65535.
LOBS_DIR_TAB If Yes is specified, SQLWays creates a subdirectory in the LOB directory for each table and writes LOB data for each table to the LOB subdirectories. The name of the subdirectory is the table name. A file is created for each LOB value, and the file name is the table name with the extension equal to the sequence number of the LOB value.
If No is specified, SQLWays does not create LOB subdirectories for each table and writes all LOB files to the LOB directory.
The default 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 largest 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 divides the text file into parts. Each file contains a whole number of rows. SQLWays creates the 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, will be used 23:59:59 in 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 MSSQL foreign key on char columns treats values in case insensitive mode. In Oracle such behavior is not allowed. So this option will allow to extract data from referenced columns in specific case. If this option is set as Lower, data will be extracted in lower case. If specified Upper - in upper case. If this option is empty or not set, data will be extracted as is.
The default value is Empty. Possible values - Yes, No, Empty.
BOOLEAN_REPRESENTATION This option controls the way boolean values will be converted in target database. Possible values “Y/N”, “T/F” or “1/0”. If set “Y/N” will be used “Y” or “N” values, if set “T/F” will be used “T” or “F” values, 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 will add 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 will 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 running the Export process. It will allow to get actual information from Informix database.
If this option is set to “No”, UPDATE STATISTICS will not be executed. It could speed up the export process.
This option will 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 with the scale allowed for numeric data types in table definition. If the number of digits exceeds the allowed scale, SQLWays will cut them. This feature 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 migration direction 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. Otherwise, “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 into “Yes”, additional files with conflict data are 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 if columns names should be add 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 a source direction. 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”, then empty strings in default values for character columns will be converted to NULLs. When target database is Oracle, need to leave empty strings as is, as in Oracle it is 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 set the APP_CTL_HEAP_SZ parameter in DB2 database when running the export of data from DB2. In this option we need to specify the number of KBs.
DATE_TIME_DATA_CONCAT_MAPPING In this option you specify the path to the txt file with the date and time mapping.
IBMRPG_VERSION This option defines 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 will check whether the .txt file with the 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, then 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, that should be defined in this option.
Default value - 4.
DECIMALPOINT This option defines the decimal point that should be used by SQLWays tool for numeric data types.
REVERSE_BLOCK_NAMES If this option is set to “Yes”, block names that starts 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 way of 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.

Example:

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


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


Ispirer Home Page Ispirer SQLWays Product Page Request SQLWays


© 1999-2024, Ispirer Systems, LLC. All Rights Reserved.