Job Hangs in Queue

I had 2 different jobs with problems last night. In each case the job started and was in setting in its queue, but nothing appeared to be happening with the job. I killed the job in the queue and re-ran each job and it ran fine. These jobs are JAL script jobs and the first step they do is to call a user-defined-function that writes some logging information to an Oracle table (nothing was written to the table). I have been doing this for some time, about the only thing I changed was that I pass a macro variable to the UDF now instead of the hard-coded job name, see below:

insert_status ("@V"job_name"")

This appears to work most of the time but for some reason these jobs hung last night and I'm afraid this may be a re-occuring problem. They are set up synchronous and non-detached. I was thinking of changing them to detached, but not sure if that would affect this at all. Any ideas?

Thanks,
Jeff

I decided to turn on tracing, so if it happens again I can hopefully see what step is hanging. Are there issues with leaving tracing on for several days?

Thanks,
Jeff

The only side effect is that the trace file can potentially contain some sensitive information, such as log records for executed programs and their command line parameters, including user names, password, and so on, that you don't want other people to see.

I finally encountered this problem again after having the tracing turned on. The job normally runs at 6:15am for about 10 minutes. This morning it started but never completed. It is still setting in the queue showing that it is running. When I look at the trace log, I get the following:

**** 5/12/2010 06:15:01 ****

1: DIM
1: Executing DIM("PROCESSID", "NUMBER")
2: DIM
2: Executing DIM("RECIPIENTS", "STRING")
3: DIM
3: Executing DIM("LOGFILE", "STRING")
4: DIM
4: Executing DIM("SUBJECT", "STRING")
5: DIM
5: Executing DIM("RESULT", "STRING")
6: DIM
6: Executing DIM("PROBLEM", "BOOLEAN")
7: DIM
7: Executing DIM("MSG", "STRING")
8: DIM
8: Executing DIM("REJECTDIR", "STRING")
9: DIM
9: Executing DIM("REJECTS", "STRING")
10: DIM
10: Executing DIM("COUNT", "NUMBER")
12: SET
12: Executing SET("recipients", "msx_dl_team_grpplanningsystems@****.com")
15: SET
15: Executing SET("logfile", "e:\Transfer\incstmt\logs\updateincstmt.log")
16: SET
16: Executing SET("rejectdir", "e:\transfer\incstmt\rejects\")
19: INSERT_STATUS
19: Executing INSERT_STATUS("Update Incstmt.Rptg")

------------ Start of INSERT_STATUS ------------
1: DIM
1: Executing DIM("ROWS", "NUMBER")
2: DIM
2: Executing DIM("JOB_NO", "STRING")
3: DIM
3: Executing DIM("SEQ_SQL", "STRING")
4: DIM
4: Executing DIM("INSERT_SQL", "STRING")
5: DIM
5: Executing DIM("GLOBAL.SEQ_KEY", "NUMBER")
6: DATABASECONNECT
6: Executing DATABASECONNECT("edwp")

I am running a user defined function to insert the status to a relational table in the database EDWP. However, it appears to have hung on the databaseconnect command. We ran this same job remotely at 7:15am and it ran fine. This seems to happen randomly to my jobs, any idea how to correct?

That is unfortunately a dark spot. DatabaseConnect loads db driver, ask the driver to connect, the diver loads something else, attempts to connect to the database server, etc... What I want to say is that the connection can hang in any of these steps, most of which are outside of 24x7.

I think if this issue is frequent, you should try using an alternative database connection driver, for example, using ODBC instead of ADO, or native instead of ODBC or visa versa.

It is also a good idea to set the job to run detached, so that if it hangs, it doesn't affect other jobs in other job queues; also set a timeout for the detached job, so that the scheduler can kill it after timeout.

Ok, thanks for the input. I will try those changes.