Any itera users finding issues doing roleswaps and it messing up identity columns?

We had an issue this week when we did a role swap with itera.

When we swapped back from the backup box to the primary box, we started to get duplicate key errors.

The duplicate key errors were from the inability of the database to insert rows into a table because the last known identity value the system had was already used in the table. It's like itera was not syncing the fact that additional data has been entered into the database and still had the last identity value set to a previous value.

Needless to say this caused issues. So I wrote a QC script that will detect if this happens again, pending a itera call to ask them what the heck is going on with their product.

Wondering if anyone else has experienced this before.

Here is the script that identifies identity column issues:

CREATE OR REPLACE PROCEDURE
ARRTFLIB.Detect_Identity_Counter_Issues
(
IN peSchemaName VARCHAR(128)
)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN

DECLARE sqlcode INTEGER;
DECLARE maxid BIGINT;
DECLARE idnextvalue BIGINT;
DECLARE idcolname VARCHAR(128);
DECLARE stmttxt VARCHAR(1000);
DECLARE stmttxtmain VARCHAR(1000);
declare tablename varchar(1000);
DECLARE at_end INTEGER DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000'; /*https://www.ibm.com/docs/en/i/7.1?topic=euc-step-3-specifying-what-do-when-end-data-is-reached*/
DECLARE msgvar varchar(100);
DECLARE END_CL INT DEFAULT 0;

DECLARE cur CURSOR FOR prepstm;
DECLARE curmain CURSOR FOR
SELECT sc.column_name, sc.table_name FROM
qsys2.syscolumns AS sc WHERE sc.table_schema = peSchemaName AND sc.is_identity = 'YES';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET END_CL = 1;
OPEN curmain;
WHILE END_CL = 0 DO
FETCH curmain INTO idcolname, tablename;

SET stmttxt = 'SELECT IFNULL(MAX("' ||
idcolname || '") , 0) FROM "' ||
peSchemaName || '"."' || tablename || '"';

PREPARE prepstm FROM stmttxt;
SET maxid = 0;
OPEN cur;
FETCH cur INTO maxid;
CLOSE cur;

select NEXT_IDENTITY_VALUE INTO idnextvalue
from QSYS2.SYSPSTAT
where TABSCHEMA = peSchemaName
and TABNAME =tablename;
IF idnextvalue <= maxid THEN
SET msgvar = 'WARNING! Table: ' || tablename || ' Column: ' || idcolname || ' Max ID: ' || TRIM(maxid) || ' Current next value: ' || TRIM(idnextvalue);
call systools.lprintf(msgvar);
ELSE
SET msgvar = 'OK! Table: ' || tablename || ' Column: ' || idcolname || ' Max ID: ' || TRIM(maxid) || ' Current next value: ' || TRIM(idnextvalue);
call systools.lprintf(msgvar);
END IF;
END WHILE;
CLOSE curmain;

/*
SET peTableName = UCASE(peTableName);
SET peSchemaName = UCASE(peSchemaName);

SET stmttxt = 'SELECT IFNULL(MAX("' ||
idcolname || '") , 0) FROM "' ||
peSchemaName || '"."' || peTableName || '"';
DECLARE cur CURSOR FOR prepstm;
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
;