I have a listener (a batch job written in shell script) that calls a perl file (say apple.pl). The listener runs at scheduled timing (say 11 pm to 6 am)&.
Owner of the listener is a common id.
My question is..
Once the listener starts running (here, Im NOT attempting to generate any reports from my application. Just starting the listener), 0kb AT jobs get queued showing the id of the listener owner.
Per second 10 AT jobs are getting queued. Max size of the queue is 29000
What will be the reason for this?
Note: This listener is working for years. We noted this weird behavior when we started getting problems last month in generating the reports since the log said at: cant be created for you. at.allow had my username.
Time being weve removed my name from at.allow so that the AT queue does not reach the max size.
Im using the d queue in my code.
When I executed the below command in my server, there was no d queue defined. I just listed a and b queue
cat /etc/cron.d/queuedefs
Will this add to the cause of the issue?
Will running any patch cause such weird behavior of the listener?
Please advise.
apple.pl
.
.
.
$file_cnt = 0;
AGAIN:
# $!
# $! Define logical for error checking
# $!
#ACE comment: log.name was replaced with file $job_logicals/PIPE_FAUCET_STATUS_$my_pid.
$job_logicals = $ENV{JOB_LOGICALS} or die "Can't get env. JOB_LOGICALS.\n";
$my_pid = $$;
$my_file = "$job_logicals/PIPE_FAUCET_STATUS_$my_pid";
system("touch $my_file");
use my_timestamp;
$time_stamp = file_suffix();
$time_stamp = substr($time_stamp,0,16);
$file_cnt = $file_cnt + 1;
$RUNFILE = $LOGFILES . "/E401226R_" . $time_stamp . "_" . $file_cnt . ".PL";
print "Run file: $RUNFILE\n";
#ACE comment: add $my_pid as second parameter to refer to $JOB_LOGICALS/PIPE_FAUCET_STATUS_$my_pid file.
system("sqlplus -S / \@$RATING/orange.SQL $RUNFILE $my_pid");
$jobstat = $?;
if ($jobstat != 0) {
goto ERROR_CHECK;
}
system("at -f $RUNFILE -q d now");
goto AGAIN;
ERROR_CHECK:
# $! If job is still waiting for message then shutdown gracefully
if (-e "$my_file") {
unlink "$my_file";
exit;
}
exit $jobstat;
Content of orange.sql
WHENEVER SQLERROR EXIT FAILURE
SET TERMOUT ON ECHO ON DOCUMENT OFF
SET LINESIZE 250 PAGES 0
REM *************************************************************************
REM * orange.SQL - Run stored function that checks DBMS_PIPE for message.
REM * Spools message to a file; message is actual DCL command
REM * line that will execute a report script file.
REM *************************************************************************
DEFINE SPOOLFILE = &1
REM ACE Comment: add parameter to identify file that must be deleted (replaced the logical name).
DEFINE TOKEN = &2
VARIABLE BATCH_COMMAND VARCHAR2(250);
VARIABLE ERROR_CODE NUMBER;
EXECUTE :ERROR_CODE := SFUNC_CHECK_DBMS_PIPE('MY_REPORT_REQUEST',:BATCH_COMMAND);
SPOOL &SPOOLFILE
PRINT BATCH_COMMAND
SPOOL OFF
REM ACE Comment: log.name was replaced with file $job_logicals/PIPE_FAUCET_STATUS_$my_pid.
REM HOST $DEFINE/JOB/NOLOG PIPE_FAUCET_STATUS "MESSAGE RECEIVED"
HOST rm $JOB_LOGICALS/PIPE_FAUCET_STATUS_&TOKEN
EXIT
Content of SFUNC_CHECK_DBMS_PIPE
CREATE OR REPLACE FUNCTION SFUNC_CHECK_DBMS_PIPE
(PIPE_NAME IN VARCHAR2,
BATCH_COMMAND IN OUT VARCHAR2)
RETURN NUMBER
IS
STATUS NUMBER := 0;
TIME_OUT INTEGER := 86400; /* in seconds */
BEGIN
STATUS := DBMS_PIPE.RECEIVE_MESSAGE(PIPE_NAME,TIME_OUT);
DBMS_PIPE.UNPACK_MESSAGE(BATCH_COMMAND);
RETURN(STATUS);
END;
We pass message to the pipe from a db procedure which will be called from my Oracle forms application on clicking a button (generating reports)
Below is that procedure&
CREATE OR REPLACE PROCEDURE SPROC_SUBMIT_REPORT
(REPORT_TYPE IN VARCHAR2,
CASE_ID IN VARCHAR2,
REQUEST_ID IN VARCHAR2,
SCENARIO_ID IN VARCHAR2,
CVRGCTGRY_CD IN VARCHAR2,
CVRGTYP_CD IN VARCHAR2,
NODE_PRINTQ IN VARCHAR2,
RATING_MTHD IN VARCHAR2,
STATUS_CD IN OUT VARCHAR2,
ERROR_MSG IN OUT VARCHAR2) IS
BATCH_COMMAND VARCHAR2(250);
PIPE_NAME VARCHAR2(30) := 'MY_REPORT_REQUEST';
PIPE_SIZE INTEGER := 8192;
SERVER_SYSDATE DATE;
TIME_OUT INTEGER := 60; /* in seconds */
BEGIN
ERROR_MSG := NULL;
SERVER_SYSDATE := SPACK_GET_SERVER_TIME;
BATCH_COMMAND := 'perl ${RATING}E401226E.PL '||report_type||' '||
case_id||' '||request_id||' '||scenario_id||' '||cvrgctgry_cd||
' '||cvrgtyp_cd||' '||node_printq||' '||rating_mthd||'&';
DBMS_PIPE.PACK_MESSAGE(BATCH_COMMAND);
STATUS_CD := DBMS_PIPE.SEND_MESSAGE(PIPE_NAME,TIME_OUT,PIPE_SIZE);
EXCEPTION
WHEN OTHERS THEN
STATUS_CD := '9';
ERROR_MSG := SQLERRM;
END SPROC_SUBMIT_REPORT;