Control-M/EM Mass update,some examples on how to use it

Introduction

We are back today to write about a powerful tool that we can use to update our folders

Question

Suppose you have to update two jobs of your folder; the task should be easy

But what happens when you must update 50 jobs in a restricted delay?

Solution

To get this easier and smarter we will use the find and update tool

We will use the find and update tool

• To find specifics jobs
• To find specific commands
• To find specific users
• To update jobs with new parameters

Before going further, we will list the methods to find information’s in Control M folders

We have many ways to get information about our folders

1. By using The SQL queries
2. By using The XML export
3. By using The find and update method

Depending of the need, you can use the three methods as well knowing that they are the result of SQL queries

Using the find and update option

First of all, we have to connect on CTM GUI and go to the planning panel to check job definitions

We will select the folder where we will have to perform the task

Then we will use the find and update button

We will check how many jobs contain the word TEST in their name

Result:

We found 12 jobs corresponding to these criteria

Now supposing we want to list and update only a certain type of job for example OS jobs, excluding MFT jobs and dummy jobs

We select JOB/Folder Type in the huge drop down panel and enter the type requested ( here *OS* for OS JOB )

Note:

As explained at the beginning of the blog this searching tool is based on SQL queries, it only gives us a more “friendly” way to perform our queries

When we launch the search we have 10 jobs corresponding to OS jobs and containing TEST is their name

Now you know the principle, we can proceed to any update of these jobs depending of your request

For example, let’s try to update the jobs by assigning them a Quantitative resource

First step is to make a “backup” of our folder

Easiest way to do that is to make a xml export file (that is also a good way to search information’s about your folder)

Once your save done, we can proceed to QR adding

We will assign a QR for each jobs containing TEST in their name

Important

Ensure you have clicked on the checkout button to update your jobs:

Select in update part the function you want to assign or update and enter the character chain you want to apply it for your select jobs

After the jobs update you can check them to see if the update is corresponding to what you expect

If it’s not the case, you can perform a Rollback Updates command.

Let’s open a job and see if QR update worked.

Update worked well as we see in prerequisite tab, we can also see that no quantity is defined nor total Amount (which must be defined in the QR definitions tab, maybe in a new blog in coming)

So what about adding this quantity to each job? 😀

After that we have to check if jobs are configured as expected

Watch out !

If you want to add QR by mass update be careful to add your quantity in the same command to avoid adding twice a time a QR

Example

If you repeat the update above , it will be incremented so you will find two identical QR in a same job and you would get this kind of result

So make sure you double check you updates and if possible do the update in a single row like below

After your update don’t forget to do your check-in 😉

Tip:Save and load presets

Last time I did a tricky query, but I forgot how did I achieved to have the result I was expecting for…So instead of searching like me during hours to find it,what about doing some presets of our favorites or our more complicated requests?

Select the Presets button than save your preset ( the drop down panel show also the other saved presets)

That’s it , you will no longer blame your old age for having forgotten how to sort our jobs…

Update jobs by editing XML file or Jobs & Folders file Editor

Update from XML file

You can also update your jobs by exporting the corresponding xml file (as for our previous folder backup)

In the planning tab you select the folder that you want to export, then once exported you can edit it with your favorite file editor

It’s a quick way to identify jobs and other components of your folder such as hostnames, conditions or variables

Once your fields updated you import the file in the planning then you can upload it

But there is a constraint:

You can’t keep the modification’s history of your folder if you upload it from an xml file,as control will consider it as a brand new folder.

Update with Jobs & Folders file Editor tool:

You can also use the Jobs & Folders file Editor which is a tool mixing XML file and graphical interface for your jobs:

Load the XML file you want to update:

After your modifications you can save it:

Then you can upload the XML file.

This tool let you have a better translation and understanding of your XML file in a Control M folder display format

Reminder:

The result is the same as updating with XML file in other terms you will not be able to keep your folder’s history.

Using SQL queries :

This method is the basic method and the modification on the database doesn’t need a “checkout check-in” process

In the following examples we will check how to update jobs through SQL queries and see the result on the planning

We will use the table below to update our jobs and check if they are taken in account in control M planning

You can get the Control M DB schema following this link to process your requests:

ftp://ftp.bmc.com/pub/control-m/opensystem/DB_Schemas/918_ERDs/Control-M_9.0.18_DB_Ports_Diagram.zip

Connect on user control and type SQL to get the prompt:

Constraint

You need to know the name of the table to do your update and of course the associated command.

If you have no SQL skills it could be better to use find and update graphical tool.

The below example will show us how to update a jobname with PostgreSQL command:

• List the jobs by SQL query

First we will list the jobs defined in the folder:

ctmem=> select job_name,application from def_ver_job where application like 'MFTAPPLICATION' ;
              job_name              |  application
------------------------------------+----------------
 JOB_TEST_CR3                       | MFTAPPLICATION
 JOB_TEST_PGADMIN_re                | MFTAPPLICATION
 JOB_TEST_CR2                       | MFTAPPLICATION
 JOB_TEST_CR6                       | MFTAPPLICATION
 FileWatcher_Job                    | MFTAPPLICATION
 JOB_TEST_SHOUT                     | MFTAPPLICATION
 FileWatcher_Job1                   | MFTAPPLICATION
 JOB_TEST_CR5                       | MFTAPPLICATION
 JOB_TEST_CR4                       | MFTAPPLICATION
 JOB_TEST                           | MFTAPPLICATION
 JOB_TEST_SHOUT2                    | MFTAPPLICATION
 MFT_TRANSFERT_CENT_TO_SRVCTM3      | MFTAPPLICATION
 MFT_TRANSFERT_CENT_TO_SRVCTM4      | MFTAPPLICATION
 MFT_TRANSFERT_CENT_TO_SRVCTM1      | MFTAPPLICATION
 JOB_TEST_CR1                       | MFTAPPLICATION
 JOB_TEST_PGADMIN_updated           | MFTAPPLICATION
 MFT_TRANSFERT_TEST_CENT_TO_SRVCTM2 | MFTAPPLICATION
 TEST_DUMMY                         | MFTAPPLICATION
(18 rows)

• Update and verification on PostgreSQL

We will update the jobname and check if the update worked

ctmem=>
ctmem=> update def_ver_job set job_name='JOB_TEST_PGADMIN_updated' where job_name like 'JOB_TEST_PGADMIN_ re ' ;
UPDATE 2
ctmem=> select job_name from def_ver_job where job_name like 'JOB_TEST%' and application like 'MFT%' ;
         job_name
--------------------------
 JOB_TEST_CR3
 JOB_TEST_PGADMIN_updated
 JOB_TEST_CR2
 JOB_TEST_CR6
 JOB_TEST_SHOUT
 JOB_TEST_CR5
 JOB_TEST_CR4
 JOB_TEST
 JOB_TEST_SHOUT2
 JOB_TEST_CR1
 JOB_TEST_PGADMIN_updated
(11 rows)
 
ctmem=>

• Verification on the control M GUI planning panel

It’s taken in account in the planning (you must quit your workspace and reload it , no need to log off)

Now we want to repeat this action on many jobs

Let’s try to update the ‘run as’ part of every job running as controlm user and compare it to the find and update graphical method

First we have to identify the column corresponding to the ‘run as’ in dbschema , it matches with ‘owner’ parameter

ctmem=> select owner,job_name,application from def_ver_job where application like 'MFTAPPLICATION' ;
       owner        |              job_name              |  application
--------------------+------------------------------------+----------------
 controlm           | JOB_TEST_CR3                       | MFTAPPLICATION
 controlm           | JOB_TEST_PGADMIN_updated           | MFTAPPLICATION
 controlm           | JOB_TEST_CR2                       | MFTAPPLICATION
 controlm           | JOB_TEST_CR6                       | MFTAPPLICATION
 controlm           | FileWatcher_Job                    | MFTAPPLICATION
 controlm           | JOB_TEST_SHOUT                     | MFTAPPLICATION
 controlm           | FileWatcher_Job1                   | MFTAPPLICATION
 controlm           | JOB_TEST_CR5                       | MFTAPPLICATION
 controlm           | JOB_TEST_CR4                       | MFTAPPLICATION
 NABIL_MFT_2        | MFT_TRANSFERT_CENT_TO_SRVCTM3      | MFTAPPLICATION
 CMT_NABIL_EXPORTED | MFT_TRANSFERT_CENT_TO_SRVCTM4      | MFTAPPLICATION
 controlm           | JOB_TEST                           | MFTAPPLICATION
 controlm           | JOB_TEST_SHOUT2                    | MFTAPPLICATION
 NABIL_MFT_2        | MFT_TRANSFERT_CENT_TO_SRVCTM1      | MFTAPPLICATION
 controlm           | JOB_TEST_CR1                       | MFTAPPLICATION
 controlm           | JOB_TEST_PGADMIN_updated           | MFTAPPLICATION
 CMT_NABIL_EXPORTED | MFT_TRANSFERT_TEST_CENT_TO_SRVCTM2 | MFTAPPLICATION
 DUMMYUSR           | TEST_DUMMY                         | MFTAPPLICATION
(18 rows)

Now we have listed all the users of my folder we can update the ‘run as’ parameter to modify from controlm ‘run as user’ to emuser

Find jobs to modify on control M GUI

We make a find and update to list the jobs running with user controlm then we will update them by PostgreSQL query (don’t use the update button we will do this update through PostgreSQL query just below to get the same result 😉 )

Mass update the jobs from SQL query

We will perform the update by sql query and check if it is taken in account on Control M GUI

ctmem=> update def_ver_job set owner='emuser' where owner like 'controlm' and application like 'MFTAPPLICATION' ;
UPDATE 13
ctmem=>

Then we can list again if run as update worked:

Checking with PostgreSQL query

ctmem=> select owner,job_name,application from def_ver_job where application like 'MFTAPPLICATION' ;
       owner        |              job_name              |  application
--------------------+------------------------------------+----------------
 emuser             | JOB_TEST_CR3                       | MFTAPPLICATION
 emuser             | JOB_TEST_PGADMIN_updated           | MFTAPPLICATION
 emuser             | JOB_TEST_CR2                       | MFTAPPLICATION
 emuser             | JOB_TEST_CR6                       | MFTAPPLICATION
 emuser             | FileWatcher_Job                    | MFTAPPLICATION
 emuser             | JOB_TEST_SHOUT                     | MFTAPPLICATION
 emuser             | FileWatcher_Job1                   | MFTAPPLICATION
 emuser             | JOB_TEST_CR5                       | MFTAPPLICATION
 emuser             | JOB_TEST_CR4                       | MFTAPPLICATION
 emuser             | JOB_TEST                           | MFTAPPLICATION
 emuser             | JOB_TEST_SHOUT2                    | MFTAPPLICATION
 NABIL_MFT_2        | MFT_TRANSFERT_CENT_TO_SRVCTM3      | MFTAPPLICATION
 CMT_NABIL_EXPORTED | MFT_TRANSFERT_CENT_TO_SRVCTM4      | MFTAPPLICATION
 NABIL_MFT_2        | MFT_TRANSFERT_CENT_TO_SRVCTM1      | MFTAPPLICATION
 emuser             | JOB_TEST_CR1                       | MFTAPPLICATION
 emuser             | JOB_TEST_PGADMIN_updated           | MFTAPPLICATION
 CMT_NABIL_EXPORTED | MFT_TRANSFERT_TEST_CENT_TO_SRVCTM2 | MFTAPPLICATION
 DUMMYUSR           | TEST_DUMMY                         | MFTAPPLICATION
(18 rows)
 
ctmem=>

Checking from GUI find and update method

By checking on the GUI we see Run as user is now updated , the owner emuser is now assigned as ‘run as’ to the 12 jobs ,they were updated, furthermore we didn’t see any controlm user in the search:

As a result , we can see that the check of run as user controlm return no match.

Using SQL query is a powerful  tool to do the mass update but it is more friendly to use find and update tool , because it is easier for you to do a  rollback update  and also find the rows you want to update without consulting the Control M dbschema which be a bit less intuitive.

To make a quick review of the the main solutions here’s a quick summary of good and bad points of each of them:

And you, which method would you chose?

Conclusion

Now you know how to perform your updates on your Control M’s folders, naturally  you can choose the most adapted solution depending of your need.

Be careful to always have a backup of your folders before doing your update and feel free to give other tips or tricks in the comment part.

Once again you can check my  dbi colleagues blogs concerning Control M and other technologies and of course see you for a next session 😀

You also have the possibility to check BMC site and their excellent videos.

By the way,to avoid any influence I prefer to do my topic before checking if any similar is existing on this site or elsewhere,then I can compare if we have the same methodology and way to use Control M  :D.

Also feel free to check my other blogs and don’t hesitate to share your advice on it ;).

Another example on how to use Mass update, I told you it’s a really cool tool !