Hi
DB2 9.7, FP 3a/ Win 2008 R2

I have a LARGE type tablespace and contain couple of big tables with around 80 million rows. Each has around 6 indexes in different tablespace (LARGE type).
I am going to do the offline REORG for this tables and want to use the temporary tablespace (USERTEMP).

### REORG TABLE XYZ USE USERTEMP;

Also I am lookig to recover some space back as the current HWM is too high.

My question is,
1. Does the USERTEMP tablespace must be LARGE type TBS?
2. Will this rebuild the indexes too?

Please advise
Thanks, valan.

From the syntax diagram for CREATE TABLESPACE, you cannot create a "LARGE" SYSTEM TEMPORARY tablespace. You do need one that is the same pagesize of the tablespace that the table resides in. It will not reorg your indexes at the same time. You need to reorg those separately. It does rebuild the indexes in the sense that the pointers to the rows get changed to the new layout of the table data.

Andy

I believe that an offline reorg of a table will automatically rebuild (reorg) all the indexes also.

My LARGE TBS page size is 4K. The table size is around 40GB, Indexes also smilar size.
If I use the TEMPSPACE (4k page), will the table and indexes will fit in this TBS? (4K page TSB size limit is 64GB)


Thanks.

You believe, I'm sure

What I am not sure of, but would be nice to test in this case:
drop the 4k usertemp
create a 32k usertemp (I assume there are NO 8 & 16k usertemps).
Now when you do your REORG it will use the 32K tempspace which can be much larger.

Hmmmmm... suppose this works... Why the *beeb* do we need 4, 8 & 16k temp-tablespaces???? Maybe we never "needed" them but just created them out of a habit

the problem is that if this 4K_temp is the only with 4K size pages - you can not drop it
if both exist (4K-32K)- you can not force usage of one or another
we have the same problem with a table in 32K ts - and join/order/group is using 4K temp and hitting max limit - (see older post) - not easy to force the usage of this 32K temp

This is true for a query, but not for a reorg--which is what the OP is asking about. If you specify a System Temporary Tablespace on the reorg command, it will use the one you requested (if it is the same pagesize as that of the table).


The limits for the table and indexes are separate. You can have table and indexes that are bigger than 64GB in a 4K tablespace. The table alone cannot exceed 64GB. The index size is either 64GB or 2048GB depending on the tablespace type (DMS/SMS).

Andy

tables as large as these should be partitioned.
it will improve your query performance and maintenance time (reorg, runstats).

might be a good idea to place them in separate tablespaces too but it depends...

Thanks for all your suggestions, I will try this on our test system first.

Regards, valan.

Just to add:
Table reorg will use a tempspace specified on the reorg command. If you happen to have two tempspaces of the same page size (not sure why someone would do that but I've seen it) and rebuilding of the indexes require a tempspace due to the spilling of sorts, then it will be chosen in a round robin (just for the index rebuild phase).

working/testing with that right now. Not as simple as "presented on the whiteboard".
To really gain REORG time you must define ALL your indexes as partitioned.
When your partitioning column is not part of your PK definition you'll have to make choices:

- The unique PK index remains global. bye bye performance gain because that index has to be rebuilt completely when you do a reorg (on data partition)
- drop the PK constraint and drop the unique-ness of your index. bye bye FK constraints because your table does not have a PK anymore. Works, but not as designed.

But if you manage to convert all your indexes to partitioned the performance gain during "reorg data partition" ís impressive.

A good reason would be to place them on different file systems so that when you reorg a table on one filesystem you can use the temp space on an other filesyatem to speed thing up.

Andy