ca.portal.admin

Defining System Owned indexes with  Offsets

Discussion created by ca.portal.admin on Sep 27, 2006
Latest reply on Sep 27, 2006 by ca.portal.admin
Hello Group,
Can anyone shed any insight on the pros and cons of using OFFSETS on the
WITHIN AREA clause while defining indexes. If defining several indexes
within a single area, Is it best to partition the indexes off based on
page range or percentages or could I just not use offsets and let the
indexes battle it out as to where they are within the area.

TIA
Tim
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Defining System Owned indexes with Offsets
"Tim,

Once you isolate each index to its own area (the only way I'll ever do
it for the reasons stated by Bill and Kay), then if you use the formula
in the DBAdmin manual to calculate the offsets, All the L1 SR8s will be
isolated from the L2 SR8s so that a heavily-used index will keep the
pages containing the L1 SR8s in its buffer (yes, whenever possible, I
create a buffer for the index to have all to its own) thereby reducing
paging.

Someplace I have a handy-dandy XL spreadsheet that calculates all the
offsets that works fine on everything as far as rel 15. If you want a
copy, let me know.

John Wheeler
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Defining System Owned indexes with Offsets
"Thanks for the insights all.

We have several index areas with multiple indexes in them. One of the
problems I encountered was a user getting a 1211 on an index that was in a
multiple index area. I ran DB-Analyzer but the area was only half full.
There is no easy way to determine how much space the individual indexes
are using.
I guess the best thing going forward is to start breaking those indexes out
into their own areas.

Thanks
Tim




Bill Allen
<ARCHCONB@AOL.COM To: IDMS-L@LISTSERV.IUASSN.COM
> cc:
Sent by: IDMS Subject: Re: Defining System Owned indexes with Offsets
Public Discussion
Forum
<IDMS-L@LISTSERV.
IUASSN.COM>


09/27/2006 01:18
PM
Please respond to
IDMS Public
Discussion Forum






Hello Tim:

I agree with Kay 100%. I don't recommend that you put more than one index
into an area because there are to many issues to deal with.

For example, if you put multiple index sets into one area with offsets,
what
happens if one of the indexes needs more space? You can do a number of
things such as changing the offsets or running an expand page. But, in this
example you only need to increase the space for one index and now you have
to
expand the entire area. If you change the offsets of one index what will
the
effect be on the other index sets in the area? A new analysis of all the
index
sets would be required.

By isolating one index to one area you can deal with any issues a lot
quicker.

You might consider putting multiple files in the area and isolating each
index to a specific file, but there are gotcha's with that technique as
well.

My rule of thumb is one to one mapping when it comes to index sets.

Bill Allen

In a message dated 9/27/2006 12:52:25 P.M. Eastern Daylight Time,
KAY.ROZEBOOM@IOWA.GOV writes:

If you are concerned about performance, each index should be isolated.
We put every index in its own area at our shop. This has the added
benefit of allowing you to use index-tuning features such as
displacements and page reserves. =20

Outcomes