How DB2 decides which tablespace to use if not specified

[Bruce]

All -

AIX 6.1 with DB2 9.7.5

db2level
DB21085I Instance "db2inst1" uses "64" bits and DB2 code release
"SQL09075"
with level identifier "08060107".
Informational tokens are "DB2 v9.7.0.5", "s111017", "IP23285", and Fix
Pack "5".
Product is installed at "/usr/opt/db2_09_07".

We have developers to fail to specify a tablespace when creating
tables...so DB2 chooses this or that tablespace...Of course developers
(and even the truckers on the highway) all know the instance password
so everybody can do everything (to the DBA's constant frustration...)

How can we:

1). All-out Prevent the Table from being even created if no
tablespace is specified?
2). Force DB2 to use a specific tablespace (like USERSPACE1 which is
what used to happen as I recall..)?

Frustrated in Florida

-B

[Frederik Engelen]

1) No idea
2) The Create Table entry in the infocenter has the detailed information. Short form: revoke USE from everything they're not allowed to use.

--
Frederik

[Helmut Tessarek]

Hi Bruce,

On 02.11.12 10:23 , Bruce wrote:
> A co-worker suggested that I move all 4k Tablespaces to 16K or 32K and
> then drop all 4k Bufferpools...not a bad idea but the System Catalogs
> still use 4K...so that's not gonna work...

You can assign a bufferpool which is greater or the same page size as the
tablespace's page size.

This means you can have 4k, 8k, 16k, 32k tablespaces which all use the same
32k bufferpool.

You can also assign your syscat to this bufferpool, but you won't be able to
drop the system bufferpool (the one which was created during database
creation) - even if there are no tablespaces using this system bufferpool.

Hope this helps.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

[Bruce]

Hi Helmut and thanks for your input...

I mustsa missed something in your reply because I tried this...

db2 "select bpname from syscat.bufferpools order by 1"

BPNAME
--------------------------------------------------------------------------------------------------------------------------------
BP_DATA_16K
BP_DATA_32K
BP_DATA_4K
BP32K_DATA
BP32K_INDX
BP4K_CAT
BP4K_DATA_1
BP4K_HAGRP_1
BP4K_HAINDX
BP4K_INDX
BP4K_LG
BP4K_TEMP
BP8K_CAT
BPREORG
IBMDEFAULTBP

15 record(s) selected.

db2inst1@buildsvr /db2inst1/scripts $ db2 "alter tablespace
systoolspace bufferpool BP_DATA_32K"
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1582N The PAGESIZE of the table space "SYSTOOLSPACE" does not
match the
PAGESIZE of the bufferpool "BP_DATA_32K" associated with the table
space.
SQLSTATE=428CB

CREATE REGULAR TABLESPACE "SYSTOOLSPACE" IN DATABASE PARTITION GROUP
IBMCATGROUP PAGESIZE 4096 MANAGED BY SYSTEM
USING ('/xxxxx/db2inst1/NODE0000/SQL00001/SYSTOOLSPACE')
EXTENTSIZE 32
PREFETCHSIZE AUTOMATIC
BUFFERPOOL IBMDEFAULTBP
OVERHEAD 5.600000
TRANSFERRATE 0.100000
NO FILE SYSTEM CACHING
DROPPED TABLE RECOVERY ON;

What did I miss?

[Helmut Tessarek]

Hi Bruce

On 06.11.12 6:25 , Bruce wrote:
> I mustsa missed something in your reply because I tried this...

I'm sorry. My bad, I mixed something up. You know it can be quite confusing
sometimes, if you hack in different parts of the code and try to keep in sync
with the 'real' world...

[Bruce]

Helmut, I think maybe you're confusing the fact that you can put a 4k
table page into a 8k, 16k or 32k tablespace...just not a 4k table page
into a 8k, 16k or 32k bufferpools....

All I really need is some control over which tablespace others place
tables into when they don't specify a valid target TS... or a better
explanation has to how DB2 decides which TS to use in this case.

Here is my DB2 LUW wish list...several new registry entries:

DEFAULT_TABLESPACE=MYDEFAULTTS

PROHIBIT_TABLE_SCANS_IN_SQL="My_HUGE_Table1, Another_LARGE_TABLE...,
etc."

I sure would love to keep idiots from doing a table scan in SQL
against a 500,000,000 row table....

"All I want for my Birthday is..."...haha

-B