Topic
IC4NOTICE: 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.
3 replies Latest Post - ‏2012-05-02T00:19:44Z by SystemAdmin
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic Stored procedure SQL

‏2012-04-30T23:53:42Z |
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))
P1: BEGIN
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 ;
END P1
Updated on 2012-05-02T00:19:44Z at 2012-05-02T00:19:44Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Stored procedure SQL

    ‏2012-05-01T01:11:15Z  in response to SystemAdmin
    Just realised my 'while' statement will cause an infinite loop but still haven't sorted out the rest
    • mor
      mor
      489 Posts
      ACCEPTED ANSWER

      Re: Stored procedure SQL

      ‏2012-05-01T06:17:25Z  in response to SystemAdmin
      Always 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.
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Stored procedure SQL

    ‏2012-05-02T00:19:44Z  in response to SystemAdmin
    Yep, I had sort of worked that bit out last night. Finally have it sorted out, probably not pretty but does the job
    thanks