Database Management Jobs

Control-M for Databases enables you to define and monitor Stored Procedures, SQL Scripts, SQL Server Integration Services (SSIS) Packages, and Embedded Query database jobs.

The following topics describe Database job commands:

Job:Database:EmbeddedQuery

The following example shows how to define an Embedded Query job:

Copy 
{
   "PostgresDBFolder": 
   {
      "Type": "Folder",
      "EmbeddedQueryJobName":
      {
         "Type": "Job:Database:EmbeddedQuery",
         "ConnectionProfile": "POSTGRESQL_CONNECTION_PROFILE",
         "Query": "SELECT %%firstParamName AS VAR1 \\n FROM DUMMY \\n ORDER BY \\t VAR1 DESC",
         "Host": "${agentName}",
         "RunAs": "PostgressCP",
         "Variables": [
         {
            "firstParamName": "firstParamValue"
         } ],
         "Autocommit": "N",
         "OutputExecutionLog": "Y",
         "OutputSQLOutput": "Y",
         "SQLOutputFormat": "XML"
      }
   }   
}

The following table describes the Embedded Query job parameters.

Parameter

Description

Host

Defines the name of the host machine where the job runs. A Control-M/Agent must be installed on this host, as well as Control-M Databases plug-in version 9.0.00 or higher.

(Optional) You can define a host group instead of a host machine.

Query

Defines the embedded SQL query that you want to run.

The SQL query can contain auto edit variables. During job run, these variables are replaced by the values that you specify in Variables parameter (next row).

For long queries, you can specify delimiters using \\n (new line) and \\t (tab).

Variables

Defines variables as pairs of name and value. Every name that appears in the embedded script will be replaced by its value pair.

For information about valid variable names and values, see Variable Names and Values.

The following table describes additional, optional parameters for all types of database jobs.

Parameter

Description

Autocommit

(Optional) Determines whether to commit statements to the database that completes successfully.

Default: N

OutputExecutionLog

(Optional) Determines whether to show the execution log in the job output.

Default: Y

OutputSQLOutput

(Optional) Determines whether to show the SQL sysout in the job output.

Default: N

SQLOutputFormat

(Optional) Defines the output format as either Text, XML, CSV, or HTML.

Default: Text

Job:Database:SQLScript

The following examples show how to define an SQL Script job:

  • This JSON defines a regular SQL Script job.

    Copy 
    {
       "OracleDBFolder": 
       {
          "Type": "Folder",
          "testOracle": 
          {
             "Type": "Job:Database:SQLScript",
             "Host": "AgentHost",
             "SQLScript": "/home/controlm/sqlscripts/selectOrclParm.sql",
             "ConnectionProfile": "ORACLE_CONNECTION_PROFILE",
             "Parameters": [
             {
                "firstParamName": "firstParamValue"
             },
             {
                "secondParamName": "secondParamValue"
             } ]
          }
       }
    }

  • This JSON defines an SQL script job that runs an SQL script from a file system:

    Copy 
    {
       "OracleDBFolder": 
       {
          "Type": "Folder",
          "testOracle": 
          {
             "Type": "Job:Database:SQLScript",
             "Host": "app-redhat",
             "SQLScript": "/home/controlm/sqlscripts/selectOrclParm.sql",
             "ConnectionProfile": "ORACLE_CONNECTION_PROFILE"
          }
       }
    }

The following table describes the SQL Script job parameters.

Parameter

Description

Host

Defines the name of the host machine where the job runs. A Control-M/Agent must be installed on this host, as well as Control-M Databases plug-in version 9.0.00 or higher.

(Optional) You can define a host group instead of a host machine.

Parameters

Defines parameters as pairs of name and value. Every name that appears in the SQL script is replaced by its value pair.

For additional optional parameters, see above.

Job:Database:StoredProcedure

The following examples show how to define a Stored Procedure job:

  • This JSON defines a regular Stored Procedure job.

    Copy 
    {
       "storeFolder": 
       {
          "Type": "Folder",
          "jobStoredProcedure": 
          {
             "Type": "Job:Database:StoredProcedure",
             "Host": "myhost.mycomp.com",
             "StoredProcedure": "myProcedure",
             "Parameters": [ "value1","variable1",["value2","variable2"]],
             "ReturnValue":"RV",
             "Schema": "public",
             "ConnectionProfile": "DB-PG-CON"
          }
       }
    }

  • This JSON defines a Stored Procedure job with more definitions for the parameters and return value. This format enables you to use the connection profile to deploy stored procedures without accessing the database during deployment.

    To support the previous JSON notation, API commands will continue to GET Stored Procedure definitions in the old JSON format. To enable getting the Stored Procedure object in the new format, see Controlling the Database Connection for Stored Procedure Data.

    Copy 
    {
        "storeFolder": 
       {
          "Type": "Folder",
          "jobStoredProcedure": 
          {
             "Type": "Job:Database:StoredProcedure",
             "Host": "myhost.mycomp.com",
             "StoredProcedure": "myProcedure",
             "Parameters" : [
             {
                "Name" : "table_name",
                "ParameterType" : "text",
                "Direction" : "In",
                "Value": "TestTable"
             }, 
             {
                "Name" : "chunksize",
                "ParameterType" : "int4",
                "Direction" : "Out",
                "Value": "4"
             }, 
             {
                "Name" : "rows_deleted",
                "ParameterType" : "int4",
                "Direction" : "InOut",
                "ValueIn" : "1",
                "ValueOut" : "2"
             } ],
             "ReturnValue": 
             {
                "Name" : "returnValue",
                "ValueType" : "int4",
                "Value" : "RV"
             },
             "Schema": "public",
             "ConnectionProfile": "DB-PG-CON"
          }
       }
    }

The following table describes the Stored Procedure job parameters.

Parameter

Description

Host

Defines the name of the host machine where the job runs. A Control-M/Agent must be installed on this host, as well as Control-M Databases plug-in version 9.0.00 or higher.

(Optional) You can define a host group instead of a host machine.

StoredProcedure

Defines the name of stored procedure that the job runs.

Parameters

Defines all the parameters in the procedure, in the order of their appearance in the procedure.

The format depends on the version of Automation API and on whether a connection to the database is required for the evaluation of Stored Procedures, as described in Controlling the Database Connection for Stored Procedure Data.

  • For Deployment with Access to the Database: A comma-separated list of values or variables for all parameters in the procedure. In the first example above, three parameters are listed, in the following order: [In,Out,Inout]. The value that you specify for any specific parameter in the procedure depends on the type of parameter.

    • For an In parameter, specify an input value.

    • For an Out parameter, specify an output variable.

    • For an InOut parameter, specify a pair of input value + output variable, enclosed in brackets, as follows:

      [value,variable]

  • For Deployment without Access to the Database: Definitions of each parameter are provided on the next level, as in the second example above.

    • Name

    • ParameterType

    • Direction: In, Out, or InOut

    • Value: The input value of an In parameter or the output variable of an Out parameter.

      For an InOut parameter, two properties are used instead of one, ValueIn and ValueOut.

ReturnValue

Defines the variable for the Return parameter (if the procedure contains such a parameter).

The format depends on the version of Automation API and on whether a connection to the database is required for the evaluation of Stored Procedures, as described in Controlling the Database Connection for Stored Procedure Data.

  • For deployment with access to the database: The value for the ReturnValue variable, as in the first example above.

  • For deployment without access to the database: Definitions of the variable are provided on the next level, as in the second example above.

    • Name

    • ValueType

    • Value

Schema

Defines the database schema where the stored procedure resides.

Package

(Oracle only) Defines the name of a package in the database where the stored procedure resides.

Default: * (any package in the database).

ConnectionProfile

Defines the name of a connection profile that contains the details of the connection to the database.

For additional optional parameters, see above.

Job:Database:MSSQL:AgentJob

The following example shows how to define a MSSQL Agent job:

Copy 
{
   "MSSQLFolder": 
   {
      "Type": "Folder",
      "ControlmServer": "LocalControlM",
      "MSSQL_3LK": 
      {
         "Type": "Job:Database:MSSQL:AgentJob",
         "ConnectionProfile": "MSSQL-WE-EXAMPLE",
         "JobName": "get_version",
         "RunFromStep": "2",
         "RerunFromPointOfFailure": "true",
         "Category": "Data Collector"
      }
   }
}

The following table describes the MSSQL Agent job parameters.

Parameter

Description

Host

Defines the name of the host machine where the job runs. A Control-M/Agent must be installed on this host, as well as Control-M Databases plug-in version 9.0.00 or higher.

(Optional) You can define a host group instead of a host machine.

JobName

Defines the name of the job, as defined in the SQL server.

Category

Defines the category of the job, as defined in the SQL server.

RerunFromPointOfFailure

Determines whether the job reruns from the step that failed during the previous run.

For example, if the job fails on step 3, it will rerun from step 3.

Valid Values:

  • false

  • true

Default: false

RunFromStep

Determines whether the job runs from a specific step.

Valid Values: 1-9999

Default: 1

For additional optional parameters, see above.

Job:Database:MSSQL:SSIS

The following example shows how to define a MSSQL SSIS Package job:

Copy 
{
   "MSSQLFolder": 
   {
      "Type": "Folder",
      "ControlmServer": "LocalControlM",
      "SSISCatalog": 
      {
         "Type": "Job:Database:MSSQL:SSIS",
         "ConnectionProfile": "MSSQL-CP-NAME",
         "Host": "agentHost",
         "PackageSource": "SSIS Catalog",
         "PackageName": "\\Data Collector\\SqlTraceCollect",
         "CatalogEnv": "ENV_NAME",
         "ConfigFiles": [
            "C:\\Users\\dbauser\\Desktop\\test.dtsConfig",
            "C:\\Users\\dbauser\\Desktop\\test2.dtsConfig" ],
         "Properties": [
         {
            "PropertyName": "PropertyValue"
         },
         {
            "PropertyName2": "PropertyValue2"
         } ]
      },
      "SSISPackageStore": 
      {
         "Type": "Job:Database:MSSQL:SSIS",
         "ConnectionProfile": "MSSQL-CP-NAME",
         "Host": "agentHost",
         "PackageSource": "SSIS Package Store",
         "PackageName": "\\Data Collector\\SqlTraceCollect",
         "ConfigFiles": [
            "C:\\Users\\dbauser\\Desktop\\test.dtsConfig",
            "C:\\Users\\dbauser\\Desktop\\test2.dtsConfig" ],
         "Properties": [
         {
            "PropertyName": "PropertyValue"
         },
         {
            "PropertyName2": "PropertyValue2"
         } ]
      }
   }
}

The following table describes the MSSQL SSIS job parameters.

Parameter

Description

Host

Defines the name of the host machine where the job runs. A Control-M/Agent must be installed on this host, as well as Control-M Databases plug-in version 9.0.00 or higher.

(Optional) You can define a host group instead of a host machine.

PackageSource

Determines the source of the SSIS package, one of the following:

  • SQL Server: Package stored on an MSSQL database.

  • File System: Package stored on the local Agent file system.

  • SSIS Package Store: Package stored on a file system that is managed by an SSIS service.

  • SSIS Catalog: Package stored on a file system that is managed by an SSIS Catalog service.

PackageName

Defines the name of the SSIS package.

CatalogEnv

(Optional) Defines the name of the environment where the package is run when PackageSource is set to SSIS Catalog.

Use this parameter if you want to run the package on a different environment from the one that you are currently using.

ConfigFiles

(Optional) Defines names of configuration files that contain specific data that you want to apply to the SSIS package.

Properties

(Optional) Defines pairs of names and values for properties defined in the SSIS package.

Each property name is replaced by its defined value during SSIS package execution.

For additional optional parameters, see above.