Creating indexes manually

If the target database of your Db2 Data Gate setup is Db2, indexes on the target tables are created automatically when you load the tables. However, if these indexes do not satisfy your needs, you can manually create indexes for your target tables.

About this task

The automatically created table indexes aim for an optimization of the synchronization process with regard to the deletion of records from the target database.

No indexes are imported or migrated from the Db2 for z/OS® source tables.

For Db2 target databases, the automatic index creation follows these selection rules:

  1. The unique constraints of the table are identified.
  2. Possible indexes are identified. These must include one of the unique constraints, and be among the possible choices with the lowest number of non-nullable columns.
  3. From the possible indexes identified in step 2, one of the indexes with the lowest overall number of columns is selected.

An automatic index creation does not exist for tables in Db2 Warehouse target databases.

If the automatically created index does not meet your (performance) requirements, you can manually create indexes of your own choosing.

In the case of a Db2 target database, an index with fewer columns might reduce the time needed for synchronization or table load processes.

On the contrary, synchronization and table load processes might slow down if you increase the overall number of indexes on a table. So you have to decide what is more important to you: faster synchronization and load, or faster query execution.

Follow the procedure to create target table indexes manually:

Procedure

Db2 Data Gate does not expose the names of the target tables. To create an index, you must therefore identify the underlying table names first. To this end, you can use db2look, which is a tool provided by your target database.

  1. From the command line of the Cloud Pak for Data system where your target database is deployed, enter the following command 
    db2look -d bludb -e > db2look.out
    This command writes the data definition (DDL) statements of your target tables to a file called db2look.out.
  2. Open the db2look.out file in a text editor and search for the string TABLE1. Db2 Data Gate adds the string TABLE plus an integer number to the names of all the tables it creates. The string TABLE1 will be included in the name of the first table that was added to the target database.
    You might, for example, find the following strings:
    • "DWAXXX"."TABLE1-ID_182-V1"
    • "DWAXXX"."TABLE1-ID_190-V2"1

    This result indicates that two versions of TABLE1 exist.

  3. Create the index on the newest version of the table.
    In the example provided in step 2, this is "DWAXXX"."TABLE1-ID_190-V2".
    Attention: Db2 Data Gate does not save the indexes you create. After reloading a target table, you must create its index again.
1 XXX stands for the location name of the table. This is the name of the table in the catalog of the target database.