Occasionally, Applications Manager (AM) will have difficulty processing jobs due to corrupt data in the so_job_queue table. If this should occur and it is necessary to clean out the so_job_queue table you can create a backup of the table in order to be able to review that data later and re-submit those jobs that you want to run again. Once you have stopped all the Applications Manager processes you can use the SQL statements below to help you do that.
CAUTION: All Application Manager Processes should be stopped prior to deleting any data from any of the AM tables.
Log into the machine where AM is installed as the AM OS user and execute the sosite file to set up the environment and then issue a "stopso all" command. Verify that all the AM processes have stopped prior to preceding. Once all the AM processes are no longer running you can delete the data as outlined below.
Copy all the data currently in the so_job_queue table and store it in a temporary table using this SQL statement:
SQL> create table bu_so_job_queue as select * from so_job_queue;
Then you can delete all the rows in the so_job_queue table:
SQL> delete from so_job_queue;
Once the jobs have been cleared from the so_job_queue table, you should also delete all the data from aw_job_queue_activity table so there is no longer a reference to any of the jobs that you just deleted from the so_job_queue table.
SQL> delete from aw_job_queue_activity;
At this point you can restart all the Applications Manager processes if you want and then continue to evaluate the data in the bu_so_job_queue table to decide which jobs you want to restart.
Following are some useful SQL statements that can be used to continue to look at the information from the deleted so_job_queue table.
In order to see how many jobs were in each status you could run:
SQL> select so_status_name, count(so_status_name) from bu_so_job_queue
group by so_status_name;
It will display results similar to this:
PRED WAIT 21
STG SKIP 31
CONDITN WAIT 1
11 rows selected.
If you want to list all the jobs that were in the so_job_queue table and sort them by the Process Flow they were in and then by jobid, you can run this SQL:
Your results will be displayed similar to this:
SQL> select so_module, so_job_seq, so_jobid, so_chain_seq, so_chain_id from bu_so_job_queue
order by so_chain_id, so_chain_seq, so_jobid;
SO_MODULE SO_JOB_SEQ SO_JOBID SO_CHAIN_SEQ SO_CHAIN_ID
------------------------------ ---------- ---------- ------------ -----------
INC0324875_TEST7 797 5982 5982
SFTP-SIMPLE 33 5984.01 797 5982
SFTP_#2 33 5985 797 5982
SFTP_#1 33 5986 797 5982
SFTP_AGENT 33 5987 797 5982
SFTP_TOP_FLOW_ID 33 5988 797 5982
INC0324875_TEST7_1 804 5989 5989
SFTP-SIMPLE 33 5991.01 804 5989
SFTP_#2 33 5992 804 5989
SFTP_#1 33 5993 804 5989
SFTP_AGENT 33 5994 804 5989
SFTP_TOP_FLOW_ID 33 5995 804 5989
TEST_JOB 20 5996
In the above sample it shows that there were 2 Process Flows (INC0324875_TEST7 and INC0324875_TEST7_1) and one job (TEST_JOB) in the backlog when the records were deleted. You can then decide which jobs you want to request again to run.
Once you have Applications Manager back up and running jobs successfully and you have requested all the jobs you want to restart, you should drop the temporary so_job_queue table that you created by executing the following SQL statement:
SQL> drop table bu_so_job_queue;