Adding tables to Data Gate

From the dashboard, you can always add tables to your configured 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 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 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 Data Gate, table columns will always be encoded in Unicode unless you select Use this Data Gate instance for query acceleration in step 10 as you create the Data Gate instance.
Important: Consider the following if Db2 for z/OS tables are to be synchronized with Data Gate:
  • In general, tables in the target database are Unicode tables. That is, tables to be created in the target database are converted to Unicode if you select Db2 for z/OS tables in a different format. Different requirements with regard to column widths are taken into account during the conversion. In many cases, the converted (Unicode) tables need wider columns than the EBCDIC-encoded tables to avoid a truncation of values.

    Suppose you have a VARCHAR column in an EBCDIC table. During the conversion to Unicode, the column width is determined as follows:

    • For EBCDIC columns with a column width of up to 10 characters, the length is doubled.
    • For EBCDIC columns with a greater column width, the original column width is multiplied by a buffer factor.
    • Finally, the maximum length for Unicode VARCHAR columns is compared with either of these values, and the smaller one is used as the Unicode column length. That is:
      For EBCDIC column widths lower than 10:
      MIN(max. Unicode column width, EBCDIC column width * 2)
      For EBCDIC column widths greater than 10:
      MIN(max. Unicode column width, EBCDIC column width * buffer factor)

    The resulting length after the conversion can be significantly greater than the original length.

    Tables are not converted, however, if you selected Use this Data Gate instance for query routing in step 10. In this case, the original encoding of the Db2 for z/OS tables is preserved. For example, if the columns of an original Db2 for z/OS table are encoded in EBCDIC, the columns of the corresponding table in the target Db2 Warehouse database are also EBCDIC-encoded.

  • 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 Data Gate tables.
  • You might have to reload or even remove tables from Data Gate after an ALTER TABLE or ALTER TABLESPACE statement is applied in Db2 for z/OS.
  • The Db2 source tables of your Data Gate replicas have an attribute named DATA CAPTURE. The attribute can carry the value Y or N (default), for yes or no. When synchronization is 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. Make sure that the Cloud Pak for Data perspective is selected. If necessary, click Perspective icon to change the perspective.
  2. Under My instances, click the name of the instance that you want to edit.
  3. Click Open instance.
  4. On the Table tab of the instance, click the Add table button, which is located on top of the tables list on the right.
  5. 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 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.
  6. 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.
  7. If needed, reduce the number of selected tables by clearing some of the check boxes in front of the table names.
  8. 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.
  9. 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 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).
  10. Click Finish to confirm.
    Important: The process might take several minutes to complete. Do not close the Add tables page during that time.