Encountered an interesting gotcha during a recent PeopleTools upgrade using Change Assistant.
One of the CA SQL jobs failed trying to drop an index that did not exist. The index really didn't exist either in the database or in meta data against the PSINDEXDEFN table and so I couldn't understand why the CA job was scripting the delete.
After much head scratching here's what we found.
The table in question was the PSPMTRANSARCH (Performance Monitor archiving table) and the index (which didn't exist) that CA was trying to delete was PSPMTRANSARCH.PSSMTRANSARCH.
The PSPMTRANSARCH table did however have one additional index which was not defined in Application Designer. It was an experimental index added directly in the database that had not made it's way into the Record definition in App Designer. The name of this index was GRS_TEST_INDEX and therefore did not conform to the standard index naming convention used when the index is created in App Designer. The standard is that the name of the index is the same name as the RECORD object name with the PS_ prefix The 3rd character _ is then reserved for the KEYS index and custom indexes replace the 3rd character _ with a single alpha character (sequencing multiple indexes starting with A).
When App Designer was asked to script the table alter (which includes index alters) it sees there is an index in the database that does not exist in App Designer, it assumes the index must have been created legitimately by App Designer and therefore must be in the standard naming convention. It therefore scripts the DROP INDEX assuming the index name is the same as the record name and leaves the 3rd character alone - in this example the 3rd character of the rogue index GRS_TEST_INDEX is S so App Designer scripts DROP INDEX PSPMTRANSARCH.PSSPMTRANSARCH. This index does not exist and so returns and error which is trapped by CA.
Lesson to be learned here? Always build indexes in App Designer. if you can't (and there are some instances where you have to work outside App Designer) then be aware of this gotacha!
One of the CA SQL jobs failed trying to drop an index that did not exist. The index really didn't exist either in the database or in meta data against the PSINDEXDEFN table and so I couldn't understand why the CA job was scripting the delete.
After much head scratching here's what we found.
The table in question was the PSPMTRANSARCH (Performance Monitor archiving table) and the index (which didn't exist) that CA was trying to delete was PSPMTRANSARCH.PSSMTRANSARCH.
The PSPMTRANSARCH table did however have one additional index which was not defined in Application Designer. It was an experimental index added directly in the database that had not made it's way into the Record definition in App Designer. The name of this index was GRS_TEST_INDEX and therefore did not conform to the standard index naming convention used when the index is created in App Designer. The standard is that the name of the index is the same name as the RECORD object name with the PS_ prefix The 3rd character _ is then reserved for the KEYS index and custom indexes replace the 3rd character _ with a single alpha character (sequencing multiple indexes starting with A).
When App Designer was asked to script the table alter (which includes index alters) it sees there is an index in the database that does not exist in App Designer, it assumes the index must have been created legitimately by App Designer and therefore must be in the standard naming convention. It therefore scripts the DROP INDEX assuming the index name is the same as the record name and leaves the 3rd character alone - in this example the 3rd character of the rogue index GRS_TEST_INDEX is S so App Designer scripts DROP INDEX PSPMTRANSARCH.PSSPMTRANSARCH. This index does not exist and so returns and error which is trapped by CA.
Lesson to be learned here? Always build indexes in App Designer. if you can't (and there are some instances where you have to work outside App Designer) then be aware of this gotacha!
Comments