Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DB2 HADB

Re: DB2 HADB

From: Mark A <nobody_at_nowhere.com>
Date: Sun, 27 Nov 2005 09:09:20 -0700
Message-ID: <Q5GdnSM43ND9RxTenZ2dnUVZ_sSdnZ2d@comcast.com>


"Noons" <wizofoz2k_at_yahoo.com.au> wrote in message news:43897d3e$0$10206$5a62ac22_at_per-qv1-newsreader-01.iinet.net.au...
> Obviously! The thing uses VSAM under the covers to keep the
> data and the only way to index VSAM efficiently is to use
> the multi-key ISAM. Which is exactly what clustered tables
> are in DB2 for z/OS! Come on, Mark: both I and DA have used
> MVS/XA and VM all the way back to DOS/VSE, don't try to
> hide the obvious! Besides: I have access to DB2 for z/OS
> doco, it explains how VSAM is used. ;)
>
>
> What I find suprising is that in db24luw this is also
> still the case. I wonder: did IBM port VSAM to those
> environments "under the covers"? Not that it'd be a bad
> thing, mind you: VSAM would certainly be FAR superior
> to most native file system handling in those platforms.
> It's one of the few mainframe things I sorely miss.
>
> --
> Cheers
> Nuno Souto

Actually, I think there is some confusion here. DB2 for z/OS uses VSAM files, but they are Linear VSAM files, not key-sequenced (KSDS), for both the tables and indexes. So clustering in DB2 has nothing to do with VSAM or ISAM or any other underlying file system. The term "clustering" may be common to both DB2 and KSDS VSAM and ISAM, but the concepts and implementation are different.

It is just that DB2 has a feature that will try to maintain an "approximate" order of the rows on inserts. But if there is no room on the correct page, or a nearby page, it gives up and inserts the row wherever there is room. DB2 does not reorder rows on a page when it tries to maintain clustering sequence. For this to work effectively, there needs to be freespace on each page, which is one of the reasons reorgs are performed in DB2. The way table clustering is implemented by DBA's in DB2 is to define one of the indexes as clustered, which specifies the order of the table, not the index (since all indexes are always in exact order).

DB2 for Linux, UNIX, and Windows also has multi-dimensional clustering (MDC) which reserves certain blocks for data with the same key values, which is somewhat similar to Oracle b-tree single clustered tables. But this method does not require the same level of reorgs as is needed by regular DB2 clustering.

In DB2 for z/OS there is always a clustered index, because if you don't define one, the first index created is clustered. However, DB2 for z/OS has added a feature where you can specify in the tablespace that the new rows are added where ever space exists, ignoring to clustering specification. But even in this case, although clustering is ignored during inserts, it is maintained after a reorg. But most people just leave clustering on (turning it off for inserts is a relatively new feature), which is why close to 100% of tables have clustering in DB2 for z/OS (and not because of VSAM).

In DB2 for Linux. UNIX, Windows (LUW), clustering has always been optional from day one. If it is not specified, then DB2 inserts rows wherever it has room. You can also tell DB2 LUW to insert new rows at the end of the table (APPEND option) . The reason that DB2 LUW is different than DB2 for z/OS is that they were developed by two different development labs a long time ago, and it is hard to get them completely in synch because they each needs to maintain compatibility for there existing installed base of customers. Received on Sun Nov 27 2005 - 10:09:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US