0 / 0
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. 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.

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

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.

 

Learn more

Connecting to a data source in DataStage

Parent topic: Supported connections

Generative AI search and answer
These answers are generated by a large language model in watsonx.ai based on content from the product documentation. Learn more