|
View previous topic :: View next topic
|
| Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
 |
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
try declaring it as
DECLARE GLOBAL TEMPORARY TABLE SESSION.DEPTTMP |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Still getting the SAME error message ASHIMER.
Sushanth |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Guyz,
It worked.
Thank You,
Sushanth Bobby |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Good to hear it worked - thank you for letting us know 
d |
|
| Back to top |
|
|
|
|
