×
The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.
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
midrange.com
