hi all,

Following is the Stored procedure definition in oracle, i want to convert
this to DB2, could any plz help on this.

The idea of this stored procedure is
1) This will scan table-wise based on the column 'oid' & find's out the
max in that
2) It will add up 5000 + max(oid)
3) then this 5000 + max(oid) will set it in starting of the sequence
Example:
~~~~~~
table1 table 2 table 3
OID 25 - 30


Out put:
Result need is 5000 +30 = 5030

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The below one is the stored procedure for Oracle: ----- similarly need for
DB2 ???
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
create or replace procedure reset_seqbeachhead is
v_sqlstroid VARCHAR2(30000);
v_sqlstrseq VARCHAR2(30000);
v_sqlstraln VARCHAR2(30000);
v_highval number;
CURSOR c_tablenames IS

select 'union (select nvl(max(oid),0) oid from '||table_name||
')' head from user_tab_cols where COLUMN_NAME='OID';

begin
v_sqlstroid:='(select 0 oid from dual) ';
--dbms_output.put_line(v_sqlstroid);
FOR v_sqlstraln IN c_tablenames LOOP
v_sqlstroid:= v_sqlstroid || v_sqlstraln.head;
--dbms_output.put_line(v_sqlstraln.head);
END LOOP;
v_sqlstroid:='select max(oid) + 5000 val from (
'||v_sqlstroid ||')';

EXECUTE IMMEDIATE v_sqlstroid into v_highval;
dbms_output.put_line(v_highval);

v_sqlstrseq:='create sequence beachheadsequence start with '||v_highval||'
minvalue 1 maxvalue 999999999999999999999999999 increment by 1 nocache';

--EXECUTE IMMEDIATE 'drop sequence beachheadsequence';
EXECUTE IMMEDIATE v_sqlstrseq;

END reset_seqbeachhead;
________________________________________________
Please share any ideas on this issue.

Thanks in Advance,
Prabhakar.

DB2 & UDB email list listserv db2-l LazyDBA home page