The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 20 Mar '15, 15:54

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

edited 20 Mar '15, 20:54

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:

CREATE TABLE t ( pkey BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t VALUES (-9223372036854775808); -- -2 ^^ 63
CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';
-- returns 0
INSERT t VALUES (DEFAULT);
SELECT * from t; -- lists rows with -2 ^^ 31 and with 1
CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';
-- returns 1

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?

(23 Mar '15, 04:35) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×12

question asked: 20 Mar '15, 15:54

question was seen: 678 times

last updated: 23 Mar '15, 04:52