SAP CPS add parameter to all jobs

[former_member1244799]

Hi All,

I am trying to add a job definition parameter "JCS_USER"  to jobs of type "CMD" using the below script. But it gets errored if "JCS_USER" already exists for the jobs. Could you please help how to check in the code if the parameter already exists, if the parameter don't exist...add the parameter.

{

      String query = "select JobDefinition.* from JobDefinition where BranchedLLPVersion = -1";
      for (Iterator it = jcsSession.executeObjectQuery(query, null); it.hasNext();)

{
       JobDefinition jobdef = (JobDefinition) it.next(); 
      if (jobdef.getJobDefinitionType().getName().equals("CMD"))
      {
                  
        JobDefinitionParameter User = jobdef.createJobDefinitionParameter();
      
        User.setName("JCS_USER");
        User.setDefaultExpression("=Table.getColumnString('System_Variables', 'JCSUSR', 'SystemValue')");
       User.setDisplayOrder(new Long(30));
       User.setDirection(ParameterDirection.In);
       User.setDataType(ParameterType.String);
       jcsOut.println("Param2" + User.getName());
            jcsSession.persist();       

     }
    }

}

Thank you in advance,

Regards,

Ramana

[former_member1244799 · Accepted solution]

I tried to change the script as below but it is returning only the null values. Please advise.

{

      String query = "select JobDefinition.* from JobDefinition where BranchedLLPVersion = -1";
      for (Iterator it = jcsSession.executeObjectQuery(query, null); it.hasNext();)

{
       JobDefinition jobdef = (JobDefinition) it.next(); 
     
      if (jobdef.getJobDefinitionType().getName().equals("CMD"))
      {
                  
       JobDefinitionParameter User = jobdef.createJobDefinitionParameter();
       jcsOut.println("Param1: " + User.getName());

       if((User.getName() != null) && (User.getName() != "JCS_USER"))
{      

        User.setName("JCS_USER");
        User.setDefaultExpression("=Table.getColumnString('System_Variables', 'JCSUSR', 'SystemValue')");
       User.setDisplayOrder(new Long(30));
       User.setDirection(ParameterDirection.In);
       User.setDataType(ParameterType.String);
            jcsSession.persist();
       jcsOut.println("Param2" + User.getName());  

}
else

{
  jcsOut.println("already exists: " + User.getName());
}   

     }
    }

}

Regards,

Ramana

[former_member1244799 · Accepted solution]

Thank you all.

Below is the script.

{

      String query = "select JobDefinition.* from JobDefinition where BranchedLLPVersion = -1";
      for (Iterator it = jcsSession.executeObjectQuery(query, null); it.hasNext();)

{
     
      // JobDefinition jobdef = jcsSession.getJobDefinitionByName("CMD");
       JobDefinition jobdef = (JobDefinition) it.next(); 
      if (jobdef.getJobDefinitionType().getName().equals("CMD"))
      {
 
         // jcsOut.println("job name: " + jobdef.getName());
        JobDefinitionParameter parameterJCS;
       parameterJCS = jobdef.getJobDefinitionParameterByName("JCS_USER");

        if (parameterJCS == null)
{
          
        JobDefinitionParameter User = jobdef.createJobDefinitionParameter();           
        User.setName("JCS_USER");
        User.setDefaultExpression("=Table.getColumnString('System_Variables', 'JCSUSR', 'SystemValue')");
       User.setDisplayOrder(new Long(15));
       User.setDirection(ParameterDirection.In);
       User.setDataType(ParameterType.String);
            jcsSession.persist();
       jcsOut.println("Job name" + jobdef.getName());
       jcsOut.println("User name added" + User.getName());
         }

     }
    }

}

Regards,

Ramana

[nanda_kumar21 · Accepted solution]

I recommend to modify the select statement to select job definitions of type 'CMD', this way you dont have to load all job definitions and then check for CMD type.

thanks

Nanda

[former_member1244799 · Accepted solution]

Hi Nanda,

I tried to select the job defintions of type 'CMD' using the below query.

select JobDefinition.* from JobDefinition where JobDefinitionType = 'CMD' and BranchedLLPVersion = -1;

But I got the below error. So I went with the other approach. Please advise.

Caused by: com.redwood.scheduler.persistence.api.PersistenceException$RecoveryFailedException: JCS-XXXXX: Recovery failed: The SQL statement "SELECT "JCS_JOBDEF0"."F_ACTSUBJ","JCS_JOBDEF0"."A_BEHAVIOUR","JCS_JOBDEF0"."A_BRANCHLLP","JCS_JOBDEF0"."A_BRANCHMODTIMETS","JCS_JOBDEF0"."A_COMMENT","JCS_JOBDEF0"."A_CREATEDTS","JCS_JOBDEF0"."F_CRTIMEZONE","JCS_JOBDEF0"."F_DEFLOGFMT","JCS_JOBDEF0"."F_DEFOUTFMT","JCS_JOBDEF0"."F_QUEUE","JCS_JOBDEF0"."A_EXECUTIONCOUNT","JCS_JOBDEF0"."A_FAILEDEXECCOUNT","JCS_JOBDEF0"."A_SUMLOGSIZE","JCS_JOBDEF0"."A_SUMSQLOGSIZE","JCS_JOBDEF0"."A_SUMOUTPUTSIZE","JCS_JOBDEF0"."A_SUMSQOUTPUTSIZE","JCS_JOBDEF0"."A_SUMRUNTIME","JCS_JOBDEF0"."A_SUMSQRUNTIME","JCS_JOBDEF0"."A_DESCRIPTION","JCS_JOBDEF0"."A_EQUIV_OBJECT","JCS_JOBDEF0"."A_HOLDONSUBMIT","JCS_JOBDEF0"."F_JOBDEFTYPE","JCS_JOBDEF0"."F_JOBDEFTIMEZONE","JCS_JOBDEF0"."A_KEEPAMOUNT","JCS_JOBDEF0"."A_KEEPFORCE","JCS_JOBDEF0"."A_KEEPSTATUS","JCS_JOBDEF0"."A_KEEPTYPEEN","JCS_JOBDEF0"."A_KEEPUNITSEN","JCS_JOBDEF0"."A_LLPVERSION","JCS_JOBDEF0"."A_LASTMODIFTS","JCS_JOBDEF0"."F_LASTMODSUB","JCS_JOBDEF0"."A_MAINTENANCE","JCS_JOBDEF0"."F_MASTERJOBDEF","JCS_JOBDEF0"."A_MAXPRIORITY","JCS_JOBDEF0"."A_NAME","JCS_JOBDEF0"."A_OVERDUE","JCS_JOBDEF0"."F_OVERDUEEVTDEF","JCS_JOBDEF0"."A_OVERDUESTATUSEN","JCS_JOBDEF0"."A_OVERVIEWFMT","JCS_JOBDEF0"."F_CREATOR","JCS_JOBDEF0"."F_PARENTAPP","JCS_JOBDEF0"."F_PARTITION","JCS_JOBDEF0"."A_PRIORITY","JCS_JOBDEF0"."F_RESOURCE","JCS_JOBDEF0"."A_RESTARTCOUNT","JCS_JOBDEF0"."A_RETURNMAPTOCOMPL","JCS_JOBDEF0"."A_SEARCHNAME","JCS_JOBDEF0"."A_TEMPLATE","JCS_JOBDEF0"."F_TIMEWINDOW","JCS_JOBDEF0"."F_TIMEWINDOWTZ","JCS_JOBDEF0"."A_TOPLEVEL","JCS_JOBDEF0"."A_UNIQUEID" FROM "JCS_JOBDEF0" WHERE ("JCS_JOBDEF0"."A_UNIQUEID" IS NULL OR EXISTS (SELECT 1 FROM "JCS_JOBDEF0" "JCS__JD", "JCS_PARTITION0" "JCS__P", "JCS_ROLEGRANTEX0" "JCS__SRGE" WHERE "JCS_JOBDEF0"."F_MASTERJOBDEF" = "JCS__JD"."A_UNIQUEID" AND "JCS__JD"."F_PARTITION" = "JCS__P"."A_UNIQUEID" AND "JCS__SRGE"."F_GRANTEE" = 3723466 AND "JCS__P"."F_USERCLASS" IN (3) AND (EXISTS (SELECT 1 FROM "JCS_OBJPRIVGRANT0" "JCS__SOPG" WHERE "JCS__SOPG"."A_PRIVVIEW" = 't' AND "JCS__SOPG"."A_REFUID" = "JCS_JOBDEF0"."F_MASTERJOBDEF" AND "JCS__SOPG"."F_GRANTEE" = "JCS__SRGE"."F_GRANTED") OR EXISTS (SELECT 1 FROM "JCS_OBJDEF0" "JCS__OD", "JCS_TYPPRIVGRANT0" "JCS__SOTPG" WHERE "JCS__SOTPG"."F_OBJECTTYPE" = "JCS__OD"."A_UNIQUEID" AND "JCS__OD"."A_OBJECTNAME" = 'JobDefinition' AND "JCS__SOTPG"."A_PRIVVIEW" = 't' AND "JCS__SOTPG"."F_GRANTEE" = "JCS__SRGE"."F_GRANTED" AND ("JCS__SOTPG"."A_LEVELEN" = 'S' OR "JCS__SOTPG"."A_LEVELEN" = 'I' AND "JCS__SOTPG"."A_ISOGROUPPART" = "JCS__P"."F_USERCLASS" OR "JCS__SOTPG"."A_LEVELEN" = 'P' AND "JCS__SOTPG"."A_ISOGROUPPART" = "JCS__JD"."F_PARTITION"))))) AND "JCS_JOBDEF0"."F_JOBDEFTYPE" = 'CMD' AND "A_BRANCHLLP" =  - 1" contains the semantics error[s]: - 1:2353 - type check error: the left hand side >>"JCS_JOBDEF0"."F_JOBDEFTYPE"<< (BIGINT) and the right hand side >>'CMD'<< (CHAR) of a comparison operator are not comparable

at com.redwood.scheduler.persistence.helper.RetryThenFailAction.recover(RetryThenFailAction.java:71)

at com.redwood.scheduler.persistence.helper.RecoverySQLExceptionWrapper.wrapSQLException(RecoverySQLExceptionWrapper.java:95)

at com.redwood.scheduler.persistence.impl.InnerPersistenceUnitOfWorkManager.execute(InnerPersistenceUnitOfWorkManager.java:45)

at com.redwood.scheduler.persistence.impl.LowLevelPersistenceImpl.executeQuery(LowLevelPersistenceImpl.java:460)

at com.redwood.scheduler.cluster.persistence.ClusteredLowLevelPersistence.executeQuery(ClusteredLowLevelPersistence.java:155)

at com.redwood.scheduler.model.SchedulerSessionImpl.executeObjectQuery(SchedulerSessionImpl.java:1096)

... 16 more

Caused by: com.sap.sql.log.OpenSQLException: The SQL statement "SELECT "JCS_JOBDEF0"."F_ACTSUBJ","JCS_JOBDEF0"."A_BEHAVIOUR","JCS_JOBDEF0"."A_BRANCHLLP","JCS_JOBDEF0"."A_BRANCHMODTIMETS","JCS_JOBDEF0"."A_COMMENT","JCS_JOBDEF0"."A_CREATEDTS","JCS_JOBDEF0"."F_CRTIMEZONE","JCS_JOBDEF0"."F_DEFLOGFMT","JCS_JOBDEF0"."F_DEFOUTFMT","JCS_JOBDEF0"."F_QUEUE","JCS_JOBDEF0"."A_EXECUTIONCOUNT","JCS_JOBDEF0"."A_FAILEDEXECCOUNT","JCS_JOBDEF0"."A_SUMLOGSIZE","JCS_JOBDEF0"."A_SUMSQLOGSIZE","JCS_JOBDEF0"."A_SUMOUTPUTSIZE","JCS_JOBDEF0"."A_SUMSQOUTPUTSIZE","JCS_JOBDEF0"."A_SUMRUNTIME","JCS_JOBDEF0"."A_SUMSQRUNTIME","JCS_JOBDEF0"."A_DESCRIPTION","JCS_JOBDEF0"."A_EQUIV_OBJECT","JCS_JOBDEF0"."A_HOLDONSUBMIT","JCS_JOBDEF0"."F_JOBDEFTYPE","JCS_JOBDEF0"."F_JOBDEFTIMEZONE","JCS_JOBDEF0"."A_KEEPAMOUNT","JCS_JOBDEF0"."A_KEEPFORCE","JCS_JOBDEF0"."A_KEEPSTATUS","JCS_JOBDEF0"."A_KEEPTYPEEN","JCS_JOBDEF0"."A_KEEPUNITSEN","JCS_JOBDEF0"."A_LLPVERSION","JCS_JOBDEF0"."A_LASTMODIFTS","JCS_JOBDEF0"."F_LASTMODSUB","JCS_JOBDEF0"."A_MAINTENANCE","JCS_JOBDEF0"."F_MASTERJOBDEF","JCS_JOBDEF0"."A_MAXPRIORITY","JCS_JOBDEF0"."A_NAME","JCS_JOBDEF0"."A_OVERDUE","JCS_JOBDEF0"."F_OVERDUEEVTDEF","JCS_JOBDEF0"."A_OVERDUESTATUSEN","JCS_JOBDEF0"."A_OVERVIEWFMT","JCS_JOBDEF0"."F_CREATOR","JCS_JOBDEF0"."F_PARENTAPP","JCS_JOBDEF0"."F_PARTITION","JCS_JOBDEF0"."A_PRIORITY","JCS_JOBDEF0"."F_RESOURCE","JCS_JOBDEF0"."A_RESTARTCOUNT","JCS_JOBDEF0"."A_RETURNMAPTOCOMPL","JCS_JOBDEF0"."A_SEARCHNAME","JCS_JOBDEF0"."A_TEMPLATE","JCS_JOBDEF0"."F_TIMEWINDOW","JCS_JOBDEF0"."F_TIMEWINDOWTZ","JCS_JOBDEF0"."A_TOPLEVEL","JCS_JOBDEF0"."A_UNIQUEID" FROM "JCS_JOBDEF0" WHERE ("JCS_JOBDEF0"."A_UNIQUEID" IS NULL OR EXISTS (SELECT 1 FROM "JCS_JOBDEF0" "JCS__JD", "JCS_PARTITION0" "JCS__P", "JCS_ROLEGRANTEX0" "JCS__SRGE" WHERE "JCS_JOBDEF0"."F_MASTERJOBDEF" = "JCS__JD"."A_UNIQUEID" AND "JCS__JD"."F_PARTITION" = "JCS__P"."A_UNIQUEID" AND "JCS__SRGE"."F_GRANTEE" = 3723466 AND "JCS__P"."F_USERCLASS" IN (3) AND (EXISTS (SELECT 1 FROM "JCS_OBJPRIVGRANT0" "JCS__SOPG" WHERE "JCS__SOPG"."A_PRIVVIEW" = 't' AND "JCS__SOPG"."A_REFUID" = "JCS_JOBDEF0"."F_MASTERJOBDEF" AND "JCS__SOPG"."F_GRANTEE" = "JCS__SRGE"."F_GRANTED") OR EXISTS (SELECT 1 FROM "JCS_OBJDEF0" "JCS__OD", "JCS_TYPPRIVGRANT0" "JCS__SOTPG" WHERE "JCS__SOTPG"."F_OBJECTTYPE" = "JCS__OD"."A_UNIQUEID" AND "JCS__OD"."A_OBJECTNAME" = 'JobDefinition' AND "JCS__SOTPG"."A_PRIVVIEW" = 't' AND "JCS__SOTPG"."F_GRANTEE" = "JCS__SRGE"."F_GRANTED" AND ("JCS__SOTPG"."A_LEVELEN" = 'S' OR "JCS__SOTPG"."A_LEVELEN" = 'I' AND "JCS__SOTPG"."A_ISOGROUPPART" = "JCS__P"."F_USERCLASS" OR "JCS__SOTPG"."A_LEVELEN" = 'P' AND "JCS__SOTPG"."A_ISOGROUPPART" = "JCS__JD"."F_PARTITION"))))) AND "JCS_JOBDEF0"."F_JOBDEFTYPE" = 'CMD' AND "A_BRANCHLLP" =  - 1" contains the semantics error[s]: - 1:2353 - type check error: the left hand side >>"JCS_JOBDEF0"."F_JOBDEFTYPE"<< (BIGINT) and the right hand side >>'CMD'<< (CHAR) of a comparison operator are not comparable

at com.sap.sql.jdbc.common.StatementAnalyzerImpl.check(StatementAnalyzerImpl.java:38)

at com.sap.sql.jdbc.common.StatementAnalyzerImpl.preprepareStatement(StatementAnalyzerImpl.java:116)

Regards,

Ramana

[nanda_kumar21 · Accepted solution]

hey Ramana,

JobDefinitionType values are stored in a different table, so the query should be

"select JobDefinition.* from JobDefinition, JobDefinitionType where JobDefinition.JobDefinitionType=JobDefinitionType.UniqueId and JobDefinitionType.Name='CMD'"

thanks

Nanda

[former_member1244799 · Accepted solution]

Thank you so much Nanda:-)