I'm a bit new to stored procedures and need some help please. I've been trying to produce code (probably not very good) as per below
The create statement fails in Data Studio, objecting to the 'set rstrt' statement and saying that inschema.tname doesn't exist. I';e obviously got the syntax wrong but after trying various ways of passing the variables to the 'select' I'm beaten.
Any help appreciated
CREATE PROCEDURE PRO_restart (IN inschema varchar(50))
Declare rstrt INT ;
Declare tname varchar(55) ;
declare cname varchar (55) ;
declare s1 varchar(255) ;
declare SQLCODE int ;
declare c2 cursor for select tabname, colname from syscat.columns where identity = 'Y' and tabschema = inschema order by tabname ;
open c2 ;
while (SQLCODE = 0) do
fetch from c2 into tname, cname ;
set rstrt = (select (max(cname) + 1) from inschema.tname) ;
set s1 = 'alter table '||inschema||'.'||tname||' alter column '||cname||' restart with '||rstrt ;
execute immediate s1 ;
end while ;
close c2 ;
This topic has been locked.
Pinned topic Stored procedure SQL
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Re: Stored procedure SQL2012-05-01T01:11:15ZThis is the accepted answer. This is the accepted answer.Just realised my 'while' statement will cause an infinite loop but still haven't sorted out the rest
mor 110000Q8NJ577 Posts
Re: Stored procedure SQL2012-05-01T06:17:25ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
Your "set rstrt = (select (max(cname) + 1) from inschema.tname) ;"
will fail at compile time (i.e. when you issue the CREATE PROCEDURE)
because at that time there is no such object as "inschema.tname".
The real object name won't be known until runtime.
Use dynamic SQL for this part - read about "PREPARE" and "EXECUTE" and "EXECUTE IMMEDIATE"
in the manuals. See the examples in the SAMPLES sub-directory tree of your DB2 server installation.
To become proficient at SQL PL, I recommend the book
"DB2 SQL Procedural Language for Linux, UNIX, and Windows"
by Paul Yip, Drew Bradstock and 5 others, IBM Press, 2003.
Re: Stored procedure SQL2012-05-02T00:19:44ZThis is the accepted answer. This is the accepted answer.Yep, I had sort of worked that bit out last night. Finally have it sorted out, probably not pretty but does the job