FORMAT

You can specify different output formats for Optim™ High Performance Unload in the FORMAT clause.

FORMAT

Use the FORMAT option to specify the format of the output to be generated. If this option is not specified, the DEL format is used.

Syntax: FORMAT DEL|IXF|DELIMITED|ASC|DSNTIAUL|XML|MIGRATION|JSON|ORC|PARQUET|EXTERNAL
Variable: No variable value
Default: DEL

Among all these supported formats, there are ones supported as an input for the Db2® Load utility (DEL, IXF, DELIMITED, ASC). When specifying one of these formats, a MODIFIED BY option can be used too with a list of keywords corresponding to modifiers of the Db2 Load utility. When generating a Db2 Load command, the specification of any of the modifiers supported behaves on the content of this command. Some of these modifiers might also behave on the way the data is extracted in the output file.

Here is the list of modifiers which are supported as keywords of the MODIFIED BY option and not impacting the way the data is unloaded:

ANYORDER
GENERATEDIGNORE
GENERATEDMISSING
GENERATEDOVERRIDE
IDENTITYIGNORE
IDENTITYMISSING
IDENTITYOVERRIDE
IMPLICITLYHIDDENINCLUDE
IMPLICITLYHIDDENMISSING
NOROWWARNINGS
PERIODIGNORE
PERIODMISSING
PERIODOVERRIDE
ROWCHANGETIMESTAMPIGNORE
ROWCHANGETIMESTAMPMISSING
ROWCHANGETIMESTAMPOVERRIDE
TRANSACTIONIDIGNORE
TRANSACTIONIDMISSING
TRANSACTIONIDOVERRIDE
USEDEFAULTS

Refer to Db2 documentation for the description of these modifiers.

Here is the list of modifiers which are supported as keywords of the MODIFIED BY option, which must be specified with a value as a parameter:

CDEANALYZEFREQUENCY

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the CDEANALYZEFREQUENCY modifier in its MODIFIED BY option. It must be specified with a numeric value between 0 and 99 inclusive. For example:

CDEANALYZEFREQUENCY 15

INDEXFREESPACE

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the INDEXFREESPACE modifier in its MODIFIED BY option. It must be specified with a numeric value between 0 and 99 inclusive. For example:

INDEXFREESPACE 10

MAXANALYZESIZE

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the MAXANALYZESIZE modifier in its MODIFIED BY option. It must be specified with a numeric value followed by an ‘M’ or ‘G’ character corresponding to the Megabytes or Gigabytes units. For example:

MAXANALYZESIZE 100M

PAGEFREESPACE

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the PAGEFREESPACE modifier in its MODIFIED BY option. It must be specified with a numeric value between 0 and 100 inclusive. For example:

PAGEFREESPACE 20

TOTALFREESPACE

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the TOTALFREESPACE modifier in its MODIFIED BY option. It must be specified with an integer greater than or equal to 0. For example:

TOTALFREESPACE 5

Here is the list of modifiers which are supported as keywords of the MODIFIED BY option, which behave on the way the security label data is extracted in the output file:

SECLABELCHAR: When executing a task in native mode, use the SECLABELCHAR modifier if you want to replace the security label values by their corresponding string format values into the output file. Using this modifier for a task processed through the Db2 engine does not behave on the way the security label values are unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the SECLABELCHAR modifier in its MODIFIED BY option. This modifier is incompatible with the SECLABELNAME modifier. If they are specified together, the second one specified is ignored.
SECLABELNAME: When executing a task in native mode, use the SECLABELNAME modifier if you want to replace the security label values by their corresponding label names into the output file, if there are existing ones. Any security label value does not necessarily correspond to a label created for this exact value. This modifier can only be used if all the security label values involved into a given task have a corresponding label. Using this modifier for a task processed through the Db2 engine does not behave on the way the security label values are unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the SECLABELNAME modifier in its MODIFIED BY option. This modifier is incompatible with the SECLABELCHAR modifier. If they are specified together, the second one specified is ignored.

DEL

The DEL format is delimited ASCII format that uses the same delimiter and column separator as Db2 Export. See the Db2 publications for further information regarding the output of the Db2 Export command. If you use the DEL format for data migration, you can use the following option:

INTO tablename or qualifier.tablename

When you migrate or load a specific table and specify an explicit SELECT request for this table, you can use the INTO option to supply the table name. If the target table qualifier and name are different from the source table qualifier and name, you can specify the qualifier and name with the INTO option. Optim High Performance Unload uses the table name and qualifier from the INTO clause in the corresponding LOAD command instead of the ones in the source table.

Important: If you do not specify a name by using the INTO clause, the name of the source table will be used in the load file.

MODIFIED BY modifiers

With the MODIFIED BY option of a FORMAT DEL clause, you can specify Db2 Load modifiers that behave on the way an associated Db2 Load command is generated. The MODIFIED BY must contain at least one of the modifiers shown at the beginning of the FORMAT clause description. You can set more than one modifier by specifying the MODIFIED BY option followed by a space-separated list of the wanted modifiers. For example:

MODIFIED BY IDENTITYIGNORE GENERATEDMISSING ROWCHANGETIMESTAMPOVERRIDE

DELPRIORITYCHAR: Use the DELPRIORITYCHAR modifier to change the delimiter priority that is used by the Db2 Load command to character delimiter, record delimiter, column delimiter. The default delimiter priority is record delimiter, character delimiter, column delimiter. This modifier has no impact on the unloaded data; it is reported only in the generated LOAD command.
FASTPARSE: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the FASTPARSE modifier in its MODIFIED BY option.
DUMPFILE “filename”: This modifier has no impact on the data unloaded. The value specified to it must be an absolute path. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILE modifier in its MODIFIED BY option.
DUMPFILEACCESSALL: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILEACCESSALL modifier in its MODIFIED BY option.

IXF

The Integration Exchange Format (IXF) is a generic relational database exchange format. Optim High Performance Unload generates end-of-record delimiters that are supported by the IXF standards. If you use the IXF format for data migration, you can use the following option:

INTO tablename or qualifier.tablename

Important: If you do not specify a name by using the INTO clause, the name of the source table will be used in the load file.

Restriction: You cannot use the IXF output format when migrating data to a target Db2 instance with several database partitions. The Db2 Load utility cannot be used to load data from an IXF file into a multi-partitioned environment. For more information, see the Db2 Load utility documentation.

MODIFIED BY modifiers

With the MODIFIED BY option of a FORMAT IXF clause, you can specify Db2 Load modifiers that behave on the way an associated Db2 Load command is generated. The MODIFIED BY must contain at least one of the modifiers shown at the beginning of the FORMAT clause description, or one of the modifiers shown in the following list. You can set more than one modifier by specifying the MODIFIED BY option followed by a space-separated list of the wanted modifiers. For example:

MODIFIED BY IDENTITYIGNORE GENERATEDMISSING ROWCHANGETIMESTAMPOVERRIDE

FORCEIN: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the FORCEIN modifier in its MODIFIED BY option.
NOCHECKLENGTHS: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the NOCHECKLENGTHS modifier in its MODIFIED BY option.

DELIMITED

The DELIMITED format operates the same way as the DEL format except with the DELIMITED format you can specify the column separator, column value delimiter, or the null column delimitation options. If you do not specify a value for any of the delimited format options, the DELIMITED format operates the same way as the DEL format. When you specify DELIMITED format options, the separator must be different from the delimiter and must not exceed 1 byte in length. The selected separator and delimiter characters must be placed in single quotation marks. You can specify one or more of the three options together by using a space between each option. To specify values for delimited_block, use the following syntax:

DELIMITED INTO tablename|qualifier.tablename 
MODIFIED BY modifiers NULL DELIM NULLVAL "value"

Or:

DELIMITED INTO tablename|qualifier.tablename SEP|COLDEL 
'char' DELIM|CHARDEL 'char' NULL DELIM NULLVAL "value"

where:

INTO tablename or qualifier.tablename

When you use the LOADFILE option, you can use the INTO statement to supply the target table name and qualifier, which is useful when the target table name and qualifier are different from the source table name and qualifier. Optim High Performance Unload will use the table name and qualifier from the INTO clause in the corresponding LOAD command instead of the table name and qualifier in the source table.

Important: If you do not specify a name by using the INTO clause, the name of the source table will be used in the load file.

MODIFIED BY delimited_modifiers

With the MODIFIED BY option of a FORMAT DELIMITED clause, you can specify Db2 Load modifiers that behave on the way an associated Db2 Load command is generated. You can also specify modifiers from the ones listed below that describe the way that data is unloaded. The MODIFIED BY must contain at least one of the modifiers shown at the beginning of the FORMAT clause description, or one of the modifiers shown in the following list. You can set more than one modifier by specifying the MODIFIED BY option followed by a space-separated list of modifiers. For example:

MODIFIED BY CHARDEL '%' DECPLUSBLANK

Tip: You can use the MODIFIED BY option without generating a load file to modify the unloaded data format (except for the DELPRIORITYCHAR modifier, which has no impact on the unloaded data).

You can select from the following list of modifiers:

COLDEL 'char'|x'XX'

Use the COLDEL modifier to specify the separator character that is used for separating columns. The default value is the comma character (,).

CHARDEL 'char'|x'XX'

Use the CHARDEL modifier to specify the delimiter that is used to enclose the column values. The default value is the double quotation mark character (“).

DECPT 'char'|x'XX'

Use the DECPT modifier to specify the decimal point character that is used for decimal data. The default value is the period character (.). This modifier is incompatible with the IMPLIEDDECIMAL modifier.

DECPLUSBLANK

Use the DECPLUSBLANK modifier to specify if positive decimal data is to be unloaded with a blank space as the leading sign instead of a plus sign (+). By default, the plus sign (+) is used.

NOCHARDEL

Use the NOCHARDEL modifier if you do not want column values to be enclosed within delimiters. When you specify the NOCHARDEL modifier, the options DOUBLE DELIM and DELPRIORITYCHAR are ignored.

DELPRIORITYCHAR

Use the DELPRIORITYCHAR modifier to change the delimiter priority that is used by the Db2 Load command to character delimiter, record delimiter, column delimiter. The default delimiter priority is record delimiter, character delimiter, column delimiter. This modifier has no impact on the unloaded data; it is reported only in the generated LOAD command.

STRIPLZEROS

Use the STRIPLZEROS modifier to unload decimal data without leading zeros. Use this option to save disk space and improve performance when unloading data with leading zeros.

For example, if you use the following control file, Optim High Performance Unload creates an output file that contains the value +0001000.00:

GLOBAL CONNECT TO SAMPLE ;
UNLOAD TABLESPACE DB2 NO 
SELECT BONUS FROM EMPLOYEE WHERE BONUS=1000;
OUTFILE("Out")
FORMAT DELIMITED;

If you add the STRIPLZEROS modifier to the FORMAT clause, the value in the output file will be +1000.00:

FORMAT DELIMITED MODIFIED BY STRIPLZEROS;

KEEPBLANKS

Use the KEEPBLANKS modifier if you want it to be taken into account when generating a Db2 Load command, in order that leading and trailing blanks of strings not enclosed within delimiters be preserved when the Db2 Load command will be executed. When the NOCHARDEL modifier is not specified, the KEEPBLANKS modifier is ignored.

IMPLIEDDECIMAL

Use the IMPLIEDDECIMAL modifier if you want to unload the decimal type data without a character denoting the decimal separator. If the LOADFILE clause is also specified, the Db2 Load command generated will include the IMPLIEDDECIMAL modifier in its MODIFIED BY option. This modifier is incompatible with the DECPT modifier.

USEGRAPHICCODEPAGE

Use the USEGRAPHICCODEPAGE modifier if you want to unload the double-byte types data in DBCS code page into the output file. If the LOADFILE clause is also specified, the Db2 Load command generated will include the USEGRAPHICCODEPAGE modifier in its MODIFIED BY option.

FASTPARSE

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the FASTPARSE modifier in its MODIFIED BY option.

DUMPFILE “filename”

This modifier has no impact on the data unloaded. The value specified to it must be an absolute path. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILE modifier in its MODIFIED BY option.

DUMPFILEACCESSALL

This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILEACCESSALL modifier in its MODIFIED BY option.

Note

Through the COLDEL, CHARDEL, DECPT options, the characters to be used as delimiters can be specified between quotes. But, this is not suitable for non-printable characters. If one wants to use a non-printable character as a delimiter, one can do so by specifying a 'x' character followed by its hexadecimal value expressed with two hexadecimal digits between quotes.

SEP 'char'|x'XX' or COLDEL 'char'|x'XX'

Use the SEP and COLDEL keywords to specify a separator character (delimiter) for separating columns. A space between the SEP or COLDEL keywords and the variable ‘char' is optional. In DEL format, this character is a comma ( , ). A non-printable character can be used with an hexadecimal specification.

Important: The SEP and COLDEL keywords are supported for compatibility with earlier versions only. For new FORMAT clauses, use the MODIFIED BY clause instead. For example, use

MODIFIED
                        BY COLDEL '%'

instead of COLDEL '%'

DELIM 'char'|x'XX' or CHARDEL 'char'|x'XX'

Use the DELIM keyword to specify the delimiter that is used to enclose the column values. A space between the DELIM keyword and the variable ‘char' is optional. In DEL format, the delimiter is a double quotation mark ( " ). The keyword CHARDEL can be used the same as the DELIM keyword. A non-printable character can be used with an hexadecimal specification.

Important: The CHARDEL and DELIM keywords are supported for compatibility with earlier versions only. For new FORMAT clauses, use the MODIFIED BY clause instead. For example, use MODIFIED BY CHARDEL '%' instead of

CHARDEL
                        '%'

NULL DELIM

Use the NULL DELIM keyword to specify that the column value delimiter should be used in columns that contain null values. By default, null column values are not enclosed with delimiters. The result of specifying the NULL DELIM keyword is two consecutive column delimiters that represent the presence of a column that contains a null value. The NULL DELIM option applies only to columns that require surrounding delimiters, such as the DEL format where character columns need delimiters, but integer columns do not.

For example, if you accept the defaults for the column separator (SEP) and column value delimiter (DELIM), consider a table that contains three columns: INTEGER, nullable CHARACTER(5), and INTEGER. Place two rows in the columns with the following data: (1,'row1',1) and (2,null,2). When you unload this data with the DELIMITED format without the NULL DELIM option, the two rows would look like this:

1,"row1 ",1  and 2,,2

When you unload this data by using the DELIMITED format and the NULL DELIM option, the two rows would look like this:

1,"row1 ",1  and 2,"",2

NULLVAL

Use the NULLVAL option to specify a value to be written in the output file when a null value is encountered.

By default, nothing at all is written in the output file to represent a null value found.

For example, considering the following control file:

GLOBAL CONNECT TO SAMPLE;
UNLOAD TABLESPACE
SELECT * FROM TB_NULL;
OUTFILE("outfile")
FORMAT DELIMITED;

With this control file, Optim High Performance Unload creates an output file which content is the following:

[i975@lat186(hm510_mnt:) hm500_mnt]$ cat outfile
1,,,
2,2,"",2014-01-01
3,3,"  ",2014-01-01

If you change the format clause specification to FORMAT DELIMITED NULLVAL "NULL", the content of the output file would become:

[i975@lat186(hm510_mnt:) hm500_mnt]$ cat outfile
1,NULL,NULL,NULL
2,2,"",2014-01-01
3,3,"  ",2014-01-01

It shows that the null values are replaced by a specific value into the output file generated.

ASC

The ASC format is a non-delimited, sequential ASCII file with fixed-length records, ordered by row and column. ASC files can be used with the LOAD command, and can be used for data exchange with any ASCII product that has a columnar format for data.

The ASC format uses the following options:

INTO tablename or qualifier.tablename

If you are using the LOADFILE option and you must change the target table name or qualifier.tablename, you can specify the new name to be used in the LOAD file as follows:

FORMAT ASC INTO qualifier.tablename

For example:

GLOBAL CONNECT TO SAMPLE; 
UNLOAD TABLESPACE 
FLUSH BUFFERPOOLS NO 
LOCK NO 
SELECT * FROM EMPLOYEE; 
CCSID(930,300) 
OUTFILE("test.out") LOADFILE("test.load") 
FORMAT ASC INTO DBHPU.EMPLOYEE;

The resulting generated Load File (test.load) would look like this:

LOAD FROM test.out OF ASC 
MODIFIED BY STRIPTBLANKS 
METHOD L (1,6,7,18,19,20,34,36, 38, ...108,118) 
NULL INDICATORS(0,0,0,0,35,... 107) 
INSERT INTO TABLE DBHPU.EMPLOYEE 
(EMPNO,FIRSTNME,MIDINT,LASTNAME,WORKDEPT,...)

Attention: If you do not specify a name using the INTO clause, the name of the source table will be used in the load file.

MODIFIED BY modifiers

With the MODIFIED BY option of a FORMAT ASC clause, you can specify Db2 Load modifiers that behave on the way an associated Db2 Load command is generated. You can also specify modifiers from the ones listed below that describe the way that data is unloaded. The MODIFIED BY must contain at least one of the modifiers shown at the beginning of the FORMAT clause description, or one of the modifiers shown in the following list. You can set more than one modifier by specifying the MODIFIED BY option followed by a space-separated list of the wanted modifiers. For example:

MODIFIED BY PACKEDDECIMAL STRIPNULLS

Tip: The features of the MODIFIED BY option can be used without generating a load file to modify the unloaded data format.

BINARYNUMERICS: You can specify numeric data (INTEGER, DECFLOAT, and real data) to be unloaded in binary format by using the BINARYNUMERICS modifier. The binary representation used will always be big-endian, the one expected by Db2 Load. By default, unload does not use binary format for numeric data.
When you specify BINARYNUMERICS and the LOADFILE option is also selected, BINARYNUMERICS will be added to the MODIFIED BY clause in the load file.
PACKEDDECIMAL: Use this modifier to unload decimal data types in packed representation. This is not the default behavior.
When specifying PACKEDDECIMAL and the LOADFILE option is also selected, PACKEDDECIMAL will be added to the MODIFIED BY clause in the load file. This modifier is incompatible with the IMPLIEDDECIMAL and ZONEDDECIMAL modifiers.
DECPLUSBLANK: You can specify if positive decimal data is to be unloaded with a blank space as the leading sign instead of a plus sign (+) by using DECPLUSBLANK. By default, the plus sign (+) is used.
STRIPTBLANKS or STRIPTNULLS: You can specify if data for character columns of variable size is to be unloaded padded with a blank character or a null character by using the corresponding mutually exclusive modifiers: STRIPTBLANKS and STRIPTNULLS.
By default, the padding character is the blank (STRIPBLANKS). If the LOADFILE option is also selected, the load command will include either the STRIPTBLANKS or the STRIPTNULLS modifier in the MODIFIED BY clause.
NULLINDCHAR ‘char’|x’XX’: Use the NULLINDCHAR modifier if you want to specify the single character to be considered for denoting a NULL value into the output file. Its default value is ‘Y’. When this modifier is specified, it overrides the potential usage of the older NULL option, which also allows to specify the characters denoting if a value is NULL or not.
IMPLIEDDECIMAL: Use the IMPLIEDDECIMAL modifier if you want to unload the decimal type data without a character denoting the decimal separator. If the LOADFILE clause is also specified, the Db2 Load command generated will include the IMPLIEDDECIMAL modifier in its MODIFIED BY option. This modifier is incompatible with the PACKEDDECIMAL and ZONEDDECIMAL modifiers.
ZONEDDECIMAL: Use the ZONEDDECIMAL modifier if you want to unload the decimal type data in zoned representation into the output file. If the LOADFILE clause is also specified, the Db2 Load command generated will include the ZONEDDECIMAL modifier in its MODIFIED BY option. This modifier is incompatible with the PACKEDDECIMAL and IMPLIEDDECIMAL modifiers.
USEGRAPHICCODEPAGE: Use the USEGRAPHICCODEPAGE modifier if you want to unload the double-byte types data in DBCS code page into the output file. If the LOADFILE clause is also specified, the Db2 Load command generated will include the USEGRAPHICCODEPAGE modifier in its MODIFIED BY option.
FASTPARSE: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the FASTPARSE modifier in its MODIFIED BY option.
NOCHECKLENGTHS: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the NOCHECKLENGTHS modifier in its MODIFIED BY option.
DUMPFILE “filename”: This modifier has no impact on the data unloaded. The value specified to it must be an absolute path. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILE modifier in its MODIFIED BY option.
DUMPFILEACCESSALL: This modifier has no impact on the data unloaded. If the LOADFILE clause is also specified, the Db2 Load command generated will include the DUMPFILEACCESSALL modifier in its MODIFIED BY option.

DSNTIAUL

DSNTIAUL is an output that produces a format that allows the unloaded file to be loaded into Db2 for z/OS®. The output format is a sequential file without row or column delimiters. This file consists of an unbroken sequence of fixed-length records.

INTO tablename or qualifier.tablename

If you are using the LOADFILE option to generate a LOAD file and you want to change the name of the target table name or qualifier.tablename, you can specify the new name using the following option:

FORMAT DSNTIAUL INTO qualifier.tablename

Attention: If you do not specify a name using the INTO clause, the name of the source table will be used in the load file.

XML

The XML format is a file that consists of a sequence of records of variable length that are ordered by rows and columns. All of the records are string formatted with XML tags. Each record contains column names and column data.

The XML format uses the following options:

COLATTVAL or COLATTVAL_XML

The data in column names or column values can contain invalid XML characters. The COLATTVAL and COLATTVAL_XML options control the replacement of invalid XML characters in the result string.

When you use the COLATTVAL option, the program replaces invalid XML characters in both column values and column names. The COLATTVAL option is the default. You must specify the COLATTVAL_XML option if you want to replace invalid characters in column names only.

Table 1. Character replacement for XML attribute values and element values
Character	Replacement
<	<
>	>
“	"
&	&
‘	'

ROWTAGSTRING

Use the ROWTAGSTRING option to modify the name of the row tag, that the XML format uses to identify each row. Using this modifier, you can specify a different string constant for the tag. By default, the value "row" is assumed. You can use this option to customize the output to fit your specific XML requirements.

For example:

FORMAT XML ROWTAGSTRING "line"

When you unload data and this option is specified, the "row" tag is replaced with the "line" tag in the output file:

<?xml version="1.0" encoding="windows-1252" ?>
<table name="TBL_XML">
<line>
  <column name="NUMBER">123</column>
  <column name="NUMBER_NN">345</column>
</line>
</table>

MIGRATION

Use the MIGRATION keyword to specify the default format for data migration. At this time, the MIGRATION format works the same way as the DEL format. The MIGRATION keyword uses the following option:

INTO tablename or qualifier.tablename: When you migrate a specific table and specify an explicit select request for this table, the INTO option can be used to supply the table name. If the target table qualifier and name are different from the source table ones, use this option to specify the qualifiers. Optim High Performance Unload uses the table name and qualifier from the INTO clause in the corresponding load command instead of the ones in the source table.
Important: If you do not specify a name using the INTO clause, the name of the source table will be used in the load file.

JSON

The JSON format is an appropriate one to generate files which can be imported into a NoSQL database like Cloudant, CouchDB or MongoDB.

In addition to the list of rows extracted, the output files generated in the JSON output format might also contain a header and a footer, if the task specified implies that associated header and footer be there around the rows.

The layout of a table row is the following one:

An opening brace.
For each column, a pair compounded by the column name and its value, separated by a colon. These columns pairs are separated by commas.
A closing brace.

The layouts for the columns and their associated values are the same ones for all destinations.

Specifying a LOADDEST clause behaves on the content of the output file generated. The contents of its header and footer depend on the NoSQL destination chosen (either Cloudant, CouchDB or MongoDB). In the case where there is no LOADDEST clause specified, which means that there is no specific destination chosen, a generic output file is generated, without any content specific to any destination. As a result, in such a case, the output file generated does not contain a header and a footer, their contents depending on the destination to be considered.

When an output file with the JSON output format is generated with an associated upload command, the LOADDEST clause is mandatory.

Db2 and JSON data types:

The JSON output format is not compatible with CLOB, BLOB, DBLOB and XML data type columns. Specifying an unload in JSON output format against an SQL statement referring to a column of one of these data types would be rejected. The lack of finding an automatic user-friendly method to associate LOB or XML data to JSON documents is the main reason for this incompatibility.

Among the data types supported by the JSON output format, there is no distinction between numeric data, the data type is named number. Its values layout is the numeric value without any rounding character. As a result, the following Db2 data types are formatted into this data type:

SMALLINT
INTEGER
BIGINT
DECIMAL
REAL
DOUBLE

The Db2 string data types, (VAR)CHAR and (VAR)GRAPHIC, the Db2 time data types, DATE, TIME and TIMESTAMP, and the DECFLOAT Db2 data type are formatted into the JSON data type called string. Its values layout is the value rounded with double-quotes.

For the null values, they are expressed into the JSON data type called null, which value is always the constant string 'null' without any rounding character.

Big Data output formats

The Big Data output formats supported are the Parquet format and the ORC format. Their use can be specified with the PARQUET and ORC keywords respectively.

This ability is restricted to Linux and Windows environments, it cannot be used on AIX. The reason is that the requirements listed below for this feature do not exist on AIX.

When specifying a Parquet or an ORC output format for a data unload, it is mandatory to specify an OUTFILE clause.

For obtaining a file having a Big Data format, either for a data unload or for a data migration, Optim High Performance Unload needs to perform two internal steps on the machine where such a file is aimed to be generated:

Unloading the data from the source into a temporary file of delimited format: during this step, this data is unloaded into an output file having the DELIMITED format, with a header at its beginning containing the column names of the considered table.
Converting this temporary delimited file to another file of the specified Big Data format: this step relies on the execution of a Python script generated on the fly by Optim High Performance Unload, and relying on the use of the PyArrow library. Concerning this second step, there are two requirements for it to be executed successfully:
- Python 3.6 at least must be installed and declared in the system path. If not yet installed, it can be downloaded here: https://www.python.org/downloads/.
- The PyArrow library must be installed in the Python environment. If not yet installed, its installation can be done with the pip tool, as described here: https://arrow.apache.org/docs/python/install.html#using-pip.

Before performing the second step on the machine where it has to be performed, the Python environment is controlled.

For a successful execution of the conversion from a delimited file to a file in Big Data format, there must be enough disk space for landing both the temporary file and the final one. Once the conversion is terminated, the temporary delimited file is removed.

EXTERNAL

The EXTERNAL format is the one to be used for interfacing with the Db2 external tables. When it is associated to a DDLFILE clause, it allows to generate an external table creation command. The output format of the generated data files depends on the value of the FORMAT EXTERNAL clause, which can be TEXT, FIXED or BINARY.

The external table creation command is always based on, either a unique data file, or a set of data files following the naming convention appropriate for a data distribution at the Db2 level (a single radix, and an extension with a period and a 3-digit number).

TEXT: The TEXT option corresponds to a delimited format. It internally behaves like the DELIMITED format.
FIXED: The FIXED format is a non-delimited, sequential, ASCII file with fixed-length records, ordered by row and column. It internally behaves like the ASC format.
BINARY: The BINARY format is a binary one, which layout is an internal one used by Db2 for external tables of this type.

For all the external formats, through the use of the USING option, one can specify options which behave on the way an associated Db2 external table creation command is generated. The USING option must contain at least one of the options described in the list of options of the EXTERNAL format. One can set more than one option by specifying the USING option followed by a space-separated list of the options to be considered. You can refer to the Db2 documentation for the description of these eponymous options.

USING ( ext_options )

There is something to be mentioned, specific to Optim High Performance Unload, with the use of the S3 and AZURE options.

S3

When specifying the S3 option, the generation of the external table creation command is based on the use of various parameters which values must be set within the db2hpu.dest configuration file, into an associated S3 section.

"alias": It is an optional one. The purpose of this option is to support several sections relative to a given object storage within the db2hpu.dest configuration file. The alias is the mean to distinguish them. It must correspond to the alias set into a section relative to the object storage considered configured into the db2hpu.dest file. Its value is case sensitive. When specifying such an option, Optim High Performance Unload will search into the db2hpu.dest configuration file a section corresponding to the S3 object storage and containing an 'alias' parameter set with the alias considered. Otherwise, if this option is not specified, the first section corresponding to the S3 object storage found in the db2hpu.dest configuration file is taken into account by the Optim High Performance Unload task.

AZURE

When specifying the AZURE option, the generation of the external table creation command is based on the use of various parameters which values must be set within the db2hpu.dest configuration file, into an associated Azure section.

"alias": It is an optional one. The purpose of this option is to support several sections relative to a given object storage within the db2hpu.dest configuration file. The alias is the mean to distinguish them. It must correspond to the alias set into a section relative to the object storage considered configured into the db2hpu.dest file. Its value is case sensitive. When specifying such an option, Optim High Performance Unload will search into the db2hpu.dest configuration file a section corresponding to the Azure object storage and containing an 'alias' parameter set with the alias considered. Otherwise, if this option is not specified, the first section corresponding to the Azure object storage found in the db2hpu.dest configuration file is taken into account by the Optim High Performance Unload task.

Generation of an external table creation command:

When performing an unload task with the EXTERNAL format, one can also generate a command ready for a subsequent creation of an external table based on the output file obtained. This ability can be enabled by specifying a DDLFILE clause in the control file. In such a case, the command generated is based on the syntax specific to the creation of an external table, and one can behave on its content by using options of his choice in his specification of the FORMAT EXTERNAL clause.

Creation of an external table based on a data file located on a remote location:

An external table can refer to a file which location is a remote one for the environment where this external table is created from. The various situations are the following ones:

an external table based on a file located in an S3 compatible object store,
an external table based on a file located in a Microsoft Azure blob storage,
an external table created from a Db2 client environment, and based on a file located on the Db2 server cataloged.

These various cases respectively correspond to the S3, AZURE and REMOTESOURCE LOCAL options of a creation statement for an external table. Depending on the remote location to be considered, one can specify in the control file a FORMAT EXTERNAL clause with the S3, AZURE or REMOTESOURCE LOCAL options respectively.

For such a scenario of an external table based on a remote file, Optim High Performance Unload can also be used through an unique execution for preparing a subsequent creation of such an external table, by allowing to generate:

a data file ready for being associated to an external table
an external table creation command referring to the data file as being a remote one located at the appropriate location
an upload command ready to be used for a subsequent transfer of the data file to the appropriate remote location

Of course, when a FORMAT EXTERNAL clause is specified, the generation of the external table creation command can be enabled by specifying in the control file a DDLFILE clause referring to a file which will contain it.

For enabling the generation of an upload command as well, it is necessary to specify in the control file the LOADFILE and LOADDEST clauses. The LOADFILE clause is the one allowing to specify the file which will contain the upload command. The LOADDEST clause is the one allowing to specify a destination of the expected type, referring to a section of the configuration file for destinations containing the parameters needed for generating the upload command with the appropriate content.

After having executed Optim High Performance Unload for this purpose, one can then:

execute the generated upload command to copy the data file to the appropriate remote location for being considered by an external table
execute the external table creation command to create the external table

In such a scenario, some rules and restrictions apply to the FORMAT EXTERNAL, LOADFILE and LOADDEST clauses:

the LOADFILE and LOADDEST clauses must be specified together.
the LOADDEST and FORMAT EXTERNAL clauses must be specified consistently, relatively to the destination type.
for an S3 compatible destination or a Microsoft Azure destination, the LOADDEST and FORMAT EXTERNAL clauses must refer to the same section of the configuration file for destinations: either both clauses must not refer to any destination alias or they must refer to the same destination alias.

For a data file located in an S3 compatible object store, specify these clauses like the following:

FORMAT EXTERNAL TEXT|FIXED|BINARY USING (S3 [“alias”])
LOADDEST (OBJECT_STORAGE AWS_S3 [“alias”])

For a data file located in a Microsoft Azure blob storage, specify these clauses like the following:

FORMAT EXTERNAL TEXT|FIXED|BINARY USING (AZURE [“alias”])
LOADDEST (OBJECT_STORAGE AZURE [“alias”])

For a data file located on a Db2 server, and an external table to be created with a command executed from a Db2 client, specify these clauses like the following:

FORMAT EXTERNAL TEXT|FIXED|BINARY USING (REMOTESOURCE LOCAL)
LOADDEST (OBJECT_STORAGE FILESYSTEM [“alias”])

Implicit options:

When generating an external table creation command, some options might implicitly be added into this command by Optim High Performance Unload, depending on the execution case. The following list describes the concerned options:

CCSID: This option is always added into the generated external table creation command, and its value is based on the output locale in place for the Optim High Performance Unload execution.
DATAOBJECT: This option is always added into the generated external table creation command, and its value corresponds to the generated output file path and name. If the external table is not aimed to be based on a file located on an S3 compatible or a Microsoft Azure environment, and if the OUTFILE clause is used, it must be specified with an absolute path.
PARTITION: If data repartitioning is enabled, this option is added and set with a comma separated list of the involved partition numbers. It allows Db2 to correctly handle the partitioned output files when subsequently executing the external table creation. The pattern of the data file names must follow the naming convention appropriate for a data distribution at the Db2 level (a single radix, and an extension with a period and a 3-digit number).

Options compatibility:

For all the options available in the FORMAT EXTERNAL clause, here are the output format keywords they are compatible with:


Option	TEXT	FIXED	BINARY	Impact on unloaded data
AZURE	X	X	X
CARDINALITY	X	X	X
CRINSTRING	X	X
CTRLCHARS	X	X
DECIMALDELIM	X	X		X
DECPLUSBLANK	X	X		X
DELIMITER	X			X
ENCODING	X
ESCAPECHAR	X
FILLRECORD	X		X
IGNOREZERO	X
LFINSTRING	X
LOGDIR	X	X	X
MAXERRORS	X	X	X
MAX_ROWS	X	X
NOLOG	X	X	X
NULLVALUE	X			X
QUOTEDVALUE	X			X
RECORDDELIM	X	X		X
REMOTESOURCE	X	X	X
REQUIREQUOTES	X
SKIPROWS	X	X
SOCKETBUFSIZE	X	X	X
STRICTNUMERIC	X	X
S3	X	X	X
TIMEROUNDNANOS	X	X	X
TRIMBLANKS	X	X
TRUNCSTRING	X

Restrictions:

There are some restrictions applying to the specification of a control file, when considering the use of a FORMAT EXTERNAL clause.

It is not possible to generate a reliable external table creation command when the OUTFILE clause:

contains the %{listValFile} or %{listValDir} template keywords.
is specified with a comma-separated list of files.
implies the creation of output files which names refer to partition numbers, and these names are not suitable for a partitioned environment.

The LOADMODE, LOBFILE or MIGRATE clauses cannot be specified

The LOADDEST clause cannot be specified with another option than the AWS_S3, AZURE or FILESYSTEM values.

If the LOADDEST clause is not specified, then the LOADFILE clause cannot be specified.

If a FILEMAXSIZE clause has been specified and if the output is split into multiple files, the external table creation command will only refer to the first generated output file, because Db2 cannot handle multiple input data files through the DATAOBJECT option.

It is not possible to specify the REQUIREQUOTES TRUE and QUOTEDVALUE NO options of the FORMAT EXTERNAL clause together, because these specifications are incompatible.

The AZURE, REMOTESOURCE and S3 options of the FORMAT EXTERNAL clause are mutually exclusive.