Mass convert job status to unknown

[madhavat]

Hi All

CPS build is M33.120-83827,

We have more than 70K+ jobs in status canceling, we need to set the status to unknown.

I'm looking for a query or script to do the conversion.

I need to use Job.Status , Job.JobDefinition and Job.Queue as the the condition in where clause.

Please provide the exact SQL query or script or any other method to get rid of changing each job status to unknown.

Help is very much appreciated

Thanks

Maddy.

[abhishek_singh29]

Hello Maddy,

I am not sure if you can move the cancelling status to unknown, by running the jobs - System_ForceJobStatusUnknown by providing the job id check if they are moved to unknown status.

You the below query should work to check all jobs are in cancelling status ("c" is cancelling code)

query = "Select a.* from Job a, JobDefinition b where a.JobDefinition = b.UniqueId and b.Name = '" + pJobName + "' and a.Status in ('c')";

You can use the table iteration as below to perform this action, try j.unknown() to move the job to unknown status.

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

j.disable() or j.unknown()

Regards,

Abhishek

[madhavat]

Hi Abhishek,

Thanks for the reply, have few doubts and need more assistance.

1. in the query you provided what is the section '" + pJobName + "' for? in my case i am looking for JobDefinition.Name as "SAP_CcmsJob" so should that to be replaced in the query

2. Can you provide a query where i can extract a single record based on JobId as the input from user, just to make sure before we convert the whole records test for one.

3. Last thing, can you provide the entire script file with instructions how and where to execute them, as i'm in Basis admin and not aware of scripting

Thanks Much

Maddy

[abhishek_singh29]

Just remove that and try below-

Select a.* from Job a where a.Status in ('c')";

Hope you know the SQL. I am also in Infra.

Cheers, Abhishek