Control-M for Databases - best practice

Control-M for Databases has a direct integration to the Databases Process Scheduler and can extract and insert jobs into the Process Request Table(s). The Application Plug-in connection profile, the connection information to access Databases including the userid & password as well as the connection string to Databases, is stored and encrypted in the Control-M database. To create or modify a connection profile, log into the Control-M Configuration Manager (CCM). Once you are connected select Control-M for Databases. Right click and select Connection Profile Management:

To create or modify a connection profile:

1. Log into the Control-M Configuration Manager (CCM).
2. Select Control-M for Database and then right click and select Connection Profile Management.

The following window displays all the connection profiles, allows you to add or delete profiles, copy profiles and validate the connection. Click the + to add a new profile or select one to display/modify:

1. Name the profile something indicative of its purpose to the database instance to which it will connect. Naming standards are important.This makes it easier when selecting the profile you will need to define a job. In the example above, we are selecting SQLSRV1_SP. We have used this example to align with the function in the database we are performing.
2. Double click and the screen on the top of the next page appears.

   The following connection profile requires the database connection information: the user name, user password and domain password. The application server is displayed at the bottom of the page.

   

3. Click the + and you will get the screen below, to add another application server.

   

4. Walk through the screens and supply the required information. Once you click OK, your new Connection is added.

   

5. Click the Test button in the lower left to validate your credentials. It will connect to Databases using the credentials you just provided and will report success or failure in a pop-up window:

   

6. Now click OK to close the Test window, and click OK again to save the Connection Profile.
7. To create a new job flow, select the Planning domain on the left of the Control-M Interface:

   

   You will see the work you have previously opened and saved and will have the option to open other workflows in Control-M by Selecting Load Folders and Jobs. We’ll cover that in a moment.

8. Click Blank Workspace on the ribbon at the upper left of the page. The screen below will appear:

   

   On the ribbon at the top you will see the types of jobs you may use. This list is controlled by role-based security and job type templates. This list has all the job types in it, but you may exclude job types for which you have no need.

9. For our purpose, select the drop-down button on the job types.

   

10. Select Databases and Drag the job into the workspace.

    Control-M will create the job and place it in a folder. Folders are logical groupings used to encapsulate workflows for various reasons. Customer-specific, function specific, geographically specific, application specific are some examples of reasons to group jobs. You may also wish to create an application and/or sub-application to further quantify your workflow. Once the job is in the workspace, we can right-click it and add properties for the job:

    

    The folder can now be renamed to something indicative of the flow’s function, like Customer_Payroll or some other appropriate name.

11. Right click on the folder and you get the option to rename it; click Properties:

    

    Then:

    

    Smart folders – see button in upper right – may have scheduling criteria like RUN DAILY, RUN FRIDAY, RUN WORKDAYS. We will not cover that here; feel free to experiment with various options.

12. Click in the Folder Name field and type in an appropriate name.
13. Select the Order method; Automatic means it will be selected to run every day, None means the entire flow must be triggered manually and Specific User Daily means the job is ordered based on a pre-defined scheduled job that will evaluate this folder’s schedule to see if it is eligible to run.
14. Click OK when finished.
15. This example has a single job - Databases_Job#1; double click it or right click and select Properties to open it for editing.

    The window below will open and we can begin building the job based on the criteria supplied by our request for from the Databases requester.

    

16. Change the job’s name to something meaningful and add a description if desired.
17. Select the host where the agent running the Databases Control Module is installed; in this case it’s hou-ctmerp8. We get that from Control-M Configuration Manager:
18. After adding the host, fill in the Application and Sub-application. Once that’s done, click on the details button to fill in the Databases job attributes.
19. Click on the Details button.
20. Plug in the Connection Profile name we just created into the Account field and it will populate the Database Type field. Use the drop-down list to select they type of job – SSIS, SQL Script, Open Query or Stored Procedure – and it will change the screen to the appropriate form for the job type.
21. Complete the screen and save the job.

    

    Example 1: Stored procedure job

    

    Example 2: SQL Script

    

    Example 3: Open Query, you may also use the query designer to help with SQL syntax:

    

    Example 4: Stored Procedure Job:

    

    Save the jobs as a folder as in the example below and check them in. Control-M will validate your job definition as you save it and prompt you for any missing values. Once saved and checked-in, you may run your job(s).

    You may also add other job types to your workflow. Control-M supports ERPs for PeopleSoft, Oracle EBS and SAP; Advanced File Transfer for moving files between servers securely; ETL Tools like Informatica, Cognos and DataStage; application servers like JBOSS, Oracle WebLogic, IBM WebSphere and .NET. Control-M Business Process Integration Suite also covers web and message services in addition to the aforementioned application servers. Control-M for Cloud & Virtualization provides integration to VMware, BMC BladeLogic and Amazon EC2 for helping manage workload on cloud and virtual environments. Currently Synovus is licensed for all these options. Speak to the Synovus scheduling team or to your BMC representative for more information.