ujo_cleanup_jobruns procedure caused ASE has run out of LOCKS

Products

• Autosys Workload Automation

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?

Environment

Release :

Component :

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.

 

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`