We are back today to write about a powerful tool that we can use to update our folders
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?
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
- By using The SQL queries
- By using The XML export
- 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
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 )
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
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
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
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:
Connect on user control and type SQL to get the prompt:
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?
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 !