Db2 for z/OS data access methods

Db2 for z/OS data can be accessed by the Data Service server using different data access methods.

The following Db2 for z/OS data access methods are available:
  • Traditional Db2 access. This method accesses Db2 data through traditional Db2 APIs. This access method allows for reading and writing of the data and provides transactional integrity.
  • Db2 Direct. This method accesses Db2 data by reading the underlying Db2 VSAM linear data sets directly. This access method allows read-only access to the data and provides high performance, bulk data access.
The Db2 data access method is specified when creating virtual tables in the Data Service Studio for access to Db2 data.

The following topics provide more information about the Db2 for z/OS data access methods.

Using traditional Db2 access

Traditional Db2 access methods access Db2 data through APIs such as Distributed Relational Database Architecture (DRDA), Call Attachment Facility (CAF), and Resource Recovery Services attachment facility (RRSAF). Using traditional Db2 access allows for reading and writing of the data and provides transactional integrity.

Traditional DB2 access methods provide MapReduce and Virtual Parallel Data support. MapReduce is an algorithm that enables the Data Service server to streamline how it accesses Db2 data, thereby reducing the processing time required to virtualize Db2 data. Statistics about the Db2 database are collected and stored within a metadata repository from which the SQL engine optimizes the MapReduce process.

In order to exploit MapReduce for Db2 when using traditional Db2 access, the Data Service server must collect information about the Db2 database. This information is collected using the DRDARange command and is stored within the Data Service server metadata repository.

Traditional Db2 access is used automatically when Db2 Direct access is not available.

Using Db2 Direct

Db2 Direct is a Data Service server access method that reads the data in the Db2 VSAM linear data sets directly instead of accessing the data through traditional Db2 APIs. Using Db2 Direct, large data pulls can be performed in service request block (SRB) mode, and MapReduce and Virtual Parallel Data features can by exploited without any prerequisite processing, such as the collection of statistics using the DRDARange command. Db2 Direct access provides a significant increase in performance and reduced elapsed time in processing analytical type queries.

Db2 Direct allows read-only access to the data. When using Db2 Direct, there is no locking involved when accessing the data, so updates may not be captured and deleted records may have been captured. Results from Db2 Direct queries may be out of sync with the current state of a Db2 table due to recent table updates not being flushed to the linear data sets.

Security is managed using Db2 table authorization.

Restrictions and considerations:
Consider the following points when using Db2 Direct:
  • The Db2 subsystem hosting a Db2 table must be active when Db2 Direct-enabled tables are loaded or refreshed in the data server. The map build process requires Db2 system access to identify data set information in the Db2 system catalog.
  • The Data Service server requires read access to the Db2 VSAM linear data sets. The linear data sets containing the Db2 rows must be available to the data server processing SQL requests for Db2 data. If the data sets are unavailable or archived, Db2 Direct will be disabled during map load or refresh for the virtual table.
  • Virtual tables enabled for Db2 Direct must include all the columns defined in the base Db2 table. This is necessary because the columns describe the internal format of the Db2 data.
  • If Db2 is not available or some other error occurs during map build or map refresh processing, Db2 Direct is automatically disabled for the table and a message is written to the trace log:
    DB2 direct processing disabled for map map-name
  • If Db2 Direct processing is disabled, processing will continue with traditional Db2 APIs when possible.
  • To determine if Db2 Direct is active, the following messages appear in the server trace:
    • At startup and map refresh, the following message is issued:
      DB2 direct processing enabled for map map-name
    • When DB2 Direct is used in a query, the following message is issued:
      Processing table map-name using DB2 direct
  • If Db2 Direct table security is enabled, the Db2 subsystem must be available to check security at SQL query time.
  • If Db2 Direct table security is disabled, unauthorized users who would normally receive a -551 SQLCODE attempting to access data through traditional APIs may gain access to Db2 data.
  • Db2 Direct does not support tables with edit procedures or SQL statements containing joins, LOB columns, or key columns.
  • If Db2 Direct security is disabled, the CCSIDs of table columns will be assumed based on the ENCODING_SCHEME (EBCDIC, Unicode, ASCII) of the table.