467,877 Members | 1,124 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,877 developers. It's quick & easy.

temporary tablespace without sufficient page for replication

Hi ,

I've setup the replication for db2 but at the end of the replcation ,
DB2 came out with the message error (see below) .

I have no idea why it shows me this error . I have two tablespaces ,
one for the system which has a pagesize of 4K and another one at 32K
for user . Each tablespace has 1 Gb .

Does someone have seen this ?

Regards

Andy the humble beginner in DB2 (better say the ignorant)

ps1: DB2 7.x and linux 7.x .

*** SQL ERROR ***: SQL1585N A system temporary table space with
sufficient page , size does not exist. SQLSTATE=54048

CDSET: SQL1 for fetching answerset: SELECT IBMSNAP_OPERATION,
IBMSNAP_INTENTSEQ, IBMSNAP_COMMITSEQ, "ID", "SHORT_NAME", "TYPE_FIC
HIER", "STRING_VALUE", "NUMERIC_VALUE", "DATE_VALUE", "BOOLEAN_VALUE",
"ID_DOCUMENT", "TIMESTAMP", "OBSERV_VALUE" FROM "REPSRC"."
CD_ATTR_INST" A , ASN.IBMSNAP_UOW WHERE ASN.IBMSNAP_UOW.IBMSNAP_UOWID
= A.IBMSNAP_UOWID AND ASN.IBMSNAP_UOW.IBMSNAP_COMMITSEQ >
? AND ASN.IBMSNAP_UOW.IBMSNAP_COMMITSEQ <= ? ORDER BY
IBMSNAP_COMMITSEQ ASC, A.IBMSNAP_INTENTSEQ ASC OPTIMIZE FOR 50000
ROWS.
CDSET: Prepare failed. errcode is 920101. sqlstate is 54048. sqlcode is
-1585
-------------------

Nov 12 '05 #1
  • viewed: 13675
Share:
9 Replies
You need to create a system temporary tablespace with page size of 32K.

Nov 12 '05 #2
Hi ,

I did that .

create bufferpool plantestpool size 500 pagesize 32K;

CREATE TEMPORARY TABLESPACE TEMPSPACE1 pagesize 4k MANAGED BY DATABASE
USING (FILE '/opt/database/mongtest/temp02' 128000) EXTENTSIZE 80
bufferpool ibmdefaultbp;

CREATE USER TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY
DATABASE USING (FILE
'/home/db2inst3/db2inst3/NODE0000/SQL00001/SQLOGDIR/data3.dbf' 32768)
EXTENTSIZE 80 bufferpool plantestpool;

GRANT USE OF TABLESPACE TEMPSPACE2 TO asn;

Any hints will be welcome.

Andy .

Nov 12 '05 #3
CREATE SYSTEM TEMPORARY TABLESPACE

Nov 12 '05 #4
Hi ,

I'm quite baffled and I'm wondering isn' it the "create temporary
tablespace" in itself a "create system temporary tablespace" ?

I'm gonna do the trick .

Thanks for your answers .

Regards ,
Andy

Nov 12 '05 #5
You are correct. The keyword SYSTEM is optional.

Nov 12 '05 #6
Ok m00... ,

I think I tried the trick but it doesn't seem to work the proper way .
Anyway , thanks for your answer .

Regards ,
Andy

Nov 12 '05 #7
Can you recreate your TEMPSPACE2 without the USER keyword? This
tablespace is for the Declared Global Temporary Table.

e.g.
CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY
DATABASE USING (FILE
'/home/db2inst3/db2inst3/NODE0*000/SQL00001/SQLOGDIR/data3.db*f'
32768)
EXTENTSIZE 80 bufferpool plantestpool;

Nov 12 '05 #8
Hi ,

Done that again & again . I think i found something .

tell you more about this when i'll be done .

Thanks ,

Andy the beginner in db2 .

db2newbie a écrit :
Can you recreate your TEMPSPACE2 without the USER keyword? This
tablespace is for the Declared Global Temporary Table.

e.g.
CREATE TEMPORARY TABLESPACE TEMPSPACE2 pagesize 32k MANAGED BY
DATABASE USING (FILE
'/home/db2inst3/db2inst3/NODE0*000/SQL00001/SQLOGDIR/data3.db*f'
32768)
EXTENTSIZE 80 bufferpool plantestpool;


Nov 12 '05 #9
Hey everyone !

Thanks to all of you guys (should I mention db2newbie & m00) .

You were right . I needed a system tablespace with a pagesize of 32 k.

After that, my replcation was working and is still working fine .

Thanks !

Andy the humble beginner in DB2 .

ps: in case u need some documentation on DB2 , reply to me (don't let a
post) and i'll give you the data that I have on replication for db2 7.x
..

Andy K a écrit :
Hi ,

I've setup the replication for db2 but at the end of the replcation ,
DB2 came out with the message error (see below) .

I have no idea why it shows me this error . I have two tablespaces ,
one for the system which has a pagesize of 4K and another one at 32K
for user . Each tablespace has 1 Gb .

Does someone have seen this ?

Regards

Andy the humble beginner in DB2 (better say the ignorant)

ps1: DB2 7.x and linux 7.x .

*** SQL ERROR ***: SQL1585N A system temporary table space with
sufficient page , size does not exist. SQLSTATE=54048

CDSET: SQL1 for fetching answerset: SELECT IBMSNAP_OPERATION,
IBMSNAP_INTENTSEQ, IBMSNAP_COMMITSEQ, "ID", "SHORT_NAME", "TYPE_FIC
HIER", "STRING_VALUE", "NUMERIC_VALUE", "DATE_VALUE", "BOOLEAN_VALUE",
"ID_DOCUMENT", "TIMESTAMP", "OBSERV_VALUE" FROM "REPSRC"."
CD_ATTR_INST" A , ASN.IBMSNAP_UOW WHERE ASN.IBMSNAP_UOW.IBMSNAP_UOWID
= A.IBMSNAP_UOWID AND ASN.IBMSNAP_UOW.IBMSNAP_COMMITSEQ >
? AND ASN.IBMSNAP_UOW.IBMSNAP_COMMITSEQ <= ? ORDER BY
IBMSNAP_COMMITSEQ ASC, A.IBMSNAP_INTENTSEQ ASC OPTIMIZE FOR 50000
ROWS.
CDSET: Prepare failed. errcode is 920101. sqlstate is 54048. sqlcode is
-1585
-------------------


Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Pattnayak | last post: by
5 posts views Thread by N | last post: by
3 posts views Thread by Joe Weinstein | last post: by
6 posts views Thread by gimme_this_gimme_that | last post: by
4 posts views Thread by sandeep.iitk | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.