The TDV native loading option improves cache performance when:

The native cache loading options are enabled by default. Native loading makes use of functionality that is inherent to the data source to improve the speed of data movement.

If TDV detects that your data source and your cache data target are on the same TDV data source resource, it determines if a direct SELECT and INSERT is the best way to move data or if one of the configurable native loading options might work faster. Most databases have proprietary data movement functionality that is optimized for that database. For example, all Oracle databases come with database link functionality. That database link functionality can be used to provide cache loading performance gains that are not possible when using the TDV JDBC connections to move data.

If both the native database loading and the TDV cache loading fail, the refresh status is set to failed.

For DB2, the TDV native loading option makes use of DB2’s LOAD utility. The LOAD utility can quickly load or add data to a table where large amounts of data need to move. LOAD can perform significantly faster than IMPORT because LOAD writes formatted pages directly into the database while IMPORT uses SQL INSERTs. Before attempting to use this method, we recommend that you see if using JDBC batch insert options can give you acceptable performance improvements. If you choose to implement the JDBC functionality, you do not need to configure the DB2 LOAD utility, or change any TDV configuration settings.

The DB2 LOAD utility when working with TDV requires that the DB2 command-line utility be run. How it works with TDV varies by platform:

This feature is not valid for:

Temporary files used to support this feature are created in <TDV_install_dir>\...tmp\cacheloading\db2, all files, data, commands and logs are deleted by TDV after the upload process completes.

For Microsoft SQL Server, the TDV native loading option makes use of Microsoft’s bulk import and bulk export (bcp) function.

The bulk import and bulk export functionality available caching for Microsoft SQL Server requires the configuration of the Microsoft bcp utility. When the bulk import and bulk export functionality is used instead of the JDBC functionality, performance is improved. The bcp utility (bcp.exe) is a command-line tool that uses the Bulk Copy Program API. The Microsoft bcp utility performs the following tasks:

If you choose to implement the JDBC functionality, you do not need to configure the bcp utility, or change any TDV configuration settings.

When the cache source and target exist on the same SQL Server data source, native loading of cache data using a direct SELECT and INSERT can provide significant performance gains. The feature requires that the value of the Enable Bulk Data Loading Studio configuration parameter be set to True.

When configuring data ship or a cache for Microsoft SQL Server, you can configure the bcp utility and then set up access permissions. Both of these processes are described below.

The configuration steps are similar for caching and data ship, for more information see Configuring Data Ship for Microsoft SQL Server.

Note: In the bcp utility, NULL values are interpreted as EMPTY and EMPTY values are interpreted as NULL. TDV passes the ‘\0’ value (which represents EMPTY) through the bcp utility to insert am EMPTY value into the cache or data ship target. Similarly, TDV passes an EMPTY string (“”) to the bcp utility to insert a NULL value into the cache or data ship target.

For Netezza, the TDV native loading option makes use of the Netezza external tables. You must follow the Netezza documentation for how to set up the external tables on the Netezza data source.

For Netezza you have the following configuration choices:

When the cache source and target exist on the same Netezza data source, native loading of cache data using a direct SELECT and INSERT can provide significant performance gains. The feature requires that the value of the Enable Bulk Data Loading Studio configuration parameter be set to True. For Netezza, the parameter value must also be True to enable bulk loading of data cached from procedures.

XML, BINARY, and VARBINARY are not supported by Netezza, any data type related to these data types will not be added to the Netezza cache.

By default, TDV analyzes the table selected for caching to see if DISTRIBUTE can be used to achieve performance gains through parallel processing of the cache query. All keys and indexes specified on the resource are consulted. This is done to determine if the DISTRIBUTE clause was added to the native DDL used to create the cache target tables. For single-table caching, the cachekey column is also added to the DISTRIBUTE clause. If neither keys nor indexes are specified, DISTRIBUTE ON RANDOM is used.

Optionally, if you need to move data that has NUL characters, you can use the following procedure to determine how those characters are managed by TDV.

When the Ignore Nul Characters in Strings configuration parameter is true, NUL('\0') characters get discarded by Netezza and rest of the String is loaded. When the configuration parameter is false, NUL characters in Strings are not discarded. The default value is false.

When you want to use Oracle as your cache target, you can finish setup of the TDV native loading option that uses Oracle’s database link functionality to provide better caching performance. Additionally, you can define the TDV parallel cache loading option. This feature is supported for Oracle cache targets.

Depending on the platform you are installing to, your steps might vary. These steps do not detail every step as might be required by Sybase, refer to your Sybase documentation for further details.

By default, TDV is configured to use Vertica’s Bulk Load utility or INSERT/SELECT as the native loading mechanism. There are no TDV configuration parameters that you need to set.