Configuring Data Ship Bulk Loading Option for DB2
For DB2, the TDV bulk 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:
Platform | DB2 Command-Line Utility Name | Execution Details |
Windows | DB2CMD.exe | When it runs, a command window pops up, requires a password, and stays active until the upload completes. While the window is open, the password might be visible. |
UNIX | db2 | It can be run as a background process. |
Requirements
• The data being loaded must be local to the server.
• Requires advanced DB2 database level configuration
Limitations
This feature is not valid for:
• Binary and BLOB data types
• Kerberos implementations
To configure the DB2 LOAD utility to work with TDV for data ship
1. Consult the IBM documentation on configuring and using the DB2 LOAD utility.
2. Install and configure all relevant parts of the DB2 LOAD utility according to IBM’s instructions for your platform.
The client drivers might need to be installed on all the machines that are part of your TDV environment.
3. Verify the full path to the DB2 command-line utility that you want to use to run the DB2 LOAD utility. For example, locate the path to your DB2 SQL command-line client.
4. Open Studio.
5. Select Administration > Configuration.
6. Locate the DB2 Command-Line Utility Path configuration parameter.
7. For Value, type the full directory path to the DB2 LOAD command-line program. For example:
For example: for Windows, set the value to “C:/Program Files/IBM/SQLLIB/BIN/db2cmd.exe”; for UNIX, set it to /opt/ibm/db2/V10.5/bin/db2. If there are spaces or special characters in the pathname, be sure to enclose the whole string in double quotes.
8. Locate the Debug Output Enabled for Data Sources configuration parameter and set the value to True.
9. Locate the Enable Bulk Data Loading configuration parameter.
10. Set the value to True.
11. Click Apply.
12. Click OK.
13. Restart your TDV Server.
Some configuration parameter value changes are applied while the TDV Server is running, but many of them require a server restart to work correctly.
14. Open Studio and locate the DB2 data source for which you want to enable the bulk load feature.
15. Open the data source editor.
16. Select the Advanced tab.
17. Scroll down to locate the Database Alias (Used For DB2 Load) field.
This property is passed on to the DB2 Load to identify the cataloged database.
18. For Value, type the name of the DB2 database. For example: dvbudb21.
19. Save your changes.
20. Refresh your cache.
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.