ca.portal.admin

Re:Indexes with Duplicates Problem

Discussion created by ca.portal.admin on Mar 6, 2006
Hello



On the weekend I was running an unload/reload on a number of Areas in our database. I noticed that the IDMSDBL3 step was taking a long time and doing a lot of I/o's

In this job I was Unloading and Reloading a large Index area. I put a Page reserve on it, like I always do to tune the Index to allow for new records on the page and for the records to increase in size. I build the index using a ICB and then increase it by a couple to allow insertions without the index splitting and spawning. I worked out the number of full sized records that could reside on a page. It worked out to be 5 on a page size of 13680. I put a page reserve of 2540, The SR8 Record size was to be 2539 and I could get 4 records on the page and it would be over the 70% full and mark the SMP Page.



I cancelled the Reload job after it had been running for about 1 ¾ hours and restored the database. I modified the DMCL and removed the page reserve from that area, I reran the Unload and the reload and everything worked out fine. The IDMSDBL3 step took about 10 minutes. I ran a print Index on the area this morning and noticed most of the Level 0 SR8's were not full length SR8's some were as small as 760 bytes.

This Index is defined as

Duplicates are last and uncompressed.

I looked at a number of other indexes I had rebuilt on the weekend and noticed that any Index that had duplicates allowed and uncompressed that the Level 0 SR8's were not full size. Indexes that did not allow duplicates, the Level 0 SR8's were full size.




The Database Administration Manual states that there is to be full key definitions when duplicates are allowed and no compression is to take place when the Index is defined as Uncompressed.



I ran this same Unload/reload on February 24th and had the same problem. I thought I had done something wrong in my calculations so I cancelled the Job after it had done 8-9 million I/O's.



I have never had this type of problem before when I have used page reserve on indexes that I have built or rebuilt using Maintain Index or when doing and Unload/Reload. I have been doing this for almost 20 years.



We are running IDMS Rel 16 SP1 on ZOS 1.6.



Dennis Robock

IDMS DBA

Alberta Department of Energy







This communication is intended for the use of the recipient to which it is addressed, and may contain confidential, personal and or privileged information. Please contact us immediately if you are not the intended recipients of this communication, and do not copy, distribute, or take action relying on it. Any communication received in error, or subsequent reply, should be deleted or destroyed.

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








Normal

Normal
Re: interesting phenomena (was[IDMSVENDOR-L] Indexes with Duplicates Problem)
"Here is the index definition from the schema

ADD
SET NAME IS TRANCMT-IX
ORDER IS SORTED
MODE IS INDEX USING B-TRANCMT-IX
OWNER IS SYSTEM
WITHIN AREA BT-TXNCMIX-A OFFSET 1 PAGES FOR 100 PERCENT
MEMBER IS DB-TRANCMT-BT
WITHIN AREA BT-TRANCMT-A
INDEX DBKEY POSITION IS 1
MANDATORY AUTOMATIC
KEY IS (
TM-GENRC-TRAN-CLASS-BT ASCENDING
TM-GENRC-TRAN-NBR-BT ASCENDING
TM-GENRC-TRAN-PRI-SFX-BT ASCENDING
TM-GENRC-TRAN-SEC-SFX-BT ASCENDING
TM-PROCSSD-DATE-BT ASCENDING
TM-PROCSSD-TIME-BT ASCENDING
TM-USER-ID-BT ASCENDING )
DUPLICATES ARE LAST
UNCOMPRESSED

UNCOMpressed

Applies to sorted indexed sets only and specifies that similar
index
entries will be maintained in their entirety.
Database Administration Manual 13.6.2



All SR8's should be full length. When I look at the Print Index for
this index, there are no full length SR8's in Level0. The Level 3,2 and
1 are 2532 bytes.
I am expecting the Level 0 SR8's to be 2532 as well.
My question is why are the lower Level SR8's being compressed.

The index key is 41 characters and 50 keys per SR5
Which works out to be 2539
2539 * 4 = 10156
70% of page size 13680 is 9576

Dennis Robock
IDMS DBA
Alberta Department of Energy

Outcomes