ExecuteQuery and Group By-Failed Jobs

[Former Member]

Hi,

Am planning to get the failed jobs in particular time period  . I need to get the job name and no of times it failed.

I used below query can some one please correct the query

SELECT JobDefinition, COUNT(JobDefinition) AS OCCURENCE from Job WHERE Status IN ('K','E') GROUP BY JobDefinition HAVING(COUNT(JobDefinition)>1)

I will get the job name from job definition later.

Also please help me how get the results using ExecuteQuery

Thanks in advance.

Ravi

[Former Member]

Hii,

Later I will restrict this to some period

DateTimeZone startTime = jcsJob.getJobParameterByName("DATE_START").getInValueDate();

DateTimeZone endTime = jcsJob.getJobParameterByName("DATE_END").getInValueDate();

DATE_START and DATE_END are my parameters

NEW query

String query = "SELECT JobDefinition, COUNT(JobDefinition) AS OCCURENCE from Job WHERE Status IN ('K','E') AND RunStart > TODATE('"+startTime.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT') AND RunStart < TODATE('"+endTime.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT') GROUP BY JobDefinition HAVING(COUNT(JobDefinition)>1)";

jcsSession.executeObjectQuery(query, null, ??????)

Please let me know how to get this result set

[h_carpenter]

Hi  Ravi Reddy,

You would not want to use that, as it returns an object, such as a JobDefinition, for example. You need to use jcsSession.executeQuery!

Search the help for that and you find this example:

import com.redwood.scheduler.api.model.report.Reporter;

import com.redwood.scheduler.api.model.report.ReportDestination;

{

  String query = "select Job.JobId,Job.Description from Job where Job.Status = 'E'";

  Reporter reporter = jcsSession.createReporter(jcsOut);

  ReportDestination destination = reporter.getCSVReportDestination();

  jcsSession.executeQuery(query, null, destination);

}

So for you, you would do this:

import com.redwood.scheduler.api.model.report.Reporter;

import com.redwood.scheduler.api.model.report.ReportDestination;

{

String query = "SELECT JobDefinition, COUNT(JobDefinition) AS OCCURENCE from Job WHERE Status IN ('K','E') AND RunStart > TODATE('"+DATE_START.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT') AND RunStart < TODATE('"+DATE_END.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT') GROUP BY JobDefinition HAVING(COUNT(JobDefinition)>1)";

Reporter reporter = jcsSession.createReporter(jcsOut);

ReportDestination destination = reporter.getCSVReportDestination();

jcsSession.executeQuery(query, null, destination);

}

You will notice that I I have removed you code that retrieve parameter values ... parameters are automatically declared in RedwoodScript job definitions, assuming they are on the same job.

Regards,

HP

[Former Member]

Hi HP,

Thanks for your prompt response.

Am looking to get some data from retrieved JobDefinition  instead of directly sending them to report.

I need to validate this Jobdefinitnion before sending this to report ,can I do it here , if so can u suggest how to do it.

instead of using below is there any way to handle the query results

ReportDestination destination = reporter.getCSVReportDestination();

jcsSession.executeQuery(query, null, destination);

Thanks in advance.

[nanda_kumar21]

you can just use jcsSession.executeObjectQuery(string, null).

It returns an iterator, so you can type cast it to your required type.

thanks

nanda

[h_carpenter]

Hi Nanda,

Yes he can but in that case he will have to change his query, and remove the count()'s.

BTW,the SQL'92 implementation does not accept count(<column>), you must use count(*) and no, this does not cause any performance degradation and all jobs always have a JobDefinition. This does not solve his problem.

Also, you should always prepend the table name, select Job.JobDefinition from Job iso select JobDefinition from Job, this is because the implementation is a little flakey.

BTW, there are examples in the documentation for reports in redwoodscript.

Regards,

HP

[nanda_kumar21]

thanks carpenter for clearing that up.

Ravi,

As suggested by HP, its better to get a CSV report using the query and filter the duplicates out or modify the query like he suggested.

thanks

Nanda

[h_carpenter]

Hi Ravi,

Actually, thinking about it ... you could do something like this:

jcsOut.println("<table><tr><td>JobDefinition</td><td>Number of errors</td></tr>");

String query = "SELECT Job.* from Job WHERE Job.Status IN ('K','E') AND Job.RunStart > TODATE('"+startTime.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT') AND Job.RunStart < TODATE('"+endTime.toFormattedString("yyyy-MM-dd HH:mm:ss")+"', 'CST6CDT')";

for (Iterator it = jcsSession.executeObjectQuery(query,null); it.hasNext();)

  {

    Job j = (Job) it.next();

    //lots of validation here

   //print each table row

   jcsOut.println("<tr><td>"+variable1+"</td><td>"+variable2+"</td></tr>");

  }

//close table

jcsOut.println("</table>");

Pay attention to branched job definitions ... every time you edit a job definition for which jobs exist, a new "branched JobDefinition" is created. The MasterJobDefinition is the one used when you submit the job definition. So you will have to handle duplicates. You can look at the api documentation on Job.

Regards,

HP

[Former Member]

Hi HP, Nanda,

Thanks a lot for showing interest to solve my query.

what you suggested was already in place , but are looking towards removing duplicates(Job name)and getting no.of times each job failed and before sending to report , I want to validate the job.

Also If we query for Job.* or Jobdefiniton.* we can use executeObjectQuery(...) for executing queury

If we select individual columns like Job.JobDefiniton, Job.JobId instead of Job.*  I need to use executeQuery(..)

Here if we limit the query for only for some columns we can do some many operations like groping based on some columns which are not present in table also we can improve system performance by liming the columns.

Example:if we consider job duration it is not present in Job table we need to use TimeDiff(...) method and  we can order the report in duration order. here executeObjectQuery wont work.

My final aim is to how to handle executeQuery() method , please let me know.

Thanks a lot.