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

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

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

Cheers

Sathyaram

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

Thank You ...

I understand now ..

Cheers

Sathyaram

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

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?

Amishra's Last post was made 10-12-04, 15:04....

Amishra, where are you ?

Lenny

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.

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.)