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:

ConnectionProfile:Database:DB2

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

Copy
"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:

Copy
"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.

Parameter

Description

Driver

Defines the JDBC driver name that you define in Control-M or via the Driver:JDBC:Database object.

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:

Copy
"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.

Parameter

Description

TargetAgent

Determines the Agent where the JDBC driver object connection profile deploys.

StringTemplate

Defines the structure according to which a connection profile string is created.

DriversJarsFolder

Defines the path to the folder where the database driver jars are located.

ClassName

Defines the name of driver class.

LineComment

Defines the syntax used for line comments in the scripts that run on the database.

StatementSeparator

Defines the syntax used for statement separator in the scripts that run on the database.

ConnectionProfile:Database:MSSQL

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

Copy
"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.

Parameter

Description

AuthenticationType

Determines the SQL Server Authentication.

Valid Values:

  • NTLM2 Windows Authentication.

  • Windows Authentication.

  • SQL Server Authentication.

ConnectionProfile:Database:MSSQL:SSIS

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

Copy
"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.

Parameter

Description

SkipPackageTest

Determines whether to skip validation of SSIS packages when testing the connection profile.

Valid Values:

  • true

  • false

SSIS

Defines connection details for all associated SSIS Packages.

Source

Determines source location of the SSIS Package.

Valid Values:

  • SQL Server.

  • File System.

  • SSIS Package Store.

Name

Defines the name of the SSIS Package.

Password

Defines password required for accessing the SSIS Package.

To update an existing connection profile and keep the current password, type five *, as follows:

*****

ConnectionProfile:Database:Oracle

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

ConnectionProfile:Database:Oracle:SID

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

Copy
"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:

Copy
"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.

Copy
"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.

Copy
"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.

Copy
"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.

Parameter

Description

Port

Determines the database port number.

If the port is not specified, the following default values are used for each database type.

Default Valid Values:

  • MSSQL: 1433

  • Oracle: 1521

  • DB2: 50000

  • Sybase: 4100

  • PostgreSQL: 5432

Password

Defines a password to the database account. Use Secrets in code to not expose the password in the code.

To update an existing connection profile and keep the current password, type five *, as follows:

*****

DatabaseName

Defines the name of the database.

DatabaseVersion

Determines the version of the database.

For a list of databases and versions supported by your version of Control-M for Databases, open the BMC Product Compatibility page. Search for Control-M for Databases, and then select the relevant version.

The default version for each database is the earliest supported version.

MaxConcurrentConnections

Defines the maximum number of connections that the database can process at the same time.

Valid values: 1–512

Default: 100

ConnectionRetryTimeOut

Defines the number of seconds to wait before attempting to connect again.

Valid values: 1–300

Default: 5

ConnectionIdleTime

Defines the number of seconds that the database connection profile can remain idle before disconnecting.

Default: 300

ConnectionRetryNum

Defines the number of times to attempt to reconnect after a connection failure.

Valid values: 1–24

Default: 5