Corrupt Indexes

lbegley · Post by **lbegley** » Tue Jan 18, 2005 7:13 am

Last week our site had 2 instances where we were unable to view the joblists in Designer/Director/Manager. From this forum we received responses to correct this issue (DS.REINDEX ALL).
My question is: What would cause the index to get corrupt for DS_JOBS or DS_JOBOBJECTS? It seems rather strange that this would occur 2 days in a row.

Thanks.

kduke · Post by **kduke** » Tue Jan 18, 2005 7:29 am

You would need to do something like run out of disk space or kill a process with a kill -9 pid or some other situation which DataStage cannot recover from.

ArndW · Post by **ArndW** » Tue Jan 18, 2005 7:30 am

Since you wouldn't be asking this question if you'd had a machine crash, I will assume that you didn't have any. The most likely culprit is a full disk or partition during processing and writing - this is the most frequent cause of file corruption. Have you checked your disk loads?

lbegley · Post by **lbegley** » Tue Jan 18, 2005 7:47 am

We are a Windows environment.
Disk space is not an issue nor did we have a server 'crash'.
I and one other person have admin privs on the server (end a process via Task Manager) but we do have an local admin account that our Development team have access to to use Administrator to unlock processes.
Could any of the DS.TOOLS if executed without knowledge of there results cause this to happen?

kduke · Post by **kduke** » Tue Jan 18, 2005 8:49 am

I and one other person have admin privs on the server (end a process via Task Manager) but we do have an local admin account that our Development team have access to to use Administrator to unlock processes.

All of these can cause this. Why are you doing these things on a regular basis? These should be drastic measures not normal daily tasks. Locks exist to keep 2 processes from writing to the same record at the same time. You should not need to unlock anything nor should you ever need to end a process in Task Manager. Find ways not to do these.

lbegley · Post by **lbegley** » Tue Jan 18, 2005 8:58 am

These are not daily routines that are being executed. Process'es (for what ever reason, job aborts or client is closed with job running, are left behind.) Developer group on occassion, uses an Admin account to clear these locks for the reason that they cannot gain acess to the job, etc. For uvsh.exe process that have been around for a couple of days are the only process that are stopped via Task Manager.

Thanks.

kduke · Post by **kduke** » Tue Jan 18, 2005 9:02 am

That is why you are corrupting indexes. Teach your developers to logout of DataStage when they leave their desk.

lbegley · Post by **lbegley** » Thu Jan 20, 2005 10:14 am

Thanks to everyone for their candid responses.
This site has been using DataStage for about 6 months.
During training/mentoring we were provided the following:

" There may be times where the DataStage Designer did not close a job properly and the locks remain on a job. This can happen when the pc gets locked up and you have to shut down the DataStage client abnormally. When an attempt is made to re-open the DataStage job, a message is displayed stating the job is being accessed by another user.

Most people correct this problem by shutting down and starting up the DataStage Engine, but there is a way around this. The locks on the DataStage job can be released through the Universe, using Universe commands."

This is why we were using the process to 'unlock', it was assumed that this was a normal process.

Is their a 'best practice' that we should be following?

Thanks again

chulett · Post by **chulett** » Thu Jan 20, 2005 10:17 am

You might want to post the actual 'unlock' process you were given... what 'Universe commands' you are executing.

lbegley · Post by **lbegley** » Thu Jan 20, 2005 10:21 am

Per your request.

In order to use the UNLOCK command, it must be added as an entry into the VOC Universe file. The VOC Universe file holds all the verbs(commands) that may be executed.

To check to see if the UNLOCK command exists in the VOC file, log into the DataStage Administrator and into the project. Execute the command: UNLOCK
If there is an error message stating that the UNLOCK command is not in the VOC file, it must be added.

To add the UNLOCK command to the VOC file, type in the two following commands. These two commands should only be executed once for every instance of DataStage.

>SET.FILE UV VOC UV.VOC

>COPY FROM UV.VOC TO VOC UNLOCK

To view the locks Universe has, execute the following command from the command window in the DataStage Administrator:

>LIST.READU

You should get a listing of all objects that have a lock on them. Below is example output of the LIST.READU command.

EXAMPLE OF OUPUT NOT POSTED HERE.

The name of the DataStage job will be in the right-most column. When the DataStage job is found that you are looking for, make note of the Userno attached to it (fourth column in the listing).

To unlock the DataStage job, execute the following command:

UNLOCK USER <Userno> ALL

kduke · Post by **kduke** » Thu Jan 20, 2005 12:14 pm

This still should not happen on a regular basis. If people are in the Designer and have a job pulled up and walk away from their PC then tell them not to. Always leave no jobs up in Designer when you walk away from your PC. This is easy to do. If you develop good work habits then these issues go away.