Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2003
    Posts
    3

    Question Unanswered: alter table in stored procedure

    I'm trying to write a stored procedure which contains a 'alter table' statement.

    CREATE PROCEDURE ppg_gc (IN expiryTime INTEGER, IN batchSize INTEGER)
    LANGUAGE SQL
    MODIFIES SQL DATA
    BEGIN
    DECLARE TotalRowsToDelete INTEGER;

    alter table test_tbl activate not logged initially;
    insert into test_tbl (12, 100);

    END @

    However I get the following error:

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "alter table test_tbl activat" was found
    following "test_tbl'); ". Expected tokens may include:
    "<psm_statement>". LINE NUMBER=22. SQLSTATE=42601

    The sql works fine in CLP but not as a stored procedure. Does anyone know why?
    Thanks

  2. #2
    Join Date
    Feb 2003
    Location
    San Francisco,US
    Posts
    15

    do this

    hi
    u can not execute dynamic query inside procedure.
    store the alter command in a varchar variable ,
    then say
    declare v_stmt varchar(50);
    SET v_stmt = 'ALTER TABLE -------';
    PREPARE s1 from v_stmt;
    EXECUTE s1;


    this will solve ur problem.

    thanks and regards.
    ashu
    Ashutosh Mishra
    Accenture India,Chennai
    call on - +91-9884118430

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: do this

    Can you please elaborate, ashu ... Please can you give an example or two of what you mean a dynamic query ...

    Cheers

    Sathyaram



    Originally posted by amishra
    hi
    u can not execute dynamic query inside procedure.
    store the alter command in a varchar variable ,
    then say
    declare v_stmt varchar(50);
    SET v_stmt = 'ALTER TABLE -------';
    PREPARE s1 from v_stmt;
    EXECUTE s1;


    this will solve ur problem.

    thanks and regards.
    ashu

  4. #4
    Join Date
    Feb 2003
    Location
    San Francisco,US
    Posts
    15

    dynamic query

    Hi
    alter table is a command which runs at run time and depends on the run time availability of the table.so at the compile time it can not check for the table and the related constraints.
    so u have to dynamically prepare an SQL statement for execution.
    that is done by preparing the statement then executing it.

    inside stored procedure.u say alter table ..........
    this will tell u alter table token is not expected.like this there are some other commands also,

    so u declare a varibale .

    declare v_stmt varchar(20);
    now store ur query inside this variable,then prepare the statement.

    v_stmt = 'alter table emp drop primary key';
    prepare S1 from v_stmt;
    EXECUTE IMMEDIATE S1;

    u can put alter table inside any sql procedure without doing this.because these r not compiled rather directly executed.
    if u want to know more,tell me

    thanks
    ashu
    Ashutosh Mishra
    Accenture India,Chennai
    call on - +91-9884118430

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: dynamic query

    Thank You ...

    I understand now ..

    Cheers

    Sathyaram

    Originally posted by amishra
    Hi
    alter table is a command which runs at run time and depends on the run time availability of the table.so at the compile time it can not check for the table and the related constraints.
    so u have to dynamically prepare an SQL statement for execution.
    that is done by preparing the statement then executing it.

    inside stored procedure.u say alter table ..........
    this will tell u alter table token is not expected.like this there are some other commands also,

    so u declare a varibale .

    declare v_stmt varchar(20);
    now store ur query inside this variable,then prepare the statement.

    v_stmt = 'alter table emp drop primary key';
    prepare S1 from v_stmt;
    EXECUTE IMMEDIATE S1;

    u can put alter table inside any sql procedure without doing this.because these r not compiled rather directly executed.
    if u want to know more,tell me

    thanks
    ashu

  6. #6
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by amishra View Post
    Hi
    alter table is a command which runs at run time and depends on the run time availability of the table.so at the compile time it can not check for the table and the related constraints.
    so u have to dynamically prepare an SQL statement for execution.
    that is done by preparing the statement then executing it.

    inside stored procedure.u say alter table ..........
    this will tell u alter table token is not expected.like this there are some other commands also,

    so u declare a varibale .

    declare v_stmt varchar(20);
    now store ur query inside this variable,then prepare the statement.

    v_stmt = 'alter table emp drop primary key';
    prepare S1 from v_stmt;
    EXECUTE IMMEDIATE S1;

    u can put alter table inside any sql procedure without doing this.because these r not compiled rather directly executed.
    if u want to know more,tell me

    thanks
    ashu
    Awesome....thanks for the help.

    Rajesh

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs up Bravo

    Quote Originally Posted by Rajesh1203 View Post
    Awesome....thanks for the help.

    Rajesh
    Congratulations !
    It got 7 years only, and you understood how !!
    Awesome.... But in 2003 DB2 version was 5 or 6....

    Doesn't matter !!!

    You understood finally !!!!

    Lenny

  8. #8
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by Lenny77 View Post
    Congratulations !
    It got 7 years only, and you understood how !!
    Awesome.... But in 2003 DB2 version was 5 or 6....

    Doesn't matter !!!

    You understood finally !!!!

    Lenny
    Is this what you do all the time? Throwing comments on others? Mind your business dude...This thread may be 7 is years old but I found it helpful today...

    @Amishra::
    Will this 'activate not logged initially' will turn logging back on after the truncation is done or Do I have to explicitly do it?

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Amishra's Last post was made 10-12-04, 15:04....

    Amishra, where are you ?

    Lenny

  10. #10
    Join Date
    Jun 2011
    Posts
    1
    Quote Originally Posted by Rajesh1203 View Post
    Is this what you do all the time? Throwing comments on others? Mind your business dude...This thread may be 7 is years old but I found it helpful today...

    @Amishra::
    Will this 'activate not logged initially' will turn logging back on after the truncation is done or Do I have to explicitly do it?
    Rajesh,
    Don't mind the douchebag. In America we call these guys douchebags.

    The beauty of the internet is that it can be timeless. Dormant threads can pick up a life of its own if it's still relevant. I get you when expressing joy after finding nuggets of helpful information, albeit many years old. I've certainly come back to many older postings.

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by jethro1234 View Post
    I've certainly come back to many older postings.
    You're too smart for this world.

    This is something amazing me all the time: many folks don't even try to search for an answer to their questions first - the question is simply posted. (Otherwise, I can't explain why things like pivoting tables is asked oh so often despite there being at least 100 answers in this forum alone.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •