Configuring database connection using Perl or Java ETLs

This topic contains the following sections:

Note

When you add a Perl or Java Out-of-the-box ETL module, you can select an existing datasource or a custom datasource. For more information on configuring datasources, see Adding external database connections.

For further information and configuration tips, contact your Database Administrator. If you are still unable to solve your issue, you can provide the testconnection.txt file to BMC Support, specifying database settings and access credentials.

Configuring an Oracle connection using Perl ETLs

To set up the connection to an Oracle database using Perl ETLs, you can either edit the tnsnames.ora file using the oracle user, or manually specify the host details in the Database TNS name field of the configuration panel.

Configuration by editing TNSNAMES.ORA

Note

Ensure that you have write permissions on this file.

On your ETL engine server, from the installation directory of the Oracle client, edit the file $ORACLE_CLIENT_HOME/network/admin/tnsnames.ora to configure the connectivity to your database. For example:

DB=
(
 DESCRIPTION=
  (ADDRESS=
    (PROTOCOL=TCP)
    (HOST=<your_server_ip>)
    (PORT=<port>)
  )
  (CONNECT_DATA=
    (SERVER=default)
    (SERVICE_NAME=<service_name>)
  )
)

oracle conn param detail.png

Testing the Oracle connection from the command line

Run the following commands as BMC Helix Continuous Optimization OS user:

. $CPITBASE/env.sh
sqlplus user/password@DB

$CPITBASE is the BMC Helix Continuous Optimization installation path — for example{{/opt/cpit}}.

To troubleshoot the database connection, verify that you can:

  • Reach your database server from the ETL engine machine by running the command ping <your_db_server_ip>
  • Remotely access your database server using telnet from the ETL engine machine, by running the command telnet<your_db_server_ip> <your_db_server_port>
  • Connect to your database using a software client, specifying the same credentials

Configuration without editing TNSNAMES.ORA

If you do not have the required permissions to edit tnsnames.ora, you can set up the connection to an Oracle database specifying the host details in the Database TNS name field of the configuration screen:

host=<database_host>;sid=<DBSID>;port=1521

Perl ETLs - Configuring an Oracle connection without editing tnsnames.ora
oracle conn param.png

Configuring an Oracle connection using Java ETLs

To set up the connection to an Oracle database using Java ETLs, you do not need to modify the tnsnames.ora file. The following figure shows a configuration example:

Java ETLs - Configuring an Oracle connection
oracle conn param.png

You can configure the URL with any JDBC compliant driver.

Configuring a Microsoft SQL Server connection using Perl ETLs

To configure a SQL Server connection, edit the freetds properties file. On your ETL engine server, edit the file $CPITBASE/etl/freetds/etc/freetds.conf to configure the connectivity to your database. For example:

[DBSRV]
   host = <your_server_ip>
    port = 1433
    tds version = 7.0

Note

Use tds version = 8.0 if your SQL Server is version 2005 or later.

Only if your database is exposed through a SQL Server Browser Service, database connectivity should be configured as follows (first try the above configuration format):

[DBSRV]
     host = <your_db_server_ip>
      instance = <database_instance>
      tds version = 7.0

Perl ETLs - Configuring an SQL Server connection
sql - sybase.png

Testing the SQL Server connection from the command line

Run the following commands as BMC Helix Continuous Optimization OS user:

. $CPITBASE/env.sh
cd $CPITBASE/etl/freetds/bin
export TDSDUMP=testconnection.txt
./tsql -S DBSRV -U 'username' -P 'password'

$CPITBASE is the BMC Helix Continuous Optimization installation path — for example{{/opt/cpit}}.

In alternative to the command above you can also try this one, if you get an error like IP address pointer is empty:

./tsql -H 'HOST' -p 'port' -U 'username' -P 'password'

To troubleshoot the database connection:

  • If you did not install in /opt/cpit check the $CPITBASE/env.sh file for the presence of the definition of this environment variable:
    FREETDS=$CPITBASE/etl/freetds
    export FREETDS
     If it is not present, create a file named customenv.sh in your $CPITBASE folder and put this environment variable definition inside.
  • Check the content of testconnection.txtfor any of the following:
    • Incorrect username and password combination
    • Incorrect database server IP or port
    • Verify that the database server IP and port match those specified in freetds.conf, and check there are no other freetds.conf files on your filesystem
    • Verify that the tds version logged for connection matches that specified in freetds.conf, and check there are no other freetds.conf files on your filesystem
    • Connection stack traces; if this is the case, try modifying your tds version from 7.0 to 8.0
  • Verify that you can:
    • Reach your database server from the ETL engine machine by running the command ping <your_db_server_ip>
    • Remotely access your database server using telnet from the ETL engine machine, by running the command telnet <your_db_server_ip> <your_db_server_port>
    • Connect to your database using a software client
    • If the username is part of a Windows domain, verify that you inserted it in the format YOURDOMAIN\username; verify that your settings match those used to connect to the same database with a software client

Configuring a Sybase connection using Perl ETLs

To configure a Sybase connection, edit the freetds properties file. On your ETL engine server, edit the file $CPITBASE/etl/freetds/etc/freetds.conf to configure the connectivity to your database.

Following is an example of the connectivity configuration to be used with a Sybase database:

[DBSRV]
     host = <your_server_ip>
      instance = <batabase_instance>
      port = 2638
      tds version = 5.0

Perl ETLs - Configuring a Sybase Server connection

sql - sybase.png

Testing the Sybase connection from the command line

Run the following commands as BMC Helix Continuous Optimization OS user:

. $CPITBASE/env.sh
cd $CPITBASE/etl/freetds/bin
export TDSDUMP=testconnection.txt
./tsql -S DBSRV -U 'username' -P 'password'

$CPITBASE is the BMC Helix Continuous Optimization installation path — for example /opt/cpit.

In alternative to the command above you can also try this one, if you get an error like "IP address pointer is empty":

./tsql -H 'HOST' -p 'port' -U 'username' -P 'password'

To troubleshoot the database connection:

  • If you did not install in /opt/cpit check the $CPITBASE/env.sh file for the presence of this environment variable:
    FREETDS=$CPITBASE/etl/freetds
    export FREETDS
     If it is not present, create a file named customenv.sh in your $CPITBASE folder and put this environment variable definition inside.
  • Check the content of testconnection.txtfor any of the following:
    • Incorrect username/password combination
    • Incorrect database server IP or port
    • Verify that the database server IP and port match those specified in freetds.conf, and check there are no other freetds.conf files on your filesystem
    • Verify that the tds version logged for connection matches that specified in freetds.conf, and check there are no other freetds.conf files on your filesystem
    • Connection stack traces; if this is the case, try modifying your tds version from 5.0 to 4.2
  • Verify that you can:
    • Reach your database server from the ETL engine machine by running the command ping <your_db_server_ip>
    • Remotely access your database server using telnet from the ETL engine machine, by running the command telnet <your_db_server_ip> <your_db_server_port>
    • Connect to your database using a software client

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*