Sometimes it can be difficult to remember where information is stored in the Db2 Catalog. Usually, with a little rumination and a little review of Appendix A of the IBM Db2 SQL Reference manual (SC27-8859), you can come up with a solution.
For example, I was talking to some DBAs who were trying to remember if they had ever created any business-time temporal tables. A comment was made that we could surely find that in the Db2 Catalog and the conversation moved along... but then I thought, hmmm, let me see what I can do about coming up with a catalog query.
The first step was to think about where this information might be found, which took me to SYSTABLES. A good first thought, but no, it isn't there. So I thought, how about SYSCOLUMNS? And lo' and behold, there was the answer.
The columns identified as the start and end date/time for the temporal range are documented in SYSCOLUMNS in the PERIOD column. PERIOD is defined as a CHAR(1) column and it contains one of the following values for every column defined for each table:
| Value | Meaning |
|---|---|
| B | Column is the start of period BUSINESS_TIME |
| C | Column is the end of period BUSINESS_TIME with an exclusive endpoint |
| I | Column is the end of period BUSINESS_TIME with an inclusive endpoint |
| S | Column is the start of period SYSTEM_TIME |
| T | Column is the end of period SYSTEM_TIME |
| blank | Column is not used as either the start or the end of a period |
So using this information, here is a query that will show information about all of the business-time temporal tables you have created:
SELECT SUBSTR(TBCREATOR,1,8) || '.' || SUBSTR(TBNAME,1,30)
AS TABLENAME,
SUBSTR(NAME,1,40) AS COLUMNNAME,
COLNO,
PERIOD
FROM SYSIBM.SYSCOLUMNS
WHERE PERIOD IN ('B', 'C', 'I')
ORDER BY TABLENAME, PERIOD, COLUMNNAME;
If you want to find the system-time temporal tables, just swap out the WHERE clause with this one:
