Adding tables to Db2 Data Gate

From the dashboard, you can always add tables to your configured Db2 Data Gate instances.

Before you begin

The synchronization function requires that tables have a unique key. Therefore, you might need to add unique keys to some of your source tables. If the chosen key column is neither a primary key, nor a primary index, you must explicitly specify it as an informational unique constraint. This cannot be done in Cloud Pak for Data. You must run alter or re-create the tables in Db2 for z/OS®.

About this task

  • When you add a Db2 for z/OS source table to Db2 Data Gate, an alias name is created for the target table. In most cases, this alias has the same name as the source table, unless the schema name of the source table starts with the string SYS, such as a user-created table named "SYSABC"."TABLETEST" or a system catalog table like "SYSIBM"."SYSTABLES". In the latter case, a different alias name is chosen, in which the schema name of the alias starts with the prefix DG_ followed by the name of the Db2 subsystem that the source table resides in (the Db2 location name). To this string, an underscore character (_) is added, followed by the original schema and table name. The reason is a limitation in Db2 and Db2 Warehouse, which do not allow alias names to start with the string SYS. For more information, see CREATE ALIAS statement. You find naming examples in the following table:
    Source table name Target alias name
    "SCHEMATEST"."TABLETEST" "SCHEMATEST"."TABLETEST"
    "SYSABC"."TABLETEST" "DG_DB2A_SYSABC"."TABLETEST"
    "SYSIBM"."SYSTABLES" "DG_DB2A_SYSIBM"."SYSTABLES"
  • For Db2 Data Gate, table columns will always be encoded in Unicode unless you selected Use this Db2 Data Gate instance for query routing in step 10 as you created the Db2 Data Gate instance.
Important: Consider the following if Db2 for z/OS tables are to be synchronized with Db2 Data Gate:
  • Tables must have a unique constraint (primary key or primary index). If such a key does not exist in the table or cannot be determined, you must redefine the table and specify such a key. The columns that you choose for the key must contain unique values or form such values when they are combined.
  • If you update Db2 for z/OS tables by running the LOAD utility, you must set the following keywords for the LOAD utility:
    • SHRLEVEL CHANGE
    • LOG YES
    Otherwise, the changes that were made by the LOAD utility are not detected by the synchronization function, and will thus not be reflected in your copied Db2 Data Gate tables.
  • You might have to reload or even remove tables from Db2 Data Gate after an ALTER TABLE or ALTER TABLESPACE statement is applied in Db2 for z/OS.
  • The Db2 source tables of your Db2 Data Gate replicas have an attribute named DATA CAPTURE. The attribute can carry the value Y or N (default), for yes or no. When incremental updates are enabled for a table, the DATA CAPTURE attribute of the table is set to the value Y. Once set, this attribute value persists, even if the table is disabled at a later time. Bear this in mind, especially if you run applications that use the DATA CAPTURE attribute.

    The DATA CAPTURE attribute is set by an ALTER TABLE statement, which is run as part of the SYSPROC.ACCEL_SET_TABLES_REPLICATION stored procedure. However, the attribute can only be set successfully if the ID of the user who runs the stored procedure has ALTER TABLE authorization. If this is not the case, a database administrator must set the attribute for all tables Db2 for z/OS.

Procedure

  1. Click Cloud Pak for Data main menu icon to open the Cloud Pak for Data main menu.
  2. Select Services > Instances.
    You see the Instances page. The Instances tab is in front. Your instances are listed in a table.
  3. Move your mouse pointer to the table row that represents the instance you want to work on. Move the mouse pointer to the very right of that row until a menu icon comes into view (Drop-down menu on instances table on My instances page.).
  4. From that drop-down menu (Drop-down menu on instances table on My instances page.), select Open.
    You see the dashboard of your instance.
  5. From the Table tab of the dashboard, click the Add table button, which is located on top of the tables list on the right.
  6. You might want to use the search function to narrow the choice first, especially if the list is long.
    1. Selecting Schema or Table from the drop-down list under Search and select tables for synchronization, you determine what to search for (schemas or tables).
    2. Type the names of schemas or tables in this field, either fully or partially, to display just the objects starting with or containing the search string. The names of schemas tables that have already been selected are not listed.
    Note: If a Db2 source table was created after Db2 Data Gate connected to a Db2 subsystem, you might be unable to locate this table in the list. In such a case, refresh the web page in your browser.
  7. Select schemas. On the left, you see a list of the table schemas in the connected Db2 subsystems or data sharing groups. Select one or more of the check boxes in front of the schema names.
    The tables of the selected schemas are listed in the box to the right of the schema list. By default, all tables that belong to the chosen schema are selected.
  8. If needed, reduce the number of selected tables by clearing some of the check boxes in front of the table names.
  9. Click Continue.
    You see a summary of your table selection:
    Total schemas
    The total number of schemas involved in your selection.
    Total tables
    The total number of tables you selected.
    Total estimated table size
    An estimate of the overall size of the selected tables in Bytes based on the most recent RUNSTAT utility results. If the RUNSTAT utility has never been run for the table space, this value will be N/A.
  10. Under the summary, you see a switch labeled Enable synchronization and load tables. It is already enabled.
    This setting triggers the load process for all selected tables. Only loaded tables contain data; if the tables are unloaded, your instance will be worthless for connecting applications. The setting also ensures that the selected tables in your Db2 Data Gate instance are continually updated by the synchronization function. Leave the switch enabled. If you disable it, you need to complete these tasks in a separate step later on (that is, first enable synchronization, then load the tables).
  11. Click Finish to confirm.
    Important: The process might take several minutes to complete. Do not close the Add tables page during that time.