Hi,

My apologies if this has already been addressed in another thread - I first performed a search and did not find anything. My question is: is DB2 for Linux/Unix/Windows such that "there is no partitioning avaiable in DB2 LUW and therefore all processing must go through the same bufferpool. As a result of this, scalability is impacted since the application is throttled by a single bufferpool." Does this sound correct or did I misinterpret something (this is what I was told - I did not read it in a manual)? - I'm guessing it's incorrect myself and I am therefore looking for clarification.

Thanks!

Partitioning is available in DB2 (But, you may have to explain what you mean by the term ) .. Search for DPF/EEE ...

In 32 bit architecture, each partiontion is constrained by a 2Gig (normally 1.5 - 1.75 gig) limit for bufferpools ... This can be multiple bufferpools also, but the total is 2 Gigs ... You can, of course , use the extended pool ....

The statement, "there is no partitioning avaiable in DB2 LUW and therefore all processing must go through the same bufferpool. As a result of this, scalability is impacted since the application is throttled by a single bufferpool." contains two unrelated claims that are not true anyway.

1) There is partitioning in DB2 for LUW (EEE edition is required).
2) You can have multiple bufferpools.
3) There's no relation between partitions and bufferpools (except that each partition has its own bufferpools).

The person who made that statement may benefit from learning more about DB2.

To expand on what was said before:

1. DB2 with partitioning in version 7 is called DB2 EEE. In version 8 it is called DB2 ESE with DPF (data partitioning feature).

2. You can have multiple bufferpools in DB2, however the ability to have many bufferpools is not directly related to scalability. Most people create too many bufferpools, when a fewer number of larger bufferpools is often better. When using multiple partitions, each partition has its own bufferpools.

The ability do have a scalable architecture depends on inter-partition parallelism (DB2 ESE with DPF) and/or intra-partition parallelism (any version of DB2), when the database is set up properly to exploit parallel operations.

Thanks everyone for your prompt replies. Tell me, how is the data order in DB2 LUX - is it the same as it is in MVS - namely, by the clustering INDEX?

Thanks.

It is a little different.

Insert Rule
If you define a clustering index, then it works pretty much the same way as DB2 for z/OS (previously known as OS/390). If you don't define a clustering index in LUW, new rows are inserted at the end of the table (in DB2 for z/OS, the first index is the clustering index by default if no clustering index is defined). In either version, rows are inserted at the end if the APPEND option is used on the table (create or alter).

Reorgs
For reorgs on DB2 for z/OS, the clustering index (or first index if no clustering index is defined) is always used to order the rows. For DB2 LUW, you can specify an index to be used to order the rows during the reorg if no clustering index has been defined. If a clustering index has been defined you can ignore the index specification in the reorg command, but if you do include the index name it must be the clustering index.

The main reason for the differences between DB2 z/OS and DB2 LUW is that the original version of LUW did not have clustering indexes, although one could always reorg by a particular index.

Reorgs: I use db2 v7.2 and if cluster index is specified on table and also a regular index. Table can be reorged by bouth indexes!!! I created test table and inserted rows into it and then reorg table and then "select * from table". Table is sorted according of index specified in reog statement.

So if table has cluster index it can be also sorted according to regular index.

Grofaty, thank you for the correction. Actually, even in version 8, what I said only applies to an INPLACE reorg. From the Command Reference:

"For an inplace table reorg, if a clustering index is defined on the table and an index is specified, it must be clustering index. If the inplace option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table."

Hi,

What is INPLACE reorg? Is its marketing name: "On-line reorg"?

Grofaty

According to the manual, INPLACE means reorganizing the table while permitting user access. The INPLACE keyword is the actual syntax used in version 8. I would not blame the marketing people for that one.

I think INPLACE is actually more descriptive becasue an INPLACE reorg is different from an ONLINE reorg on DB2 for z/OS where the table is written to another dataset in the order of the clustering index, and then the old dataset is renamed, and the DB2 logs are applied for any changes made during the reanme process.

I believe that for an INPLACE reorg, the rows are just moved around within the same file.

I am little confused with the grofaty reply,correct me ,a table cann't be reorg
based on two seperate indexes( there should be only one clustered index on a table). so when we talk about reorg of table in the context of index , we
generally mean the physcial reordering of the table based on that index sorting order.

This is what I have from IBM manual
Part 1 :
The table option reorganizes a table by reconstructing the rows to eliminate
fragmented data, and by compacting information.


Part 2:
INDEX index-name
Specifies the index to use when reorganizing the table. If you
do not specify the fully qualified name in the form:
schema.index-name, the default schema is assumed. The schema
is the user name under which the index was created. The
database manager uses the index to physically reorder the
records in the table it is reorganizing.
For an inplace table reorg, if a clustering index is defined on
the table and an index is specified, it must be clustering index.
If the inplace option is not specified, any index specified will
be used. If you do not specify the name of an index, the
records are reorganized without regard to order. If the table
has a clustering index defined, however, and no index is
specified, then the clustering index is used to cluster the table.
You cannot specify an index if you are reorganizing an MDC
table.


from these parts you will find that a table can be reorg without any indexes.
Indexes can be reorg seperatly.


regards,

mujeeb

I think he meant that he reorged once with one index, and then later with another index.

That is correct.

I just meant: table can be reorged according cluster index at one time and can be also reorged (when finised first reorg) with regular index.

There is no limitation that table containing cluster index can't be reorged according to regular index, when bouth cluster and regular index are defined on table.

Hope this helps,
Grofaty

just going through this discussion.

if you reorg your table first on cluster index and then on another index, the first reorg is lost or become useless.

After your second reorg the new data that will be inserted into the table will follow the cluster index ordering sequence, so now you have two different ordering divided at the reorg path.

regards,

mujeeb

Hi bmujeeb,

You missunderstood me.
Reorg one and the same table according to two indexes is useless. The first ordering will be lost. The last ordering is applied.

My reply to Marcus_A was: If regular index and cluster index are defined on table, then reorg can be done with regular OR!!!!!! cluster index. You have the choice!!! You can reorg table according to regular or cluster index. Marcus said: "... if you do include the index name it must be the clustering index." This statement is false!!! This is only true for inplace reorg. So: if regular and cluster index are defined on table, then reorg can be done with regular or cluster index.

Hope this helps,
Grofaty