Sybase error: Message String: #ujo_bge not found

Products

• CA Workload Automation AE - Business Agents (AutoSys)
• CA Workload Automation AE - Scheduler (AutoSys)
• CA Workload Automation Agent

Issue/Introduction

Autosys failed with error messages in the event_demon log file:

CAUAJM_E_10163 Exhausted maximum number of query retries. 

CAUAJM_E_18002 Stored Procedure execution returned a failure: -6 

CAUAJM_E_18017 Procedure: ujo_batch_get_event 

CAUAJM_E_18018 Server message: Message number(2,601), Severity(14), State(6), Line(17), Message String: Attempt to insert duplicate key row in object 'syscolumns' with unique index 'csyscolumns' 

CAUAJM_E_18017 Procedure: ujo_batch_get_event 

CAUAJM_E_18018 Server message: Message number(208), Severity(16), State(6), Line(44), Message String: #ujo_bge not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output). 

CAUAJM_E_18012 Event Server: <xxxxxxxxx:yyyyyyyyy> Failed Stored Procedure: <EXEC ujo_batch_get_event @time0, @time0_prev <<1501288354,0>>> 

CAUAJM_E_18002 Stored Procedure execution returned a failure: -6 

CAUAJM_E_18017 Procedure: ujo_batch_get_event 

CAUAJM_E_18018 Server message: Message number(2,762), Severity(16), State(3), Line(17), Message String: The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database. 

Cause

For some reason, Sybase  stopped the ujo_batch_get_event stored procedure from creating the temporary table #ujo_bge and then procedure tried to insert into the non existing table and hence the problem.

The temp table is expected to stay in the tempdb for the completion of the SP, but in this case the tempdb did not allow SP creating the table. 

Environment

CA WAAE 11.3.6 SP5 on RedHat 6 with database on Sybase.Sybase version and EBF number are: 15.7/EBF 26397 SMP SP138 /P/x86_64/Enterprise Linux/ase

Resolution

Recommendations from Sybase support are:

1) Look into these three KBAs : 

2207664 - RPC failure with CREATE TABLE command is not allowed within a multi-statement transaction in the tempdb database." - ASE 

2507907 - Msg 2762, Level 16, State 3 with temporary tables 

1937484 - Msg 2762 when creating table within a transaction - ASE 

 

2) Sybase suggested all customers following resolution: 

Log in to ASE Server as user 'sa'. Use system procedure 'sp_dboption' to change the database option 'ddl in tran' to true. 

Example: 

1. Log in to ASE Server 'SYB' as user 'sa'.Please replace 'SYB' with your actual server name. 

          isql -Usa -SSYB 

          password: 

2. Run following commands to change the database option 'ddl in tran' to true. Please replace 'pubs2' with your actual database name. 

         use master 

          go 

          sp_dboption pubs2,'ddl in tran',true 

          go 

          use pubs2 

          go 

          checkpoint 

          go