MON_GET_SECTION_OBJECT table function - List objects accessed by a section
The MON_GET_SECTION_OBJECT table function retrieves the list of objects that are accessed by a section. This information can be used in filtering or for later analysis.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
- executable_id
- An optional input parameter of type VARCHAR(32) FOR BIT DATA that uniquely identifies a section for which the list of objects is to be returned. If this argument is NULL then information for all sections in the package cache are returned.
- object_type
- An optional input parameter of type VARCHAR(16) that identifies the object types to be returned. If this argument is null or an empty string, then all types of objects are returned. Valid object types are listed in Table 1.
- object_schema
- An optional input parameter of type VARCHAR(128) that identifies the schema for the objects to be returned. If this argument is null or an empty string, then objects in all schemas are returned.
- object_module
- An optional input parameter of type VARCHAR(128) that identifies the module of the objects to be returned. If this argument is null or an empty string, then objects defined in all modules are returned.
- object_name
- An optional input parameter of type VARCHAR(128) that identifies the name of the object to be returned. If this argument is null or an empty string, then objects are not filtered by name.
- member
- An optional input argument of type SMALLINT that specifies a valid member in the same instance as the currently connected database when this function is called. Specify -1 for the current database member or -2 for all active database members. If the null value is specified, -1 is set.
Value | Description |
---|---|
C | Dynamically prepared compound SQL statement or PL/SQL anonymous block |
CO | Column-organized table |
DP | Data partitioned table |
F | Function |
IX | Index |
LI | Partitioned index |
LP | Partitioned physical XML index |
LX | Partitioned logical XML index |
NK | Nickname |
P | Procedure |
PI | Physical XML index |
RX | RCT index |
T | Trigger |
TA | Table |
XI | Logical XML index |
Information returned
Column name | Data type | Description |
---|---|---|
MEMBER | SMALLINT | member - Database member monitor element |
EXECUTABLE_ID | VARCHAR(32) FOR BIT DATA | executable_id - Executable ID monitor element |
OBJECT_TYPE | VARCHAR(16) | An object type, with the following valid values:
|
OBJECT_SCHEMA | VARCHAR(128) | object_schema - Object schema monitor element |
OBJECT_MODULE | VARCHAR(128) | object_module - Object module monitor element |
OBJECT_NAME | VARCHAR(128) | object_name - Object name monitor element |
Example
As part of your investigation of
a poorly performing statement, you want to examine which objects are
used in the access plan for the statement. Assuming the section for
the statement is identified by the executable_id x'0100000000000000010000000000000000000000020020130726092341206252',
you can list the objects that are accessed by the section with this
query:
SELECT DISTINCT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_MODULE, OBJECT_NAME
FROM TABLE (MON_GET_SECTION_OBJECT(
x'0100000000000000010000000000000000000000020020130726092341206252',
NULL, NULL, NULL, NULL, -2)) as SectionObject
Sample
output from this query is as follows:OBJECT_TYPE OBJECT_SCHEMA OBJECT_MODULE OBJECT_NAME
-------------- ----------------- ---------------- -------------------
IX SYSTOOLS - ATM_UNIQ
TA SYSTOOLS - HMON_ATM_INFO
TA SYSIBM - SYSTABLES
3 record(s) selected.