DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
The SNAPTAB administrative view and the SNAP_GET_TAB table function return snapshot information from the table logical data group.
This administrative view allows you to retrieve table logical data group snapshot information for the currently connected database.
Used in conjunction with the SNAPTAB_REORG administrative view, the SNAPTAB administrative view returns equivalent information to the GET SNAPSHOT FOR TABLES ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT SUBSTR(TABSCHEMA,1,8), SUBSTR(TABNAME,1,15) AS TABNAME, TAB_TYPE,
DBPARTITIONNUM FROM SYSIBMADM.SNAPTABTABSCHEMA TABNAME TAB_TYPE DBPARTITIONNUM
--------- --------------- ------------ --------------
SYSTOOLS HMON_ATM_INFO USER_TABLE 0
1 record selected.The SNAP_GET_TAB table function returns the same information as the SNAPTAB administrative view, but allows you to retrieve the information for a specific database on a specific database member, aggregate of all database members or all database members.
Used in conjunction with the SNAP_GET_TAB_REORG table function, the SNAP_GET_TAB table function returns equivalent information to the GET SNAPSHOT FOR TABLES ON database-alias CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_TAB--(--dbname--+----------+--)-------------------->< '-, member-'
The schema is SYSPROC.
If both dbname and member are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_TAB table function takes a snapshot for the currently connected database and database member number.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT SUBSTR(TABSCHEMA,1,8) AS TABSCHEMA, SUBSTR(TABNAME,1,15) AS TABNAME,
TAB_TYPE, DBPARTITIONNUM FROM TABLE(SNAP_GET_TAB('',-2)) AS TTABSCHEMA TABNAME TAB_TYPE DBPARTITIONNUM
--------- --------------- ------------- --------------
SYSTOOLS HMON_ATM_INFO USER_TABLE -
JESSICAE EMPLOYEE USER_TABLE -| Column name | Data type | Description or corresponding monitor element |
|---|---|---|
| SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
| TABSCHEMA | VARCHAR(128) | table_schema - Table schema name |
| TABNAME | VARCHAR(128) | table_name - Table name |
| TAB_FILE_ID | BIGINT | table_file_id - Table file identification |
| TAB_TYPE | VARCHAR(14) | table_type - Table type . This interface returns
a text identifier based on defines in sqlmon.h,
and is one of:
|
| DATA_OBJECT_PAGES | BIGINT | data_object_pages - Data object pages |
| INDEX_OBJECT_PAGES | BIGINT | index_object_pages - Index object pages |
| LOB_OBJECT_PAGES | BIGINT | lob_object_pages - LOB object pages |
| LONG_OBJECT_PAGES | BIGINT | long_object_pages - Long object pages |
| XDA_OBJECT_PAGES | BIGINT | xda_object_pages - XDA Object Pages |
| ROWS_READ | BIGINT | rows_read - Rows read |
| ROWS_WRITTEN | BIGINT | rows_written - Rows written |
| OVERFLOW_ACCESSES | BIGINT | overflow_accesses - Accesses to overflowed records |
| PAGE_REORGS | BIGINT | page_reorgs - Page reorganizations |
| DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
| TBSP_ID | BIGINT | tablespace_id - Table space identification |
| DATA_PARTITION_ID | INTEGER | data_partition_id - Data Partition identifier. For a non-partitioned table, this element will be NULL. |
| MEMBER | SMALLINT | member - Database member monitor element |