0 / 0
ODBC connection (DataStage)
ODBC connection (DataStage)

ODBC connection (DataStage)

Use the ODBC connection to connect to a database with the ODBC application programming interface (API). The ODBC connection is optimized for the DataStage ODBC connector and can be used only in DataStage flows.

The ODBC connection and connector provides these benefits:

  • View error messages that are generated when your jobs run.
  • Specify fine-grained control on the connector usage properties.
  • Additional connection or ODBC driver properties for customized deployments, such as LDAP database authentication or large database support.

Restriction

The ODBC connection can use only the IBM-provided ODBC drivers.

Create an ODBC connection to a data source

  1. Click New asset > Connection, and select ODBC.
  2. Select a data source type.
  3. Enter the details for the connection.

Apache Cassandra

  • Hostname
  • Port number
  • Cluster nodes: A comma-separated list of member nodes in your cluster. Use format HostName:Port,HostName:Port.
  • Keyspace: The keyspace to which you want to connect. The keyspace value is also used as the default qualifier for unqualified table names in queries.
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Apache Cassandra server. For available properties, see Progress Connection option descriptions for Apache Cassandra.

Restrictions for Apache Cassandra

  • Create and Replace Write modes are not supported.
  • The database doesn't enforce primary key constraint on Insert New rows only and Insert then update modes. Instead, it updates that row (even through CQLSH shell).

Apache Hive

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Apache Hive server. For available properties, see Progress Connection option descriptions for Apache Hive.

    Restriction for Apache Hive

    If the generated SQL doesn't work, you must provide your own SQL statement.

Greenplum

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Greenplum server. For available properties, see Progress Greenplum Connection Option Descriptions.

IBM Db2

The Db2 data source can be used for multiple Db2 data source types. For example, Db2 Hosted, Db2 on AIX, Db2 on Linux, Db2 on Windows, or Db2 Warehouse.

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Db2 database server. For available properties, see Progress Connection Option Descriptions for DB2.

IBM Db2 on Linux on System z

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Db2 database server. For available properties, see Progress Connection Option Descriptions for DB2.

IBM Informix

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Informix database server. For available properties, see Progress Connection Option Descriptions for Informix.

Restriction for IBM Informix
Unicode characters are not supported for the ODBC Informix data source.

IBM Db2 on iSeries (AS400)

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Db2 database server. For available properties, see Progress Connection Option Descriptions for DB2.

IBM Netezza

  • Database name
  • Hostname
  • Port number
  • Username and password

Impala

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Impala database server. For available properties, see Progress Connection Option Descriptions for Impala.

Microsoft SQL Server

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Microsoft SQL Server. For available properties, see Progress Connection option descriptions for Microsoft SQL Server.

MongoDB

SSL on MongoDB
To connect to a database that uses an SSL port, you must enter the EncryptionMethod (EM) attribute and the ValidateServerCertificate (VSC) attribute as follows in the connection Additional properties field:

EM=1;AM=1;VSC=0

Restrictions for MongoDB

  • To use the MongoDB data source as a target, you must enter ReadOnly=0 in the connection Additional properties.
  • Complex data types are not supported
  • For nested collections, you must either flatten the schema or normalize the schema and use proper queries. See information about the SchemaFormat attribute.
  • Create and Replace Write modes are not supported
  • Insert with primary key violation, Insert new rows only, and Insert then update Write modes are not supported (database limitation)
  • Views are not supported
  • The following data types are not supported as a target:
    • Date
    • Decimal
    • Numeric
    • Time
    • Timestamp

MySQL

  • Database name
  • Hostname
  • Port number
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the MySQL database server. For available properties, see Progress Connection Options Descriptions for MySQL.

Restriction for MySQL

MySQL Community Edition Servers are not supported.

Oracle

The Oracle data source can be used for other Oracle data source types. For example, Amazon RDS for Oracle.

  • Hostname
  • Port number
  • Service name: The Oracle service name that specifies the database.
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Oracle database server. For available properties, see Progress Connection option descriptions for Oracle.

PostgreSQL

The PostgreSQL data source can be used for other PostgreSQL data source types. For example, Amazon RDS for PostgreSQL or Databases for PostgreSQL.

SAP ASE

  • Database name
  • Network address: SAP ASE server name or IP address followed by a comma and the port number.
    For example, server-name,5000 or 192.0.2.0,5000.
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the SAP ASE server. For available properties, see Progress Connection Option Descriptions for Sybase Wire Protocol.

SAP IQ

  • Database name
  • Network address: SAP IQ server name or IP address followed by a comma and the port number.
    For example, server-name,5000 or 192.0.2.0,5000.
  • Username and password
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the SAP IQ server. For available properties, see Progress Connection Option Descriptions for SAP IQ.

Learn more

DataStage connectors

Parent topic: Supported connections