Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Posts
    64

    Thumbs down Unanswered: Droping SESSION(GLOBAL TEMPORARY table) table

    Dear Friends,

    I have a stored procedure , inside the procedure i DECLARE A GLOBAL TEMPORARY TABLE session.tab1 . And i insert some values to it . I created a CURSOR to return values from this SESSION table . After opening that cursor i am dropping the table using DROP query
    DROP TABLE SESSION.tab1
    While executing this procedure it throws an error . If i remove DROP TABLE SESSION ......its working properly . But i have to DROP the SESSION table . Please suggest some suitable solution to this

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version of DB2 LUW you are using and what the exact error message is?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    64
    I am using DB2 8,
    CALL PROC(12);
    [Error] Script lines: 1-1 --------------------------
    DB2 SQL error: SQLCODE: -910, SQLSTATE: 57007, SQLERRMC: null
    Message: The SQL statement cannot access an object on which a modification is pending.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I think the explanation of the message is clear:
    $ db2 "? sql910"


    SQL0910N The SQL statement cannot access an object on which a
    modification is pending.

    Explanation:

    The application program attempted to access an object within the same
    unit of work in which either:
    * The application program issued a DROP against the object or a related
    object (for example, an index for a table)
    * The application program issued a statement against the object that
    added or dropped a constraint.
    * The application program issued a DROP TRIGGER or CREATE TRIGGER
    statement that affected the object, either directly or indirectly.
    * The application program issued a ROLLBACK TO SAVEPOINT statement,
    which placed the object in the modification pending state.
    * The application program issued a statement that caused all rows of a
    NOT LOGGED declared temporary table to be deleted.
    * The application program issued an ALTER TABLE ... ADD PARTITION,
    ALTER TABLE ... ATTACH PARTITION or ALTER TABLE ... DETACH PARTITION
    statement against the object (in this case a table). If a unit of
    work contains an ALTER TABLE ... ATTACH PARTITION, then subsequently
    in the same unit of work, an ALTER TABLE ... DETACH PARTITION cannot
    be done on the same table.
    * The application program issued a DETACH to create this object from a
    data partition in a partitioned MDC table.
    * The application program issued an SQL statement that attempted to
    access a trusted context object on which a modification is pending.
    The SQL statement may be one of the following:
    * ALTER TRUSTED CONTEXT
    * CREATE TRUSTED CONTEXT
    * DROP TRUSTED CONTEXT
    In short: you (still) access a table and, therefore, cannot drop it from underneath. After all, the cursor may still need the table's data.

    The question is why you want to drop the DGTT in the first place. Just leave it around and when the connection is terminated, it will be cleaned up automatically.
    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
  •