Creating custom indexes manually

If the target database of your 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.

Attention:
  • When tables are created in a target database, an alias table and one or more table views are created at the same time. You have to make sure that you create manual indexes on the tables, and not the aliases or views. Especially the aliases can be hard to distinguish from the tables because their names might be exactly the same.
  • Data Gate does not save the indexes you create. After reloading a target table, you must create its index again. For Data Gate versions on Cloud Pak for Data 4.8.x, you can avoid this by using custom index templates. See Preserving custom indexes for more information. It describes the recommended way to create custom indexes. The procedure in this topic is recommended only if you do not want to fully reload the tables to be indexed.

Follow the procedure to create target table indexes manually:

Procedure

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 of your source table name (e.g., TABLE1). Data Gate adds the TABLE ID and TABLE VERSION strings to the names of all the tables it creates.
    You might, for example, find the following strings:
    • "DWAXXX"."TABLE1-ID_182-V1"
    • "DWAXXX"."TABLE1-ID_182-V2"

      XXX stands for the location name of the table. ID-182 is the table ID. V1 and V2 are table version. This is the name of the table in the catalog of the target database.

    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". Example of a corresponding SQL command:
    CREATE INDEX DG_CUSTOM_IDX1 ON "DWAXXX"."TABLE1-ID_190-V2" (COL1, COL2, "DWA_Partition_ID (hidden)")

    "DWA_Partition_ID (hidden)" is a helper column in the target table. It increases the performance of the index. To add it at the end of the column list is therefore recommended.

    Important:
    • Global indexes, that is, non-partitioned indexes on partitioned tables, are not recommended.
    • Pause synchronization before you create a presumably big index. Otherwise, tables can end up in the ERROR state because lock issues arise.