Adding tables to Data Gate on Cloud

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 OpenShift®. 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 on Cloud, 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 Warehouse on Cloud, which does 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"
Draft comment: kuester@de.ibm.com
The last bullet was not crossed out by Eirini. However, the option was crossed out previously, so logically, it also has to be deleted here.
Important: Consider the following if Db2 for z/OS tables are to be synchronized with Data Gate on Cloud:
  • 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. The column length of the Unicode column is calculated according to this formula:
    MIN(max. column width, MAX(column width + fixed buffer, column width * buffer factor))

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

    • The maximum length for Unicode VARCHAR columns is determined.
    • The greater of the following two values is calculated:
      • The length of the EBCDIC column plus a 10-byte buffer.
      • The length of the EBCDIC columns multiplied by a buffer factor. However, this buffer factor is only applied to columns with a width greater than 10 characters.
    • Finally, the maximum length for Unicode VARCHAR columns is compared with the maximum previously calculated, and the smaller of these two values is used as the Unicode column length. The resulting length after the conversion can be significantly greater than the original length.
  • 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 on Cloud tables.
  • You might have to reload or even remove tables from Data Gate on Cloud after an ALTER TABLE or ALTER TABLESPACE statement is applied in Db2 for z/OS.
  • The Db2 source tables of your Data Gate on Cloud 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. Open the Data Gate on Cloud instance UI (as described in chapter xx)
    Draft comment: kuester@de.ibm.com
    Resolve the reference "as described in chapter xx"
    You see the dashboard of your instance.
  2. From the Table tab of the dashboard, click the Add table button, which is located on top of the tables list on the right.
  3. 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 on Cloud 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.
  4. 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.
  5. If needed, reduce the number of selected tables by clearing some of the check boxes in front of the table names.
  6. 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.
  7. 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 on Cloud 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).
  8. Click Finish to confirm.
    Important: The process might take several minutes to complete. Do not close the Add tables page during that time.