[Data] Subsection

This subsection is used for customizing data conversion and specifying data format in export files.

COLUMN_DELIMITER - for the /CDEL option (Column delimiter).

LINE_DELIMITER - for the /LDEL option (Line delimiter).

DECIMAL_POINT - for the /DECPT option (Decimal point character).

DATETIME_FORMAT - This option specifies the format of the datetime datatypes in the text files.

Datetime data consist of valid date and time combinations. Depending on the database, this is the 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 accuracy to the milliseconds and FFFFFF for accuracy to the microseconds.

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 part FFF 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)

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.

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 - for the /LOBIN option. (Place the LOB data inside the text file). Possible values - Yes, No.

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.

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.

BOOLEAN_REPRESENTATION - When this option is set to "default" SQLWays tool automaticaly chooses how to represent data for boolean datatype. But it is possible to set the boolean representation explicitly using available values: "Y/N", "1/0".

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.

Ispirer SQLWays Database Migration Software

[Data] Subsection