Does anyone have a program to adopt orphans within user index sets that they can share?
I thought I'd ask before I write one.
Have you considered using the TUNE INDEX utility to do this?
Yes, and it works just fine, for EVERY user index occurrence in the area. When it comes to user index sets, TUNE INDEX is all or nothing.
That’s not what we want to do.
It’s the old 80/20 rule. 80% of the user indexes are fine, but the other 20% have varying amounts of orphans and need some TLC.
What we are going to do, one way or another, is come up with a program to which we can feed the owner information to and have it go out an adopt the orphans for that specific owner. It will have the ability to process as many owners as needed in single or multiple runs.
If someone has a working user index adoption program it will save some work. Either way around it, we’re going to write one. I’m looking for a starting point, but I can easily start from scratch.
Charles (Chuck) Hardee<mailto:Chuck.Hardee@ThermoFisher.com>
Senior Systems Engineer/Database Administration
EAS Information Technology
Thermo Fisher Scientific
300 Industry Drive | Pittsburgh, PA 15275
Phone +1 (724) 517-2633 | Mobile +1 (412) 877-2809 | FAX: +1 (412) 490-9230
Chuck.Hardee@ThermoFisher.com<mailto:Chuck.Hardee@ThermoFisher.com> | www.thermofisher.com
WORLDWIDE CONFIDENTIALITY NOTE: Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent of a system responsible for delivering the message to the intended recipient, is prohibited. If you are not the intended recipient, please inform the sender and delete all copies.
I have no proof this would work, but just a suggestion
If a non-SQL tune index requires a subschema name, what if y6ou were to create customized subschemas that contained only the index(es) you wish to tune – would that give you the selectivity you desire?
Technology Architect, Database Infrastructure Services
Technology Solution Services
123 East Main Street
Louisville, KY 40202
(502) 714-8615, (502) 476-2538
I think the stumbling block, here, is the definition of selection.
We have thousands, and in some indexes, millions, of owner records with user indexes to other records.
Some of the owner/members have so many updates that it is not unusual to have an owner with, say, 2000 members, and 3000 or 4000 orphan only SR8s.
Yes, it does get that bad.
We don’t want to select an index, that is to say, Index-1, we want to select a specific owner, for instance, the owner who’s key is 12345 (calc or Dbkey, doesn’t matter, just something that identifies it) and then adopt the orphans for the specific, unique, user index.
That is what we mean by selective.
Sorry I didn’t understand – so what you want is the ability to tune a specific user index OCCURANCE …..
You hit the nail on the head. Now, if we can just get a big enough hammer!
First let me say that TUNE is the way to go even though it processes all set occurrences for user-owned indexes.
Next, a user program to force adoption will only adopt on the bottom level of the index.
I think you know the standard method of doing this. It is quite simple.
You have to access the members via something Other than the index set in question so you do not establish full currency within the index. Typically this is done by Area Sweep. As you Obtain each member in the area, you would then RETURN dbkey-fld FROM index-set-name NEXT. Then you continue the Area Sweep to the next member record. The RETURN does not affect currency and since the DBMS does not have full currency in the index set when you Obtain the member by area sweep (all it has are the pointers in the member record) you will force the DBMS to use the INDEX UP pointer in the member to go to the SR8 that supposedly contains the current member's entry, so it can then return to you the dbkey of the NEXT in set. To do this it will force orphan adoption until it finds the current and next member entries. (make sure the area is readied in UPDATE :-) )
This was used by many customers (and me) before TUNE existed, but as described I think you see it must do all members in the area.
To do what you want to do it would require the members to have OWNER POINTERS. Do they? If so I think this would work, although I don't know anyone who has tried it. If you create a file or internal table of owner dbkeys whose sets you want to process, for each member obtained in the area sweep, before doing the RETURN that will force the adoption, you could RETURN dbkey-fld FROM index-set-name OWNER and see if it is one in your list. I believe this will only work if the members have owner pointers because the OWNER will be established when you Obtain the member by Area Sweep and this Return will not have to go up into the index structure to get the Owner.
In my reply with the suggested program code that used Area Sweep on the member record type and issued the RETURN... NEXT CURRENCY for each member obtained in the Areas sweep, I said the RETURN does not affect currency. What I meant was it does not affect AREA Currency. That is why this pre-TUNE user-written program method used by many users in the past, did not need to go through any extra code to re-establish Area currency so no members would be missed.
What Chuck did was more efficient than Area sweep because a separate program first walked the specific set occurrence he wanted to Tune (adopt orphans) and wrote a file of member dbkeys in the order they were encountered when walking the set in the Next direction.
The Adoption program then would read the dbkey file, Obtain the member by dbkey, then issue the RETURN... NEXT CURRENCY.
When Chuck tested and said that no orphans were adopted, that seemed odd to me since the Area Sweep method always worked for customers who used it back before Tune Index was created. Further investigation revealed that his method did force adoption of the first orphaned member, but no more.
I reproduced this on my CV with my own small test database.
Since the first one was adopted, at a point when there was definitely no currency of any kind established for the set, I thought I'd try obtaining the orphaned members by dbkey in the reverse order. So I obtain the last member in set by dbkey, issued the RETURN... NEXT CURRENCY and continued this for each member, moving backward.
All orphaned members were adopted.
Chuck changed the extraction program to read the set backward so the member dbkeys would be written to the flat file in reverse order and the adoption program then was successful in adopting all orphaned member records. As mentioned this type of program only clears orphans on the bottom level of the index. TUNE does all levels.
The reason the older Area Sweep method worked was because the members were not being obtained in set order. The order in which they were encountered during Area Sweep was random.
When we Obtain an orphaned member and are forced to use its Index UP pointer to find Next in set, the SR8 pointed to by the UP pointer does not contain the current member's entry so we go through the process of Orphan Adoption until we find the current member's entry and then we can return the Next member's dbkey.
It seems that when the RETURN... NEXT is executed it establishes NEXT currency for the set so subsequent RETURNs are not forced to use the UP pointer to get Next.
Processing the member dbkeys in reverse order forced it each time.
That being said, I would still recommend the IDMS utility TUNE INDEX which we know will clean up all orphans in the index.
You could create a program that accesses the specific owners, then run through the set for each owner saving the dbkey, and I believe you can save it in a table in the program, after running through the set issue a COMMIT ALL, then obtain the members by dbkey and get next dbkey, do another COMMIT then go to the next selected owner.
Alternatively you can extract the DBKeys for the members of the owners you are interested in and store them in a file then use that file as input to the next program.
Just remember, the area(s) must be readied for update.
Yep, we’ve written a program to do just that and we have been unsuccessful at adopting orphans.
We have two programs, one locates the owner record and then walks the user index set. For each member in the index set it gets the DBKey and writes it to an output file. The next program in the sequence reads the DBKey file and does a FIND DBKEY.. and then it does a RETURN DBKEY NEXT to force traversal up to the SR8 and then thru the SR8s to get the dbkey of the next record in the index, adopting orphans along the way.
Oh, yeah, the area are in update mode. Two areas, one for the owners and SR8s, one for the members.
We do a PRINT INDEX OWNER.. for the owner DBKey before and after the program runs.
The numbers in both print indexes are the same.
That is why I am asking for a sample of a working program. I want to compare what we are doing against something that someone else is actually using.
So far, everyone has made suggestions but no one has offered up a sample.
I’ve opened a ticket with CA, and given them our program but I suspect that after they change the record names in order to run it on the CA systems, it will work there, but still won’t work for us.
Charles (Chuck) Hardete<mailto:Chuck.Hardee@ThermoFisher.com>
Think it is worth asking CA if they could provide a variation of "Tune Index" which has a DB-Key "driver file" as input? It shouldn't be much work for CA and it would mean the "heavy lifting" work would be done by a CA Utility?
Just a thought - it doesn't cost anything to ask (well - any more than what is already paid in annual maintenance)! So may as well get your $'s worth ... Cheers - GaryC
I would prefer the syntax the TUNE INDEX to accept an owner key and “tune” the explicit index from there.
Here’s the syntax of the TUNE INDEX we use:
TUNE INDEX FOR
DEFAULT TUNE OPTIONS
NOTIFY INTERVAL 1000
What I would prefer is that it be modified like this:
Then, instead of doing all user index occurrences for the ACTIVE-PA-EXCPT user index, it would “TUNE” just the occurrence identified by the owner dbkey 5062418:14.
That is what is accomplished with a user written program with one exception. A user written program does not clean up orphans at the intermediate level, only the bottom level “next to” the member records. The TUNE INDEX utility cleans up orphans at ALL levels so, in the long run, it’s a better choice, but it definitely needs the ability to handle a single user index occurrence.
However, it would be nice if the OWNER clause would accept one or more owner keys, or, possibly, a DD name which points to a file of dbkeys.
Okay, I’m asking a slightly different question now, but it has 100% bearing on the original question of a program being available.
Is anyone out there executing a user written program to adopt index orphans *AND* the user index set is defined as OPTIONAL MANUAL?
I have a ticket open with CA and they are adamant that it works.
WE (I) on the other hand have a program, written and verified by CA as being correct, and it absolutely DOES NOT adopt orphans.
I have asked the question of CA what their set participation is, but being it’s Sunday, they aren’t in yet.
Our set is OPTIONAL MANUAL, and orphans aren’t being adopted but CA says that they should be. This is why I am now looking for anyone that has a user written program to adopt orphans and the target used index is defined as OPTIONAL MANUAL.
It may be that the set could be OPTIONAL AUTOMATIC or MANDATORY MANUAL, but since my set isn’t either of those, I don’t want to suggest that they may or may not work. Ultimately it may be an issue with optional set participation is thwarting or skipping the adoption process, but right now, all I can say is the MANDATORY MANUAL is not working.
Thanks in advance for all your feedback.
Create a dummy schema where the set IS defined as MA??? And use IT??
Great minds think alike.
I plan on doing that tomorrow morning when I can get a copy of the data structure diagram and figure out which entities I need.
I’d do it from home today, but I don’t have the info at my finger tips.
One thing going for me is that while there’s about 16 areas with 7 user indexes in them, I can create a complete standalone schema and change the set(s) as needed.
Well, as I typed my last reply I remembered that there were only 16 areas in the database section where this index lives.
That made me think, I should be able to edit out what I need from the global schema to create a smaller schema with the view of the data I want.
Well, 45 minutes later I have a complete schema providing me a view of the records and sets within the 16 areas containing these 7 indexes.
Not only did that 45 minutes get me a schema, it also got me a subschema, a test of my adoption program and a print index of the index after the program was executed.
So, at this point you’re probably wondering, “well, did it work?”.
And the answer is, no. Nothing changed.
And, yes, I changed all of the OPTIONAL MANUAL user indexes to be MANDATORY AUTOMATIC.
So, the problem is definitely something subtle since CA is saying this works and I can prove that it doesn’t.
And, as to the authority at CA, yes, I have complete confidence in this person and do not doubt for a second that they are telling me something incorrect.
Any other thoughts?
Retrieving data ...