Database Management Connection Profiles

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.

Connection profiles are available for the following databases:

• IBM Db2 Database: ConnectionProfile:Database:DB2

• Java Database Connectivity (JDBC): ConnectionProfile:Database:JDBC

• Microsoft SQL Server (MSSQL): ConnectionProfile:Database:MSSQL

• Oracle Database: ConnectionProfile:Database:Oracle

• PostgreSQL Databse: ConnectionProfile:Database:PostgreSQL

• Sybase Database (SAP ASE): ConnectionProfile:Database:Sybase

ConnectionProfile:Database:DB2

The following example shows how to define an IBM Db2 Database connection profile:

"DB2_CONNECTION_PROFILE":
{
   "Type": "ConnectionProfile:Database:DB2",
   "TargetCTM":"CTMHost",
   "TargetAgent": "AgentHost",
   "Host": "DB2Host",
   "Port":"50000",
   "User": "db user",
   "Password": "db password",
   "DatabaseName": "db2"
} 

IBM Db2 Database connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

ConnectionProfile:Database:JDBC

The Database:JDBC connection profile is based on a custom-defined database, created with the Java Database Connectivity (JDBC) API.

The following example shows how to define Database:JDBC connection profile:

"JDBC_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:JDBC",
   "User":"db user",
   "TargetCTM":"CTMHost",
   "Host": "PGSQLHost",
   "Driver":"PGDRV",
   "Port":"5432",
   "TargetAgent": "AgentHost",
   "Password": "db password",
   "DatabaseName":"dbname"
}

Most of the Java Database Connectivity (JDBC) connection profile parameters are described in Control-M for Databases General Connection Profile Parameters. The following table describes an additional parameter that is unique to the JDBC connection profile.

Driver:JDBC:Database

The JDBC:Database command enables you to define a driver object that is used by a connection profile.

The following example shows how to define JDBC driver object connection profile:

"MyDriver": 
{
   "Type": "Driver:Jdbc:Database",
   "TargetAgent":"app-redhat",
   "StringTemplate":"jdbc:sqlserver://<HOST>:<PORT>/<DATABASE>",
   "DriverJarsFolder":"/home/controlm/ctm/cm/DB/JDBCDrivers/PostgreSQL/9.4/",
   "ClassName":"org.postgresql.Driver",
   "LineComment" : "--",
   "StatementSeparator" : ";"
}

The following table describes the JDBC driver object connection profile parameters.

ConnectionProfile:Database:MSSQL

The following example shows how to define Microsoft SQL Server (MSSQL) connection profile.

"MSSQL_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:MSSQL",
   "TargetCTM":"CTMHost",
   "TargetAgent": "AgentHost",
   "Host": "MSSQLHost",
   "User": "db user",
   "Port":"1433",
   "Password": "db password",
   "DatabaseName": "master",
   "DatabaseVersion": "2005",
   "MaxConcurrentConnections": "9",
   "ConnectionRetryTimeOut": "34",
   "ConnectionIdleTime": "45"
}

Most of the MSSQL connection profile parameters are described in Control-M for Databases General Connection Profile Parameters. The following table describes an additional parameter that is unique to the MSSQL connection profile.

ConnectionProfile:Database:MSSQL:SSIS

The following example shows how to define the connection profile for Microsoft SQL Server Integration Services (SSIS) packages:

"SSIS_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:MSSQL:SSIS",
   "TargetAgent": "AgentHost",
   "TargetCTM": "LocalControlM",
   "Host": "localhost",
   "User": "administrator",
   "Port": "1433",
   "Password": "db password",
   "DatabaseName": "testdb",
   "DatabaseVersion": "2016",
   "AuthenticationType": "Windows Authentication",
   "SkipPackagesTest" : false,
   "SSIS": [
   {
      "Source": "File System",
      "Name": "file_system_package",
      "Password": "password"
   },
   {
      "Source": "SSIS Package Store",
      "Name": "ssis_package",
      "Password": "password"
   },
   {
      "Source": "SQL Server",
      "Name": "sql_server_package",
      "Password": "password"
   } ]
}

Most of the Microsoft SSIS packages connection profile parameters are described in Control-M for Databases General Connection Profile Parameters. The following table describes an additional parameter that is unique to the Microsoft SSIS packages connection profile.

ConnectionProfile:Database:Oracle

Oracle Database connection profiles are available for the following connection types:

• Site Identifier (SID): ConnectionProfile:Database:Oracle:SID

• Service Name: ConnectionProfile:Database:Oracle:ServiceName

• Connection String: ConnectionProfile:Database:Oracle:ConnectionString

ConnectionProfile:Database:Oracle:SID

The following example shows how to define an Oracle Database connection profile via a site identifier (SID):

"ORACLE_CONNECTION_PROFILE": 
{   
   "Type": "ConnectionProfile:Database:Oracle:SID",   
   "TargetCTM": "controlm",   
   "Port": "1521",   
   "TargetAgent": "AgentHost",
   "Host": "OracleHost",
   "User": "db user",   
   "Password": "db password",
   "SID": "ORCL" 
}

Oracle Database connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

ConnectionProfile:Database:Oracle:ServiceName

The following example shows how to define an Oracle Database connection profile via a service name:

"ORACLE_CONNECTION_PROFILE":
{   
   "Type": "ConnectionProfile:Database:Oracle:SID",   
   "Centralized": true,
   "Port": "1521",   
   "Host": "OracleHost",
   "User": "db user",   
   "Password": "db password",
   "SID": "ORCL" 
}

Oracle Database connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

ConnectionProfile:Database:Oracle:ConnectionString

The following example shows how to define an Oracle Database connection profile via a connection string that contains text from your tnsname.ora file.

"ORACLE_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:Oracle:ConnectionString",
   "TargetCTM":"CTMHost",
   "ConnectionString":"OracleHost:1521:ORCL",
   "TargetAgent": "AgentHost",
   "User": "db user",
   "Password": "db password"
}

Oracle Database connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

ConnectionProfile:Database:PostgreSQL

The following example shows how to define a PostgreSQL Database connection profile.

"POSTGRESQL_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:PostgreSQL",
   "TargetCTM":"CTMHost",
   "TargetAgent": "AgentHost",
   "Host": "PostgreSQLHost",
   "Port":"5432",
   "User": "db user",
   "Password": "db password",
   "DatabaseName": "postgres"
} 

PostgreSQL Database connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

ConnectionProfile:Database:Sybase

The following example shows how to define a Sybase Database (SAP ASE) connection profile.

"SYBASE_CONNECTION_PROFILE": 
{
   "Type": "ConnectionProfile:Database:Sybase",
   "TargetCTM":"CTMHost",
   "TargetAgent": "AgentHost",
   "Host": "SybaseHost",
   "Port":"4100",
   "User": "db user",
   "Password": "db password",
   "DatabaseName": "Master"
} 

Sybase Database (SAP ASE) connection profile parameters are described in Control-M for Databases General Connection Profile Parameters.

Control-M for Databases General Connection Profile Parameters

All Control-M for Databases connection profiles share a number of general parameters, which are described here. For unique connection profile parameters, see ConnectionProfile:Database:JDBC and ConnectionProfile:Database:MSSQL.

The following table describes the Control-M for Databases general connection profile parameters.