temporary tablespace without sufficient page for replication

Andy K

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

m0002a

You need to create a system temporary tablespace with page size of 32K.

Nov 12 '05 #2

Andy K

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

m0002a

CREATE SYSTEM TEMPORARY TABLESPACE

Nov 12 '05 #4

Andy K

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

m0002a

You are correct. The keyword SYSTEM is optional.

Nov 12 '05 #6

Andy K

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

db2newbie

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

Andy K

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

Andy K

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