Re: Identity Columns

Hi Craig,

You can reset the identity counter after out of sync updates. Here is an
example of a stored procedure that we use after such special maintenance.

Regards,
-Arco

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
;



2018-06-22 15:35 GMT+02:00 Craig Richards <craig@xxxxxxxxxxxxxxxx>:

Had I realised, I might not have used them because I worry that no matter
how much I try to document the issue, there are folks who are still very
likely to do things with these files that will get the ID out of sync with
the data.
Well, of course I need to take responsibility for not having
researched/tested it enough so, hey, let's call it a draw...

regards,
Craig