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.
  • Enter the DSN name to identify the data source. See Use DSN from ODBC configuration.

Federal Information Processing Standards (FIPS) compliance

The ODBC connection is compliant with FIPS.

Create an ODBC connection to a data source

  1. From the project page, click the Assets tab.
  2. Click New asset > Connection.
  3. Select ODBC and then select a data source type.
  4. Enter the details for the connection.

For Credentials, you can use secrets if a vault is configured for the platform and the service supports vaults. For information, see Using secrets from vaults in connections.

You can add this connection to a deployment space. Click Add to space > Connection. See Adding connections to a deployment space.

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.

Restrictions for the generated SQL for Apache Hive

  • If the generated SQL doesn't work, you must provide your own SQL statement.
  • The Enable quoted identifiers property is not supported for the Apache Hive data source.
  • To preview the data, use lowercase letters for the value in the Table name field in the target Stage properties.

Google BigQuery

  • Project: The name of the project to connect to. Projects in Google BigQuery are equivalent to catalogs in ODBC. See Project in the Progress documentation.
  • Dataset: The name of the dataset to connect to. Datasets in Google BigQuery are equivalent to schemas in ODBC. See Dataset in the Progress documentation.
  • Additional properties: Enter a semicolon-separated list of any properties that are required by the Google BigQuery instance. For available properties, see Google BigQuery Connection option descriptions in the Progress documentation.

Authentication method

Select OAuth2.0 or Service Account

OAuth2.0:

  • Client ID: The consumer key for your application
  • Client secret: The consumer secret for your application.
  • Refresh token: The refresh token that is used to either request a new access token or renew an expired access token.
  • Access token: The access token to authenticate to the Google BigQuery instance.

Service Account:

  • Service account email: The email address that is associated with your service account.
  • Input method for service account key: Key content is selected by default.
  • Service account key content: The private key that you use to authenticate to the Google BigQuery instance.

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.

Previewing target data in Greenplum
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

IBM Db2

The Db2 data source can be used for multiple Db2 data source types. For example, 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.

Previewing target data in IBM Db2
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

Db2 Warehouse with SSL authentication
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

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.

Previewing target data in IBM Db2 on iSeries (AS400)
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

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 Netezza

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

Previewing target data in IBM Netezza
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

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.

Include the Windows domain name and the authentication method in the Additional properties field.

For example: Domain=myco;AM=9;

MongoDB

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

MongoDB with SSL authentication
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.

Previewing target data in Oracle
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

PostgreSQL

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

Previewing target data in PostgreSQL
If your DataStage flow uses the ODBC connector as a target and you want to preview the data, in the target stage, select Enable quoted identifiers under the Stage properties.

PostgreSQL with SSL authentication
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

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.

Text

Additional properties: Enter a semicolon-separated list of properties for the connection. The DB attribute is required and is the directory that contains the data files. For available properties, see Progress Connection Option Descriptions for Text.

Additional properties examples:

  • DB=/ds-storage;
  • DB=/ds-storage;DataFileExtension=csv;FirstLineNames=1;

Use DSN from ODBC configuration

  • Data source name: Enter the data source name that is defined in the associated DSN entry in the odbc.ini file.
  • Additional properties: Optional: Enter any properties to override the properties in the associated DSN entry in the odbc.ini file.
  • Username and password.

Learn more

Connecting to a data source in DataStage

Parent topic: Supported connections