ODBC connection (DataStage)

Data sources with SSL support

For an SSL-enabled connection, you can specify the certificate with the TS=data: attribute in the Additional properties field.
Example: EncryptionMethod=1;AuthenticationMethod=0;ValidateServerCertificate=1;TS=data://-----BEGIN CERTIFICATE-----xxxx -----END CERTIFICATE-----

Consult the Progress documentation for the data source for any updates to the syntax.

Apache Cassandra

Progress Software DataDirect ODBC library version: lib/VMcsndr00.so
Progress Software DataDirect ODBC file version: 08.00.0340 (B1181, U0931)

• 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.
  • Use the ConfigOptions (CO) attribute for customization and troubleshooting.

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

Progress Software DataDirect ODBC library version: lib/VMhive00.so
Progress Software DataDirect ODBC file version: 08.01.1035 (B2055, U1864)

• 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

Progress Software DataDirect ODBC library version: lib/VMgbq00.so
Progress Software DataDirect ODBC file version: 08.00.0357 (B1909, U1715)

• 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.

Greenplum

Progress Software DataDirect ODBC library version: lib/VMgplm00.so
Progress Software DataDirect ODBC file version: 07.16.1136 (B0805, U0647)

• 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.

Progress Software DataDirect ODBC library version: lib/VMdb200.so
Progress Software DataDirect ODBC file version: 08.02.0627 (B2069, U1878)

• 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)

Progress Software DataDirect ODBC library version: lib/VMdb200.so
Progress Software DataDirect ODBC file version: 08.02.0627 (B2069, U1878)

• 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

Progress Software DataDirect ODBC library version: lib/VMdb200.so
Progress Software DataDirect ODBC file version: 08.02.0627 (B2069, U1878)

• 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

Progress Software DataDirect ODBC library version: lib/VMifcl00.so
Progress Software DataDirect ODBC file version: 08.02.0124 (b9990, U1362)

• 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

Progress Software DataDirect ODBC library version: libnzodbc.so

• 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

Progress Software DataDirect ODBC library version: lib/VMimpala00.so
Progress Software DataDirect ODBC file version: 07.16.0515 (B0805, U0647)

• 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

The Microsoft SQL Server data source can be used for multiple data source types. For example, stand-alone Microsoft SQL Server, Microsoft Azure SQL Database, or Azure Synapse SQL.

Progress Software DataDirect ODBC library version: lib/VMsqls00.so
Progress Software DataDirect ODBC file version: 08.02.1500 (B2058, U1867)

• 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=1;

MongoDB

Progress Software DataDirect ODBC library version: lib/VMmongo00.so
Progress Software DataDirect ODBC file version: 08.10.0248 (B2064, U1873)

• 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.
  • Use the ConfigOptions (CO) attribute for customization and troubleshooting.

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

Progress Software DataDirect ODBC library version: lib/VMmysql00.so
Progress Software DataDirect ODBC file version: 08.02.0466 (B1697, U1497)

• 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.

Progress Software DataDirect ODBC library version: OracleClient-based driver: lib/VMor800.so. Oracle Wire Protocol: lib/VMora00.so
Progress Software DataDirect ODBC file version: OracleClient-based driver: 07.16.0221 (B0572, U0414). Oracle Wire Protocol: 08.02.3140 (B2076, U1885)

• 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.

Progress Software DataDirect ODBC library version: lib/VMpsql00.so
Progress Software DataDirect ODBC file version: 08.02.2505 (B2078, U1890)

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

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

Progress Software DataDirect ODBC library version: lib/VMase00.so
Progress Software DataDirect ODBC file version: 07.16.0373 (B0805, U0647)

• 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

Progress Software DataDirect ODBC library version: lib/VMsyiq00.so
Progress Software DataDirect ODBC file version: 08.02.0279 (B2038, U1847)

• 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.