Problem with user-owned index

[Anon Anon]

In a user-owned index set, the owner points to a valid index structure. However, due to a faulty backup/restore situation, the member records that the index points to no longer exist. Now the owner record cannot be deleted. Is there any way to delete the hanging index structure?

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: [phone] Fax: [phone]
Email: [e-mail]

[Chris Hoelscher]

Use what used to be called pfix to zap the pointers in the owner record to whatever they should be (null? Itself?)

Chris hoelscher
Technology Architect | Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:image001.png@01CD13D7.1A57CAF0]
123 East Main Street |Louisville, KY 40202
[e-mail]
Humana.com
Keeping CAS and Metavance safe for all HUMANAty

From: CA IDMS (IUA EIUA) Global User Community [mailto:[e-mail]]
Sent: Tuesday, October 02, 2012 1:20 PM
To: [e-mail]
Subject: [DBA Issues - Open Forum] Problem with user-owned index

In a user-owned index set, the owner points to a valid index structure. However, due to a faulty backup/restore situation, the member records that the index points to no longer exist. Now the owner record cannot be deleted. Is there any way to delete the hanging index structure?

Kay Rozeboom
Information Technology Enterprise
Iowa Department of Administrative Services
Telephone: [phone] Fax: [phone]
Email: [e-mail]<mailto:[e-mail]>
Posted by:Kay.Rozeboom
--
CA Communities Message Boards
99248194
[e-mail]<mailto:[e-mail]>
https://communities.ca.com


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.

[Gary Cherlet]

I would go with Chris's recommendation. PFIX the SR8 NEXT and PRIOR pointers in the Owner record to point to itself to remove it from the index structure. Without any Owner --> SR8 structure in place you will be able to ERASE the user defined owner record. The SR8's will continue to live in the database in a peperpetual sort of pergatory - but they will do nothing except occupy a bit of space.

HTH - cheers - Gary

[Anon Anon]

Why not just rebuild the user owned index? I sent Kay a program and JCL to rebuild user owned indexes, but it may fail if the index structure is corrupt. It’s an old Cobol program that was donated by someone and I have used it many times.



William M. Allen, Jr.

President

ARCH Consulting Associates, Ltd.

[phone]

From: CA IDMS (IUA EIUA) Global User Community [mailto:[e-mail]]
Sent: Tuesday, October 02, 2012 4:35 PM
To: [e-mail]
Subject: [DBA Issues - Open Forum] RE: Problem with user-owned index



I would go with Chris's recommendation. PFIX the SR8 NEXT and PRIOR pointers in the Owner record to point to itself to remove it from the index structure. Without any Owner --> SR8 structure in place you will be able to ERASE the user defined owner record. The SR8's will continue to live in the database in a peperpetual sort of pergatory - but they will do nothing except occupy a bit of space.

HTH - cheers - Gary
Posted by:Gary_Cherlet
--
CA Communities Message Boards
99251302
[e-mail]
https://communities.ca.com

[Tommy Petersen]

I responded via email yesterday, but I am not sure where that went, so at the risk of posting twice....

I feel your pain, I had a similar situation a few years back, thankfully with a chained set, not a user-owned index.

With all due respect to Chris and Gary, I would lean towards William's suggestion.

But I don't know the extent of the problem.

Have you considered restoring the database to a known good backup, and then roll forward to just before the problem, if that is still possible.
If the problem is with all the owners, meaning all member records are gone, you can run a restructure to set the pointers of the owner.
If the problem is with just a few records, PFix is certainly an option (it is now a BCF utility statement, FIX PAGE).
If the problem is that many members are missing, but not all, and it affects only part of the owners, or all owners, but members are missing in some of the sets, rebuilding the indexes might be the solution.
If the member record just happens to have the key for the owner record, you can run a restructure to null out the pointers for the set in both the owner and the member records, then run a program to connect the members back to the owners, this solution might not be ideal if there are many records, it could take quite a while to complete the connects.

Take a look at the jobs and programs you have. Since you have user-owned indexes there may already be proceures for rebuilding those.
Wouldn't it be nice if it was that simple.

If you do rebuild the indexes you need to write a program that calls TABX to create a file for the MAINTAIN INDEX utility.
If there are members for which you cannot find the owner, either beause it is not there or the index block is not there, you will need to account for that in the program, and write out a record to null out the pointer from the member to the index.
I don't know how you would handle it if the next or prior record is missing, I do not believe you can use the missing dbkeys in the MAINTAIN INDEX utility.

Then send the file into MAINTAIN INDEX starting with SORT3. I am sure all that is in what William has sent you. But if you are interested, you can look in the Utilities Manual for Maintain Index, seach for "user-written program".