Create procedure - VALIDATE RUN

markroger · #1 (**permalink**) 11-27-06, 09:54

How to create a stored procedure referring to a table that does not exist ? I tried using the Bind Option VALIDATE RUN without success.

Code:

CALL SET_ROUTINE_OPTS('DYNAMICRULES BIND VALIDATE RUN')
@


create procedure testproc(i int)
language sql
begin

update gl.t1 set x=1 where y=0 ;

end
@

the error message is

Quote:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0204N "GL.T1" is an undefined name. LINE NUMBER=5. SQLSTATE=42704

Here’s the scenario:

Procedures created in DB2 V7.2 Solaris. For the procedure packages, SYSCAT.PACKAGES column VALIDATE is R and VALID set to Y. A number of tables referred in the procedure body do not exist.

Now I want to create all these procedures in DB2 V8.2 Windows. So, created all tables and when trying to create procedure, I get the -204 errors like the one shown above

Anyone has a suggestion ?

Thanks

guyprzytula · #2 (**permalink**) 11-27-06, 09:59

in v7 the stored proc in sql is translated to c-language - bind is done...
if bind is with sqlerror continue it would allow missing object
in V8 the sp is internal to db2 and will check the existance of objects
I have to check, but don't think you can add a parameter to continue even if error is detected...

Internet Services

Web Development

Digital Marketing

Consumer Tech