Creating a Db2 Data Gate instance

A Db2 Data Gate instance is a profile that describes a collection of server processing resources, databases, and database tables. To make the data in the tables accessible to connecting target applications, you must create a Db2 Data Gate instance first.

Before you begin

Create a IBM® Db2 or IBM Db2 Warehouse instance
An IBM Db2 or IBM Db2 Warehouse database must exist because you must associate a target database with your Db2 Data Gate instance.
Configure network access
If you (or your network administrator) have not yet configured network access between Db2 for z/OS® and the Db2 Data Gate instance, follow the process specified in Configuring network access between Db2 Data Gate and IBM Z.
  • For configuration on IBM Z®, record the hostname and port number for this Db2 Data Gate instance. You will also need the IP address of this instance, which can be determined using the ping command.
  • If you specified a port other than the default port of 443 when creating this Db2 Data Gate instance, configure iptables rules to redirect access from the specified port to the port used in the OpenShift® route:
    1. Log in to your Cloud Pak for Data machine using ssh.
    2. Issue the following command if it has not been run for any other Db2 Data Gate instance on this IBM Cloud Pak for Data system:
      iptables -t nat -A POSTROUTING -j MASQUERADE
    3. Configure the iptables rules for this Db2 Data Gate instance by issuing the following command, where PortNum is the port specified while creating the instance: 
      iptables -t nat -A PREROUTING -p tcp --dport PortNum -j REDIRECT --to-port 443
    Note: Consider making the iptables configuration persistent as it can reset if the OpenShift environment reboots. The following commands (issued using ssh) show one way to configure iptables in a persistent manner:
    yum install iptables-services
chkconfig iptables on
iptables -t nat -A PREROUTING -p tcp --dport PortNum -j REDIRECT --to-port 443
service iptables save

Procedure

  1. Open the IBM Cloud Pak for Data landing page in a web browser and log in with your user ID and password.
  2. Click Cloud Pak for Data main menu to open the main menu.
  3. Select Services > Services Catalog.
  4. In the navigator on the left, click the Category drop-down list.
  5. Select Data sources.
  6. On the right, select the tile labeled Db2 Data Gate. You might have to scroll down to find it.
    The Db2 Data Gate landing page opens. You see a screen capture of the Db2 Data Gate dashboard.
  7. If this is very first instance you are creating, click the Provision instance button in the upper right. If there are other instances already, this button is not shown. Instead, you see the Three-dot-menu menu icon. Click it to display the choices, then select New instance.
    A page with the title Create data gate instance opens.

Target database:

  1. In the Target database section, select the type of workload for your instance:
    Transactional
    Simple, but high-volume data retrieval as in point queries.
    Analytic
    Involved queries that access many different tables.
    The radio buttons will be active only if a suitable target databases exists. If this is the case, the Target database drop-down will offer selectable choices. Depending on the selected radio button, a suitable database instance will be preselected in the Target database drop-down list.

    When you select a target database, the system "tries" to use your Cloud Pak for Data credentials (platform credentials) to access the selected database. Access is granted if the current user has the admin role on that database. If the ID you want to use lacks this role, an existing Cloud Pak for Data administrator can add it by following these steps:

    1. Click Cloud Pak for Data main menu icon to open the Cloud Pak for Data main menu.
    2. Select Data > Databases. You see tiles that represent existing database instances.
    3. On the tile of the database you want to access, click Drop-down menu on database tile and select Manage access. You see a list of the users with access to the database.
    4. In the list, move the mouse pointer over the entry of the user you want to give administrator access to.
    5. In the Service role column of the list, you find the current role of the user. Click the Drop-down icon icon next to that role to open the related menu.
    6. From the drop-down menu, select Admin.

    If a suitable database does not exist for the selected workload type, one or both radio buttons will be grayed out. In this case, you will see one or two links that allow you to create the missing database instances:

    Create transactional database
    This link takes you to the Cloud Pak for Data page for creating a Db2 database. This is the right database type for transactional workloads. On this page, click Provision instance and follow the instructions.
    Create analytic database
    This link takes you to the Cloud Pak for Data page for creating a Db2 Warehouse database. This is the right database type for analytic workloads. On this page, click Provision instance and follow the instructions.
    Important: Follow the requirements and recommendations in Installing a Db2 instance for Db2 Data Gate on Cloud Pak for Data.
  2. Select Deploy this Db2 Data Gate instance on the node of the target database if you want you want both instances to co-exist on a single node. By default, the Db2 Data Gate instance is deployed on a different node. The performance might be better if both instances are on the same node.
  3. If Analytic was selected in step 8, you see an additional check box Use this Db2 Data Gate instance for query acceleration.
    When selected, queries against your Db2 for z/OS source tables will be routed to the Db2 Data Gate instance you are creating, and executed on copies of the original tables in the target database. The query results are returned to Db2 for z/OS. This way, you can shift some of the query workload to a different environment and save valuable z/OS processing resources.

Compute resources:

  1. Under Compute resources, specify the number of processing cores (CPUs) and the amount of memory that you want allocate to your instance.
    You can type an integer number in the entry field to the right of the CPU slider bar, or adjust the slider until the correct number is displayed in the field.
  2. In the same way, specify the amount of memory you want to allocate to your instance. Use the entry field or the slider next to Memory.

Namespace:

  1. Under Namespace, select an existing namespace.
    A namespace is a virtual cluster within the physical cluster that your instance is running on. It is used to organize and divide resources between multiple users.

Storage:

  1. Under Storage, select one of the following choices:
    • Create new storage
    • Use existing storage
  2. Select a file system type from the drop-down list underneath.
    The file system you select must be compatible with the client apps that finally access the data of your instance.
  3. In the Size field, specify the size of the storage used by your instance.
    The number you specify must be an integer. It signifies the storage size in GB.

Route:

  1. In the Host field, specify the first part or prefix of the route hostname.
    A generated route hostname is inserted automatically. You can change the prefix, that is, the part before the first dot. Do not change the domain part of the hostname, that is, the part after the first dot or prefix. Your chosen name is used to create a unique URL that can be used by client applications to access the data of your instance.
  2. In the Port field, specify the network port that is used by the route.
    Port 443 is the default for your first instance. The number increases by one with each additional instance. This holds true even if you specify a different port number. Suppose you have specified the port number 44443. The system would then propose 44444 as the port for your next instance.

    Use the default setting unless there are multiple Db2 Data Gate instances in the Cloud Pak for Data system connecting to the same Db2 for z/OS subsystem. In that case, specify a different port number for each Db2 Data Gate instance. On IBM Z, PAGENT rules need to be configured to connect to multiple Db2 Data Gate instances using the same IP address and different port numbers.

    Each Db2 Data Gate instance creates an OpenShift route, which is a hostname plus a port (the OpenShift route port is always 443). If you use a port other than the default port of 443 for Db2 Data Gate, you will need to configure iptables rules to redirect access from the specified port to the OpenShift route port. See Configure network access for information about configuring iptables rules.

    Tip: Later on, when you have completed the instance creation, you can review the values you entered by displaying the details page of the instance. For more information, see Viewing Db2 Data Gate instance details.
  3. Click Review in the lower right of the page.
  4. In the Name field, type a name for your instance.
  5. Review your settings. When ready, click Confirm in the lower right.
    You see a progress window. The process takes up to 10 minutes to complete. Do not close the browser page during that time. When the process has finished, the Configure page is opened, and you can continue with the configuration of your instance.

In the section under the heading Source database, you specify the details of a Db2 subsystem or data sharing group that is supposed to serve as the data source of your Db2 Data Gate instance.

  1. In the Host field, type the IP address or TCP/IP host name by which the Db2 subsystem or data sharing group can be contacted over a TCP/IP network.
  2. In the Secure DDF Port field, type the configured secure DDF port number used by the Db2 for z/OS data server.
  3. In the Location field, type the location name of the Db2 subsystem or data sharing group to be accessed by Db2 Data Gate.
    The location name is the name of the Db2 for z/OS data server in the SYSIBM.LOCATIONS table of the communications database.
  4. In the Username field, type the ID of the user who will access the data source.
    This is one of the privileged user user IDs created or specified while Creating Db2 Data Gate users and granting privileges on z/OS.
  5. In the Password field, type the password of the user specified in step 25.

In the next section, you enter information that is related to the log reader. The log reader accesses the logs of the connected source database (Db2 subsystem or data sharing group) over the network.

  1. Under Log reader, select whether you want to use the same user credentials for the log reader that you also use for accessing the data source (user specified in step 25), or different user credentials. That is, you might want to specify a different user ID and password, a different host IP address or a different secure DDF port for the log reader (compare steps 22 and 23).

    This is especially useful in connection with a Db2 for z/OS data sharing group because it allows you to configure a dedicated secure port and location alias for the log reader.

    Use source database connection information for the log reader
    Same user ID, password, host IP address, and secure DDF port as for the source database
    Use different connection information for the log reader
    One or more pieces of the access information are different from the information used to access the source database: user ID and password, host IP address, or secure DDF port.
    The log reader user is the user who starts the log reader task. You must use the ID of the user you gave MONITOR2 authorization during your network setup (USRT001 in the example). See Granting privileges to the log reader user. Select Use source database connection information for the log reader only if that user is the same as the user in step 25.

    If you choose Use different connection information for the log reader, you will see a few extra controls:

    1. In the Host field, type the hostname or IP address of a single Db2 subsystem or Db2 data sharing group that is configured as the log reader.
    2. In the Secure DDF port field, type the configured DDF port number. See Defining a secure network port for connections to Db2 Data Gate for more information,
    3. In the Username field, type the ID of a log reader user, as configured in Creating Db2 Data Gate users and granting privileges on z/OS.
    4. In the Password field, type the password of that user.
  2. Under TLS certificate, you see an area in which you can drag and drop a certificate file from your local machine. You can also click the link in that area. It allows you select a file for upload. The certificate you need is the one you created and exported during your setup for outbound network traffic. See Generating and exporting a key pair and a certificate for Db2 Data Gate.
    Note: If you ran the AQTSSLDG sample job for the network setup, mind that AQTSSLDG creates two certificates. The first is for encrypted network traffic between Db2 for z/OS and Db2 Data Gate. The second is for outbound network traffic. The one that is required here is the second.
  3. In the Keystore password field, type the password for the certificate.
  4. In the Data gate pairing name field, type a unique name or ID. This name will be used to identify the association of your Db2 Data Gate instance with the Db2 for z/OS source.
  5. Click Continue.
    An information window opens that displays the configuration progress. When this has finished, you see that the Select tables tab is selected and in front.

You now have access to the tables in connected Db2 subsystems or data sharing groups. The next step is to select some or all of these tables for Db2 Data Gate. Replicas of the selected tables will then be placed on the Db2 Data Gate server and made available to connecting applications.

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.

  1. If the list of schemas or tables is long, you can use the search field under the header Search and select tables for synchronization. The field allows you to search for particular schema or table names. The drop-down list to the left of the search field allows you to choose whether your want to search for schemas or tables. By default, Schema is selected. Change this setting if needed. Then type your search string in the field to search for schema or table names starting with or containing the search string. The search always lists the schema names on the left. If you chose to search for tables, you will see the schemas containing the tables you searched for. The names of schemas or tables that have already been selected show a black check mark.
  2. 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. If you select a schema, you automatically select all the tables in that schema. To select all schemas, you can select the check box to the left of the column header (Schema).
    The tables of the selected schemas are listed on the right part of the page.
  3. Now that you have selected one or more schemas, you can reduce the number of tables that you make available to your Db2 Data Gate instance by clearing the check boxes in front of the table names.
    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.
  4. Click Continue.
    The Finish tab is selected and in front. You see a summary of your table selection:
    Host
    The host name of your Db2 for z/OS data source.
    Port
    The secure DDF port for connecting to the Db2 for z/OS data source.
    Location
    The location name of the Db2 for z/OS data source.
    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.
  5. 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).
  6. Click Finish to complete the configuration process for your Db2 Data Gate instance.
    Important: The configuration process might take several minutes to complete. Do not close the Configure page during that time.

Results

Your see the dashboard of your new instance. Note also that the provisioning process makes changes to the selected target database (Db2 or Db2 Warehouse):
  1. Db2 registry variables are updated:
    Db2 Db2 Warehouse
    • DB2_WORKLOAD=ANALYTICS_ACCELERATOR
    • DB2_SELECTIVITY=ALL
    • DB2_APPENDERS_PER_PAGE=1
    • DB2LOCK_TO_RB=STATEMENT
    • DB2_WORKLOAD=ANALYTICS_ACCELERATOR
    • DB2CODEPAGE=1208
    • DB2_RUNTIME_DEBUG_FLAGS=SECTION_LEVEL_LOB
    • DB2_SECTION_SCRATCH_BUFFER_SIZE=512K
    • DB2LOCK_TO_RB=STATEMENT
    • DB2_SELECTIVITY=ALL,AJSEL
    • DB2COMPOPT=CDE_NEQN_ENABLE
    • DB2_ATM_CMD_LINE_ARGS="-include-manual-tables -low-priority-update-systables"
    • DB2_OBJECT_TABLE_ENTRIES=65532
    • DB2_EXT_TABLE_SETTINGS=COMM_BUFFER_SIZE:1113840
    • DB2_CDE_DICTIONARY_CACHE_CLEANUP_SCAN_LIST_INTERVAL=30
    • DB2_CDE_DICTIONARY_CACHE_CLEANUP_INTERVAL=60
    • DB2_APPENDERS_PER_PAGE=1
    • DB2_EXTENDED_OPTIMIZATION="COL_RTABLE_UD_THR 0,XGBPART ON FULL NONHDIR,ENABLE_OLAP2AGG ON,NI2NE_WITH_NULLS OUTER"
    • DB2_REDUCED_OPTIMIZATION=
    • DB2_CDE_DATA_SETTINGS=VECTORIZED_INSERT:YES
    • DB2_WLM_SETTINGS=
    • DB2_CDE_AUTO_REORG_RECOMPRESS=
    • DB2_CDE_COMPRESSION_SETTINGS="ENABLE_VECT_ADC:YES"
    • DB2_CDE_DCC=no
    • DB2_STATISTICS="USCC:0;DISCOVER:ON;CGS_SAMPLE_RATE_ADJUST:0"
    • DB2_TCG_DEFAULT_OPTIONS="set percentile_cont_spill on"
    • DB2_CORRELATED_PREDICATES="CGS_CARD_BOUND ON"
    • DB2_SQB_EXTENTMOVEMENT_BUFFER_SIZE=16384
  2. For Db2 Warehouse, the following Database Manager configuration parameters are set or changed:
    • DIAGSIZE 500
    • START_STOP_TIME 5
    • MAX_QUERYDEGREE 6
  3. For the target databases, the configuration changes as follows:
    Db2 Db2 Warehouse
    • PAGE_AGE_TRGT_MCR 1
    • LOGARCHMETH1=OFF

      Setting LOGARCHMETH1=OFF disables archive logging for a Db2 instance, so that the database runs in circular logging mode. This can improve the synchronization performance, but in some cases you might have to re-enable archive logging. Consider:

      • A Db2 database also contains tables that are not managed by Db2 Data Gate. You can only recover these by using the backup and archive logs.

        In contrast, Db2 Data Gate tables can be recovered from Db2 for z/OS by simply reloading these.

      • Other replication tools might use the Db2 database as a source, and replicate changes to a new target as in a replication chain. Such tools require that archive logging is enabled.
    • MAXLOCKS 10
    • PCKCACHESZ 65536
    • LOGARCHMETH1 OFF
    • LOGARCHMETH2 OFF
    • SECTION_ACTUALS NONE
    • AUTO_MAINT ON
    • AUTO_TBL_MAINT ON
    • AUTO_REORG ON
    • AUTO_STMT_STATS ON
    • AUTO_RUNSTATS ON
    • EXTBL_LOCATION /
    • AUTO_SAMPLING ON
    • MON_LOCKTIMEOUT HIST_AND_VALUES
    • MON_LOCKWAIT NONE
    • AUTO_CG_STATS ON
    • AUTO_DEL_REC_OBJ ON
    • NUM_DB_BACKUPS 16
    • REC_HIS_RETENTN 91
    • TRACKMOD YES
    • MON_LCK_MSG_LVL 3
    • WLM_AGENT_LOAD_TRGT 24
  4. The database instance is restarted so that the changes can take effect.
  5. A memory table function is installed by the following SQL statement:
    CALL SYSINSTALLOBJECTS('ANACC','C','','')
  6. The following tag file is added: 
    /mnt/blumeta0/home/db2inst1/dg_config_done.XXX

    where XXX is the instance ID of the target database.