Unanswered: 32K Page Size for Temporary Tablespace
I am trying to run a TPC-H benchmark for DB2 on Linux. The Database will have about 15 GB of data. In looking at a document that IBM published for a TPC-H benchmark they performed, IBM created a Temporary Tablespace with a 32K pagesize. All of the user Tablespaces in the IBM TPC-H database have a 32K page size.
In the DDL that IBM provided in their documentation of the benchmark, they dropped Tablespace TEMPSPACE1 (the default temporary Tablespace) after they created the Temporary Tablespace with a 32K pagesize.
When I create a 32K Temporary Tablespace and then try to drop the default Temporary Tablespace created when the Database was created, DB2 gives me an error message saying that I cannot drop the only remaining Temporary Tablespace with 4K page size since the System Catalog Tablespace uses 4K pages.
When I try to create a Database and specify the System Catalog to use a Database Managed Tablespace with a 32K page size, I get an error basically saying the Pagesize parameter is not recognized (which is not surprising since it does not show as a valid parameter in the manual for a Catalog Tablespace). Unfortuneatly.
I need to force the use of the 32K page size Temporary Tablespace, which is 5GB and is optimized across several disks.
1. Will the 32K Temporary Tablespace always be used for data that comes from a 32K User Tablespace (even if only a few columns are selected)?
2. How did IBM drop the default TEMPSPACE1 (or how id they make the catalog use a 32K System Catalog Tablespace)?
I have had similar issues in trying to drop the 4k tempspace1. The catalog tablespace is 4k SMS no matter what and that requires the 4k tempspace in the IBMTEMPGROUP which will not show up in syscat.nodegroupdef though it exists across all nodes.
I'm not sure about that paper or about what IBM support says when they are online with you about this issue. I hope you have better luck than I did. I would be really interested in knowing how they did it.
to my understanding if the tablespaces have a 32k pagesize, then, by default, db2 will use the 32k pagesize tempspace and may actually require a larger tempspace if > than a certain number of columns are selected.
The administration Guide Planning actually does mention the dbm always enforces the existence of a 4k pagesize tempspace to enable catalog table reorgs. The section on recommendations for temporary tablespaces in this manual may be informative for you.
The rows on the TPC-H benchmark tables are actually quite small (less than 200 bytes). But the TPC-H queries force a lot of table space scans, so I suppose that IBM figured that the 32K page tablespaces would facilitate faster pre-fetches.
I have not contacted IBM support about this, because the DB2 documentation does not show any way to specify the page size for a Catalog Tablespace. IBM support will not know anything about the IBM benchmark document I got a hold off.
It's surprising how IBM's TPC-H Documentation says that the 4K tablespace can be dropped ... V8 manual :
Catalog table spaces are restricted to using the 4 KB page size. As such, the database manager always enforces the existence of a 4 KB system temporary table space to enable catalog table reorganizations.
I think, if you have a large bufferpool associated with the 32K tempspace, that is the one db2 should use ...
Visit the new-look IDUG Website , register to gain access to the excellent content.
Just to clarify, the drop of TEMPSPACE1 was in their script that was used to create all the objects. I don't know whether that particular statement actually worked (I suppose not). I guess if it failed it would not matter much. The benchmark was run on DB2 7.2 EEE.