Configuring Data Ship for DB2
Requirements
• The DB2 database must have EXPLAIN plan tables created within the DB2 SYSTOOLS schema on the database that will participate in TDV data ship.
Limitations
This feature is not valid for:
• Binary and BLOB data types
• Kerberos implementations
About Data Ship and DB2 Temporary Tables
When a DB2 database is the target for your TDV data ship operation, you can ship the data to temporary tables. The ‘Use global temporary space for temporary tables’ option creates global temporary tables in DB2 and drops the data and table structure after the data ship query is completed.
The temporary table is materialized under the DB2 session schema. CREATE TABLE permissions are required on the database or schema where the temporary table will be created. The schema name is not used in the DDL to create the temporary table. The table structure is saved in the shared data dictionary, when TDV inserts data, the table is created in the session schema, and data is loaded.
When TDV drops the temporary table, it drops the instance tied to the session and the shared table structure.
To configure the DB2 data sources that might participate in data ship
1. Log into DB2 system and start DB2 command processor db2 and call a system procedure:
$ db2
2. Connect as a user with privileges to create and modify tables within the DB2 SYSTOOLS schema on the database that will participate in TDV data ship. For example:
db2 => CONNECT TO <db-name> USER '<db-user>' USING '<db-pass>'
3. Determine if EXPLAIN tables already exist in SYSTOOLS schema, using syntax similar to the following:
db2 => SELECT NAME, CREATOR, TYPE
FROM SYSIBM.SYSTABLES
WHERE TYPE = 'T' AND (NAME LIKE 'ADVISE\_%' ESCAPE '\' OR NAME LIKE 'EXPLAIN\_%' ESCAPE '\')
If these tables exist, skip the rest of this section.
4. If the EXPLAIN tables do not exist, create them using syntax similar to the following:
db2 => CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)),
CAST (NULL AS VARCHAR(128)))
5. Verify that the command shows:
Return Status = 0
6. Verify that the tables were created by using the following syntax:
db2 => list tables for schema systools