Capturing a database health snapshot from a client application

You can capture health snapshots using the snapshot monitor API in a C or C++ application. A number of different health snapshot request types can be accessed by specifying parameters in the db2GetSnapshot API.

Important: The health monitor, health indicators, and related components have been deprecated and might be removed in a future release. Health monitor is not supported in Db2® pureScale® environments. For more information, see Health monitor has been deprecated.

Before you begin

You must be attached to an instance to capture a health snapshot. If there is not an attachment to an instance, then a default instance attachment is created. To obtain a snapshot of a remote instance, you must first attach to that instance.

Procedure

Include the sqlmon.h and db2ApiDf.h libraries in your code. These libraries are located in the sqllib\include directory.
```
 
#include <db2ApiDf.h>
#include <sqlmon.h>
```
Set the snapshot buffer unit size to 50 KB.
```
#define SNAPSHOT_BUFFER_UNIT_SZ 51200
```

Declare the sqlma, sqlca, sqlm_collected, and db2GetSnapshotData structures.

struct sqlma *pRequestedDataGroups;
struct sqlca sqlca;
memset (&sqlca, '\0', sizeof(struct sqlca));
struct sqlm_collected collectedData;
memset (&sqlm_collected, '\0', sizeof(struct sqlm_collected));
db2GetSnapshotData getSnapshotParam;
memset(&db2GetSnapshotData, '\0', sizeof(db2GetSnapshotData));

Initialize a pointer to contain the snapshot buffer, and to establish the buffer's size.

 
static sqluint32 snapshotBufferSize = SNAPSHOT_BUFFER_UNIT_SZ;
sqluint32 outputFormat;
char *snapshotBuffer;

Initialize the sqlma structure and specify that the snapshot you are capturing is of database manager level information.

pRequestedDataGroups = (struct sqlma *)malloc(SQLMASIZE(1));
memset(&pRequestedDataGroups, '\0', sizeof(struct pRequestedDataGroups));
pRequestedDataGroups->obj_num = 1;
pRequestedDataGroups->obj_var[0].obj_type = SQLMA_DB2;

Initialize the buffer, which will hold the snapshot output.

snapshotBuffer = (char *)malloc(snapshotBufferSize);
memset (&snapshotBuffer, '\0', sizeof(snapshotBuffer));

Populate the db2GetSnapshotData structure with the snapshot request type (from the sqlma structure), buffer information, and other information required to capture a snapshot.

getSnapshotParam.piSqlmaData = pRequestedDataGroups;
getSnapshotParam.poCollectedData = &collectedData;
getSnapshotParam.poBuffer = snapshotBuffer;
getSnapshotParam.iVersion = SQLM_DBMON_VERSION9_5;
getSnapshotParam.iBufferSize = snapshotBufferSize;
getSnapshotParam.iStoreResult = 0;
getSnapshotParam.iNodeNumber = SQLM_CURRENT_NODE;
getSnapshotParam.poOutputFormat = &outputFormat;
getSnapshotParam.iSnapshotClass = SQLM_CLASS_HEALTH;

Capture the health snapshot. Pass the following parameters:
- db2GetSnapshotData structure, which contains the information necessary to capture a snapshot
- A reference to the buffer where snapshot output is directed.
```
db2GetSnapshot(db2Version810, &getSnapshotParam, &sqlca);
```

Include logic to handle buffer overflow. After a snapshot is taken, the sqlcode is checked for a buffer overflow. If a buffer overflow occurs, the buffer is cleared, reinitialized, and the snapshot is taken again.

while (sqlca.sqlcode == 1606)
{
  free(snapshotBuffer);
  snapshotBufferSize += SNAPSHOT_BUFFER_UNIT_SZ;
  snapshotBuffer = (char *)malloc(snapshotBufferSize);
  if (snapshotBuffer == NULL)
  {
    printf("\nMemory allocation error.\n");
    return;
  }
 
  getSnapshotParam.iBufferSize = snapshotBufferSize;
  getSnapshotParam.poBuffer = snapshotBuffer;
  db2GetSnapshot(db2Version810, &getSnapshotParam, &sqlca);
}

Process the snapshot monitor data stream. Refer to the figure following these steps to see the snapshot monitor data stream.

Clear the buffer.

free(snapshotBuffer);
free(pRequestedDataGroups);

Results

After you capture a health snapshot with the db2GetSnapshot API, the API returns the health snapshot output as a self-describing data stream.

The following example shows the data stream structure:

Figure 1. Health snapshot self-describing data stream

Legend:

Available only when the SQLM_CLASS_HEALTH_WITH_DETAIL snapshot class is used.
Available only in Db2 Enterprise Server Edition. Otherwise, table space container stream follows.

The following hierarchies display the specific elements in the health snapshot self-describing data stream.

The hierarchy of elements under SQLM_ELM_HI:

  SQLM_ELM_HI
     SQLM_ELM_HI_ID
     SQLM_ELM_HI_VALUE
        SQLM_ELM_HI_TIMESTAMP
           SQLM_ELM_SECONDS
           SQLM_ELM_MICROSEC
     SQLM_ELM_HI_ALERT_STATE

The hierarchy of elements under SQLM_ELM_HI_HIST, available only with the SQLM_CLASS_HEALTH_WITH_DETAIL snapshot class:

  SQLM_ELM_HI_HIST
     SQLM_ELM_HI_FORMULA
     SQLM_ELM_HI_ADDITIONAL_INFO
        SQLM_ELM_HEALTH_INDICATOR_HIST
           SQLM_ELM_HI_ID
           SQLM_ELM_HI_VALUE
              SQLM_ELM_HI_TIMESTAMP
                 SQLM_ELM_SECONDS
                 SQLM_ELM_MICROSEC
           SQLM_ELM_HI_ALERT_STATE
           SQLM_ELM_HI_FORMULA
           SQLM_ELM_HI_ADDITIONAL_INFO

The hierarchy of elements under SQLM_ELM_OBJ_LIST:

  SQLM_ELM_HI_OBJ_LIST
     SQLM_ELM_HI_OBJ_NAME
     SQLM_ELM_HI_OBJ_DETAIL
     SQLM_ELM_HI_OBJ_STATE
        SQLM_ELM_HI_TIMESTAMP
           SQLM_ELM_SECONDS
           SQLM_ELM_MICROSEC

The hierarchy of elements under SQLM_ELM_OBJ_LIST_HIST, available only with the SQLM_CLASS_HEALTH_WITH_DETAIL snapshot class:

  SQLM_ELM_HI_OBJ_LIST_HIST
     SQLM_ELM_HI_OBJ_NAME
     SQLM_ELM_HI_OBJ_STATE
        SQLM_ELM_HI_TIMESTAMP
           SQLM_ELM_SECONDS
           SQLM_ELM_MICROSEC