db2有主键时默认hash分区_db2 中关于 IBMTEMPGROUP 默认分区组

关于 IBMTEMPGROUP 默认分区组为什么 list DATABASE PARTITION GROUPS 不显示

无论是单分区/多分区数据库 IBMTEMPGROU 组都将无法显示

单分区 ：

antn32:/home/db2inst1>db2 list DATABASE PARTITION GROUPS

DATABASE PARTITION GROUP

-----------------------------

IBMCATGROUP

IBMDEFAULTGROUP

2 record(s) selected.

多分区：

db2inst1@host123:/db2home/db2inst1/> db2 list DATABASE PARTITION GROUPS

DATABASE PARTITION GROUP

-----------------------------

IBMCATGROUP

IBMDEFAULTGROUP

PDPG

SDPG

4 record(s) selected.

### 官方概念

Partition groups

A partition group is a collection of one or more database partitions. In a single

partition environment, a partition group is not of concern. Table spaces for a

database are created in a partition group. By default, there are three partition

groups created when a database is created:

IBMCATGROUP

IBMDEFAULTGROUP

IBMTEMPGROUP

You create the IBMCATGROUP partition group when you issue the CREATE

DATABASE statement and it contains the system catalog table space

(SYSCATSPACE) together with the system catalog tables. The IBMCATGROUP

partition group spans only the catalog partition and cannot be altered or dropped.

The IBMDEFAULTGROUP partition group spans all the database partitions, and

it is the default group for the CREATE TABLESPACE statement. By default, the

USERSPACE1 table space is created in the IBMDEFAULTGROUP partition

group. The IBMDEFAULTGROUP partition group is a system object and cannot

be altered or dropped.

The IBMTEMPGROUP partition group spans all the database partitions and

contains the TEMPSPACE1 table space. The IBMTEMPGROUP partition group

is a system object and cannot be altered or dropped.

IBMTEMPGROUP 关于该组为什么不显示  IBM 官方也没有给出任何理由

倒是一些IBM 官方的技术贴中提到 就是不显示，没理由 ～

http://www.ibm.com/developerworks/data/library/techarticle/dm-0403chong/

“ list database partition group, command to list all your partition groups (IBMTEMPGROUP will not be listed) ”

“5. Next, you need to create partition group pg23 on partitions 2 and 3. Figure 12 shows you how to accomplish this. In addition,

the figure shows you how to list your partition groups. The IBMTEMPGROUP is never listed with this command. ”

在网上搜索 看到两个帖子关于此问题  的讨论还是比较有说服力的 ， 这里摘录过来供大家分享

http://database.ittoolbox.com/groups/technical-functional/db2-l/ibmtempgroup-doesnt-show-up-in-syscatdbpartitiongroupdef-4470337

Why doesn't IBMTEMPGROUP show up in syscat.DBPARTITIONGROUPDEF?

Checked List Database Partition Groups show detail, IBMTEMPGROUP isn't shown either.

Anyone know why?

Thanks,

Sheila

++++++++++++++   回复一

@Sheila,

IBMTEMPGROUP is something where end user like us (DBA's) are not allowed to

play with. Maybe for that reason it's kept away from listing. As like

other restrictions we cannot drop or alter IBMTEMPGROUP. So it's fine

being not listed. :-)

Regards,

Pramod

++++++++++++++    回复二

Read this documentation.

https://www1.columbia.edu/sec/acis/db2/db2v3/db2v3 20.htm#HDRGRPPART

Run db2look using options to generate DDL for tablespaces and you will notice that you may not see some of the default objects like these groups and the USERSPACE1 tablespace generated. Someone in IBM DB2 development simply wants this to work that way and they do not generate or give DBA's visibility to all of these objects though many 3rd party software like BMC SmartDBA, DBArtisan, Quest for DB2, TOAD and others will show you all of the IBM system objects if you configure these to do so.

By default when you create a database DB2 LUW will create a TEMPSPACE1 and a USERSPACE1 tablespace. The TEMPSPACE1 page size will match the default database page size. Normally 4K unless you change the configuration before creating the database or explicitly create the database using an 8K, 16K or 32K default page size.

I like to create databases using 16K page sizes and then create a TEMPSPACE32 tablespace that uses a 32K page size and of course the default IBMTEMPGROUP. If DB2 LUW joins rows from tables and the join result will exceed a 16K page size then having the TEMPSPACE32 tablespace can help avoid SQL errors and improve performance if result set processing is triggered by the predicate options used in the SQL.

I like 16K page databases and tablespaces because the tablespaces store more rows per page and this can reduce the number of I/O's the database performs. I like DMS LARGE tablespaces and to use one for tables TS and another for the tables indexes IX to increase the number of open container files and parallel I/O.

The IBMDEFAULTGROUP, IBMTEMPGROUP and IBMCATGROUP are always required and will always be aware of any new partitions added to a DB2 LUW DPF database. Other DBA created and maintained partition groups may not be aware of new partitions added to the instance and database until they are modified to be aware of these.

The answer to your original question is because. Because someone or some group in IBM DB2 LUW development decided that they wanted DB2 LUW to work in this way. There are times when that's the answer to the question and you just need to learn what you can about the object in question and know when and how to use it. I tried to give you an example of when you might use the IBMTEMPGROUP when creating additional temporary tablespaces to support your application table processing.

If you have applications and development DBA's that want to have tables in 4K, 8K, 16K and 32K page tablespaces based on the row size then you may want to create a 4K, 8K, 16K and 32K temporary tablespace for the database. I do this by default and by habit. Then if a DBA ever creates a 4K, 8K, 16K or 32K page tablespace with tables in it and the database needs to use a temporary tablespace for result set processing, it's there and ready to go. I prefer SMS tablespaces for temporary tablespaces.

I also like to create 4k, 8k, 16k, and 32k page user temporary tablespaces. Then applications or users can create global temporary tables dynamically and again they don't get an error when doing so.

If you use the IBMDEFAULTGROUP for tablespaces you create, you need to be aware that this will cause the tablespace to span all partitions, 0 through N. In DPF environments a lot of DBA's like to put small reference and code tables in partition 0 only and partitioned tables in tablespaces that use partition 1 through N. This is why I normally do not use the IBMDEFAULTGROUP though to every rule there's usually an exception.

I hope some of this information helps answer some of your questions.

====================  个人理解如下 ：

DB2 的开发者们可能认为 无论是单分区/多分区数据库，临时表空间是肯定少不了的，而且是每个数据库分区都必须要有临时表空间，而该IBMDEFAULTGROUP分区组又正好是包含所有分区的。

所以系统默认的这个临时表空间组 ： IBMDEFAULTGROUP 就可以认为是必需的， 而且完全不需要用户来对此对象做任何的变更，就像真理一样应该就这么存在着，不想让你对它有任何的质疑。

所以才没有列举出来，但是又肯定它是一定存在的，且不能被删除也不能被更改。

DPF环境下，如果用户有特殊需要可以创建其他分区组用来创建临时表空间。

阅读(2375) | 评论(0) | 转发(0) |