Workfile Database

This topic shows detailed information about Statistics - Workfile Database.

This block shows information about the Workfile Database used by Db2 as storage for work files for processing SQL statements, and as storage for created and declared global temporary tables.

The performance metrics in the report block distinguish between work files for declared global temporary tables (DGTTs) and work files for non-DGTT data such as created global temporary tables or sort results. In addition, Db2 supports in-memory work files which are sufficient for performing simple operations and do not require physical allocations. In-memory work files may overflow to physical records in the Workfile Database in case of memory constraints.

Statistics - Workfile Database

The field labels shown in the following sample layout of Statistics - Workfile Database are described in the following section.


WORKFILE DATABASE            QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
TOTAL STORAGE CONFIG   (KB)    256.00      N/A      N/A      N/A  
  TOT DGTT STOR CONFIG (KB)    128.00      N/A      N/A      N/A  
  TOT WF STOR CONFIG   (KB)    128.00      N/A      N/A      N/A  
TOTAL STORAGE THRESHOLD (%)     90.00      N/A      N/A      N/A  

MAX TOTAL STORAGE USED (KB)    128.00      N/A      N/A      N/A                                       
  MAX DGTT STOR USED   (KB)     64.00      N/A      N/A      N/A  
  MAX WF STORAGE USED  (KB)     64.00      N/A      N/A      N/A  

CUR TOTAL STORAGE USED (KB)      2.06      N/A      N/A      N/A
  CUR DGTT STOR USED   (KB)      1.00      N/A      N/A      N/A  
  CUR WF STORAGE USED  (KB)      1.06      N/A      N/A      N/A  
  STORAGE IN 4K TS     (KB)      2.06      N/A      N/A      N/A
  STORAGE IN 32K TS    (KB)      0.00      N/A      N/A      N/A                                                  
4K USED INSTEAD OF 32K TS        0.00     0.00      N/C     0.00
32K USED INSTEAD OF 4K TS        0.00     0.00      N/C     0.00

MAX ACTIVE (DM) IN-MEMORY        0.00      N/A      N/A      N/A  
  MAX ACT (NONSORT) IN-MEM       0.00      N/A      N/A      N/A  
CUR ACTIVE (DM) IN-MEMORY        0.00      N/A      N/A      N/A  
  CUR ACT (NONSORT) IN-MEM       0.00      N/A      N/A      N/A  
MAX STOR (DM) IN-MEM   (KB)      0.00      N/A      N/A      N/A  
CUR STOR (DM) IN-MEM   (KB)      0.00      N/A      N/A      N/A  
MAX ACTIVE (SORT) IN-MEMORY      0.00      N/A      N/A      N/A  
CUR ACTIVE (SORT) IN-MEMORY      0.00      N/A      N/A      N/A  
MAX STOR (SORT) IN-MEM (KB)      0.00      N/A      N/A      N/A  
CUR STOR (SORT) IN-MEM (KB)      0.00      N/A      N/A      N/A  
IN-MEM (NONSORT) OVERFLOWED      0.00     0.00      N/C     0.00  
IN-MEM WORKF NOT CREATED         0.00     0.00      N/C     0.00  
                                                               
AGENT STORAGE CONFIG   (KB)      0.00      N/A      N/A      N/A
  NUMBER OF LIMIT EXCEEDED       0.00     0.00      N/C     0.00
AGENT STORAGE THRESHOLD (%)     90.00      N/A      N/A      N/A  
MAX AGENT STORAGE USED (KB)      0.00      N/A      N/A      N/A  
                                                                
DM FAST INSERT PIPES             0.00      N/A      N/A      N/A
DM FAST INSERT PIPES DISAB       0.00      N/A      N/A      N/A

TOTAL STORAGE CONFIG (KB)

The total storage (KB) configured for all table spaces in the Workfile Database.

Field Name: QISTWSTG

TOT DGTT STOR CONFIG (KB)

The total preferred storage (KB) configured for DGTTs in the Workfile Database.

Field Name: QISTDGTTSTG

TOT WF STOR CONFIG (KB)

The total preferred storage (KB) configured for non-DGTT work files in the Workfile Database.

Field Name: QISTWFSTG

TOTAL STORAGE THRESHOLD (%)

The alert threshold of high space-usage for DGTTs or non-DGTT work files in the Workfile Database (derived from zparm WFSTGUSE_SYSTEM_THRESHOLD).

Field Name: QISTSSTH

MAX TOTAL STORAGE USED (KB)

The maximum total amount of storage (KB) ever used in the Workfile Database at system level since Db2 startup.

Field Name: QISTWMXU

MAX DGTT STOR USED (KB)

The maximum total amount of storage (KB) ever used for DGTTs in the Workfile Database by all agents on the system since Db2 startup.

Field Name: QISTDGTTMXU

MAX WF STORAGE USED (KB)

The maximum total amount of storage (KB) ever used for non-DGTT work files in the Workfile Database by all agents on the system since Db2 startup.

Field Name: QISTWFMXU

CUR TOTAL STORAGE USED (KB)

The total amount of storage (KB) currently used in the Workfile Database at system level.

Field Name: QISTWCTO

CUR DGTT STOR USED (KB)

The total amount of storage (KB) currently used for DGTTs in the Workfile Database by all agents on the system.

Field Name: QISTDGTTCTO

CUR WF STORAGE USED (KB)

The total amount of storage (KB) currently used for non-DGTT work files in the Workfile Database by all agents on the system.

Field Name: QISTWFCTO

STORAGE IN 4K TS (KB)

The total amount of storage (KB) currently used for 4 KB table spaces in the Workfile Database.

Field Name: QISTW4K

STORAGE IN 32K TS (KB)

The total amount of storage (KB) currently used for 32 KB table spaces in the Workfile Database.

Field Name: QISTW32K

4K USED INSTEAD OF 32K TS

The number of times that space in a 4 KB page table space was used because space in a 32 KB page table space was preferred but not available in the Workfile Database.

Field Name: QISTWFP2

32K USED INSTEAD OF 4K TS

The number of times that space in a 32 KB page table space was used because space in a 4 KB page table space was preferred but not available in the Workfile Database.

Field Name: QISTWFP1

MAX ACTIVE (DM) IN-MEMORY

The maximum number of in-memory work files (created by the Data Manager) that were active at any point in time since Db2 startup. This is a high-water mark count.

Field Name: QISTIMAH

MAX ACT (NONSORT) IN-MEM

The maximum number of non-SORT related in-memory work files created by the Data Manager that were active at any point in time since Db2 startup. This is a high-water mark count.

Field Name: QISTI2AH

CUR ACTIVE (DM) IN-MEMORY

The number of currently active in-memory work files created by the Data Manager.

Field Name: QISTIMAC

CUR ACT (NONSORT) IN-MEM

The number of currently active non-SORT related in-memory work files created by the Data Manager.

Field Name: QISTI2AC

MAX STOR (DM) IN-MEM (KB)

The maximum space used for active in-memory work files created by the Data Manager at any point in time since Db2 startup. This is a high-water mark count.

Field Name: QISTIMSH

CUR STOR (DM) IN-MEM (KB)

The total space used for currently active in-memory work files created by the Data Manager.

Field Name: QISTIMSC

MAX ACTIVE (SORT) IN-MEMORY

The maximum number of in-memory work files created by the SORT component that were active at any point in time since Db2 start. This is a high-water mark count.

Field Name: QISTSIAH

CUR ACTIVE (SORT) IN-MEMORY

The number of currently active in-memory work files created by the SORT component.

Field Name: QISTSIAC

MAX STOR (SORT) IN-MEM (KB)

The maximum space used for active in-memory work files created by the SORT component at any point in time since Db2 startup. This is a high-water mark count.

Field Name: QISTSISH

CUR STOR (SORT) IN-MEM (KB)

The total space used for currently active in-memory work files created by the SORT component.

Field Name: QISTSISC

IN-MEM (NONSORT) OVERFLOWED

The number of times non-SORT related in-memory work files overflowed into a physical table space.

Field Name: QISTI2OF

IN-MEM WORKF NOT CREATED

The number of times an in-memory work file was not created due to critical storage conditions.

Field Name: QISTIMNC

AGENT STORAGE CONFIG (KB)

The maximum amount of storage (KB) in the Workfile Database that can be used by each agent (derived from ZPARM MAXTEMPS).

Field Name: QISTWMXA

NUMBER OF LIMIT EXCEEDED

The number of times the maximum amount of storage that an agent can use in the Workfile database was exceeded.

Field Name: QISTWFNE

AGENT STORAGE THRESHOLD (%)

The alert threshold of high space-usage for DGTTs or non-DGTT work files in the Workfile Database by an agent (derived from ZPARM WFSTGUSE_AGENT_THRESHOLD).

Field Name: QISTASTH

MAX AGENT STORAGE USED (KB)

The maximum amount of storage (KB) ever used in the Workfile Database by any thread since Db2 startup.

Field Name: QISTAMXU

DM FAST INSERT PIPES

The number of Data Manager (DM) fast insert pipes that were allocated since Db2 restart.

Field Name: QISTINPA

DM FAST INSERT PIPES DISAB

The number of DM fast insert pipes that have been disabled since Db2 restart.

Field Name: QISTINPD