Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Unanswered: Declare Temporary table error

    Hi,


    I have used the following in a SP

    DECLARE GLOBAL TEMPORARY TABLE SESSION.XXXX
    (XYZ SMALLINT, XXZ CHAR(30),
    flag CHAR(1)) NOT LOGGED WITH REPLACE ON COMMIT PRESERVE ROWS;


    The below error is reported in the db2diag.log when i run the SP

    Data TitleQLCA PID:28420 Node:000
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -286 sqlerrml: 9
    sqlerrmc: 4096 DBMS
    sqlerrp : sqlrlugi
    sqlerrd : (1) 0xFFFF877E (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)


    There is tablespace
    Tablespace ID = 6
    Name = USERTEMPSPACE
    Type = System managed space
    Contents = User Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1
    Useable pages = 1
    Used pages = 1
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 8192
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    The USE of this tablespace has been granted to the user.

    Any inputs in this regard....

    THanks
    OCP,IBM UDB,SYBASE DBA
    TCS, BANGALORE, INDIA

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you look up the error message in the "Messages and Codes Manual?

  3. #3
    Join Date
    Jun 2002
    Location
    Phoenix
    Posts
    3
    Try this....
    Grant use of tablespace to public;
    Grant use of tablespace to user username;

  4. #4
    Join Date
    Dec 2002
    Posts
    134

    Re: Declare Temporary table error

    It's possible the user calling stored procedures is different from the user created stored procedures. I think in DB2 inside stored procedure you have the rights of the definer, not caller. So make sure you granted use of tablespace to the definer.

    Once I had a strange situation, when user was sysadmin, but had no explicit use of tablespace permission, the declare was failing. To make things more strange, declare with explicit specification of temporary tablespace was working.

    regards,
    dmitri

Posting Permissions

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