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.
Important: This routine is available starting with DB2® Cancun
Release 10.5.0.4. If your database was created before you applied DB2 Cancun
Release 10.5.0.4, then this routine becomes available after you apply DB2 Cancun
Release 10.5.0.4 then issue the db2updv105 command.
If your database was created before Version 10.5, upgrade to DB2 Cancun
Release 10.5.0.4 or later pack to have this routine available.
Authorization
One of the following authorizations
is required:
- EXECUTE privilege on the routine
- DATAACCESS authority
- SQLADM authority
- DBADM authority
Default PUBLIC privilege
None
Syntax
>>-MON_GET_SECTION_OBJECT--(--executable_id--,--object_type--,-->
>--object_schema--,--object_module--,--object_name--,--member--)-><
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.
Table 1. Possible values for the OBJECT_TYPE
parameter| 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 |
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.
DB2 10.5 for Linux, UNIX, and Windows