Loading tables

Successful queries against accelerator-shadow tables are possible only if the tables contain data. Therefore, you must load the tables after their definition (empty structure) has been copied to the accelerator.

Before you begin

A database connection profile must exist for the database that is associated with the accelerator.
You (your user ID) must be connected to the database. Your user ID requires privileges to:
- Control the accelerator
- Read the data to be loaded into the accelerator
The accelerator-shadow tables that you want to load or update must exist on the accelerator.

About this task

Loading accelerator-shadow tables can take a long time (for example minutes or hours) depending on the amount of data in the original Db2 tables.
An external product can be used to speed up and facilitate the load process: the Db2 Analytics Accelerator Loader, which can also load external, non-Db2 for z/OS® data into accelerator-shadow tables and comes with extra load options.
The disk throughput can be volatile, depending on the disk configuration, the placement of data sets, the number of parallel I/O operations, and so on. To a large extent, the throughput depends on the following factors:
- Partitioning of tables in your Db2 subsystem. In Db2 for z/OS, this determines the degree of parallelism that can be used internally by the Db2 Unload Utilities.
- Number of DATE, TIME, and TIMESTAMP columns in your original table. The conversion of the values in such columns is CPU-intensive.
- Compression of data in Db2 for z/OS.
- Number of available processors.
- Workload Manager (WLM) configuration.
- Workload on the IBM Z server.
- Workload on the accelerators.
During a load, IBM Db2 Analytics Accelerator automatically selects a suitable distribution key.

Restrictions:

IBM Db2 Analytics Accelerator for z/OS does not support EDITPROC encryption. SQL encryption and decryption functions, such as DECRYPT_CHAR(), require binary table columns, which cannot be used on an accelerator. Furthermore, data that is stored in Db2 with the EDITPROC encryption tool will not be encrypted on the hard disk of the accelerator. However, the data on an accelerator is encoded with a special algorithm for efficient compression and therefore cannot be easily extracted from the media.
During a load process, you cannot close IBM Db2 Analytics Accelerator Studio or the database connection before the loading has finished.
Incremental updates are not restarted automatically if you enable these for just one table and complete the enablement steps in this order:
1. Define the accelerator-shadow table.
2. Enable incremental updates for this table.
3. Load the accelerator-shadow table.
4. Start incremental updates (Replication > Start).
5. Reload the accelerator-shadow table.
Normally, when this is done for more than one table, incremental updates continue or are restarted for all tables that are not being loaded or where loading has been finished, which finally results in an automatic restart for all tables. In this special case (just one table), however, you must restart incremental updates manually after the reload.
If you change the partitioning type of the table space of an accelerator-shadow table in Db2 for z/OS (for example, from segmented to partitioned by growth), you can no longer load the accelerator-shadow table. The operation will fail. Therefore, you must remove the table from the accelerator and redefine it after changing the partitioning type.
After a load, a replication-enabled accelerator-shadow table goes into Suspended state if someone has stopped replication during the load.
Suspended means that the table is skipped when incremental updates are applied. To make this table part of the incremental update process again, you must reload it.

You can also integrate the loading and updating of accelerator-shadow tables into your scheduled ETL processes by invoking the SYSPROC.ACCEL_LOAD_TABLES stored procedure directly from a job that is run by your preferred scheduler (for example, Tivoli® workload scheduler).

Table loading is carried out by the SYSPROC.ACCEL_LOAD_TABLES stored procedure on your data server. In addition, the SYSPROC.ACCEL_SET_TABLES_ACCELERATION stored procedure is invoked to enable acceleration for the loaded table.

For information about the privileges that are required to run these procedures and further details, follow the appropriate Related reference link at the end of this topic.

Procedure

Connect to the Db2 subsystem or data sharing group that the accelerator is attached to. For more information, see Connecting to a database server.
Select the accelerator. For more information, see Selecting an accelerator.
In IBM Unified Experience for z/OS, click the Tables tab. In IBM Db2 Analytics Accelerator Studio or IBM Data Server Manager, scroll down. You see a list of the tables on the accelerator. Select the accelerator-shadow tables that you want to load.
To see the tables, you might have to expand the schema nodes first, by clicking the plus sign or twistie in front of a schema name. Selecting an entire schema will select all accelerator-shadow tables belonging to that schema for loading.

Prepare the load:

IBM Db2 Analytics Accelerator Studio	Click the Load button on the toolbar.
IBM Data Server Manager	Click the Load button on the toolbar.
IBM Unified Experience for z/OS	Select Take action > Load on the toolbar above the list of tables.
IBM Db2 Analytics Accelerator Administration Services	Start a /table/partition/load API request. In the request body: Specify the connection name (`"connectionName":`). Specify the accelerator name (`"accelName":`).

In the graphical user interfaces (IBM Db2 Analytics Accelerator Studio, IBM Data Server Manager, and IBM Unified Experience for z/OS), a secondary window opens. In this window, you can select further options.

Note: Comparable parameters for these options are not available in IBM Db2 Analytics Accelerator Administration Services.

In the graphical user interfaces, the list in this Load Tables window contains a column labeled Load Recommended. The information in this column is supposed to help you with the selection. A comparable parameter is not available in IBM Db2 Analytics Accelerator Administration Services. Because table loads are long-running processes, only load or reload an accelerator-shadow table or partition when necessary.

Important:

You can only select table partitions if the tables are partitioned by range or partitioned by growth, and if they were already loaded in the past. In all other cases, you can only load entire tables.
The information in the Load Recommended column might be inaccurate if DB2® realtime statistics are not up-to-date (RUNSTATS) or if incremental updates have been applied to the accelerator-shadow table since the last load.

The Load Recommended column contains one of the following statuses for each table or partition:

No - data unchanged: Table or partition data has not changed in Db2 for z/OS. Therefore, you need not load or reload the accelerator-shadow table or partition.
Yes - changed data: A load or reload of the accelerator-shadow table or partition is recommended because data changes have been applied to the original Db2 for z/OS tables.
Unknown - no statistics: A load or reload of the accelerator-shadow table or partition is recommended because the current status of the table or partition cannot be determined. It must therefore be assumed that the table is out of sync. The reason might be that access to Db2 statistics is not available or that the last successful invocation of RUNSTATS was some time ago.
Unknown - replication enabled: Data changes have been detected since the last load of the accelerator-shadow table, but the table is currently enabled for incremental updates. The data changes are propagated asynchronously by the incremental update function, but it can not be determined whether the table is already in sync. A reload is usually not required under this condition.
Yes - enforced: A partition with a value of Yes - enforced in the Load Recommended column will always be loaded if the entire accelerator-shadow table is selected for loading. A possible reason is that the partition is in InitialLoadPending state because it has never been loaded before.

If the list of tables and partitions is long, you can apply a filter to hide certain items from the display. To do so, select a different value from the Show recommended drop-down list (in IBM Data Server Manager, this drop-down list has no label). The following choices are available:

All (default): Displays all accelerator-shadow tables and partitions.
All recommended: Displays only the accelerator-shadow tables and partitions for which a load or reload is recommended.
No - data unchanged: Displays only the accelerator-shadow tables and partitions that need not be loaded or reloaded.
Yes - changed data: Displays only the accelerator-shadow tables and partitions for which a load or reload is recommended because data in the original tables has changed.
Unknown - no statistics: Displays only the accelerator-shadow tables and partitions for which a load or reload is recommended because the status cannot be determined.
Yes - enforced: Displays only the accelerator-shadow tables and partitions for which a load or reload will be enforced.

Select the appropriate tables or partitions.

IBM Db2 Analytics Accelerator Studio	Select the appropriate check boxes in front of the table or partition names, or use the buttons on the right: Select All Selects all tables and partitions that are shown in the Load Tables window. Deselect All Deselects all tables and partitions. Select Recommended Selects all accelerator-shadow tables and partitions for which a load or reload is recommended in the Load Recommended column. Expand All Expands all table nodes so that the partitions come into view. Collapse All Collapses all table nodes so that the table partitions are hidden from view. Next Unknown Jumps to and selects the next accelerator-shadow table or partition with a status of Unknown - no statistics in the Load Recommended column. This might prove useful if the list is crowded.
IBM Data Server Manager	Select the appropriate check boxes in front of the table or partition names. To quickly select just the tables and partitions for which a load is recommended, click the Select Recommended button.
IBM Unified Experience for z/OS	Select the appropriate check boxes in front of the schema or partition names. To quickly select just the tables and partitions for which a load is recommended, select Select the ecommended items from the table below.
IBM Db2 Analytics Accelerator Administration Services	Specify the tables in an array that serves as the value of the `"selTables"` parameter. This array, in which you specify the schemas, tables, and table partitions, must be structured as follows: `"selTables": [ { "schemaName": "SYSABC", "tables": [ { "name": "TABLE01", "partitions": [ { "name": "-" } ] }, { "name": "TABLE02", "partitions": [ { "name": "-" } ] } ] }, { "schemaName": "SYSDEF", "tables": [ { "name": "TABLE03", "partitions": [ { "name": "-" } ] } ] } ]`

Specify the locking behavior:
In the graphical user interfaces, select Lock Db2 tables while loading if you want to protect (lock) the original tables or table partitions in the database against changes during the load operation:
All Tables

Protects the Db2 source tables of to-be-loaded accelerator-shadow tables against changes during the load operation.

Data availability

Newly loaded data becomes available for queries when the load job has been finished for the entire table set.

Error behavior

If an error occurs while loading one of the tables in the set, the load job for that table is aborted, and the job proceeds with the next table in the set. Message AQT20121E is issued in case of a load error.

Current Table
Protects just the Db2 source table of the accelerator-shadow tables that is currently being loaded.
Data availability

Newly loaded data in a table becomes available for queries when the load job for that table has been finished and when the entire operations has moved on to the next table in the set.

Error behavior

If an error occurs while loading one of the tables in the set, the load job for that table is aborted, and the job proceeds with the next table in the set. Messages:

Message AQT20121E is returned if the load of all tables in the set failed.

Message AQT10420W is issued for each single table after a failure to load that table
Current Partitions
Protects the original Db2 table-space partition of the accelerator-shadow table partition to be loaded against changes during the load. With this setting, a non-partitioned table is always locked completely.
Note: Locks at partition level are not supported for Db2 for Linux®, UNIX, and Windows.
Data availability

Newly loaded data in a table partition becomes available for queries when the load job for that partition has been finished and when the entire operations has moved on to the next partition or table.

Error behavior

If an error occurs while loading a table partition, the load job for that partition is aborted, and the job proceeds with the next partition to be loaded. An error has no effect on other partitions or tables to be loaded; the job continues. Messages:

Message AQT20121E is returned if the load of all tables or all partitions of a partitioned table in the set failed.

Message AQT10421W is issued for each partition after a failure to load that partition.

Special cases

To avoid partially loaded tables, lock mode TABLE is applied during an initial load, even if you specified lock mode PARTITIONS. For details, see the description of lock mode TABLE.
If the reload of a partition that is enabled for incremental updates fails, incremental updates are disabled for the entire table even if the reload of the other table partitions succeeded.
NONE

No locking at all. However, only committed data is loaded into the accelerator-shadow tables because the Db2 data is unloaded with isolation level CS and SKIP LOCKED DATA.

Data availability

Data availability is the same as for lock mode PARTITIONS.

Error behavior

The error behavior is the same as for lock mode PARTITIONS.
Note: In IBM Db2 Analytics Accelerator Administration Services, you can apply a lock option if you set the "lockEnabled": parameter to true, and then specify the locking behavior as the value of the "lockType": parameter. Example:
```
{
  "connectionName": "My_DB2",
  "accelName": "ACCEL01",
  "lockEnabled": true,
  "accelerationEnabled": false,
  "lockType": "All Tables",
  "selTables": [
   .
   .
   . ]
}
```
In the graphical user interfaces, the option After load enable acceleration for disabled tables ensures that you can run accelerated queries against all accelerator-shadow tables immediately after the completion of the load operation. It is selected by default. In IBM Db2 Analytics Accelerator Administration Services, this option is enabled by setting the value of "accelerationEnabled": to true. It is recommended that you always use this option. Turn it off only if there is a good reason to do so.
The following scenario is thinkable: the table is big and one or more columns could not be loaded because their data type is not supported. These columns, however, would have held the crucial data. In this case, it makes perfect sense to switch the option off to prevent a waste of processing resources.

Start the load process:

IBM Db2 Analytics Accelerator Studio	Click OK.
IBM Data Server Manager	Click OK.
IBM Unified Experience for z/OS	Click Load.
IBM Db2 Analytics Accelerator Administration Services	Click Execute.

Note: If the load fails, first check whether DSNUTIL was started in Db2 for z/OS.