generating report on duplicate jobs in multiple tables

A lot of scripts ans example to extract information from Control-M tools.
Post Reply
User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

generating report on duplicate jobs in multiple tables

Post by catchtony » 12 Aug 2010 3:29

Hello all,

We are using Control-M/EM 6.2. I need to find how many duplicate instances of a job are present in multiple tables and out of these duplicate instances, how many are the command line jobs.

I would appreciate any help :)

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 12 Aug 2010 3:51

catchtony,

You may run a request which displays the duplicates and the count per single sched_table as follows:
select count(*) as NR , JOBNAME+char(9)+SCHEDTAB from CMS_JOBDEF group by JOBNAME+char(9)+SCHEDTAB order by NR desc

.. which gives the following result:
NR
----------- ---------------------------------------------------------------------------------------------------------------------------------
2 PDMNTF_040 PDMNTF_TB
2 PLOBEN010 PLOBEN_TB
1 PSVCCC010 PSVCCC_TB
1 PTSCRE7 PTNGSC_TB
1 PTSFW_FW_0000 PTSFW_CF

If you intend to identify those jobnames which are duplicates in your entire ctm environment, then the request is simpler:
select count(*) as NR , JOBNAME from CMS_JOBDEF group by JOBNAME order by NR desc

.. which returns the following:
NR JOBNAME
----------- ----------------------------------------------------------------
2 PDMNTF_040
2 PLOBEN010
2 PMGGR1GGG
2 PTSCRE2
2 PTSTJC000
2 PTSAGR1
2 PTSCRE0
2 PTSCRE1
2 toto
1 PTSCRE10
1 PTSAGR2
1 PTSCCG_01_000

You can see in the above that, for example, job PTSCRE0 is duplicate in the controlm base - table CMS_JOBDEF - (listed in second report), but in different sched tables (not listed in the first report).

The above requests are run against the ctm server base, in MS/SQL syntax.
If you intend to check the EM base, the logic is quite identical but the practical requests, especially the first one, are a bit more complex, for the link between tables and job is performed by the TABLE_ID, which is the numeric identifier of the table within the DEF_TABLES (6.3.x) or the DEF_VER_TABLES (6.4.0 and above) table.

Adapting the syntax to oracle should not be too big a deal

Cheers
Nicolas Mulot

User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

Post by catchtony » 12 Aug 2010 4:23

Thanks alot Nicolas,

Second query worked out :D

Also if you could guide me on how to determine, out of these duplicate entries, what all jobs are command based.

Againt thanks in advance !!

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 12 Aug 2010 4:39

Please clarify.

Do you want to identify those jobs which are duplicate jobnames AND tasktype=command ?

If so, do you want to distinguish between let's say 2 duplicates, one being a job and one being a command?

If it it the request, it migh be quite complex and the siplest I can imagine is to define a view based on the second request - you were staisfied with - then to select on this view to check the task type.

I wil check that and let you know soon

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 12 Aug 2010 4:51

There it is (MS/SQL as usual)

drop view DUPJOBS -- need the "go" below before create view
go
create view DUPJOBS as select count(*) as NR , JOBNAME from CMS_JOBDEF group by JOBNAME
go

Once the view is created, I check the duplicates:
select * from DUPJOBS where NR != 1

Result:
NR JOBNAME
----------- ----------------------------------------------------------------
2 PDMNTF_040
2 PLOBEN010
2 PMGGR1GGG
2 PTSAGR1
2 PTSCRE0
2 PTSCRE1
2 PTSCRE2
2 PTSTJC000
2 toto
(9 row(s) affected)

Then I select those duplicates which are commands:
select JOBNAME from DUPJOBS where NR != 1 and JOBNAME in (select JOBNAME from CMS_JOBDEF where TASKTYPE='C')

Result:
PDMNTF_040
PLOBEN010
PTSAGR1
PTSCRE0
PTSCRE1
PTSCRE2
PTSTJC000
toto
(8 row(s) affected)

Both lists are different: PMGGR1GGG is actually not a command, it is a scheduling group.
The second requests will show you a jobname which is a duplicate and for which at least one job definition occurrence is a command, not necesssarily all of the occurrences.

Don’t forget to get rid of the view once you got what you need

drop view DUPJOBS
go

User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

Post by catchtony » 12 Aug 2010 6:50

Thanks Nicolas

User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

Post by catchtony » 13 Aug 2010 3:14

Hi Nicolas,

Sorry to bother again, but could you tell me if we can make a control-M job to run this query everytime we need and get the desired result on our mail ?

Normally I had seen for UNIX jobs, where we define the script in the associated path and key in the same path to that job. So on that lines, if it is possible to create this SQL query job ?

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 13 Aug 2010 3:37

Hi,

Of course you can.
First you need to make up a script which executes the request you want to run.

If you use MSSQL, its even simpler, since the request is a single one so you can execute the following:

Isql –U<ctmdbo> -P<ctmdbopass> -S<sql> -Q”select etc..>"

Depending on the SQL version, islq has to be changed to osql (sql server 2005 and above)
If the report length is greater than 255, you may specify the “-w” flag, let’s say “-w800”

You can as well create a file which contains the request. Let’s call it report_jobdef.sql, in which case the call should be:

Isql –U<ctmdbo> -P<ctmdbopass> -S<sql> -w800 –n -ireport_jobdef.sql

With Oracle, the syntax should be

Sqlplus <ctmdbo>/<ctmdbopass>@<sql> @ report_jobdef.sql

Once your script is set up properly, executing it under the prompt will return the info you want to see on the console.

Second, define a controlm job which executes the sql script. Run the job and verify that the relevant information (same as above) is displayed in the job’s sysout.

Third, adjust your job, to include the following step information:
On stmt * code *
Domail To<someone> Subject<some_subject> Message <hello>.

Click the “detail” input field. You can then specify “Attach sysout”, and specify Yes.
Rerun the job an let me know about the results, I never checked that myself and I have no time to do it right now.

If it doesn’t work, you can modify your script to route the SQL output to a file (using “>>” with both Oracle or MSSQL).
You can the send a mail using mailx or blat to send the mail as part of the %%POSTCOMMAND AutoEdit variable. (refer to “Send mail with start and end time of the job » currently 4th in list on the present forum).

Good luck
Nicolas Mulot

User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

Post by catchtony » 13 Aug 2010 4:24

Hi Nic,

I ran the above SQL queries through the CTM utility ctm_menu -> option 10 - Troubleshooting -> option 14 - Interactive SQL Commands. I am not aware what SQL server/version this supports, just that we run on Solaris server. :!:

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 13 Aug 2010 5:16

OK then , there are no chance for you to use MS/SQL.
Depending on the version and the choices of your DBAs, it might be either Oracle or PostGre, more unlikely Sybase.

Take any Solaris console and log as controlm user.
Type the command "SQL". It should give you a prompt, where you can type anything like
select count(*) from CMS_JOBDEF;

The ";" is the command delimiter for oracle and PostGre. If you use Sybase, the command delimiter is the sytring "go" specified on the next line.

Quit with "quit"
If this works, then you use one of these 2, and I ignore if one of them allows a direct request in the command line (Im nearly sure they do not).
As a consequence, you will have to store your request in a file. This file should include the "quit" command as the list line, or else you will be stuck in the SQL interactive and you will have to type the quit command yourself.

Test the file first using the prompt by typing:

SQL < report_jobdef.sql

If it works, insert this command in the Unix script.
Run your script as is
If it works, code that script name in MEMNAME of your controlm job and run the job. Dont forget to specify your controlm Unix account as the job's owner.

I am currently checking the attach sysout story, with which I have difficulties.

Cheers

User avatar
catchtony
Nouveau
Nouveau
Posts: 50
Joined: 12 Sep 2008 12:00
Contact:

Post by catchtony » 16 Aug 2010 7:29

Hi Nic,

only the string "go" works for me so that means ours on sybase.

also I tried to test one given below :-


#!/bin/ksh
rep=$1
SQL <<EOF>> $rep
select count(*) from CMS_JOBDEF
EOF
cat $rep

Have given all the permissions to this script, but when i execute this manually, it shows nothing...if I am missing something here ?

User avatar
nicolas_mulot
Nouveau
Nouveau
Posts: 149
Joined: 07 Jan 2010 12:00

Post by nicolas_mulot » 16 Aug 2010 9:17

catchtony,
I'm not familiar with that kind of Unix syntax and have no opportunity to verify it.
First, I hope for you that when running the select under ctm_menu 10.13, you actually get some count which is not 0.

Second, try to proceed step by step, coding the request in an input file then calling SQL < the_input_file,
then calling SQL <the_input_file> an_output_file
etc..

I did that on windows and got the following:
C:\Users\Administrator.PCJCRO8>echo select count(*) from CMS_JOBDEF > tmp.sql

C:\Users\Administrator.PCJCRO8>SQL <tmp> 2>
-----------
996

(1 row affected)

C:\Users\Administrator.PCJCRO8>SQL <tmp> count.out

C:\Users\Administrator.PCJCRO8>SQL <tmp> count.out & cat count.out
1> 2>
-----------
996

(1 row affected)

The best will be to store the request in a file that either you create ince for good, ar that you build in your script using echo.

Cheers
Nicolas Mulot

User avatar
rodneytan
Nouveau
Nouveau
Posts: 1
Joined: 18 Jun 2013 12:00

Post by rodneytan » 18 Jun 2013 8:19

My head aches when I see those programs. United States businesses added to the workforce at a higher rate than envisioned, says a report from the private sector. It is the most recent indicator that the economy is increasing, but not at an adequate rate to improve significantly.

Post Reply