Running SQL command in Job.SQL using an SQL agent

[Stephen Bondar]

Hello All,

We are trying to run a vacuum command on our postgres AE database using an SQL job and an SQL database agent.

The statement is : vacuum (analyze, verbose)

The job ends with a FAULT_OTHER status and the message in the Details windows is:

U00007055 Cannot run task 'JOBS.SQL.VACUUM' with RunID '0126321409' on Agent 'SQL_MAINT'.

The usage of db service agents is limited to the resolution of dynamic variables.

The documentation seems to indicate that this is possible, but I may be interpreting that incorrectly.

Substituting a simple SQL select statement in place of the vacuum command results in the same error.

Does this mean I need to assign the statement to a variable ?

I fear that I am overlooking something very simple and obvious.

Has anyone had luck doing something like this?  What am I doing wrong?

Our environment is AAKE v21.0.9

Regards,

Steve

[Krum Ganev]

Hello,

It looks like you are using DB Service agent and not the normal DB/SQL Agent.

The DB Service agent is only for SQL VARA objects and can not run SQL Jobs.

[Stephen Bondar]

Hello Krum Ganev,

Thank you for responding.  Yes, I did notice the DB Service Agent was being used, but in the Component Download Center that is the only option for my version 21.0.9.hf1.  I will try downloading the entire release package and look for the DB/SQL agent there.

Regards,

Steve

[Krum Ganev]

Hey Stephen,

The package is the same for both.

With the only difference that for DB Service agent you need to add argument -service when starting the jar.

Prior v21 the DB Service agent was using the AE config (ucsrv.ini) and now its using its own one.

Simply make sure to adjust the config as per your requirement and start it.

java -Xmx1G -jar <agent jar>

The service is started with

java -Xmx1G -jar <agent jar> -service

[Stephen Bondar]

Ah! As I expected - simple and obvious.  

Thank you very much for your response.