The SNAPHADR administrative view and the SNAP_GET_HADR table function return information about high availability disaster recovery from a database snapshot, in particular, the hadr logical data group.
This administrative view allows you to retrieve hadr logical data group snapshot information for the currently connected database. The data is only returned by this view if the database is a primary or standby high availability disaster recovery (HADR) database.
Used with the SNAPDB, SNAPDB_MEMORY_POOL, SNAPDETAILLOG and SNAPSTORAGE_PATHS administrative views, the SNAPHADR administrative view provides information equivalent to the GET SNAPSHOT FOR DATABASE ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
SELECT SUBSTR(DB_NAME, 1, 8) AS DBNAME, HADR_ROLE, HADR_STATE,
HADR_SYNCMODE, HADR_CONNECT_STATUS
FROM SYSIBMADM.SNAPHADR
DBNAME HADR_ROLE HADR_STATE HADR_SYNCMODE HADR_CONNECT_STATUS
-------- --------- -------------- ------------- -------------------
SAMPLE PRIMARY PEER SYNC CONNECTED
1 record(s) selected.
The SNAP_GET_HADR table function returns the same information as the SNAPHADR administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.
Used with the SNAP_GET_DB_V95, SNAP_GET_DB_MEMORY_POOL, SNAP_GET_DETAILLOG_V91 and SNAP_GET_STORAGE_PATHS table functions, the SNAP_GET_HADR table function provides information equivalent to the GET SNAPSHOT FOR ALL DATABASES CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_HADR--(--dbname--+------------------+--)----------->< '-, dbpartitionnum-'
The schema is SYSPROC.
If both dbname and dbpartitionnum 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_HADR table function takes a snapshot for the currently connected database and database partition number.
SELECT SUBSTR(DB_NAME, 1, 8) AS DBNAME, HADR_ROLE, HADR_STATE,
HADR_SYNCMODE, HADR_CONNECT_STATUS
FROM TABLE (SNAP_GET_HADR (CAST (NULL as VARCHAR(128)), 0)) as T
DBNAME HADR_ROLE HADR_STATE HADR_SYNCMODE HADR_CONNECT_STATUS
-------- --------- -------------- ------------- -------------------
SAMPLE PRIMARY PEER SYNC CONNECTED
TESTDB PRIMARY DISCONNECTED NEARSYNC DISCONNECTED
2 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
DB_NAME | VARCHAR(128) | db_name - Database name |
HADR_ROLE | VARCHAR(10) | hadr_role - HADR role . This interface returns
a text identifier based on the defines in sqlmon.h, and is one of:
|
HADR_STATE | VARCHAR(14) | hadr_state - HADR state . This interface returns
a text identifier based on the defines in sqlmon.h, and is one of:
|
HADR_SYNCMODE | VARCHAR(10) | hadr_syncmode - HADR synchronization
mode . This interface returns a text identifier based on the defines
in sqlmon.h, and is one of:
|
HADR_CONNECT_STATUS | VARCHAR(12) | hadr_connect_status - HADR connection
status . This interface returns a text identifier based on the
defines in sqlmon.h, and is one of:
|
HADR_CONNECT_TIME | TIMESTAMP | hadr_connect_time - HADR connection time |
HADR_HEARTBEAT | INTEGER | hadr_heartbeat - HADR heartbeat |
HADR_LOCAL_HOST | VARCHAR(255) | hadr_local_host - HADR local host |
HADR_LOCAL_SERVICE | VARCHAR(40) | hadr_local_service - HADR local service |
HADR_REMOTE_HOST | VARCHAR(255) | hadr_remote_host - HADR remote host |
HADR_REMOTE_SERVICE | VARCHAR(40) | hadr_remote_service - HADR remote service |
HADR_REMOTE_INSTANCE | VARCHAR(128) | hadr_remote_instance - HADR remote instance |
HADR_TIMEOUT | BIGINT | hadr_timeout - HADR timeout |
HADR_PRIMARY_LOG_FILE | VARCHAR(255) | hadr_primary_log_file - HADR primary log file |
HADR_PRIMARY_LOG_PAGE | BIGINT | hadr_primary_log_page - HADR primary log page |
HADR_PRIMARY_LOG_LSN | BIGINT | hadr_primary_log_lsn - HADR primary log LSN |
HADR_STANDBY_LOG_FILE | VARCHAR(255) | hadr_standby_log_file - HADR standby log file |
HADR_STANDBY_LOG_PAGE | BIGINT | hadr_standby_log_page - HADR standby log page |
HADR_STANDBY_LOG_LSN | BIGINT | hadr_standby_log_lsn - HADR standby log LSN |
HADR_LOG_GAP | BIGINT | hadr_log_gap - HADR log gap |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |