Fetching TIMESTAMP(10..12) columns is ~10x slower: per-row CLI0182W (01S07) diagnostic handling #1079

Summary

Fetching a TIMESTAMP(10)–TIMESTAMP(12) column is about 10x slower per row than TIMESTAMP(9) or below: ~49 µs/row vs ~5 µs/row.

This hits every user of system-period temporal tables, and it is not avoidable through schema design. Db2 requires the SYSTEM_TIME columns (ROW BEGIN, ROW END and TRANSACTION START ID) to be TIMESTAMP(12). The SYSTEM_TIME period docs say "The SYSTEM_TIME period contains a pair of TIMESTAMP(12) columns". So any application that reads row-version timestamps pays this on every row: auditing, change tracking, FOR SYSTEM_TIME history queries, or reading the history table.

Application-period (BUSINESS_TIME) columns are affected only when declared with 10–12 fractional digits. The BUSINESS_TIME period docs allow "DATE or TIMESTAMP(p) columns where p can be from 0 to 12"; DATE and TIMESTAMP(0..9) are unaffected. The same applies to any ordinary TIMESTAMP(10..12) column.

The cause is not the datetime conversion. Every SQLFetch returns SQL_SUCCESS_WITH_INFO with CLI0182W Fractional truncation (SQLSTATE 01S07), and ibm_db runs full diagnostic handling for that warning on every row.

Reproduction

import time, ibm_db

conn = ibm_db.connect(DSN, "", "")
for p in (12, 10, 9, 6):
    stmt = ibm_db.exec_immediate(
        conn,
        f"select current timestamp({p}) from syscat.columns a, syscat.columns b "
        "fetch first 100000 rows only",
    )
    ibm_db.fetch_tuple(stmt)
    warn = ibm_db.stmt_warn(stmt)
    t = time.perf_counter()
    while ibm_db.fetch_tuple(stmt):
        pass
    print(p, f"{time.perf_counter() - t:.2f}s", repr(warn))

Output (100,000 rows):

12 4.89s '[IBM][CLI Driver] CLI0182W  Fractional truncation. SQLSTATE=01S07 SQLCODE=-99999'
10 4.89s  (same warning)
9  0.47s ''
6  0.47s ''

The cliff is exactly between 9 and 10 fractional digits. On a real table the effect was the same: a 106,696-row select from a temporal table took 6.9 s to fetch, 4.9 s of it the ROW BEGIN column. Run on the server alone (count plus aggregates over the same query, one row back), DB2 answered in 0.08 s.

Where it happens (v3.3.0; v3.2.9 is the same)

1. Timestamp columns are bound as SQL_C_TYPE_TIMESTAMP into a TIMESTAMP_STRUCT, whose fraction field holds nanoseconds (9 digits):
   https://github.com/ibmdb/python-ibmdb/blob/v3.3.0/ibm_db.c#L1945
2. For a TIMESTAMP(10..12) column the CLI therefore truncates on every row and returns SQL_SUCCESS_WITH_INFO, and the fetch path calls _python_ibm_db_check_sql_errors for each one:
   https://github.com/ibmdb/python-ibmdb/blob/v3.3.0/ibm_db.c#L15072-L15077
   That function calls SQLGetDiagRec, then post-processes and copies the message into the statement's warning buffer:
   https://github.com/ibmdb/python-ibmdb/blob/v3.3.0/ibm_db.c#L1008
3. The value is then built with fraction / 1000, so the Python datetime keeps only microseconds anyway. Nothing beyond 6 digits survives, and the truncation the warning reports has no effect on the result:
   https://github.com/ibmdb/python-ibmdb/blob/v3.3.0/ibm_db.c#L15247-L15250

A smaller per-row cost in the same branch: the debug snprintf that formats every fetched timestamp runs even when tracing is off, because only LogMsg checks debug_mode.

Suggested fixes (any of these)

• Treat 01S07 fractional truncation on a timestamp column as expected, and don't fetch diagnostics for it. The driver chose a 9-digit buffer and then discards everything past 6 digits.
• Or only gather the diagnostic text lazily, when stmt_warn() is actually called, rather than on every SQLFetch.
• Or bind TIMESTAMP(p > 9) columns in a way that does not truncate, e.g. as SQL_C_CHAR, and parse.
• Separately, guard the per-row debug snprintf calls with if (debug_mode).

Workaround

Because the temporal columns cannot be declared with a lower precision, the only workaround is to cast in every query that reads them: cast(row_begin_col as timestamp(6)), or timestamp(9). This returns the identical Python datetime, with no warning, at full speed. The precision lost is precision ibm_db discards anyway, so nothing is given up; but every query on a temporal table has to remember to do it.

Environment

• ibm_db 3.2.9 (code inspected in 3.3.0 too), Python 3.14.6, Windows 11 x64
• Client: DB2CLI.DLL 12.01.0000 (bundled clidriver)
• Server: DB2/LINUXX8664 12.01.0500