Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQLCODE = -904, when DECLARE GLOBAL TEMPORARY TABLE used

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Apr 03, 2009 4:08 pm    Post subject: SQLCODE = -904, when DECLARE GLOBAL TEMPORARY TABLE used
Reply with quote

Hi Guyz,

When i am trying to declare a global temporary table i getting following error in SPUFI.
Code:
  DECLARE GLOBAL TEMPORARY TABLE DEPTTMP                                       
    (DEPT    SMALLINT NOT NULL                                                 
    ,NAME    CHAR(5)  NOT NULL                                                 
    )                                                                           
  ;                                                                             
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN           
         UNAVAILABLE RESOURCE. REASON 00E7009A, TYPE OF RESOURCE 200, AND       
         RESOURCE NAME TABLESPACE IN TEMPDB                                     
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                               
DSNT415I SQLERRP    = DSNXIDCL SQL PROCEDURE DETECTING ERROR                   
DSNT416I SQLERRD    = 15 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION             
DSNT416I SQLERRD    = X'0000000F'  X'00000000'  X'00000000'  X'FFFFFFFF'       
         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION                   
---------+---------+---------+---------+---------+---------+---------+---------+


I already have a TEMPDB database and a SEGmented tablespace[ TMPSTS01 ] in it. But, still i am getting this error. I checked the RESTRICTive states of the database, everything is fine.

So, can i know the reason for this error ? Anything wrong or missing in my DECLARE TABLE syntax ?

Thank You in Advance,
Sushanth Bobby
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 03, 2009 6:26 pm    Post subject:
Reply with quote

try declaring it as

DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Apr 03, 2009 6:30 pm    Post subject:
Reply with quote

Still getting the SAME error message ASHIMER.

Sushanth
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Apr 03, 2009 6:36 pm    Post subject:
Reply with quote

pls chk the follwing

does the table space in TEMPDB database has a page size that is large enough for the declared global temporary table.

Is TEMPDB database available at the time of execution ?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Apr 03, 2009 6:46 pm    Post subject:
Reply with quote

Ashimer,

This is DDL of TEMPDB.

Code:
 CREATE DATABASE TEMPDB         
        AS TEMP                 
        STOGROUP SYSDEFLT       
        BUFFERPOOL BP0         
        INDEXBP    BP0;         
                               

CREATE TABLESPACE TMPSTS01 IN TEMPDB
USING STOGROUP SYSDEFLT       
             PRIQTY 12               
              SECQTY 12               
              ERASE NO               
        SEGSIZE 4                     
        BUFFERPOOL BP0               
        CLOSE YES;                   


Quote:
does the table space in TEMPDB database has a page size that is large enough for the declared global temporary table

Iam just declaring a SMALLINT & CHAR(5) table.

Yes it is available. I check the RESTRICTION STATES in governor.

Thank You,
Sushanth
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri Apr 03, 2009 11:15 pm    Post subject: Reply to: SQLCODE = -904, when DECLARE GLOBAL TEMPORARY TA
Reply with quote

Hi Sushanth Bobby

Try to create temporary database, tablespace and table in a single UR.

Then post the response of the system in the forum.

Regards
Raghu
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Apr 04, 2009 8:42 am    Post subject:
Reply with quote

Hi Raghu,

Temporary Database & a tablespace in that temporary database already exists in the sub-system. The DDL of the existing TEMPDB & Tablepspace is what i have posted earlier.
I came across this link , according to it may be i have to alter the existing tablespace bufferpool size to 8K, right now it is BP0(which is 4K).

Will try this out & let you guyz know.

Sushanth
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Apr 06, 2009 10:25 am    Post subject:
Reply with quote

Guyz,

It worked.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Apr 06, 2009 7:21 pm    Post subject: Reply to: SQLCODE = -904, when DECLARE GLOBAL TEMPORARY TA
Reply with quote

Good to hear it worked - thank you for letting us know icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us