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 ;
NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
This topic has been locked.
3 replies Latest Post - 2012-05-02T00:19:44Z by SystemAdmin
Pinned topic Stored procedure SQL
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-05-02T00:19:44Z at 2012-05-02T00:19:44Z by SystemAdmin
mor 110000Q8NJ489 PostsACCEPTED ANSWER
Re: Stored procedure SQL2012-05-01T06:17:25Z in response to SystemAdminAlways post your DB2-version and fixpack and Server/client operating system.
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.