Adding Db2 for z/OS tables to Data Gate for watsonx

Edit online

The process of adding tables is similar for all supported data sources. However, some of the characteristics of Db2 for z/OS tables require your attention.

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 not a primary key or 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

  • Tables are not renamed when they are added to Data Gate for watsonx. Adding a table results in a table with the same schema and table name, but in lowercase.
    Restriction:
    • If multiple tables on Db2 for z/OS have the same schema name or table name if the case is ignored, only one of these tables can be added to Data Gate for watsonx.
    • Source tables or schemas containing a period (.) or a colon (:) in the name cannot be added to Data Gate for watsonx.
    • If column names of Db2 for z/OS source tables contain a period (.) or a colon (:), these tables cannot be added to Data Gate for watsonx either.
    • Column names in the target table will be in lowercase. So, if the names of columns in a source table are the same if the case is ignored, these tables cannot be added to Data Gate for watsonx.
    • Tables with columns of the following data types cannot be added from the Data Gate for watsonx user interface:
      • BLOB
      • CLOB
      • DBCLOB
      • XML
      • TIMESTAMP WITH TIMEZONE

    The target tables have the columns of the source table, but the following additional columns are inserted before the source table columns:

    DWA_Load_Timestamp (automatically generated)
    This is an automatically generated column of the type TIMESTAMP. It contains the commit time (for loaded columns it depends on the LOCKMODE) in watsonx.data in UTC.
    DWA_Partition_ID (hidden)
    This is a hidden column of type INTEGER. It contains the physical partition number of the row in Db2 for z/OS.

    Tables with LOB columns can be added by calling the SYSPROC.ACCEL_ADD_TABLES stored procedure directly or by using one of the supported administration clients. However, LOB columns do not contain any useful data after they have been loaded to the target table. The content consists of no more than a unique internal identifier that was used by Db2 for z/OS.

    If the source table has LOB columns, but no columns of type ROWID, there will also be a DB2_GENERATED_ROWID_FOR_LOBS column of type VARBINARY(40). This column also exists in Db2 for z/OS, but it is an implicitly created column. See Row ID values for more information.

    Other columns that might be created implicitly by Db2 for z/OS are also copied to the target table.

Important: Consider the following if Db2 for z/OS tables are to be synchronized with Data Gate for watsonx:
  • The target tables to be created are converted to Unicode if you select Db2 for z/OS tables in a different format. Requirements regarding column widths are considered during the conversion. However, often, the converted (Unicode) tables need wider columns than the EBCDIC-encoded tables to avoid a truncation of values. Mind that watsonx.data does not have column-width limits for common types, such as CHAR or VARCHAR. The width of converted values will be implicitly increased, but since there is no length limitation of affected columns in Iceberg/watsonx.data, the increase will not be reflected in the table's schema.
  • 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.

    The data types that can be used as key columns in Apache Iceberg tables fed by Data Gate for watsonx are listed in the Iceberg specification. See Identifier Field IDs.

  • 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 that is 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 later. 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 not, 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 Icon (button) to change the perspective to change the perspective.
  2. On the side bar, click Instances.
    You see the Service instances page. All instances are listed in the Name column.
  3. Locate the row representing the instance that you want to view. Click the Open instance icon icon in that row. You might have to scroll to the right.
    You see the dashboard of your instance.
  4. From the Table tab of the dashboard, click the Add table button, which is 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 for watsonx 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.
    Host & Port
    The host name of your data source and the secure DDF port for connecting to that data source.
    Location
    The location name of the data source.
  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 for watsonx 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.
    Attention: While the synchronization process is running, do not manually change the received data in the storage object from the watsonx.data user interface. Doing so breaks the synchronization process and compromises the integrity of the data.