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 Glenn Paulley |
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. answered 30 Apr '10, 17:03 Glenn Paulley 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... |
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 Volker Barth |
Wow, by incident I asked this question in the newsgroup before I tried here - and now I'm backported...Thanks, Glenn:)
...and that gives Glenn a "Student" badge. Ain't it funny:)