× 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.



"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

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.