The fact that SYSTABCOL.max_identity is a BIGINT seems to imply negative autoincrement values are possible, but they are not... all that the BIGINT data type does is needlessly cut the autoincrement range in half. Here's a demonstration... (note that sa_reset_identity accepts negative values but get_identity does not return them) ----------------------------------------------------------------------------------------- -- SYSTABCOL.max_identity is BIGINT so it can be set to a negative number. CREATE TABLE t ( pkey BIGINT NOT NULL DEFAULT AUTOINCREMENT ); CALL sa_reset_identity ( 't', new_identity = -5 ); CHECKPOINT; SELECT max_identity FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id WHERE SYSTAB.table_name = 't'; max_identity -5 ----------------------------------------------------------------------------------------- -- A negative SYSTABCOL.max_identity raises an exception and is automatically set to zero. INSERT t VALUES ( DEFAULT ); Column 'pkey' in table 't' cannot be NULL SQLCODE=-195, ODBC 3 State="23000" CHECKPOINT; SELECT max_identity FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id WHERE SYSTAB.table_name = 't'; max_identity 0 ----------------------------------------------------------------------------------------- -- Positive values in SYSTABCOL.max_identity work OK. INSERT t VALUES ( DEFAULT ) COMMIT; SELECT * FROM t; pkey 1 ----------------------------------------------------------------------------------------- -- GET_IDENTITY returns UNSIGNED BIGINT. SELECT EXPRTYPE ( 'SELECT GET_IDENTITY ( ''t'' )', 1 ); EXPRTYPE('SELECT GET_IDENTITY ( ''t'' )',1) unsigned bigint |
Further evidence: You can certainly add negative values in a AUTOINCREMENT column when specifying them explicitly but that will still leave the default to be restricted to non-negative values:
Even more, GLOBAL AUTOINCREMENT values and the according GLOBAL_DATABASE_ID property are restricted to non-negative values, too.
That being said, I have never had an issue with too less BIGINT values so that may be more a theoretical problem, right?