AI-assisted recovery of a dropped Db2 table

Why this case mattered

This article is based on an example that occurred in an internal test environment where we had built:

• Specialized MCP servers exposing Db2 investigation and recovery tools
• An AI agent able to use those tools
• A catalog of controlled failure injections to trigger database incidents

The goal was simple: inject realistic failures and verify whether the agent could help a DBA investigate and recover.

One of those tests became more interesting than expected. We injected a table-drop scenario and the agent did not simply execute a few recovery commands. It found a real Db2 recovery limitation, adapted its strategy to reconstruct the data from business logic, and applied that strategy. That is the non-trivial part worth documenting.

Later, a better solution than data reconstruction was identified, but it was not yet supported by the MCP tooling: analyzing Db2 log content to reconstruct the SQL responsible for changing the lost data and replaying it. That capability was added and tested. The AI agent was then able to recover all lost data from the Db2 log.

The injected failure

The incident was injected with the following script:

#!/bin/bash
# DL-01 — DROP TABLE (no dependents)
# Drops STORE.PAYMENTS — no views or FKs depend on it.
# Symptom: SQL0204N on any query referencing PAYMENTS.

set -e

CONTAINER=db2-primary
DB=LABDB

echo "==> [DL-01] Injecting: DROP TABLE STORE.PAYMENTS"

docker exec "$CONTAINER" su - db2inst1 -c "
db2 connect to $DB > /dev/null

db2 \"INSERT INTO STORE.AUDIT_LOG (EVENT_TYPE, TABLE_NAME, DESCRIPTION)
     VALUES ('INCIDENT_INJECTED', 'STORE.PAYMENTS', 'DL-01: PAYMENTS table dropped — no cascade')\"

db2 \"DROP TABLE STORE.PAYMENTS\"

db2 commit
db2 connect reset > /dev/null
"

echo ""
echo "==> [DL-01] Done."
echo "    Symptom : SQL0204N when querying STORE.PAYMENTS"
echo "    Evidence: SYSCAT.TABLES has no entry for PAYMENTS"
echo "              SYSCAT.INDEXES missing PAYMENTS indexes"
echo "              AUDIT_LOG has the incident marker"

Everything was intentionally designed to create a simple dropped-table incident:

• A single table removed, with no dependent foreign keys or views to complicate the diagnosis.

At first sight, it looked like the kind of case that a conventional recovery playbook should handle well.

The request to the agent

After the injection, the user asked:

It seems that some tables were dropped. Investigate, create a plan and execute a plan to recover them.

The phrasing matters because the request does not tell the agent:

• Which table was dropped
• How the drop occurred
• Which recovery path should be followed

The agent had to investigate, form a plan from the evidence, and only then execute the recovery. In other words, this was a reasoning task, not a simple command-execution task.

What the agent found first

The first useful discovery was in the trace left by the injection itself:

• The table STORE.AUDIT_LOG contained the incident marker
• The marker identified STORE.PAYMENTS
• The description confirmed incident DL-01

The meaning and relevance of that table were inferred by the agent. From there, the agent verified that:

• The table STORE.PAYMENTS no longer existed, and the expected catalog information had disappeared from Db2
• The other objects in schema STORE were still present

This led to the first important conclusion: it was not a schema-wide failure, but the loss of one specific object. The investigation then focused on that object:

The agent summarized the situation as follows:

The first recovery path looked promising

The next question was whether the table could be recovered through the normal Db2 restore and rollforward mechanism.

The environment had a full backup under /database/backup and archive logging enabled through LOGARCHMETH1=LOGRETAIN.

At that point, the situation looked favorable. A common recovery pattern would be:

This is a common and perfectly reasonable technical approach. After that, the case became more interesting.

The first complication: the backup was too old

The backup existed, but it had been taken before the entire STORE schema had been created. Restoring that backup alone would not bring back table STORE.PAYMENTS. The only viable path depended on rolling forward through the logs.

Even so, the table and the data still looked recoverable because:

• Archive logging was active
• The logs needed to apply changes after the backup had been retained

The plan built by the agent

Based on that evidence, the agent built the following plan:

This was already solid DBA reasoning and, if not for Db2 point-in-time recovery limitations, the story would have ended there.

But it did not.

The real Db2 problem: PITR could not isolate the rows

The agent restored the backup into a separate recovery database and applied rollforward.

What it found was a subtle difficulty:

• The DDL for CREATE TABLE STORE.PAYMENTS was recoverable
• However, the table rows were not recoverable

Why?

Because the population of the table and the drop were both committed within the same second.

The relevant timeline was:

Db2 point-in-time rollforward accepts timestamps with a minimum granularity of one second.

That means the real recovery options were:

There was no valid target that included the inserted rows but excluded the drop, and that is the real technical heart of the case.

The failure was no longer:

• “How do we recover a dropped table?”

It became:

• “How do we recover when physical point-in-time recovery can restore the structure but no longer the original row set?”

That is a much harder problem.

What Db2 could still recover

Even without isolating the rows, the recovery database still gave the agent something very valuable:

• The exact table definition
• The primary key
• The foreign key
• The check constraints
• The definition of the existing index on the table

The agent extracted that definition with db2look and recreated the table in the live database. After that, the table structure had been recovered but the data was still missing.

The agent did not stop. It pushed further. The execution record up to that point can be summarized as follows:

The decisive step: reconstructing the data through business logic

This is the most important part of the article. The agent changed its approach from “reasoning about how to perform physical recovery” to “since physical recovery is not possible, how do we reconstruct the data using business logic”.

It analyzed the surviving transactional context around STORE.PAYMENTS, especially STORE.ORDERS, and inferred which orders should logically have corresponding payments.

The central interpretation was:

• DELIVERED orders should already have valid payments
• SHIPPED orders should also have payments
• PROCESSING, PENDING, and CANCELLED orders should not have payments

That produced:

Total:

• 15 + 4 = 19

That matched exactly the known number of lost rows.

The agent then reconstructed:

• AMOUNT from STORE.ORDERS.TOTAL_AMOUNT
• Plausible payment dates relative to order dates
• Allowed payment methods
• Valid payment status values
• Syntactically plausible references

In summary:

• Db2 recovery restored the table definition
• The agent reconstructed a business-consistent row set

As a rule, a non-AI tool would not be able to do that on its own.

It is important to remember that the agent, while doing the best it could, cannot perform miracles. The reconstruction was a fallback measure and cannot be compared with exact and complete data recovery. The original values of some columns were permanently lost, including:

• Exact payment timestamps
• Original payment methods per order
• Original reference identifiers

The details of the recovery and reconstruction are shown below:

Validation after reconstruction

The agent did not stop after inserting 19 plausible rows. It validated that:

• The recreated table matched the original DDL
• Foreign key relationships remained valid
• No orphan references existed
• Statistics had been refreshed with RUNSTATS

The final state was:

• STORE.PAYMENTS existed again
• 19 rows had been restored in a business-consistent form
• Referential integrity was clean
• The recovery was recorded in STORE.AUDIT_LOG

From an operational point of view, the table became usable again.

The final recovery summary can be presented as follows:

A better technical path would have been forensic log analysis

Although the reconstruction succeeded and has didactic value, it would not normally be the path to follow.

The better path would have been forensic analysis of Db2 logs, because that would have allowed the agent to extract the original values of the inserted rows instead of reconstructing them from business logic.

At the time of the incident, that option was not available through the MCP servers. The tools exposed by the server supported investigation, restore, rollforward, catalog inspection, and DDL extraction, but they did not yet expose forensic utilities for reading raw log content.

That limitation has since been corrected.

db2fmtlog can help format and inspect low-level log content during forensic investigation.

The capabilities that were added are:

For this PAYMENTS incident, the improved workflow would have worked like this:

The expected outcome would be:

The essential improvement that has now been added is:

• standard Db2 recovery logic remains the first path
• db2fmtlog-based forensic investigation becomes the fallback when Db2 PITR cannot isolate the wanted rows

With this addition, the agent no longer has to stop at business-faithful reconstruction in same-second collision scenarios. It can first attempt exact row recovery and only fall back to reconstruction if forensic extraction also fails.

What happened after the MCP improvement

After the MCP server improvement, the incident was revisited and the agent was able to recover the table with the exact original 19 rows, instead of only with an approximation based on business-data analysis.

This second recovery is important because it shows the difference between:

• A structurally correct reconstruction
• And a true recovery of the original row values

The post-mortem can be summarized like this:

1. Identify the correct log file

The first practical problem was scale:

• 26 log files existed across active and archived locations
• an unfiltered forensic pass produced too much output

The agent used the XHDR sections from db2fmtlog output to map LFS intervals to physical files. The relevant INSERT records had:

• LFS = 6976

That allowed the relevant activity to be mapped to:

• S0000007.LOG

which turned out to be an active log file.

2. Locate the 19 INSERT records exactly

The next step was to find the exact byte offsets of the 19 inserted rows inside S0000007.LOG.

db2fmtlog provided the metadata needed to guide the search:

• Transaction ID
• Record type
• Object identity

The binary search then looked for the combination of:

• The TID bytes of transaction 000000000C6D
• The INSREC_DP marker associated with the target object

That made it possible to locate the 19 records at exact offsets inside the 16 KB log file.

3. Decode the row format correctly

This was the hardest step, because the internal Db2 row format was not documented in a friendly way.

Several decoding mistakes had to be corrected:

After these corrections, the agent was able to decode the original values correctly.

4. Apply the exact values back to the live table

Two operational problems still had to be solved:

The final insertion path used a CLP script file with db2 -tvf, because direct quoting of multi-line INSERT statements through docker exec ... bash -c became too fragile.

What the first reconstruction had assumed incorrectly

The first business-logic reconstruction was sufficient to restore service, but it was not exact.

The later forensic recovery clearly showed the differences:

The first reconstruction had correctly guessed:

• Method patterns
• Status values
• Reference structure

But it was still wrong on exact original values in places where only real low-level log analysis could reach.

Final state after the improved recovery

After the MCP improvement and the new recovery path:

• STORE.PAYMENTS once again contained 19 rows
• The rows matched the exact original values that existed before the drop
• Identity behavior was restored to GENERATED ALWAYS
• No backup restore was required for the final exact-data recovery stage

This makes the main lesson of the case even clearer:

• Business-logic reconstruction was already valuable
• But after the MCP improvement, the agent was able to go beyond approximation and recover the original row set exactly

Why this case matters

When conventional recovery hit a real Db2 limit, the AI agent was able to continue reasoning over business semantics instead of stopping at the technical boundary.

This required three distinct levels of capability:

1. Db2 investigation tools

• Catalog checks
• Configuration inspection
• Understanding of backups and logs
• Recovery execution

1. Db2 recovery knowledge

• Redirected restore
• Rollforward limitations
• DDL extraction
• Validation steps

1. Business-logic inference

• Understanding what a payment table represents
• Inferring which orders should have payments
• Rebuilding rows consistent with the surviving business evidence

When the MCP server was improved, that capability stack was extended with true low-level forensic analysis. The combination became even stronger:

• Db2 recovery knowledge to recover structure
• Business-logic reasoning to provide a fallback path
• Forensic log analysis to recover exact values when the standard path could not

This is what made the final result remarkable:

• the agent first restored service through reconstruction
• and later, once the MCP tooling improved, recovered the original rows exactly

References

• IBM Docs: ROLLFORWARD DATABASE command
• IBM Docs: db2look command
• IBM Docs: RECOVER DROPPED TABLE command