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
-------------------

[m0002a]

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

[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 .

[m0002a]

CREATE SYSTEM TEMPORARY TABLESPACE

[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

[m0002a]

You are correct. The keyword SYSTEM is optional.

[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

[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;

[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;

[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
-------------------