search cancel

ujo_cleanup_jobruns procedure caused ASE has run out of LOCKS

book

Article ID: 240920

calendar_today

Updated On:

Products

CA Workload Automation AE

Issue/Introduction

[04/28/2022 03:30:32]      CAUAJM_E_18012 Event Server: <AUTOSYSDB1:autosys>  Failed Stored Procedure: <EXEC ujo_post_runtime_info @joid, @run_num, @ntry, @type, @seq_num, @has_runinfo, @run_info <<88190,225387,1,3,1,-1,'StdOut("/usr/pkgs/ops/logs/sp_raw_issue.20220428") StdErr("/usr/pkgs/ops/logs/sp_raw_issue.20220428")'>>>
[04/28/2022 03:30:32]      CAUAJM_E_18002 Stored Procedure execution returned a failure: -7
[04/28/2022 03:30:32]      CAUAJM_E_18016 Server: AUTOSYSDB1
[04/28/2022 03:30:32]      CAUAJM_E_18017 Procedure: ujo_post_runtime_info
[04/28/2022 03:30:32]      CAUAJM_E_18018 Server message: Message number(1204), Severity(17), State(2), Line(29), Message String: ASE has run out of LOCKS. Re-run your command when there are fewer active users, or contact a user with System Administrator (SA) role to reconfigure ASE with more LOCKS.

Above error is seen around the time DBMaint task is running. Customer had maxlocks=300000, how to fix this?

Cause

When DBMaint is running they are exhausting the maxlocks.

$AUTOSYS/bin/DBMaint shell script is the one that calls these our archive program with the batchsize mentioned in the shell script. The DB procedures are expecting this as a parameter to them, and if the parameter is <0, we hardcode to 50000 in the procedure.  Otherwise, we use the value set in the  DBMaint script.

 

Environment

Release :

Component :

Resolution

Resolution was to adjust  DBMaint to reduce batchsize from 50000  batch size to  10000  (or even 5000).  

Before the change it looks like this:
 

# Archive the Events
archive_events -A -n 7 -b 50000  >> $outfile 2>&1
ecode=`expr $? + $ecode`

# Archive the job_runs
archive_events -A -j 7  -b 50000 >> $outfile 2>&1
ecode=`expr $? + $ecode`


# Archive the machines
archive_events -A -m 7  -b 50000 >> $outfile 2>&1
ecode=`expr $? + $ecode`

# Archive the autotrack log
archive_events -A -l 7  -b 50000 >> $outfile 2>&1
ecode=`expr $? + $ecode`

 

 

After the change:

 

# Archive the Events
archive_events -A -n 7 -b 10000  >> $outfile 2>&1
ecode=`expr $? + $ecode`

# Archive the job_runs
archive_events -A -j 7  -b 10000 >> $outfile 2>&1
ecode=`expr $? + $ecode`


# Archive the machines
archive_events -A -m 7  -b 10000 >> $outfile 2>&1
ecode=`expr $? + $ecode`

# Archive the autotrack log
archive_events -A -l 7  -b 10000 >> $outfile 2>&1
ecode=`expr $? + $ecode`