Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    1

    Unanswered: Create procedure - VALIDATE RUN

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

  2. #2
    Join Date
    Jun 2006
    Posts
    471

    sp

    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...
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

Posting Permissions

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