Hello All,


My Question on the tablespaces on recent DB2 installation ..In the attachement for the database toolsdb we have totally 7 tablespaces .

1.Syscatspace. - DMS - Regular - Which has the DB status andstructure .
2.Tempspace1 - SMS - Systmp - Used for tempspace for sorts
3.Userspace1 - DMS - Large - Currently the all the data being stored .
4.Systoolsace - DMS - Large -- User data table and used by the db2 administration tools
5.Systoolstmpspace - SMS - usrtmp - Temp
6.Tbsp32k0000 - SMS -Regular - ???
7.Tbsp32ktmp000 - SMS - Systmp - ??? ..


1. Whats the table @ no 6 is used for and as you can see in the screenshot page size of tbsp32k0000 is 32kb which means the table can grown up
512GB but it's maintained as SMS .Conceptually SMS tablespaces are used to store small and diverse data . Can we convert the SMS to DMS is that advisable .

2. Systoolspace is maintained as DMS and Large Tablespace . Can we use this table for storing user data .

3. Is there an major difference between Regular and Large Tablespaces .

4. I googled and came to know that one tablespace can contain any number of container . Is it a good practice to have multiple containers for a single Tablespace . In this case the BODS user created all the repository and all its data are getting stored in USERSPACE1 and size is piling up now . What should I do in this situation .


Regards
Sam

1. You can run a query against the DB2 catalog to determine which tables are assigned to Tbsp32k0000, and hopefully that will give you some clues to know what purposes it is used. The DB2 catalog views are described in the SQL Reference Vol 1, Appendix D.

2. Yes. But I personally would just create a new tablespace if needed.

3. Large tablespaces can be much larger in total size, and also hold more row per page (which can be a factor if row size is very small). See SQL Limits in SQL Reference Vol 1, Appendix A.

4. It could help performance on tables with a lot of I/O if you have multiple containers for a tablespace. It depends somewhat on how big your bufferpools are relative to the total amount of data (if data is always in memory, then physical I/O may not be happening much).

Multiple containers are automatically used for tablespaces if it uses automatic storage and multiple paths are defined in the automatic storage configuration.

If you do have multiple containers (defined on multiple paths), they should not have any spindles in common for best performance. A spindle is a physical disk. You will need to discuss this with your storage administrator.

Marcus,

SO in this case If I set MAXSIZE for tablespace to NONE , Will the container get created automatically . Is it advisable to set the MAXSIZE to NONE if I have a sufficient space in the disk .

and

There is a huge log file generation is happening in my Database Db2 9.7 FP2 running Business object as front end . Will there be less log file generation if I increase the mincommit from 1 to 2 .Is there anything else needs to be considered on this area .

The one of the value for the below parameter in this database

All committed transactions have been written to disk = NO

Whats this parameter is used for ?


Sorry for my ignorance :-)


Regards
Sam

As long as you have free space on your mount point, you can leave MAXSIZE set to NONE. It has nothing to do with the number of containers, only the maxsize of the tablespace when the tablespace is set to automatically increase in size. Please read about the CREATE TABLESPACE in the manuals.

If you are talking about DB2 transaction recovery log, then someone is probably running some insert, update, or delete SQL that is affecting a lot of rows. Doesn't matter where you submit the SQL from, DB2 must log the insert, update, or deletes. If you are talking about some other log, please specify.

"All committed transactions have been written to disk = NO", is not a parameter, it just means that DB2 is holding some data in bufferpool memory that has not been written to tablespaces on disk yet (but it has been written to transaction recovery logs, so you are safe if your system crashes).