ca.portal.admin

Re:Re: Index definition

Discussion created by ca.portal.admin on Aug 5, 2009
Kay,

Usually you do not have much choice after the programs have been written,
changing the sequence or the order of the fields may affect programs and
how they access the data.

The best way to decrease the number of splits is simply to increase the IBC
for the index, if you double it you basically cut the number of splits in
half.
However, depending on the page size and the size of the SR8 that may not
always be possible.

I usually play around with the IBC and page reserve during index rebuilds.

Let's say you have an index where the IBC=100 and a page size of 11476.
When you do your rebuild you use a special schema that has an IBC=90 and a
special DMCL with a page reserve for the area=1148 (10%).
When you go back to using the original schema and original DMCL you now
have room for growing the SR8s by 10% without having to split them.

They will of course not grow that even, but you will reduce the number of
splits significantly, especially just after the index rebuild. If you have
much growth in the area, you may want to go a little higher on the page
reserve (maybe add the size of an SR8). This way when you do get a split,
initially you have a bigger chance that it can split and stay on the same
page - as far as I remember IDMS first checks if there is space on the
current page.

If you are really concerned about the performance, you should also use a
displacement, it moves the leaf-level SR8s away from the root of the index,
making room for the intermediate SR8s between the root and the leaf level.

Always remember that the maximum size of an SR8 should not be larger than
30% of the page size.
Larger SR8s increase the likelihood of deadlocks, but can improve
performance.
Smaller SR8s increase the splits and can hurt performance.

Tommy Petersen
110 Cokesbury Rd
Room 542H
Lebanon, NJ 08833

Phone:
Internal 200 - 3699
External (908) 236-3699
Fax: (908) 236-3692




""Rozeboom, Kay
[DAS]""
<KAY.ROZEBOOM@IOW To
A.GOV> IDMS-L@LISTSERV.IUASSN.COM
Sent by: IDMS cc
Public Discussion
Forum Subject
<IDMS-L@LISTSERV. Index definition
IUASSN.COM>


08/05/2009 10:28
AM


Please respond to
IDMS Public
Discussion Forum
<IDMS-L@LISTSERV.
IUASSN.COM>






We have an index defined as below:

KEY IS (
FIELD1 ASCENDING
FIELD2 ASCENDING)
DUPLICATES ARE FIRST

FIELD1 has a lot of duplicates, FIELD2 not so many. Neither field is
""naturally increasing"" such as a time stamp.

Is this index defined the best way to reduce the number of SR8 splits?
Would it better to define the key in descending order, or have duplicates
last?

Kay Rozeboom
State of Iowa
Information Technology Enterprise
Department of Administrative Services
Telephone: 515.281.6139 Fax: 515.281.6137
Email: Kay.Rozeboom@Iowa.Gov
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: [IDMSVENDOR-L] Index definition
"Cool stuff if you have the time.


well of course you have the time - you just did a store clock instruction
......

Chris Hoelscher
Senior IDMS & DB2 Database Administrator
Humana Inc
502-476-2538
choelscher@humana.com

you only need to test the programs that you want to work correctly




The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Index definition
"Cool stuff if you have the time.


well of course you have the time - you just did a store clock instruction
......

Chris Hoelscher
Senior IDMS & DB2 Database Administrator
Humana Inc
502-476-2538
choelscher@humana.com

you only need to test the programs that you want to work correctly




The information transmitted is intended only for the person or entity to which it is addressed and may contain CONFIDENTIAL material. If you receive this material/information in error, please contact the sender and delete or destroy the material/information.
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Duplicate index keys
"When defining an index key that is composed of multiple fields and
duplicate keys are allowed the duplicates characteristics of the
individual fields has no impact. IDMS considers the value of the entire
concatenated key when determining if a duplicate key exists. If FIELDA
has many duplicate values and FIELD2 has very few duplicates then your
index will have very few duplicate keys. =20

=20

Dick Weiland

=20

CA

Senior Software Engineer

CA-IDMS Level-II Support

=20

Phone 630-505-6561

Fax: 630 505-6097

Email: Richard.Weiland@ca.com <mailTo:Richard.Weiland@ca.com>=20

=20

=20

=20

=20

=20

=20

=20

=20

=20

=20

=20

=20

=20
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Duplicate index keys
"When defining an index key that is composed of multiple fields and
duplicate keys are allowed the duplicates characteristics of the
individual fields has no impact. IDMS considers the value of the entire
concatenated key when determining if a duplicate key exists. If FIELDA
has many duplicate values and FIELD2 has very few duplicates then your
index will have very few duplicate keys.



Dick Weiland



CA

Senior Software Engineer

CA-IDMS Level-II Support



Phone 630-505-6561

Fax: 630 505-6097

Email: Richard.Weiland@ca.com <mailTo:Richard.Weiland@ca.com>


























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








Normal

Normal
Re: Duplicate index keys
"Wellllll.
Duplicate keys is the least of your problems if you have such a
situation as do we . . .=20
We might have hundreds of thousands, even millions, of very large keys
(30 - 60 bytes) with the first half are duplicates and the last half
might not be, but this causes indexing to have many intermediate records
with identical keys, so that the search algorithm (IMVHO) for a record
somewhere within that range is not efficient, if you see what I mean . .
.=20

Plus doesn't this lend itself to hotspots in the index, where
split-and-spawns are likely to occur?

Outcomes