Database agent / SQL Server agent (SQL, DBMON, DBTRIG, DBPROC and SQLAGENT) jobs taking longer execution time.

Products

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

Issue/Introduction

 

CA WA Agent for Database  and CA WA SQL Server agent (WAAE job types: SQL, DBMON, DBTRIG, DBPROC and SQLAGENT) jobs taking longer execution time.

We have run a simple SELECT query using SQL job type, which should ideally take 6 seconds but when it is run through AutoSys, it takes more than 180 seconds.

We have tried both Microsoft JDBC Driver for SQL Server and jTDS JDBC driver for MS SQL Server with similar execution latency.

 

Environment

CA Workload Automation Agent for Databases 11.3+CA Workload Automation Agent for Microsoft SQL Server 11.3+Operating System: Supported Microsoft Windows platforms.

Cause

 

One of the potential causes is an inappropriate file type association for jarfile ; ".jar" file extension associated with "jarfile" file type and "jarfile" file type associated with an older Java version.

The file extension and file type association can be checked using commands assoc .jar and ftype jarfile, respectively.

 

For example:

D:\>assoc .jar
.jar=jarfile

D:\>ftype jarfile
jarfile="C:\Program Files (x86)\IBM\Java50\jre\bin\javaw.exe" -jar "%1" %*

 

In the aforementioned example, the jarfile file type is associated with Java 5.0 which might cause slowness.

 

Resolution

 

Dissociate the .jar extension and jarfile file type associations using the following commands run in an elevated command prompt (cmd.exe -> Run as Administrator):

D:\>assoc .jar= [Hit Enter Key]

D:\>ftype jarfile= [Hit Enter Key]

Additional Information

Install CA WA Agent for Databases

Install CA WA Agent for Microsoft SQL Server