By Wei Li and Paul McWilliams
Starting in Db2 13 for z/OS at function level 507, Db2 application developers who run their applications at APPLCOMPAT level V13R1M507 or higher can now issue SELECT statements that reference both a temporal table and an archive-enabled table, if the statement does not return any historical data.
A Db2 temporal table can be defined with an application period, a system period, or both. Respectively, it is thus called an application-period temporal table (ATT), system-period temporal table (STT), or bi-temporal table.
Transparent archiving allows Db2 users to create an archive table to manage historical data for an existing table, where the archive table stores deleted rows from the base table. The base table is called an archive-enabled table (AET).
Customers who have widely adopted these capabilities often find situations where they need to query data stored in both a temporal table (ATT or STT) and an archive-enabled table (AET) within the same SQL statement. However, Db2 for z/OS issues SQLCODE -20555 with reason-code 1 for an SQL statement that references both a temporal table and archive-enabled table. However, this restriction was found to be unnecessary in situations where customers do not intend to retrieve any historical or archive data, and the temporal and archive enabled table can be treated like regular Db2 base tables.
The following conditions must be true for a SELECT statement that references both a temporal table (application-period or system-period) and an archive-enabled table, so that no historical data is requested:
- If an application-period temporal table is referenced, a FOR BUSINESS TIME period is not specified, and the CURRENT TEMPORAL BUSINESS_TIME special register is NULL.
- If a system-period temporal table is referenced, a FOR SYSTEM TIME period is not specified, and the CURRENT TEMPORAL SYSTEM_TIME special register is NULL.
- If an archive-enabled table is referenced, the SYSIBMADM.GET_ARCHIVE global variable is set to 'N'.
Here are some examples to consider, where ATT is the name of an application-period temporal table, STT is the name of a system-period temporal table, and AET is the name of and an archive-enabled table:
- The following statement succeeds if CURRENT TEMPORAL BUSINESS_TIME is NULL, CURRENT TEMPORAL SYSTEM_TIME is NULL, and SYSIBMADM.GET_ARCHIVE is set to ‘N’.
SELECT ATT.POLICY_ID
FROM ATT, STT, AET
WHERE ATT.CLIENT_ID = STT.CLIENT_ID
AND ATT.CLIENT_ID = AET.CLIENT_ID;
- The following statement succeeds if CURRENT TEMPORAL SYSTEM_TIME is NULL, and SYSIBMADM.GET_ARCHIVE is set to ‘N’.
SELECT AET.POLICY_ID
FROM AET
WHERE AET.CLIENT_ID IN (SELECT STT.CLIENT_ID
FROM STT);
- The following statement continues to receive SQLCODE –20555 with reason code 1 because the statement specifies the FOR BUSINESS_TIME period clause.
SELECT ATT.POLICY_ID, AET.POLICY_ID
FROM ATT FOR BUSINESS_TIME AS OF '2024-12-10'
INNER JOIN
AET
ON ATT.CLIENT_ID = AET.CLIENT_ID;
For more about this new capability, see the online product documentation:
#Db2forz/OS
#Db2Z