hello,

i have 2 questions:

***** we need 8k pages in a database that is 4K by default(ibm db2 v7.2 on windows NT server)

the database is created with IBM defaults considering extensize and prefetch size and bufferpool size(it is created with ibm control center's create db wizard)

what i'm gonna do is to create a 8K bufferpool and create a tablespace that uses this bufferpool.

considering that ibm default values works for the database, what should be (approximately) my values for:
- bufferpool size
-extensize
-prefetch size
?????

***** i want a user to create tables views everything on this 8K tablespace BY DEFAULT.

what should i do to make the user use this new tablespace by default??

thanks all

If the IN TABLESPACE clause is not specified, a table space for the table is determined as follows:

IF table space IBMDEFAULTGROUP over which the user has USE privilege exists with sufficient page size THEN choose it,

ELSE IF a table space over which the user has USE privilege exists with sufficient page size (see below when multiple table spaces qualify) THEN choose it,

ELSE issue an error (SQLSTATE 42727).

If more than one table space is identified by the ELSE IF condition, then choose the table space with the smallest sufficient page size over which the authorization ID of the statement has USE privilege. When more than one table space qualifies, preference is given according to who was granted the USE privilege:

1. the authorization ID
2. a group to which the authorization ID belongs
3. PUBLIC

If more than one table space still qualifies, the final choice is made by the database manager. Determination of the table space may change when:
- table spaces are dropped or created
- USE privileges are granted or revoked.

The sufficient page size of a table is determined by either the byte count of the row or the number of columns. If the row size only requires 4K, and the user has privledges on any of the 4K tablespaces, then I don't think you can use a 8K page tablespace by default unless you delete all the 4K tablespaces (or drop the IBMDEFAULTGROUP tablespace and re-create it as 8K).

For the extent size, it depends on the type of application (OLTP vs Data Warehouse), size of tables, and stripe size if you are using RAID. The prefetch size should be equal to, or a multiple of the extent, but depends on the number of different containers that exist on different physical drives. Large prefetch is most often beneficial on large tables where tablespace scans are necessary for the query. The wizard will help you with this.

The 8K bufferpool size should be as large as possible, while allowing for the amount of real memory needed for other bufferpools, other DB2 resources, other applications on the server, and OS requirements.

You will be a lot better off in terms of performance if you could use Windows 2000, instead of NT.

thanks Marcus,
the dataase has 4 tablespace now:
syscatspace - 4K
tempspace - 4K
userspace1 - 4K
newspace1 - 8K

my real intension is that:
a tool will create tables, etc. in my 4K database.it needs data pages to be 8K
will this scenario work then?

-i will create a new user for this tool to use, and revoke USE privilege on userspace1, and grant USE privilege on newspace1. then should he automatically use newspace1, without any problem?

and another question:
do i need an 8K-tempspace to be able to use newspace1 for my new user?
if so, should it be system temporary?should any granting be done there?
or will this 4 tablespaces above work together in harmony? )

Yes, you will need a 8K system temporary tablespace. I don't think you need to grant access to a system temporary tablespaces.

I think your plan will work, but of course it should be tested.

i have a problem here:

how can i set my new 8K-tablespace to be the default tablespace for my new user?

as userspace1 is the default userspace and it is being used(with public grants), how can i revoke my new user from using this userspace1??

in fact, settimg the 8K-tablespace to default is enough for me.
how can i do that? is it possible?

tnx