Every few months of running DataStage contineously, it starts behaving oddly (missing jobs/weird errors in Director) and then most of the times a rebuild/reindex of the repository indexes solves our problem.
I remember a discussion a time ago on this forum on how to check whether a rebuild is required, something doing a select on 2 uv tables and comparing the output. I searched on the forum but can't seem to find the discussion anymore (maybe the posts that were erased a time ago).
Does anyone still remember (or can point me to) how I can see whether repository indexes need to be rebuilt in order to do it proactively if it happens.
Regards,
Ogmios
Repository rebuild checking
Moderators: chulett, rschirm, roy
Repository rebuild checking
In theory there's no difference between theory and practice. In practice there is.
-
talk2shaanc
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
Well you could have easily found relevant thread, if you would have used keyword "rebuild" while searching.
Anyways, to check if the jobs in your project needs index rebuilding. Use: LIST.INDEX DS_JOBS ALL, on Command tab in DataStage Administrator, to re-build the indexes.
If it requires rebuilding of index. Use DS.REINDEX DS_JOBS ALL, on Command tab in DataStage Administrator, to re-build the indexes.
NOTE: Before rebuilding the indexes, make sure that none of the user is working on that project.
For rebuilding of indexes for all the objects of Datastage use; DS.REINDEX ALL
Anyways, to check if the jobs in your project needs index rebuilding. Use: LIST.INDEX DS_JOBS ALL, on Command tab in DataStage Administrator, to re-build the indexes.
If it requires rebuilding of index. Use DS.REINDEX DS_JOBS ALL, on Command tab in DataStage Administrator, to re-build the indexes.
NOTE: Before rebuilding the indexes, make sure that none of the user is working on that project.
For rebuilding of indexes for all the objects of Datastage use; DS.REINDEX ALL
Shantanu Choudhary
ogmios,
I don't know which tables were referred to specifically, but the method is to explicitly disable use of the alternate keys (indices) on one query while allowing it in the other. The main table to compare would be, in my opinion, DS_JOBS; so you could do a "LIST DS_JOBS WITH JOBTYPE NE 0 BY JOBTYPE DET.SUP BREAK.ON JOBTYPE TOTAL EVAL '1'" and then CLEARSELECT; then do the same command with "NO.INDEX" appended. The counts need to be the same, this just checks whether or not the index for JOBTYPE is correct; there are other columns which are indexed as well so in order to be 100% certain you would need to select rows based upon each of the indices.
The alternative is to do a DS.REINDEX ALL which takes a lot less time than the aforemention check.
I don't know which tables were referred to specifically, but the method is to explicitly disable use of the alternate keys (indices) on one query while allowing it in the other. The main table to compare would be, in my opinion, DS_JOBS; so you could do a "LIST DS_JOBS WITH JOBTYPE NE 0 BY JOBTYPE DET.SUP BREAK.ON JOBTYPE TOTAL EVAL '1'" and then CLEARSELECT; then do the same command with "NO.INDEX" appended. The counts need to be the same, this just checks whether or not the index for JOBTYPE is correct; there are other columns which are indexed as well so in order to be 100% certain you would need to select rows based upon each of the indices.
The alternative is to do a DS.REINDEX ALL which takes a lot less time than the aforemention check.
</a>