SQL is not executing

Venkateshwarulu · Post by **Venkateshwarulu** » 23 Dec 2009 9:52

Hello,

I am trying to kill the jobs that are in Yellow status and for some reason, when i excute the command in SQL it is not upadting CMR_AJF...Could some one explain why I am not able to do this?

controlm% sql

SQL*Plus: Release 10.1.0.3.0 - Production on Wed Dec 23 14:35:47 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> update CMR_AJF set STATUS='N', STATE='8' where JOBNO='7533411'
2
SQL>

Thanks in advance for all your inputs.

Regards
Venkat

Walty · Post by **Walty** » 23 Dec 2009 11:29

Hi,

I think that the GUI does not reflect the update.

After update CMR_AJF by SQL command you must to execute the syncronisation from Control-M/Server DB and Control-M/EM DB to reflect update.
For this execute from the <ctm_menu> , opt 10 (troubleshouting), opt 16 (Force download) or executre the <reset_ecs> command from <controlm> user

gglau · Post by **gglau** » 23 Dec 2009 11:54

Use ctmpsm to get the status at server, or put the job on hold and release it at GUI.

baralem · Post by **baralem** » 24 Dec 2009 3:31

In oracle you have to use commit after the update sentence, wich also must end with ";". Try:

1) hold job

2)
SQL> update CMR_AJF set STATUS='N', STATE='8' where JOBNO='7533411';

SQL> commit;

--Update sentence shold only affect 1 row.

3) free job

regards
martin

Walty · Post by **Walty** » 24 Dec 2009 4:36

Baralem,

I think you are right, because the command invoked is not correctly formulated.
The rc 2 also gives also an indication.
I had only focused on the possibility that the GUI does not reflect the update.

nicolas_mulot · Post by **nicolas_mulot** » 07 Jan 2010 11:34

Venkat,

First the target you specified is not the correct one. You should select the ORDERNO which is the unique Id of the job in the AJF (CMR_AJF), while the
JOBNO you specified is the unique Id in the definitions (CMS_JOBDEF). You might go in trouble with that request in case the job is available
several time on the AJF, for it is ordered several times a day, or is simply late with different ODATEs (maybe this explains the RC2)
The ORDERNO is stored in decimal in the controlm DB while it appears in alphanumeric in the "Active" tab of the job properties under the GUI.
You can use the P_36 utility to convert the Order Id you see in the GUI into decimal. Ex: p_36 abc2 --> result = 481250.
P_36 must be called from a Unix prompt under the controlm account, or under a windows DOS prompt.

Second, the STATE should be specified as 5 instead of 8. "8" means "Post Processed" while "5" means "Ended".
The difference is that between 5 and 8, the server performs the cleanup of the active job environment.
If your job uses quantitative or control resources, these resources will be kept as used by the ORDERID of the job, which you could vberify under the GUI Tools --> control resources --> RBA field, and this is not very sfe to get rid of them using a SQL request.

Third, the suggested reset_ecs will download the entire AJF, which is not a very good idea: if you do that too often, you might lose archived viewpoints under EM.
A simple Hold - Free, performed either from the GUI or by ctmpsm is sufficient to refresh the status of the job on the GUI.
You can just Hold/Free after you have run the SQL requests: is is not necessary to hold before sql and free after sql.

Cheers
Nicolas_mulot

baralem · Post by **baralem** » 08 Jan 2010 3:37

You're right Nicolas. I assumed that the query was ok...i was just referring to the execution of the statement.

regards

Venkateshwarulu · Post by **Venkateshwarulu** » 08 Jan 2010 9:04

Thanks evry one for your replies...

Regards
Venkat

brownbag · Post by **brownbag** » 12 Jan 2010 6:03

You may also want to try using BMC's new solution to this problem, which is ForceEndJob.pl. Look up the new updated version of solution 88111. It is far safer than using SQL to fix this issue.