Loading a bpd file into a Db2 table

This section briefly describes how to store trace data from bpd files into DB2® tables.

Db2 tables can be used by administrators to extract performance-related data with self-written SQL queries. The further utilization of this data is outside the scope of this information. See the Report Reference for more information about possible uses of performance data.

Before you can store trace data into a table, you must create a table with an appropriate layout that can accept the data from a bpd file. This is done with the SQL CREATE TABLE statement. When you determine the table layout, you need to consider:

Whether summary or detailed data was collected. Each data type requires a different table layout.
Whether the bpd file was created with the Summary or Detail option of the BPACTIVITY FILE command. If detailed data was collected, but the Summary option was used, the bpd file contains only summary data. Consequently, the table layout must be appropriate for summary data.

When you have created the appropriate table, you can use the Db2 LOAD utility to load data from a bpd file into the table. LOAD requires the specification of the data elements that are to be stored into the table. For more information, see the IBM® Db2 11 for z/OS®: Utility Guide and Reference.

Buffer Pool Analyzer provides several samples of CREATE and LOAD statements that store data into Db2 tables in the following formats:

Summary data, from IFCID 002
Summary data, from IFCID 230
Summary data, from IFCID 251
Summary data, from IFCID 254
Detail data, from IFCIDs 6, 7, 8, 9, 10, and 198
Detail data, but aggregated by object
Detail data, but aggregated by buffer pool
Detail data, but aggregated by system

The sample statements are in members of the partitioned data set prefix.TKO2SAMP. The data set also contains members that contain descriptions of the individual Db2 table columns used with CREATE and LOAD. The italic characters and numbers in the following table show the naming associations for easier identification.

Table 1. Member names holding the sample statements and associated column descriptions
Samples for	CREATE statements are in member	For CREATE: column descriptions are in member	LOAD statements are in member	For LOAD: column descriptions are in member
Summary data, from IFCID 002	BPOQFC`2`F	BPOQFB`2`F	BPOQFL`2`F	BPOQFD`2`F
Summary data, from IFCID 230	BPOQFC`0`F	BPOQFB`0`F	BPOQFL`0`F	BPOQFD`0`F
Summary data, from IFCID 251	BPOQFC`1`F	BPOQFB`1`F	BPOQFL`1`F	BPOQFD`1`F
Summary data, from IFCID 254	BPOQFC`4`F	BPOQFB`4`F	BPOQFL`4`F	BPOQFD`4`F
Detail data	BPOQFC`D`F	BPOQFB`D`F	BPOQFL`D`F	BPOQFD`D`F
Detail data, aggregated by object	BPOQFC`O`F	BPOQFB`O`F	BPOQFL`O`F	BPOQFD`O`F
Detail data, aggregated by buffer pool	BPOQFC`B`F	BPOQFB`B`F	BPOQFL`B`F	BPOQFD`B`F
Detail data, aggregated by system	BPOQFC`S`F	BPOQFB`S`F	BPOQFL`S`F	BPOQFD`S`F
Names of index space objects	BPOQFC`N`F	BPOQFB`N`F	BPOQFL`N`F	BPOQFD`N`F

Note that the sample CREATE and LOAD statements work independently of whether the bpd file actually contains relevant data. For example, if you create a Db2 table for storing group buffer pool related data, but the bpd file does not contain such data because the data was collected from a Db2 system that is not a data sharing group member, the table remains empty after the LOAD statement is executed. Generally, if you encounter difficulties with missing data, verify the parameters that were used for the data collect and the bpd file creation tasks. See especially the job summary logs (JOBSUMDD) and the DPMLOG execution logs. The latter might contain information about record types that were not available for processing.

Example: This example (from member BPOQFCDF) shows a partial CREATE TABLE statement that creates a table for detail data:

--**Start of Specifications********************************************
--*                                                                   *
--* MODULE-NAME      = BPOQFCDF                                       *
--* DESCRIPTIVE-NAME = SQL for creating table for detail activity     *
--*                    data from IFCIDs 6, 7, 8, 9, 10, 198           *
--* COPYRIGHT = IBM Db2 Buffer Pool Analyzer for z/OS  V5R4M0         *
--*             Licensed Material - Property of IBM                   *
--*             5655-W35 (C) Copyright IBM Corp. 2001, 2016           *
--* STATUS    = Version 5.4.0                                         *
--*                                                                   *
--* FUNCTION = Sample SQL for creating table for detail activity      *
--*                   data from IFCIDs 6, 7, 8, 9, 10, 198            *
--**End of Specifications**********************************************
 CREATE TABLE DB2PE_BPA_DETAIL
  (DB2PM_RELEASE        SMALLINT          NOT NULL WITH DEFAULT,
   DB2_RELEASE          CHAR(2)           NOT NULL WITH DEFAULT,
   LOCAL_LOCATION       CHAR(16)          NOT NULL WITH DEFAULT,
   GROUP_NAME           CHAR(8)           NOT NULL WITH DEFAULT,
   SUBSYSTEM_ID         CHAR(4)           NOT NULL WITH DEFAULT,
   MEMBER_NAME          CHAR(8)           NOT NULL WITH DEFAULT,
   INTERVAL_TSTAMP      CHAR(26),
   INTERVAL_ELAPSED     DECIMAL(15,6),
   BEGIN_REC_TSTAMP     CHAR(26),
   END_REC_TSTAMP       CHAR(26),
   REQ_LOCATION         CHAR(16)          NOT NULL WITH DEFAULT,
   PRIMAUTH             CHAR(8)           NOT NULL WITH DEFAULT,
   .
   .
   .
   GETPAGE_MISS_A       INTEGER           NOT NULL WITH DEFAULT,
   GETPAGE_NOREAD       INTEGER           NOT NULL WITH DEFAULT,
   READ_REQUEST         INTEGER           NOT NULL WITH DEFAULT,
   READ_REQ_SYNC        INTEGER           NOT NULL WITH DEFAULT,
   READ_REQ_SEQ         INTEGER           NOT NULL WITH DEFAULT,
   READ_REQ_LIST        INTEGER           NOT NULL WITH DEFAULT,
   READ_REQ_DYN         INTEGER           NOT NULL WITH DEFAULT,
   READ_DEL_SYNC        DECIMAL(7,1),
   READ_DEL_SEQ         DECIMAL(7,1),
   READ_DEL_LIST        DECIMAL(7,1),
   READ_DEL_DYN         DECIMAL(7,1),
   READ_PAGE            INTEGER           NOT NULL WITH DEFAULT,
   READ_PAGE_SYNC       INTEGER           NOT NULL WITH DEFAULT,
   PRIMAUTH             CHAR(8)           NOT NULL WITH DEFAULT,
   .
   .
   .
   WRITE_PAGE           INTEGER           NOT NULL WITH DEFAULT,
   WRITE_PAGE_SYNC      INTEGER           NOT NULL WITH DEFAULT,
   WRITE_PAGE_ASYNC     INTEGER           NOT NULL WITH DEFAULT)
   PARTITION_NUMBER     INTEGER           NOT NULL WITH DEFAULT)    IN GRPBP

The following LOAD statement (from member BPOQFLDF) loads data into the previously created table:

 LOAD DATA INDDN(SYSREC)
   REPLACE LOG NO
   INTO TABLE DB2PE_BPA_DETAIL
   WHEN (13:13) = 'D'
  (DB2PM_RELEASE         POSITION(7) SMALLINT,
   DB2_RELEASE           POSITION(14) CHAR(2),
   LOCAL_LOCATION        POSITION(17) CHAR(16),
   GROUP_NAME            POSITION(33) CHAR(8),
   SUBSYSTEM_ID          POSITION(41) CHAR(4),
   MEMBER_NAME           POSITION(45) CHAR(8),
   PRIMAUTH             CHAR(8)           NOT NULL WITH DEFAULT,
   .
   .
   .
   WSNAME                POSITION(291) CHAR(18),
   BUFFERPOOL_ID         POSITION(309) CHAR(8),
   PAGESET_QUAL          POSITION(317) CHAR(27),
   PAGESET_TYPE          POSITION(344) CHAR(1),
   SYSTEM_HIT_RATIO      POSITION(345) DECIMAL
                            NULLIF SYSTEM_HIT_RATIO=X'FFFFFFFF',
   APPL_HIT_RATIO        POSITION(349) DECIMAL
                            NULLIF APPL_HIT_RATIO=X'FFFFFFFF',
   GETPAGE_TOT           POSITION(353) INTEGER,
   GETPAGE_SEQUENT       POSITION(357) INTEGER,
   PRIMAUTH             CHAR(8)           NOT NULL WITH DEFAULT,
   .
   .
   .
   READ_PAGE_SEQ         POSITION(429) INTEGER,
   READ_PAGE_LIST        POSITION(433) ICNTEGER,
   READ_PAGE_DYN         POSITION(437) INTEGER,
   UPD_WRT_PAGE          POSITION(441) DECIMAL
                            NULLIF UPD_WRT_PAGE=X'FFFFFFFF',
   PAGE_WRITE_REQ        POSITION(445) DECIMAL
                            NULLIF PAGE_WRITE_REQ=X'FFFFFFFF',
   BUFFER_UPDATE         POSITION(449) INTEGER,
   WRITE_REQ             POSITION(453) INTEGER,
   WRITE_REQ_SYNC        POSITION(457) INTEGER,
   WRITE_REQ_ASYNC       POSITION(461) INTEGER,
   WRITE_REQ_DEL_SYNC    POSITION(465) DECIMAL
                            NULLIF WRITE_REQ_DEL_SYNC=X'FFFFFFFF',
   WRITE_REQ_DEL_ASYN    POSITION(469) DECIMAL
                            NULLIF WRITE_REQ_DEL_ASYN=X'FFFFFFFF',
   WRITE_PAGE            POSITION(473) INTEGER,
   WRITE_PAGE_SYNC       POSITION(477) INTEGER,
   PARTITION_NUMBER      POSITION(485) INTEGER)