What causes syscolumn.max_identity to be updated?

[Former Member]

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?

[VolkerBarth]

Wow, by incident I asked this question in the newsgroup before I tried here - and now I'm backported...Thanks, Glenn:)

[VolkerBarth]

...and that gives Glenn a "Student" badge. Ain't it funny:)

[Former Member · Accepted solution]

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:

• LOAD TABLE statement
• CHECKPOINT statement
• REFRESH MATERIALIZED VIEW statement
• BACKUP statement
• REORGANIZE TABLE statement
• COMMIT statement when the database does not have a transaction log
• CREATE INDEX statement on a function (a computed column)
• ALTER TABLE statement - unless the ALTER TABLE trivially adds a nullable column or adds a referential integrity constraint and does not need to physically modify the table's pages in any way
• ALTER INDEX REBUILD statement
• DROP TABLE and DROP MATERIALIZED VIEW statements, but only if the table or view contains at least one row
• CREATE DBSPACE statement
• DROP DBSPACE statement

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.

[VolkerBarth · Accepted solution]

I made some tests with V 8.0.3 and GLOBAL AUTOINCREMENT and modifications of GLOBAL_DATABASE_ID (with immediate explicit checkpoint) and noticed the following: For columns that were updated between changes of GLOBAL_DATABASE_ID, the value of max_identity changed and reflected the new ID range. For columns that were not updated the value of max_identity remained the same and then was not in the current GLOBAL_DATABASE_ID range. This would mean that in those cases, the next default value will be the first from the ID range. - Just a somewhat unexpected behaviour, no bug obviously...

[VolkerBarth]

So, to sum up Glenn's elaborate answer:

No, max_identity is only updated by a (explicit or implicit) checkpoint.