Defining TDV Data Ship Configuration Parameters

Several parameters are available for configuring data ship:

• Maximum Number of Concurrent Data Transfers
• Execution Mode
• Buffer flush threshold

Because every system varies, you might need to test several values for these parameters to determine what works best.

To configure data ship parameters

1. Log into Studio as the admin user.
2. From the Administration menu, choose Configuration.
3. Navigate to and determine the best settings for the following parameters.

Parameter

Description

Execution Mode

Default server handling for data ship queries:

• EXECUTE_FULL_SHIP_ONLY—(Default) Generates an error to alert you that the full data ship was not successful.

Allows data-ship execution only when the query can be performed without federation after shipment. In other words, after shipment the query must be pass-through.

With EXECUTE_FULL_SHIP_ONLY, if parts of the query are federated after the data ship query optimization has been applied, the query execution plan Data Ship Notes contain a message like the following:

Data Ship Query is not possible because after ship query is still federated and Data Ship Mode is EXECUTE_FULL_SHIP_ONLY.

• EXECUTE_PARTIAL_SHIP – This option allows both data ship and federated queries to coexist and proceed to execution even if they cannot be fully resolved into a full data ship.

EXECUTE_PARTIAL_SHIP allows a query to proceed without throwing an error, even if a federated query is still required to complete query execution.

• EXECUTE_ORIGINAL – If certain nodes cannot be pushed and shipped because some predicates cannot be resolved prior to data pass-through, the original (pre-data ship) query plan is executed.

EXECUTE_ORIGINAL causes query execution using the pre-data-ship execution plan whenever a query cannot be completely pushed to the data sources. The Data Ship Notes reveal that fact on execution.

When a data ship query is not possible because of dependency on external results or to support something that cannot be pushed to the data source, the original pre-data-ship execution plan is used without shipping results to the targeted table to complete the invocation.

• DISABLED — causes the query execution plan to process the request invocations without data ship. DISABLED mode is useful for debugging.

Maximum Number of Concurrent Data Transfers

Limits the number of concurrent data transfers (default 100,000), to avoid affecting the performance of other processes. Beyond this limit, new queries requiring data transfers are queued until an existing transfer is completed.

4. Click Apply.
5. (Optional) Navigate to and determine the best settings for the following parameters.

Parameter

Description

Buffer Flush Threshold

Limits the size of the buffer. (Default is 10000.) Certain types of data ship SQL executions buffer large tables before delivery to the data ship target, and the buffer size can exceed available memory. The buffer is flushed when this limit is reached.

DataShip Keep Temp File or Table

The default value is false. When set to true, the server does not delete the temp table or file generated for each data ship request. This option should only be enabled when requested by a support team member.

This value is locally defined. It is not altered when restoring a backup and is not replicated in a cluster.

This parameter is ignored if you are using data ship and check the ‘Use global temporary space for temporary tables’ check box for the data ship target.

6. (Netezza only) Navigate to and determine the best setting for the following parameters.

Parameter

Description

Keep nzload Log File

The Netezza driver generates a log file for each nzload operation. If true, all log files are kept. If false (default), no log files are saved to disk.

This value is locally defined. It is not altered when restoring a backup and is not replicated in a cluster.

nzload Log Directory

The directory to save the Netezza driver nzload log file for data ship. The default log directory is $TDV/tmp/dataship/netezza.

This value is locally defined. It is not altered when restoring a backup and is not replicated in a cluster.

Escape Character

The escape character to use while exporting contents to or importing contents from a flat file. It defaults to backslash (\).

Truncate Long Strings during native loading

Boolean. True causes any string value that exceeds its declared CHAR or VARCHAR storage to be truncated to fit. False (default) causes the system to report an error when a string exceeds its declared storage.
7. Click Apply.
8. (Teradata only) Navigate to and determine the best settings for the following parameter:

Parameter

Description

DataShip FastExport/FastLoad Threshold

Limit for the FastLoad and FastExport processes within Teradata. If the row number exceeds this limit, use FastLoad or FastExport.

This value is locally defined. It is not altered when restoring a backup and is not replicated in a cluster.
9. Click Apply and then OK.
10. Restart the TDV Server.