Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: Stored Procedure Question

    I have a stored procedure which references a view. A column was dropped by the underlying table and the view was changed so it didn't reference the column anymore. The stored procedure still referenced the view and dropped column. My question is how is the stored procedure not invalid, since it references a column that has been dropped and is not inthe view any longer? We did not get an error until we tried to execute the stored procedure.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When a dependent object is dropped, all the related packages (or plans if on z/OS and using plans) are marked as invalid (not the stored procedure itself). Upon first execution of the invalid package, an automatic rebind is attempted to see if the problem has been corrected, and if it has not, then the package is marked as inoperative and no further automatic rebinds take place (and execution error is returned).

    So next time, check for invalid packages, not invalid SP's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2004
    Posts
    190
    I should have said package. I did a rebind on all packages and all were valid. Shouldn't the package in this case have been invalid ?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Are you sure that the column is actually referenced in a Select, Insert, Update, or Delete statement of the SP? If the column no longer existed you should get an error.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Apr 2004
    Posts
    190
    Yes, the SP references the column. We get an error message at runtime complaining about the missing column.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If the package associated with the SP is still valid, I would guess that the SP is using dynamic SQL. Is this an SQL SP, or Java, C, etc SP?

    Even if it is a SQL SP, is any of the SQL dynamically prepared?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Apr 2004
    Posts
    190
    Its a SQL SP. Its declaring a global temp table and then inserting into the temp table. The select statement in the within the insert.

    declare global temp table:

    insert into temp table
    (
    select ..... (the column that got dropped is in this select statement)
    )

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by redwolf
    Its a SQL SP. Its declaring a global temp table and then inserting into the temp table. The select statement in the within the insert.

    declare global temp table:

    insert into temp table
    (
    select ..... (the column that got dropped is in this select statement)
    )
    I think you answered your own question.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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