MVSFORUMS.com

[bauer]

I have a huge table, MyTable, with some fields, Field1, Field2, Field3.

All fields are included in a index, some statements access the table by the index (index only access, matchcols > 0).

The data (each row) is stored twice, in the dataset for the table and in the dataset for the index.

The storage for the table is not used, not accessed.

My question is: Is there any datacontainer, possibility, using DB2 for z/OS just to hold the Index Tree (B-Tree) with Update, Insert, Delete operations and not waste storage the table ?

So I want just to handle the index tree and save disk storage.

Any comments please ?


regards,
bauer

[papadi]

Queries might be optimized to refer only to the index, but the data still needs to be stored. . .

This is not a problem - it is the way db2 is designed. An index is made up of the content of one or more columns. . .
_________________
All the best,

di

[bauer]

Thank you for your reply.

The intention of my question was, just to find a easy way to handle the datastructure of a b-Tree. In my situation a b-Tree which is very large an can not be kept in memory.

regards,
bauer

[papadi]

Suggest you insert the rows with the data and then query against them.

Why does someone believe there is a need for some externally maintained b-tree

What needs to be accomplished that "regular" rows and queries will not support?
_________________
All the best,

di

[bauer]

Well. regular rows / queries support the requirements.

But the topic is the waste of storage, because I'm talking about tables with several millions ( 200 - 300 ) of rows. The rows are only some integer values, all included to the index of the table.

If it would be possible to store the index only, without storing the basetable, it would require only 50% (approx.) of disk storage.

But thank you very much again for answering.

[papadi]

You're welcome

Someone may be able to provide suggestions if you post the specifics of what is to be accomplished. The requirement posted is quite generic so far.

What data is contained in these 3 columns? What relationships to any ofhter tables exist?

One way to do what you describe might be to implement outside the database using some kind of bdam file where only 1 set of the data was needed. This would be completely outside of the database processing and sql which might make this very unattractive. And there would always be the risk that additional features/functions could be added to the applicatoin requiring a complete re-do of the "special" b-tree.

Is there some way to partition these many millions of rows? Do all of the rows need to be online/available all of the time?
_________________
All the best,

di

[Dibakar]

[bauer]

Why 50% ?

My answer:

In the table I have 3 columns of integer = 3 * 4 = 12 Byte, the table contains n rows (n very large), so tablespace is 12 * n Byte (plus some DB2 internal maintenace data).

In the Index I have the 3 Columns, 3 * 4 = 12 Byte plus referenz to the row (RowId) plus some data to maintain the b Tree. So Indexspace = 12 * n Byte.

Only the index is accessed, the algorithm which deals with the data only operates on the index (explain: Index Only Access).

So if it would be possible to handle the datastructure "B Tree" without the depended table, is would be possible to save disk storage.


The scenario is some kind of datawarehouse, searchengine, this is the reason for the huge table. An 500 GB or 1000 GB diskstorage is a little bit a difference.

But ok, DB2 does not support handling of "basic data structures only". DB2 is not a toolkit for datastructures, DB2 is a relat. databasesystem.

regards,
bauer

[Dibakar]

[jsharon1248]

First, DB2 doesn't work that way. Indexes are not standalone objects. They require tables. Tables can be created without indexes, but indexes cannot be created without tables. Look at the syntax,