From Volker Barth:
Just to get a better understanding of the relation between max_identity and (GLOBAL) AUTOINCREMENT column defaults:
The value in syscolumn.max_identity (or systabcol for SA 10 and above) contains the maximum value used for (global) autoincrement columns.
AFAIK, it is only updated by a CHECKPOINT statement.
When building a database with LOAD TABLE statements, each LOAD TABLE statement issues a checkpoint first, so this will automatically update max_identity for all already loaded tables. A regular database shutdown will also perform a checkpoint.
Question: Are there other operations/statements that will update max_identity?
asked 30 Apr '10, 16:54
Max_identity column values, column histograms, and other catalog metadata are persistently saved to the catalog whenever the server does an explicit or implicit CHECKPOINT.
LOAD TABLE is one statement that issues an implicit CHECKPOINT because it performs page-level undo if the LOAD TABLE fails. In addition to LOAD TABLE, the following statements in Version 12 (Innsbruck) will trigger an implicit or explicit CHECKPOINT, and hence cause the metadata for AUTOINCREMENT columns to be written to the catalog:
Most, though unfortunately not all, of these statements document the side-effect of a checkpoint in their specific documentation, usually in the "Side Effects" section. However, I noticed a few (such as CREATE DBSPACE) that did not do so - and I have corrected this in the 12.0 documentation.
So, to sum up Glenn's elaborate answer:
No, max_identity is only updated by a (explicit or implicit) checkpoint.
answered 30 Apr '10, 19:50