× 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 Matt,

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
;

Best regards,
-Arco
Op di 4 dec. 2018 om 20:18 schreef Matt Olson <Matt.Olson@xxxxxxxx>:

We migrated some of our tables to another partition for testing and we have noticed that their identity columns which should generate the next available number is infact generating an existing number and failing the primary key check. Has anyone see this before?

There some easy command to restart identity value to the next available number?

We are mystified why this didn't come over during the restore of that file...

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.