Get all job definition existing in CPS

[Former Member]

Hi,

Is it possible to get all the job definition and chains created in redwood even if it not yet scheduled.

Because using Reports we can only select jobs which have status.

What i want is to delete all jobs that doesn't have scheduling and jobs that we are no longer using.

Thanks in advance!

[gmblom · Accepted solution]

Hello,

The job definitions are in the JobDefinition table. You can also access this table from a Report.

Regards Gerben

[Former Member · Accepted solution]

Thank you Gerben, but is it also possible to get all the jobs that doesnt have scheduling. Jobs that is created but not submitted?

[gmblom · Accepted solution]

Hello Alyssa,

Of course that is possible, the below will give you all job definitions without jobs.

select jd.*

from JobDefinition jd

where jd.BranchedLLPVersion = -1

and not exists (select 1 from Job j, JobDefinition jjd where jjd.MasterJobDefinition = jd.UniqueId and j.JobDefinition = jjd.UniqueId)

Regards Gerben

[Former Member · Accepted solution]

Hi Gerben,

I wanted to extract the number of completed jobs in specific queue for the last 24hours using scripting. But it seems that i couldn't get the right value of the queue. Can you check my query below?

select count(*) from Job j where j.Status = 'C' and j.RunEnd BETWEEN TODATE('" +date2[0]+ "', 'UTC', 'yyyy/mm/dd') and TODATE('" +date1[0]+ "', 'UTC', 'yyyy/mm/dd') and j.Queue = 'XXX_Queue'

Thanks!

Alyssa

[gmblom · Accepted solution]

Hello,

What is important to know is that all Object references (like Queue) are actually foreign keys to the UniqueId in the corresponding table (Queue), you can find the name of that Object there.

To answer the question there are two ways to do this.

1) join the Queue table in your query:

select count(*) from Job j, Queue q where j.Status = 'C' and j.RunEnd BETWEEN TODATE('" +date2[0]+ "', 'UTC', 'yyyy/mm/dd') and TODATE('" +date1[0]+ "', 'UTC', 'yyyy/mm/dd') and j.Queue = q.UniqueId and q.Name = 'XXX_Queue'

2) use the Queue unique id in the query (and also use bind parameters):

{
  Queue q = jcsSession.getQueueByName("XXX_Queue");
  DateTimeZone today = new DateTimeZone();
  today.truncateDay();
  DateTimeZone yesterday = new DateTimeZone(today);
  today.add(-1, TimeUnit.Days);
  String sql = "select count(*) from Job j where j.Status = 'C' and j.RunEnd between ? and ? and j.Queue = ?";
  for (final Iterator it = jcsSession.executeObjectQuery(sql, new Object [] {new Long(yesterday.getUTCMilliSecs()), new Long(today.getUTCMilliSecs()), q.getUniqueId()}); it.hasNext(); )
  {
    Job j = (Job) it.next();
    ...
  }
}

Regards Gerben

[Former Member · Accepted solution]

Hi Gerben,

Could you check what's wrong with the query below:

=Query.getNumber('select count(*) from Job where Job.Status=? and Job.RunEnd BETWEEN ? and ? and Job.Queue=(select Queue.UniqueId from Queue where Queue.Name =?)',['C',TODATE(param1, 'UTC', 'yyyy/MM/dd hh:mm:ss,SSS z'), TODATE(param2, 'UTC', 'yyyy/MM/dd hh:mm:ss,SSS z'),'XXX_Queue'])

param1:=Time.expression(Time.now('UTC'), 'subtract 1 day')

param2:=Time.now('UTC')

Thanks!

[h_carpenter · Accepted solution]

Hi Alyssa,

I suspect BETWEEN and TODATE are not supported. Note that the dates are stored as UTC milliseconds. Also, you forgot the "parameters." prefix for parameters:

Syntax:

parameters.<parameter_name>

Example

parameters.param1

Try this:

param1: =Time.getUTCMilliseconds(Time.expression(Time.now('UTC'), 'subtract 1 day'))

param2:  =Time.getUTCMillisecondsNow()

param3: =Query.getNumber('select count(*) from Job where Job.Status=? and Job.RunEnd > ? and Job.RunEnd < ? and Job.Queue=(select Queue.UniqueId from Queue where Queue.Name =?)',['C',parameters.param1, parameters.param2,'XXX_Queue'])

All three parameters MUST BE of type Number.

Regards,

HP

[Former Member · Accepted solution]

Hello HP,

I get this error:

JCS-XXXXX: Recovery failed: Cannot assign an object of type java.math.BigDecimal to host variable 2 which has JDBC type BIGINT.

Is it possible to convert it to bigdecimal?

Thanks!

[gmblom · Accepted solution]

Hello Alyssa,

The problem is the BigDecimal, your database driver does not understand the datatype of it.

Can you try it without the reference parameters, but directly all in one REL?

=Query.getNumber('select count(*) from Job where Job.Status=? and Job.RunEnd > ? and Job.RunEnd < ? and Job.Queue=(select Queue.UniqueId from Queue where Queue.Name =?)', ['C', Time.getUTCMilliseconds(Time.expression(Time.now('UTC'), 'subtract 1 day'), Time.getUTCMillisecondsNow(), 'XXX_Queue'])

Regards Gerben

[Former Member · Accepted solution]

Hi Gerben,

The same error.

Cannot assign an object of type java.lang.String to host variable 2 which has JDBC type BIGINT.

[gmblom · Accepted solution]

Hi Alyssa,

Hmm, but that is actually a different error. Maybe an additional explicit cast is required:

=Query.getNumber('select count(*) from Job where Job.Status=? and Job.RunEnd > ? and Job.RunEnd < ? and Job.Queue=(select Queue.UniqueId from Queue where Queue.Name =?)', ['C', ToNumber(Time.getUTCMilliseconds(Time.expression(Time.now('UTC'), 'subtract 1 day')), ToNumber(Time.getUTCMillisecondsNow()), 'XXX_Queue'])

Regards Gerben

[Former Member · Accepted solution]

Hi Gerben,

Still the same error

[gmblom · Accepted solution]

Hi Alyssa,

In that case we switch to your original type of solution:

=Query.getNumber('select count(*) from Job where Job.Status=? and Job.RunEnd > ' + Time.getUTCMilliseconds(Time.expression(Time.now('UTC'), 'subtract 1 day') + ' and Job.RunEnd < ' + Time.getUTCMillisecondsNow() + ' and Job.Queue=(select Queue.UniqueId from Queue where Queue.Name =?)', ['C', 'XXX_Queue'])

Regards Gerben