SMF 101-239

Introduction

DB2 accounting will be collected from SMF type 101. In this type of record, it doesn’t really matter the SMF subtype of the SMF record because DB2 uses another specification type to distinguish different SMF specializations.

In DB2 there is an identification named IFCID (Instrumentation Facility Component Identifier) each one with different data and purpose. ZSA already extracts SMF 101 IFCID 003, but there is important information not available at IFCID 003 but present in IFCID 239, like package name, collection name, and many other fields.
IFCID 003 and 239 are related and there are several common fields present. For this IFCID 239 of SMF 101, ZSA will produce IFCID 239, a CSV record with qualifier I239 including PROD section and General Package accounting section.

Following the same rules previously established, for each CSV record, a new SQL Table should be created. In this case, we will have 1 new SQL Table

Notes

The SMF fields used as input to populate the fields of CSV have the same name of CSV fields, excluding the prefix of each CSV field. For instance, CSV field DB2239_QPACRECN is populated by SMF field QPACRECN.

CSV records I239, produced by SMF 101 IFCID 239 are related to CSV DBA, produced by SMF 101 IFCID 003. having the same information in CSV Header and in QWHS, QWHC, QWHD, QWHA and QWHU sections, which are defined in both DND2QWH macro (IFCID 239) and DNFDBA macro (IFCID 003). These macros will be described here and are located at ZCOSR.DXPV6400.SOURCE at ZDEVL environment.

When the field type is indicated as HEX, it is normally a counter or a total and should be converted to a decimal value by the windows parser.

Fields

Stored in db2accountings

Note: the package name is a concatenation of the following fields in order (spaced with spaces) :

location_name
package_collection_id
program_name

SMF Field	DB Column name	Description	Unit
QWHSLOCN	local_location_name	Local location name
QWHSNID	luwid_network_id	LUWID Network ID
QWHSLUNM	luwid_luname	LUWID LUNAME
QWHSLUUV	luwid_uniquen_hex	LUWID UNIQUEN
QWHSLUCC	luwid_commit_hex	LUWID COMMIT
QWHSACE	ace_address_hex	ACE address
QWHCAID	authorization_id	Authorization ID
QWHCCV	correlation_id	Correlation ID
QWHCPLAN	plan_name	Plan name
QWHCOPID	orig_operator_id	Original operator ID
QWHCATYP	connect_type_hex	Connection type
QWHCCN	connection_name	Connection name
QWHCEUID	enduser_user_id	Enduser USER-ID
QWHCEUTX	enduser_transaction	Enduser transaction
QWHCEUWN	enduser_workstation	Enduser workstation
QWHCTCXT	trusted_context	Trusted context
QWHCROLE	role_name	Role name
QWHCCTKN	correlation_token	Correlation token
QWHDRQNM	requester_location	Requester location
QWHDSVNM	srvnam_parameter	SRVNAM parameter
QWHDPRID	product_id_parm	Product ID parameter
QWHAMEMN	db2_member_name	DB2 member name
QWHADSGN	db2_group_name	DB2 group name
QWHUCPU	cpu_time_ziip_cp	CPU time ZIIP+CP
QWHUSE	cpu_time_ziip_hex	CPU time ZIIP
QPACRECN	qpac_data_number	Number of this DSNDQPAC data section in the series of DSNDQPAC data sections that may be externalized in IFCID 239. QWACPKGN and QPKGPKGN identify the number of packages for which package accounting has been performed
QPACFLGS	qpac_flag	Rollup QPAC Summary Rollup QPAC Non-zero accounting class 8 data Current or most recent exec package Loaded by stored procedure Non-zero accounting class 7 data Auth check success w/o access to the db2 catalog Incompatible Fonction
QPACLOCN	location_name	Location name Truncated if QPACLOCN_OFF=0
QPACCOLN	package_collection_id	Package collection ID Truncated if QPACCOLN_OFF=0
QPACPKID	program_name	Program name Truncated if QPACPKID_OFF=0
QPACCONT	consistency_token	Consistency token
QPACSQLC	sql_statements_number	Number of SQL requests This field may not equal the sum total of all SQL statement counters in the DSNDQXST data section. This is because DSNDQXST does not count all SQL statements
QPACSCB	clock_value_entry_most_recent_exec	Store clock value at entry to DB2 for most recent execution of this package
QPACSCE	clock_value_exit_most_recent_exec	Store clock value at exit from DB2 after most recent execution of this package
QPACSCT	total_time_all_exec	Total elapsed time spent processing all executions of this package	millisecond
QPACBJST	cpu_time_entry_most_recent_exec	CPU time at entry to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine
QPACEJST	cpu_time_exit_most_recent_exec	CPU time at exit to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine
QPACTJST	total_cpu_time_all_exec	CPU time for all executions of this package. This time does not include CPU consumed on an IBM specialty engine	millisecond
QPACARNA	number_db2_entries_exits_events	Number of DB2 entry or exist events processed while executing this package
QPACARNE	number_wait_for_io	Number of wait trace events processed for waits for I/O under this thread while executing this package
QPACAWTI	accum_io_wait	Accumulated I/O elapsed wait time for I/O done under this thread while executing this package	millisecond
QPACAWTL	accum_lock_wait	Accumulated lock elapsed wait time that occured while executing this package	millisecond
QPACAWTR	accum_wait_io_read_on_other_thread	Accumulated wait time for read I/O that is done under a thread other than this one while executing this package	millisecond
QPACAWTW	accum_wait_io_write_on_other_thread	Accumulated wait time for write I/O that is done under a thread other than this one while executing this package	millisecond
QPACAWTE	accum_wait_db2_services	Accumulated wait time due to synchronous executing unit switch to DB2 services from this thread while executing this package. The following DB2 service waits are included in this field Open/Close dataset SYSLGRNG update HSM recall dataset Data space manager services Define dataset Extend dataset Delete dataset Autonomous procedure There is no overlap between the elapsed time reported in this field and the other class 8 elapsed times There is no overlap between the elapsed time reported in this field and the elapsed time reported INQLACCPUL for the thread with distributed activity	millisecond
QPACALOG	accum_wait_archive_logs	Accumultad wait time due to processing of archive log mode (QUIESCE) commands while executing this package. This number represents the amount of time that an individual thread was suspended due to an archive log mode (QUIESCE) command and not the time that it took for the entire command to complete	millisecond
QPACARNL	number_wait_for_locks	Number of wait trace events processed for waits on locks while executing this package
QPACARNR	number_wait_io_read_on_other_thread	Number of wait trace events processed for waits for read I/O under another thread while executing this package
QPACARNW	number_wait_io_write_on_other_thread	Number of wait trace events processed for waits for write I/O under another thread while executing this package
QPACARNS	number_wait_db2_services	Number of wait trace events processed for waits for synchronous execution unit switching to DB2 service tasks while executing this package
QPACALCT	number_suspensions_archive_log	Number of suspensions due to processing of archive log mode (QUIESCE) commands while executing this package
QPACARND	number_wait_drain_lock	Number of wait trace events processed for waits for a drain lock while executing this package
QPACAWDR	accum_wait_drain_lock	Accumulated wait time for a drain lock while executing this package	millisecond
QPACAWCL	accum_wait_drain_release	Accumulated wait time for a drain when waiting for claims to be released while executing this package	millisecond
QPACARNC	number_wait_suspensions_release	Number of wait trace events processed for suspensions to wait for claims to be released while executing this package
QPACAWAR	accum_wait_read_archive_active_active-log	Accumulated wait time for Archive reads Active reads active log prefetch reads	millisecond
QPACANAR	number_wait_read_archive_active_active-log	Number of wait trace events processed for Archive reads Active reads active log prefetch reads
QPACAWTP	accum_wait_page_latch_contention	Accumulated wait time due to page latch contention while executing this package	millisecond
QPACARNH	number_wait_page_latch_contention	Number of wait trace events processed for page latch contention while executing this package
QPACAWTG	accum_wait_sending_msgs_db2_data_sharing_grp	Accumulated wait time due to sending of messages within the DB2 data sharing group	millisecond
QPACAWTJ	accum_wait_global_contention_parent_llocks	Accumulated wait time due to global contention for parent L-LOCKS. Global contention occurs when inter-system communication is required to resolved an IRLM lock or change request. QPACAWTL contains the wait time due to local contention. Local contention does not require inter-system communication. It can be resolved by the local subsystem	millisecond
QPACARNG	number_wait_msgs_db2_data_sharing_grp	Number of wait trace events processed for messages within the DB2 data sharing group
QPACARNJ	number_wait_global_contention_parent_llocks	Number of wait trace events processed for waits for global lock contention for parent L-LOCKS
QPACSPNS	number_stored_exec_procedures	Number of stored procedures executed
QPACUDNU	number_user_defined_scheduled_fcts	Number of user-defined functions scheduled
QPACASCH	nest_activity_schema	Nested activity schema name
QPACAANM	activity	Name of activity
QPACAAFG	activity_flag	Activity flag Stored procedure User defined function Trigger executing Native SQL stored procedure Non-inline UDF
QPACAWTK	accum_wait_global_contention_child_llocks	Accumulated wait time due to global contention for child L-LOCKS	millisecond
QPACAWTM	accum_wait_global_contention_other_llocks	Accumulated wait time due to globa contention for other L-LOCKS	millisecond
QPACAWTN	accum_wait_global_contention_pageset_part_plocks	Accumulated wait time due to global contention for PAGESET/PARTITION P-LOCKS	millisecond
QPACAWTO	accum_wait_global_contention_page_plocks	Accumulated wait time due to global contention for page P-LOCKS	millisecond
QPACAWTQ	accum_wait_global_contention_other_plocks	Accumulated wait time due to global contention for other P-LOCKS	millisecond
QPACARNK	number_wait_global_contention_child-llocks	Number of wait trace events processed for waits for global lock contention for child L-LOCKS
QPACARNM	number_wait_global_contention_other_llocks	Number of wait trace events processed for waits for global lock contention for other L-LOCKS
QPACARNN	number_wait_global_contention_pageset_part_plocks	Number of wait trace events processed for waits for global lock contention for PAGESET/PARTITION P-LOCKS
QPACARNO	number_wait_global_contention_page_plocks	Number of wait trace events processed for waits for global lock contention for page P-LOCKS
QPACARNQ	number_wait_global_contention_other_plocks	Number of wait trace events processed for waits for global lock contention for other P-LOCKS
QPACSWITCH	number_invoked_from_other_package	Number of times package was invoked from a different package. For the first package run by an aplication The initial call counts as a package switch. If this package called a nested package (a trigger, UDF or stored procedure). A switch will not be counted upon return from such a package
QPACCLS7_ZIIP	total_cpu_time_all_exec_on_specialilty_engine	CPU time for all execution of this package consumed on an IBM special engine	millisecond
QPACALBC	number_wait_lob_xml	Number of wait trace events processed for waits for TCP/IP LOB or XML materialization while executing this package
QPACALBW	accum_wait_lob_xml	Accumulated wait time for TCP/IP LOB or XML materialization while executing this package	millisecond
QPACAWLH	accum_wait_latch_suspension	Accumulated latch elapsed wait time for latch suspensions that occured while executing this package	millisecond
QPACANLH	number_wait_latch	Number of wait trace events processed for waits on latches while executing this package
QPACRLNU	number_threads_roll_data	Number of threads to roll data into this QPAC data section. Non-rollup QPACs will have a value of 1 and rollup QPACS will have a value of 1 or more
QPACAACC	number_wait_accelerator	Number of wait trace events processed for requests to an accelerator while executing this package
QPACAACW	accum_wait_accelerator	Accumulated wait time for requests to an accelerator while executing this package	millisecond
QPAC_PQS_WAIT	accum_wait_parallel_synchro_parent_child	Accumulated time waiting for parallel query processing to synchronize between parent and child tasks	millisecond
QPAC_PQS_COUNT	number_suspended_parallel_synchro_parent_child	Number of times a parallel query processing suspended waiting for parent/child to synchronize

SMF Field

DB Column name

Description

Unit

QWHSLOCN

local_location_name

Local location name

QWHSNID

luwid_network_id

LUWID Network ID

QWHSLUNM

luwid_luname

LUWID LUNAME

QWHSLUUV

luwid_uniquen_hex

LUWID UNIQUEN

QWHSLUCC

luwid_commit_hex

LUWID COMMIT

QWHSACE

ace_address_hex

ACE address

QWHCAID

authorization_id

Authorization ID

QWHCCV

correlation_id

Correlation ID

QWHCPLAN

plan_name

Plan name

QWHCOPID

orig_operator_id

Original operator ID

QWHCATYP

connect_type_hex

Connection type

QWHCCN

connection_name

Connection name

QWHCEUID

enduser_user_id

Enduser USER-ID

QWHCEUTX

enduser_transaction

Enduser transaction

QWHCEUWN

enduser_workstation

Enduser workstation

QWHCTCXT

trusted_context

Trusted context

QWHCROLE

role_name

Role name

QWHCCTKN

correlation_token

Correlation token

QWHDRQNM

requester_location

Requester location

QWHDSVNM

srvnam_parameter

SRVNAM parameter

QWHDPRID

product_id_parm

Product ID parameter

QWHAMEMN

db2_member_name

DB2 member name

QWHADSGN

db2_group_name

DB2 group name

QWHUCPU

cpu_time_ziip_cp

CPU time ZIIP+CP

QWHUSE

cpu_time_ziip_hex

CPU time ZIIP

QPACRECN

qpac_data_number

Number of this DSNDQPAC data section in the series of DSNDQPAC data sections that may be externalized in IFCID 239. QWACPKGN and QPKGPKGN identify the number of packages for which package accounting has been performed

QPACFLGS

qpac_flag

Rollup QPAC
Summary Rollup QPAC
Non-zero accounting class 8 data
Current or most recent exec package
Loaded by stored procedure
Non-zero accounting class 7 data
Auth check success w/o access to the db2 catalog
Incompatible Fonction

QPACLOCN

location_name

Location name
Truncated if QPACLOCN_OFF=0

QPACCOLN

package_collection_id

Package collection ID
Truncated if QPACCOLN_OFF=0

QPACPKID

program_name

Program name
Truncated if QPACPKID_OFF=0

QPACCONT

consistency_token

Consistency token

QPACSQLC

sql_statements_number

Number of SQL requests
This field may not equal the sum total of all SQL statement counters in the DSNDQXST data section. This is because DSNDQXST does not count all SQL statements

QPACSCB

clock_value_entry_most_recent_exec

Store clock value at entry to DB2 for most recent execution of this package

QPACSCE

clock_value_exit_most_recent_exec

Store clock value at exit from DB2 after most recent execution of this package

QPACSCT

total_time_all_exec

Total elapsed time spent processing all executions of this package

millisecond

QPACBJST

cpu_time_entry_most_recent_exec

CPU time at entry to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine

QPACEJST

cpu_time_exit_most_recent_exec

CPU time at exit to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine

QPACTJST

total_cpu_time_all_exec

CPU time for all executions of this package. This time does not include CPU consumed on an IBM specialty engine

millisecond

QPACARNA

number_db2_entries_exits_events

Number of DB2 entry or exist events processed while executing this package

QPACARNE

number_wait_for_io

Number of wait trace events processed for waits for I/O under this thread while executing this package

QPACAWTI

accum_io_wait

Accumulated I/O elapsed wait time for I/O done under this thread while executing this package

millisecond

QPACAWTL

accum_lock_wait

Accumulated lock elapsed wait time that occured while executing this package

millisecond

QPACAWTR

accum_wait_io_read_on_other_thread

Accumulated wait time for read I/O that is done under a thread other than this one while executing this package

millisecond

QPACAWTW

accum_wait_io_write_on_other_thread

Accumulated wait time for write I/O that is done under a thread other than this one while executing this package

millisecond

QPACAWTE

accum_wait_db2_services

Accumulated wait time due to synchronous executing unit switch to DB2 services from this thread while executing this package. The following DB2 service waits are included in this field

Open/Close dataset
SYSLGRNG update
HSM recall dataset
Data space manager services
Define dataset
Extend dataset
Delete dataset
Autonomous procedure

There is no overlap between the elapsed time reported in this field and the other class 8 elapsed times
There is no overlap between the elapsed time reported in this field and the elapsed time reported INQLACCPUL for the thread with distributed activity

millisecond

QPACALOG

accum_wait_archive_logs

Accumultad wait time due to processing of archive log mode (QUIESCE) commands while executing this package. This number represents the amount of time that an individual thread was suspended due to an archive log mode (QUIESCE) command and not the time that it took for the entire command to complete

millisecond

QPACARNL

number_wait_for_locks

Number of wait trace events processed for waits on locks while executing this package

QPACARNR

number_wait_io_read_on_other_thread

Number of wait trace events processed for waits for read I/O under another thread while executing this package

QPACARNW

number_wait_io_write_on_other_thread

Number of wait trace events processed for waits for write I/O under another thread while executing this package

QPACARNS

number_wait_db2_services

Number of wait trace events processed for waits for synchronous execution unit switching to DB2 service tasks while executing this package

QPACALCT

number_suspensions_archive_log

Number of suspensions due to processing of archive log mode (QUIESCE) commands while executing this package

QPACARND

number_wait_drain_lock

Number of wait trace events processed for waits for a drain lock while executing this package

QPACAWDR

accum_wait_drain_lock

Accumulated wait time for a drain lock while executing this package

millisecond

QPACAWCL

accum_wait_drain_release

Accumulated wait time for a drain when waiting for claims to be released while executing this package

millisecond

QPACARNC

number_wait_suspensions_release

Number of wait trace events processed for suspensions to wait for claims to be released while executing this package

QPACAWAR

accum_wait_read_archive_active_active-log

Accumulated wait time for

Archive reads
Active reads
active log prefetch reads

millisecond

QPACANAR

number_wait_read_archive_active_active-log

Number of wait trace events processed for

Archive reads
Active reads
active log prefetch reads

QPACAWTP

accum_wait_page_latch_contention

Accumulated wait time due to page latch contention while executing this package

millisecond

QPACARNH

number_wait_page_latch_contention

Number of wait trace events processed for page latch contention while executing this package

QPACAWTG

accum_wait_sending_msgs_db2_data_sharing_grp

Accumulated wait time due to sending of messages within the DB2 data sharing group

millisecond

QPACAWTJ

accum_wait_global_contention_parent_llocks

Accumulated wait time due to global contention for parent L-LOCKS. Global contention occurs when inter-system communication is required to resolved an IRLM lock or change request. QPACAWTL contains the wait time due to local contention. Local contention does not require inter-system communication. It can be resolved by the local subsystem

millisecond

QPACARNG

number_wait_msgs_db2_data_sharing_grp

Number of wait trace events processed for messages within the DB2 data sharing group

QPACARNJ

number_wait_global_contention_parent_llocks

Number of wait trace events processed for waits for global lock contention for parent L-LOCKS

QPACSPNS

number_stored_exec_procedures

Number of stored procedures executed

QPACUDNU

number_user_defined_scheduled_fcts

Number of user-defined functions scheduled

QPACASCH

nest_activity_schema

Nested activity schema name

QPACAANM

activity

Name of activity

QPACAAFG

activity_flag

Activity flag

Stored procedure
User defined function
Trigger executing
Native SQL stored procedure
Non-inline UDF

QPACAWTK

accum_wait_global_contention_child_llocks

Accumulated wait time due to global contention for child L-LOCKS

millisecond

QPACAWTM

accum_wait_global_contention_other_llocks

Accumulated wait time due to globa contention for other L-LOCKS

millisecond

QPACAWTN

accum_wait_global_contention_pageset_part_plocks

Accumulated wait time due to global contention for PAGESET/PARTITION P-LOCKS

millisecond

QPACAWTO

accum_wait_global_contention_page_plocks

Accumulated wait time due to global contention for page P-LOCKS

millisecond

QPACAWTQ

accum_wait_global_contention_other_plocks

Accumulated wait time due to global contention for other P-LOCKS

millisecond

QPACARNK

number_wait_global_contention_child-llocks

Number of wait trace events processed for waits for global lock contention for child L-LOCKS

QPACARNM

number_wait_global_contention_other_llocks

Number of wait trace events processed for waits for global lock contention for other L-LOCKS

QPACARNN

number_wait_global_contention_pageset_part_plocks

Number of wait trace events processed for waits for global lock contention for PAGESET/PARTITION P-LOCKS

QPACARNO

number_wait_global_contention_page_plocks

Number of wait trace events processed for waits for global lock contention for page P-LOCKS

QPACARNQ

number_wait_global_contention_other_plocks

Number of wait trace events processed for waits for global lock contention for other P-LOCKS

QPACSWITCH

number_invoked_from_other_package

Number of times package was invoked from a different package. For the first package run by an aplication
The initial call counts as a package switch. If this package called a nested package (a trigger, UDF or stored procedure). A switch will not be counted upon return from such a package

QPACCLS7_ZIIP

total_cpu_time_all_exec_on_specialilty_engine

CPU time for all execution of this package consumed on an IBM special engine

millisecond

QPACALBC

number_wait_lob_xml

Number of wait trace events processed for waits for TCP/IP LOB or XML materialization while executing this package

QPACALBW

accum_wait_lob_xml

Accumulated wait time for TCP/IP LOB or XML materialization while executing this package

millisecond

QPACAWLH

accum_wait_latch_suspension

Accumulated latch elapsed wait time for latch suspensions that occured while executing this package

millisecond

QPACANLH

number_wait_latch

Number of wait trace events processed for waits on latches while executing this package

QPACRLNU

number_threads_roll_data

Number of threads to roll data into this QPAC data section. Non-rollup QPACs will have a value of 1 and rollup QPACS will have a value of 1 or more

QPACAACC

number_wait_accelerator

Number of wait trace events processed for requests to an accelerator while executing this package

QPACAACW

accum_wait_accelerator

Accumulated wait time for requests to an accelerator while executing this package

millisecond

QPAC_PQS_WAIT

accum_wait_parallel_synchro_parent_child

Accumulated time waiting for parallel query processing to synchronize between parent and child tasks

millisecond

QPAC_PQS_COUNT

number_suspended_parallel_synchro_parent_child

Number of times a parallel query processing suspended waiting for parent/child to synchronize

Introduction

Fields

What's Next