Extracting the job schedule

Some examples running with Control-M and Control-EM database.
Post Reply
User avatar
death
Nouveau
Nouveau
Posts: 3
Joined: 25 Aug 2008 12:00

Extracting the job schedule

Post by death » 25 Aug 2008 3:44

hi,

i am new here.

anybody can help to provide me with the sql on how to extract the list of jobs that is in the db?

Thanks :)

User avatar
RoHoe
Nouveau
Nouveau
Posts: 13
Joined: 11 Apr 2008 12:00
Location: Belgium

Post by RoHoe » 25 Aug 2008 7:58

Hi, you can use :

select distinct
substr (application,1,4) OScycle,
' ', substr (application,5,20) Description_cycle,
' ', (job_name) Jobname,
' ', substr (description,1,40) description_job
from
ecs.def_job
ORDER by
1;

With this query you can also request for example the DAYS_CAL or WEEKS_CAL, DAY_STR or W_DAY_STR from the database.

User avatar
death
Nouveau
Nouveau
Posts: 3
Joined: 25 Aug 2008 12:00

Post by death » 25 Aug 2008 8:10

This is great! thanks

also, is there any restriction on accessing the Control-M DB?
I heard from somewhere that this is not advisable.

Also, by doing so, will it cause any issues in the daily operations?

User avatar
RoHoe
Nouveau
Nouveau
Posts: 13
Joined: 11 Apr 2008 12:00
Location: Belgium

Post by RoHoe » 25 Aug 2008 8:17

These are 'SELECT' statements, so there's no problem.

From the moment you try to do an UPDATE it get's tricky ...
It's advisable to do your updates via control/m-desktop & write & upload them from there to the db.

Robbie.

User avatar
death
Nouveau
Nouveau
Posts: 3
Joined: 25 Aug 2008 12:00

Post by death » 25 Aug 2008 9:53

thanks! you have been a great help, now there should be no more such taboos ;)

User avatar
arno76
Nouveau
Nouveau
Posts: 24
Joined: 29 Aug 2008 12:00

Post by arno76 » 05 Sep 2008 10:15

RoHoe wrote:Hi, you can use :

select distinct
substr (application,1,4) OScycle,
' ', substr (application,5,20) Description_cycle,
' ', (job_name) Jobname,
' ', substr (description,1,40) description_job
from
ecs.def_job
ORDER by
1;

With this query you can also request for example the DAYS_CAL or WEEKS_CAL, DAY_STR or W_DAY_STR from the database.
hello,

how do you execute this script? must i connect to the EM server before?

thanks

User avatar
fyot
Nouveau
Nouveau
Posts: 736
Joined: 26 Apr 2005 12:00
Location: PARIS
Contact:

Post by fyot » 05 Sep 2008 2:52

RoHoe wrote:These are 'SELECT' statements, so there's no problem.
Robbie.
That's not totally true.
If you execute a very important SELECT, you can decrease database performance, and even stop database.

It could be a very important issue for all your environnement.

The best way is to duplicate database content and try SELECT on duplicated database.

User avatar
fyot
Nouveau
Nouveau
Posts: 736
Joined: 26 Apr 2005 12:00
Location: PARIS
Contact:

Post by fyot » 05 Sep 2008 2:54

arno76 wrote: hello,

how do you execute this script? must i connect to the EM server before?

thanks
You must use database client to be connected to the EM database, it your administrator give you access to do it.

User avatar
arno76
Nouveau
Nouveau
Posts: 24
Joined: 29 Aug 2008 12:00

Post by arno76 » 09 Sep 2008 9:03

hello,

thanks for answers, i can now extract data from Orcale n my serveur, by CTM_MENU or just in command by typing SQL in DOS, but i want to send my script by Ctrlm. When i create my batch, sql.bat, i've "select' command not found .... How can i make sql by batch?

thanks

User avatar
fyot
Nouveau
Nouveau
Posts: 736
Joined: 26 Apr 2005 12:00
Location: PARIS
Contact:

Post by fyot » 09 Sep 2008 10:45

arno76 wrote:When i create my batch, sql.bat, i've "select' command not found ....
thanks
Verify, giving all the path to acces of your SQL client, as :

C:\sql\...\isql.exe
C:\sql\...\sqlplus.exe
...

Depends on the type of the dataserver

User avatar
arno76
Nouveau
Nouveau
Posts: 24
Joined: 29 Aug 2008 12:00

Post by arno76 » 09 Sep 2008 10:55

hello,
database = Oracle
here is my test.bat

cd E:\Program Files\BMC Software\CONTROL-M Server\Ctm_server\Exe\
sql.exe
select DATA_CNTR_NAME from CMS_CMNPRM;
go

and here my result

C:\Documents and Settings\MonUSER>E:\TEMP\extorac.bat

C:\Documents and Settings\MonUSER>cd E:\Program Files\BMC Software\CONTROL-M Serv
er\Ctm_server\Exe\

C:\Documents and Settings\MonUSER>sql.exe
1>

here i tape exit, because the scipt wait for command SQL

C:\Documents and Settings\MonUSER>select DATA_CNTR_NAME from CMS_CMNPRM;
'select' n'est pas reconnu en tant que commande interne
ou externe, un programme exécutable ou un fichier de commandes.

C:\Documents and Settings\MonUSER>go
'go' n'est pas reconnu en tant que commande interne
ou externe, un programme exécutable ou un fichier de commandes.
C:\Documents and Settings\MonUSER>

thanks for help ;-))

User avatar
fyot
Nouveau
Nouveau
Posts: 736
Joined: 26 Apr 2005 12:00
Location: PARIS
Contact:

Post by fyot » 09 Sep 2008 1:48

Here is an example :


Echo set nocount on >> %SQLTEMP%
Echo select select DATA_CNTR_NAME from CMS_CMNPRM >> %SQLTEMP%
Echo go>> %SQLTEMP%
Echo quit>> %SQLTEMP%

isql -S%EMSERVER% -U%EMUSER% -P%EMPWD% -i %SQLTEMP% -b

Post Reply