LOADDEST

Standard databases

There is no specific keyword to be added. It allows to specify that the destination is a standard Db2 database. This is the default destination, which is considered if no explicit LOADDEST clause is specified The Db2 destination is consistent with the following output formats: DEL, DELIMITED, ASC, IXF, DSNTIAUL and XML.

When a Db2 Load command is written into a file specified through the LOADFILE clause, such a file contains:

• an optional Db2 connection step, if the WITH STANDARD AUTH option has been specified into a LOADDEST clause
• the Db2 Load command itself
• an optional Db2 disconnection step, if the WITH STANDARD AUTH option has been specified into a LOADDEST clause

When a Db2 Load command has to be prepared with an authentication step, its content depends on the specification of the alias option or not. If the alias option is specified, this step is based on the usage of various parameters which values have to be set within the 'db2hpu.dest' configuration file, into its associated section for the Db2 destination. If there is no alias option specified, there is nothing to be set within the 'db2hpu.dest' configuration file.

When considering an automatic migration, credentials of local type must be defined into the 'db2hpu.creds' file.

"alias" option

It is an optional one. The purpose of this option is to support several sections relative to a Db2 destination configured within the configuration file for destinations. The alias is the mean to distinguish them. It must correspond to the alias set into a section relative to the destination type considered configured into the 'db2hpu.dest' configuration 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 called Db2 containing an 'alias' parameter set with the alias considered.

When considering a scenario of an automatic data migration towards a standard Db2 destination, it is necessary to specify either a corresponding LOADDEST clause with an appropriate alias, or a TARGET ENVIRONMENT clause.

When considering a standard Db2 destination, a LOADDEST clause with an alias and a TARGET ENVIRONMENT clause cannot be specified together.

Remote databases cataloged locally

The REMOTE keyword must be specified. It allows to specify that the destination is a remote Db2 environment cataloged locally. The remote Db2 destination is consistent with the following output formats: DEL, DELIMITED, ASC and IXF.

If both LOADFILE and OUTFILE clauses are associated to a remote Db2 destination, the OUTFILE clause must contain an absolute path specification, because a Db2 Load command for a remote database must refer to an absolute file name.

When a Db2 Load command is written into a file specified through the LOADFILE clause, such a file contains:

• an optional Db2 connection step, if the WITH STANDARD AUTH option has been specified into a LOADDEST clause
• the Db2 Load command itself
• an optional Db2 disconnection step, if the WITH STANDARD AUTH option has been specified into a LOADDEST clause

When a Db2 Load command has to be prepared with an authentication step, this step is based on the usage of various parameters which values have to be set within the 'db2hpu.dest' configuration file, into its section for the remote Db2 destination.

When considering an automatic migration, credentials of remote type must be defined into the 'db2hpu.creds' file.

Note: the WITH option of the LOADMODE clause is inconsistent with a remote Db2 destination.

"node" option

It is an optional one, and it can be specified only with the REMOTE option. The purpose of this option is to support several sections relative to a remote Db2 node configured within the configuration file for destinations. The node name is the mean to distinguish them. It must correspond to the name of a remote Db2 node 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 called RemoteDB2 containing a 'node' parameter set with the node name considered.

If this option is not specified, the first section called RemoteDB2 found in the 'db2hpu.dest' configuration file is taken into account by the Optim High Performance Unload task.

Db2 Warehouse databases

The WAREHOUSE keyword must be specified. It allows to specify that the destination is a Db2 Warehouse environment. The WAREHOUSE option is only consistent with the DEL and DELIMITED output formats.

Both IBM Db2 Warehouse on Cloud environments (IBM Bluemix®) and IBM Db2 Warehouse environments are supported. All the information about these environments is available here: https://www.ibm.com/us-en/marketplace/cloud-data-warehouse

If an upload command specified through the usage of a LOADFILE clause has to be generated, two tools can be used for it:

• CLPPlus, which can be found either in the binaries directory of the Db2 installation, or in the Db2 Warehouse driver package (downloadable on IBM website).
• cURL, which can be downloaded on its official website http://curl.haxx.se/download.html

In such a case, the file generated contains:

• an optional authentication step, if a WITH STANDARD AUTH option has been specified into the LOADDEST clause
• the upload command itself

If an automatic migration is performed, its underlying execution can be based on two methods:

• the usage of the Db2 Load API
• the usage of the cURL tool

When an upload command has to be prepared for a Db2 Warehouse destination, its generation is based on the usage of various parameters which values have to be set within the 'db2hpu.dest' configuration file, into its section for the Db2 Warehouse destination considered.

"alias" option

It is an optional one. The purpose of this option is to support several sections relative to a given destination within the configuration file for destinations. The alias is the mean to distinguish them. It must correspond to the alias set into a section relative to the destination type 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 destination chosen and containing an 'alias' parameter set with the alias considered.

Otherwise, if this option is not specified, the first section corresponding to the destination chosen found in the 'db2hpu.dest' configuration file is taken into account by the Optim High Performance Unload task.

WITH STANDARD AUTH option:

When considering an unload task, relative to a standard Db2 destination, or a remote Db2 destination, or a Db2 Warehouse destination based on the usage of the CLPPlus tool, the WITH STANDARD AUTH option affects the content of the file specified with the LOADFILE clause.

If a LOADDEST clause has been specified with a WITH STANDARD AUTH option and if a LOADFILE clause has been specified too, an authentication step is added into the file generated.

This authentication step consists in establishing a connection to the Db2 database concerned. When executing the command file generated, the user's password will be prompted, and the user involved must have the appropriate permissions to perform the expected Db2 Load command. After the Db2 Load command execution, a disconnection from the Db2 database is performed.

Depending on the nature of the destination, this authentication step takes the following information into account:

• for a Db2 destination:
  • if an alias has been specified:
    • a database name is considered: when it is configured, it is taken from the dbname parameter into the associated section in the 'db2hpu.dest' configuration file. Instead, it is taken from the Optim High Performance Unload control file (GLOBAL block) or the -d command line option.
    • a user name is considered: when it is configured, it is taken from the user parameter into the associated section in the 'db2hpu.dest' configuration file. Instead, the user having launched Optim High Performance Unload is taken.
  • if no alias has been specified:
    • a database name is considered: it is taken from the Optim High Performance Unload control file (GLOBAL block) or the -d command line option.
    • a user name is considered: when it is configured, it is taken from the 'db2hpu.creds' file within a local section for the current Db2 instance. Instead, the user having launched Optim High Performance Unload is taken.
• for a remote Db2 destination:
  • a database name is considered: it is taken from the dbname parameter into the associated section in the 'db2hpu.dest' configuration file.
  • a user name is considered: when it is configured, it is taken from the user parameter into the associated section in the 'db2hpu.dest' configuration file. Instead, the user having launched Optim High Performance Unload is taken.
• for a Db2 Warehouse destination:
  • a user name is considered: it is taken from the user parameter into the associated section in the 'db2hpu.dest' configuration file.
  • if the upload command is based on the CLPPlus tool usage, an URL, a port number and a database name are considered: they are taken respectively from the url, port and dbname parameters into the associated section in the 'db2hpu.dest' configuration file.

When considering an unload task, relative to a Db2 Warehouse destination based on the usage of the cURL tool, the WITH STANDARD AUTH option affects the content of the file specified with the LOADFILE clause. If a LOADDEST clause has been specified with a WITH STANDARD AUTH option and if a LOADFILE clause has been specified too, a preliminary step is added for an obfuscated prompting of a password.

When performing an automatic migration to a standard or a remote Db2 database, it is not mandatory to specify the authentication option, but it is internally set by default. When performing an automatic migration to a Db2 Warehouse destination, it is mandatory to specify a standard authentication method. Having an authentication method applying in such a migration case is mandatory for security reasons. Creating appropriate credentials for the destination considered is mandatory.

The supported NoSQL databases are Cloudant, CouchDB and MongoDB. They can be respectively specified through the usage of the CLOUDANT, COUCHDB and MONGODB keywords. All the information about these NoSQL databases is available here:

• Cloudant: http://www.cloudant.com
• CouchDB: http://www.couchdb.apache.org
• MongoDB: http://www.mongodb.org

When the NOSQL_DB option is specified in the LOADDEST clause, the JSON output files generated are consistent with the NoSQL destination chosen, and contain additional appropriate header and footer. If an upload command has to be prepared too, its nature is also consistent with this same destination chosen. For a NoSQL destination, the command in question is based on the usage of an upload tool depending on the effective destination chosen:

• Cloudant and CouchDB: the tool involved is cURL (it can be downloaded on its official website http://curl.haxx.se/download.html)
• MongoDB: the tool involved is mongoimport (it can be downloaded on its official website within a package of MongoDB binaries https://www.mongodb.org/downloads)

For a NoSQL destination, when an upload command is written into a file specified through the LOADFILE clause, such a file contains:

• an optional authentication step, if a WITH STANDARD/KERBEROS AUTH option has been specified into the LOADDEST clause
• the upload command itself

In order to work with an Hadoop destination, an Hadoop environment must be installed and must be working fine on the machine where the upload commands towards this destination are to be launched. The following packages, which are not always installed by default with an Hadoop environment, might be needed, depending on the intended usage:

• Apache HBase: http://hbase.apache.org
• Apache Hive: http://hive.apache.org/downloads.html
• Apache Pig: http://pig.apache.org/releases.html

It is necessary to specify a LOADDEST clause with the HADOOP option when an upload command has to be prepared for an Hadoop destination. If not, specifying the LOADDEST clause with an Hadoop destination is useless, because it has no effect at all on the content of the output files generated.

When an upload command is prepared, its nature is consistent with the destination chosen. For an Hadoop destination, the command in question is based on the usage of an upload tool depending on the effective destination keyword chosen:

• HBASE: the tool involved is hadoop or pig
• HIVE: the tool involved is beeline (compatible with the Hive 0.14 version and greater)
• HDFS: the tool involved is hdfs
• MAPREDUCE: the tool involved is hadoop

For an Hadoop destination, when an upload command is written into a file specified through the LOADFILE clause, such a file contains:

• an optional authentication step, if a WITH KERBEROS AUTH option has been specified through the LOADDEST clause,
• if the LOADDEST clause is specified with another destination keyword than HDFS, a step copying the data file to be uploaded to a temporary file located on the associated HDFS file system,
• the upload command itself,
• if the LOADDEST clause is specified with the destination keyword HBASE or MAPREDUCE, a step removing the temporary file created in the previous copying step (this step does not exist when the HIVE destination keyword is specified, because the temporary file is automatically removed by the upload command executed in this case).

When an upload command is written into a file specified through the LOADFILE clause, such a file contains:

• an optional authentication step, if a WITH STANDARD AUTH option has been specified into the LOADDEST clause
• the upload command itself

Usually, accessing to a Big Data environment is protected by an authentication mechanism. As a result, in order to succeed in uploading data to it, an appropriate authentication step is needed. The WITH STANDARD/KERBEROS AUTH option offers the ability to refer to the authentication mechanism involved. There are two types of methods supported, respectively through the two following keywords:

• the STANDARD keyword: it refers to a standard method, based on the usage of a traditional user and password combination
• the KERBEROS keyword: it refers to a Kerberos method, based on the usage of a Kerberos principal

For the NoSQL, Hadoop, Db2 Warehouse and PostgreSQL destinations, there are constraints usage which need to be mentioned:

• UTF-8 encoding: Optim High Performance Unload must use an UTF-8 encoding when generating files aimed to be uploaded to these destinations. When run for a NoSQL or an Hadoop destination, the reason is that they do not support any other encoding than UTF-8. When run for a Db2 Warehouse destination, the reason is that the underlying Db2 database is implicitly created with the UTF-8 encoding. In consequence, in order to avoid problems, for all of these destinations, if an encoding specification different from UTF-8 is made into a control file for a NoSQL, Hadoop or Db2 Warehouse destination, it is ignored and internally forced to UTF-8
• When considering an Hive destination, most of the date and timestamp formats are inconsistent with this destination date and timestamp data types. Here is a list of formats for these data types existing in Optim High Performance Unload, and supported by the Hive destination:
  • The unique date data type format supported is DATE_C.
  • The timestamp data type formats supported are TIMESTAMP_A and TIMESTAMP_G. If the TIMESTAMP_A format is considered, its time and timestamp separators must be changed in order to be consistent with an Hive destination. In this case, the time separator must be specified with the TIMEDELIM clause set with the ':' value, and the timestamp separator must be specified with the TIMESTAMPDELIM clause set with the space character for its first value, its second value being ignored.

  In order to change such a format, the output format for the output file must be set to DELIMITED.

• When considering a Db2 Warehouse destination with the usage of the CLPPlus tool, most of the date, time and timestamp formats are inconsistent with this tool. Here is a list of formats for these data types existing in Optim High Performance Unload, and supported by this tool:
  • The unique date data type format supported is DATE_C.
  • The time data type formats supported are TIME_A and TIME_B. The default separator for these formats is the dot character. The colon character is also supported as a separator for the time values by Db2 Warehouse. Its usage can be specified with the TIMEDELIM clause set with the ':' value.
  • The timestamp data type formats supported are TIMESTAMP_A, TIMESTAMP_B and TIMESTAMP_G.

  In order to change such a format, the output format for the output file must be set to DELIMITED.

• When considering a PostgreSQL destination, most of the date, time and timestamp formats are inconsistent with this destination date, time and timestamp data types. Here is a list of formats for these data types existing in Optim High Performance Unload, and supported by the PostgreSQL destination:
  • The unique date data type format supported is DATE_C.
  • The unique time data type format supported is TIME_F.
  • The unique timestamp data type format supported is TIMESTAMP_G.
  In order to change such a format, the output format for the output file must be set to DELIMITED.
• Naming convention applied to files containing upload commands generated on Windows platforms: on these platforms, the name of a file which must be executed must be terminated with a “.bat” suffix, in order to succeed in executing it. When generating an upload command file on a Windows platform, if the name specified for it through the LOADFILE clause does not end with such a “.bat” suffix, this suffix is automatically added to the name used when creating the corresponding upload command file.
• Limitations relative to the usage of a user-defined MapReduce program: if one wants to use a MapReduce of his own for the generation of an upload command, one can do so specifying a LOADDEST clause with its MAPREDUCE keyword, and setting its associated 'command' parameter within the db2hpu.dest configuration file, into its section referring to a MapReduce destination. The string to be specified for this parameter must follow a specific pattern. It must:
  • start with the name of the MapReduce program considered, which must be packaged as a jar file
  • continue with an optional part containing potential options to be given to the MapReduce program
  • expect after it the specification of the data file to be uploaded, which is going to be added to it automatically when generating the associated upload command

Here is a concrete example illustrating this usage. Considering the following LOADDEST clause:

LOADDEST(HADOOP MAPREDUCE)

with an associated destination section into the db2hpu.dest configuration file like the following one:

 [MapReduce]
  command=/tmp/myMR.jar --input
  hdfspath=/tmp/
       

where:

1. the MapReduce program is called myMR.jar
2. the --input option is an option of the MapReduce program expecting a subsequent specification of the data file to be handled

with an OUTFILE clause like the following one:

OUTFILE("outfile")

The upload command generated for such a case would look like:

hadoop jar /tmp/myMR.jar --input /tmp/outfile