Re: Identity column re-using existing numbers?

"MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx> wrote on 12/04/2018
02:28:18 PM:

For this purpose we use the following SQL function:

CREATE OR REPLACE PROCEDURE
Sync_Identity_Counter
(
IN peSchemaName VARCHAR(128),
IN peTableName VARCHAR(128)
)
LANGUAGE SQL
BEGIN
DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE cur CURSOR FOR prepstm;

SET peTableName = UCASE(peTableName);
SET peSchemaName = UCASE(peSchemaName);
SELECT
sc.column_name
INTO
idcolname
FROM
qsys2/syscolumns AS sc
WHERE
sc.table_name = peTableName
AND
sc.table_schema = peSchemaName
AND
sc.is_identity = 'YES';
IF SQLCODE = 100 THEN
SIGNAL SQLSTATE '78000'
SET MESSAGE_TEXT = 'Cannot find identity column.';
END IF;
SET stmttxt = 'SELECT IFNULL(MAX("' ||
idcolname || '") , 0) FROM "' ||
peSchemaName || '"/"' || peTableName || '"';
PREPARE prepstm FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;
SET stmttxt = 'ALTER TABLE "' || peSchemaName ||
'"/"' || peTableName ||
'" ALTER COLUMN "' || idcolname ||
'" RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;
END
;

Good idea. But for my purposes and for performance reasons, there
is no need to use a cursor -- since you're retrieving a single value. So,
I would recode that last part as follows.


SET stmttxt = 'VALUES ('
|| 'SELECT IFNULL(MAX("' || idcolname || '") , 0)'
|| ' FROM "' || peSchemaName || '"/"' || peTableName || '"'
|| ') INTO ?';
PREPARE prepstm FROM stmttxt;
SET maxid = 0;
EXECUTE prepstm USING maxid;
SET stmttxt = 'ALTER'
|| ' TABLE "' || peSchemaName || '"/"' || peTableName || '"'
|| ' ALTER COLUMN "' || idcolname || '"'
|| ' RESTART WITH ' || CHAR(maxid + 1);
EXECUTE IMMEDIATE stmttxt;


Sincerely,

Dave Clark