IBM i: The SQL Way · #12

Monitor IBM i Geographic Mirroring with SQL

Use QSYS2.GEOGRAPHIC_MIRRORING_INFO to review IASP mirror roles, copy state, synchronization progress, out-of-sync data, tracking space, and replication transport.

Saurabh Arora

IBM i Solution Architect | Technical Lead | Modernization & Integration Specialist

Published July 25, 20264 min read

Related native optionPowerHA and geographic mirroring management interfaces

Geographic mirroring health should not be reduced to one status value. QSYS2.GEOGRAPHIC_MIRRORING_INFO exposes the IASP role, mirror state, synchronization progress, tracking space, data out of sync, data in transit, and topology details as SQL columns.

The new view is:

QSYS2.GEOGRAPHIC_MIRRORING_INFO

It returns rows for independent auxiliary storage pools that have geographic mirroring configured.

Basic query

SELECT *
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO;

If the partition has no geographically mirrored IASP visible to the caller, the result may contain no rows.

A focused health query

SELECT
    DEVICE_DESCRIPTION_NAME,
    ASP_NUMBER,
    NODE_NAME,
    GEOGRAPHIC_MIRROR_ROLE,
    CONNECTION_ENTRY_TYPE,
    SOURCE_NODE,
    TARGET_NODE,
    COPY_STATE,
    COPY_DATA_STATE,
    SYNCHRONIZATION_STATUS,
    SYNCHRONIZATION_PROGRESS,
    TRANSMISSION_DELIVERY,
    PERFORMANCE_MODE
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
ORDER BY
    DEVICE_DESCRIPTION_NAME,
    CONNECTION_ENTRY_TYPE,
    TARGET_NODE;

This gives a concise view of:

which IASP is involved
which node owns the copy
whether the row describes a source or target relationship
whether replication is active
whether the data is synchronized
whether synchronization is in progress
whether transmission is synchronous or asynchronous

Mirror roles

GEOGRAPHIC_MIRROR_ROLE can include:

PRODUCTION
MIRROR
DETACHED
UNKNOWN

PRODUCTION

The system owns the production copy.

MIRROR

The system owns a mirror copy.

DETACHED

The system owns a detached mirror copy.

UNKNOWN

The role could not be determined.

The role alone does not prove replication health. Review copy and data states as well.

Find copies that need attention

SELECT
    DEVICE_DESCRIPTION_NAME,
    NODE_NAME,
    SOURCE_NODE,
    TARGET_NODE,
    COPY_STATE,
    COPY_DATA_STATE,
    SYNCHRONIZATION_STATUS,
    SYNCHRONIZATION_PROGRESS,
    TARGET_DATA_OUT_OF_SYNC,
    DATA_IN_TRANSIT,
    NUMBER_OF_OPERATIONS_IN_TRANSIT
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
WHERE COPY_STATE <> 'ACTIVE'
   OR COPY_DATA_STATE <> 'SYNCHRONIZED'
   OR COALESCE(
          SYNCHRONIZATION_STATUS,
          'NONE'
      ) <> 'NONE'
ORDER BY DEVICE_DESCRIPTION_NAME;

This is a starting point, not an automatic incident declaration.

Expected maintenance, a detached copy, or an active synchronization can legitimately appear in the result.

Show synchronization progress

SELECT
    DEVICE_DESCRIPTION_NAME,
    SOURCE_NODE,
    TARGET_NODE,
    SYNCHRONIZATION_STATUS,
    SYNCHRONIZATION_PROGRESS,
    DECIMAL(
        TARGET_DATA_OUT_OF_SYNC
          / 1073741824.0,
        15,
        2
    ) AS OUT_OF_SYNC_GB,
    DECIMAL(
        COALESCE(DATA_IN_TRANSIT, 0)
          / 1073741824.0,
        15,
        2
    ) AS DATA_IN_TRANSIT_GB
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
WHERE SYNCHRONIZATION_STATUS IS NOT NULL
ORDER BY
    DEVICE_DESCRIPTION_NAME,
    TARGET_NODE;

SYNCHRONIZATION_PROGRESS contains the percentage complete when synchronization is active.

It can be null when no synchronization is in progress.

Tracking-space usage

SELECT
    DEVICE_DESCRIPTION_NAME,
    NODE_NAME,
    GEOGRAPHIC_MIRROR_ROLE,
    DECIMAL(
        TRACKING_SPACE_IN_USE
          / 1073741824.0,
        15,
        2
    ) AS TRACKING_SPACE_USED_GB,
    DECIMAL(
        TRACKING_SPACE_ALLOCATED
          / 1073741824.0,
        15,
        2
    ) AS TRACKING_SPACE_ALLOCATED_GB,
    DECIMAL(
        CASE
            WHEN TRACKING_SPACE_ALLOCATED > 0
              THEN TRACKING_SPACE_IN_USE
                   * 100.0
                   / TRACKING_SPACE_ALLOCATED
            ELSE 0
        END,
        7,
        2
    ) AS TRACKING_SPACE_USED_PERCENT,
    OLDEST_TRACKED_OPERATION_NODE,
    OLDEST_TRACKED_OPERATION_TIMESTAMP
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
ORDER BY TRACKING_SPACE_USED_PERCENT DESC;

Tracking space records changes that still need to be handled in the mirroring environment.

The oldest tracked-operation timestamp helps show how far back pending tracked activity extends.

Alert when tracking space is heavily used

SELECT
    DEVICE_DESCRIPTION_NAME,
    NODE_NAME,
    TRACKING_SPACE_IN_USE,
    TRACKING_SPACE_ALLOCATED,
    OLDEST_TRACKED_OPERATION_TIMESTAMP
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
WHERE TRACKING_SPACE_ALLOCATED > 0
  AND TRACKING_SPACE_IN_USE
        * 100.0
        / TRACKING_SPACE_ALLOCATED >= 80
ORDER BY TRACKING_SPACE_IN_USE DESC;

Choose the threshold according to the environment’s design and operational guidance.

Do not copy an arbitrary percentage into production monitoring without understanding the IASP size, mirroring mode, workload, and recovery procedures.

Asynchronous replication backlog

For asynchronous transmission, review data and operations still in transit:

SELECT
    DEVICE_DESCRIPTION_NAME,
    SOURCE_NODE,
    TARGET_NODE,
    TRANSMISSION_DELIVERY,
    DECIMAL(
        DATA_IN_TRANSIT
          / 1048576.0,
        15,
        2
    ) AS DATA_IN_TRANSIT_MB,
    NUMBER_OF_OPERATIONS_IN_TRANSIT,
    TOTAL_TIME_TO_TRANSMIT
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO
WHERE TRANSMISSION_DELIVERY = 'ASYNCHRONOUS'
ORDER BY
    DATA_IN_TRANSIT DESC,
    NUMBER_OF_OPERATIONS_IN_TRANSIT DESC;

DATA_IN_TRANSIT and NUMBER_OF_OPERATIONS_IN_TRANSIT can be null when delivery is not asynchronous.

One snapshot cannot show whether backlog is growing or shrinking.

Store repeated samples to establish direction.

Save a monitoring history

CREATE TABLE MYHA.GEOMIRROR_HISTORY AS
(
    SELECT
        CURRENT TIMESTAMP AS CAPTURE_TIMESTAMP,
        G.*
    FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO AS G
)
WITH NO DATA;

Capture a snapshot:

INSERT INTO MYHA.GEOMIRROR_HISTORY
SELECT
    CURRENT TIMESTAMP,
    G.*
FROM QSYS2.GEOGRAPHIC_MIRRORING_INFO AS G;

Because IBM may add columns in future PTF levels, a production history table should normally list explicit columns rather than using G.*.

Compare recent samples

SELECT
    CAPTURE_TIMESTAMP,
    DEVICE_DESCRIPTION_NAME,
    SOURCE_NODE,
    TARGET_NODE,
    COPY_STATE,
    COPY_DATA_STATE,
    SYNCHRONIZATION_STATUS,
    SYNCHRONIZATION_PROGRESS,
    TARGET_DATA_OUT_OF_SYNC,
    DATA_IN_TRANSIT
FROM MYHA.GEOMIRROR_HISTORY
WHERE CAPTURE_TIMESTAMP >=
      CURRENT TIMESTAMP - 1 HOUR
ORDER BY
    DEVICE_DESCRIPTION_NAME,
    TARGET_NODE,
    CAPTURE_TIMESTAMP;

A trend can answer questions that one result cannot:

Is out-of-sync data decreasing?
Is the asynchronous backlog growing?
Has synchronization progress stopped?
How long has the copy remained suspended?
Is tracking-space use increasing?

Important state columns

COPY_STATE

Values include:

ACTIVE
RESUME PENDING
RESUMING
SUSPENDED
UNKNOWN

COPY_DATA_STATE

Values include:

SYNCHRONIZED
USABLE
INCOHERENT
UNKNOWN

A copy can be usable without currently being synchronized.

SYNCHRONIZATION_STATUS

Values include:

NONE
FULL SYNC PREPARING
FULL SYNC ACTIVE
FULL SYNC COMPLETING
PARTIAL SYNC PREPARING
PARTIAL SYNC ACTIVE
PARTIAL SYNC COMPLETING

TARGET_DATA_OUT_OF_SYNC

The number of bytes that are not synchronized.

DATA_IN_TRANSIT

For asynchronous delivery, the bytes queued for transmission but not yet received by the target.

TOTAL_TIME_TO_TRANSMIT

The time in microseconds required to send a message to the target and receive a response.

Interpret it as one operational signal, not a complete network-performance diagnosis.

Topology columns

The view exposes:

SOURCE_NODE
TARGET_NODE
CONNECTION_ENTRY_TYPE
CLUSTER_RESOURCE_GROUP
CLUSTER_RESOURCE_GROUP_SITE

These fields help explain which connection each row represents.

One IASP can have more than one row because the topology can include multiple target copies.

Authority

The caller must have either:

*IOSYSCFG special authority

or authorization to:

QIBM_IOSYSCFG_VIEW

Rows are returned only for device descriptions where the caller has *USE authority.

Prefer function-usage authorization when a monitoring profile needs view access but should not receive broad *IOSYSCFG authority.

IASP visibility

The service reports IASPs with geographic mirroring configured.

When a result is unexpectedly empty, review:

whether geographic mirroring is configured
device-description authority
function-usage authorization
which node is being queried
the state and ownership of the IASP

SQL monitoring is not HA control

This view reports state.

It does not replace:

PowerHA procedures
cluster-resource-group management
planned switch procedures
recovery runbooks
replication testing
role and authority controls

Do not automate suspend, detach, resume, or role changes solely from one query result.

Release and PTF requirement

IBM lists QSYS2.GEOGRAPHIC_MIRRORING_INFO as a new service delivered with:

IBM i 7.6 — Db2 Group PTF SF99960 Level 3
IBM i 7.5 — Db2 Group PTF SF99950 Level 12

A practical monitoring workflow

1. Query the current mirror role and copy state.
2. Check data state and synchronization status.
3. Review synchronization progress and out-of-sync bytes.
4. Review asynchronous data and operations in transit.
5. Check tracking-space utilization and oldest activity.
6. Compare with previous samples.
7. Correlate changes with workload, network, and HA events.
8. Escalate through the documented PowerHA runbook.
9. Confirm recovery objectives through regular testing.

Final takeaway

QSYS2.GEOGRAPHIC_MIRRORING_INFO turns geographic-mirroring status into queryable operational data.

Its greatest value comes from repeated sampling and context—not from treating one state value as the entire health assessment.

References

IBM documentation and support references used for this entry.

Comments

Share your thoughts, questions, or real-world IBM i experiences related to this article.