This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Has anybody ran across a script for db2 to update the maximo sequences to
the highest value? Similar to the below script I found online for oracle?
Any help would be appreciated, thanks!
*// Procedure Written By simon@utilityap.com <simon@utilityap.com>create or
replacePROCEDURE "MAXIMOIMPORT_RESET_SEQUENCE" ( p_tbname IN
STRING, p_fieldname IN STRING) ASBEGIN declare v_tbname varchar2(200):=
p_tbname; v_fieldname varchar2(200):= p_fieldname; v_SequenceName
varchar2(100); v_CurrentSequence int:=0; v_MaxSequence int:=1; v_SQL
varchar2(1000); begin --Find the sequence name for this field
select sequencename into v_SequenceName from maxsequence where tbname =
v_tbname and name = v_fieldname; dbms_output.put_line(v_SequenceName);
--Find the max value from the table v_SQL := 'select max(' || v_fieldname
|| ') from ' || v_tbname; execute immediate v_SQL into v_MaxSequence;
dbms_output.put_line(v_maxSequence); --Find current sequence value
v_SQL := 'SELECT ' || v_SequenceName || '.NEXTVAL FROM dual'; execute
immediate v_SQL into v_CurrentSequence;
dbms_output.put_line(v_CurrentSequence); --Reset sequence to 0 v_SQL
:= 'ALTER SEQUENCE ' || v_SequenceName || ' INCREMENT BY -' ||
(v_CurrentSequence) || ' MINVALUE 0 NOCACHE'; execute immediate v_SQL;
dbms_output.put_line(v_SQL); v_SQL := 'SELECT ' || v_SequenceName ||
'.NEXTVAL FROM dual'; execute immediate v_SQL into v_CurrentSequence;
dbms_output.put_line(v_SQL); dbms_output.put_line(v_CurrentSequence);
--Reset sequence to max value found in table v_SQL := 'ALTER SEQUENCE ' ||
v_SequenceName || ' increment by ' || (v_MaxSequence-1) || ' MINVALUE 0
NOCACHE'; execute immediate v_SQL; dbms_output.put_line(v_SQL); v_SQL :=
'SELECT ' || v_SequenceName || '.NEXTVAL FROM dual'; execute immediate
v_SQL into v_CurrentSequence; dbms_output.put_line(v_SQL);
dbms_output.put_line(v_CurrentSequence); --Reset sequence increment to
1 v_SQL := 'ALTER SEQUENCE ' || v_SequenceName || ' increment by 1
MINVALUE 0 CACHE 20'; execute immediate v_SQL;
dbms_output.put_line(v_SQL); v_SQL := 'SELECT ' || v_SequenceName ||
'.NEXTVAL FROM dual'; execute immediate v_SQL into v_CurrentSequence;
dbms_output.put_line(v_SQL); dbms_output.put_line(v_CurrentSequence);
--Update maximo table update MAXSEQUENCE SET MAXRESERVED = v_MaxSequence
WHERE TBNAME = v_tbname and name = v_fieldname; commit;end;END
MAXIMOIMPORT_RESET_SEQUENCE;*
--
Luke Gatza