JDBC is a Java API to connect and interact with databases. You can use a JDBC connection to extract metadata from any source system for which a JDBC driver is available. Metadata Command Center certifies the following source systems from which you can extract metadata using the JDBC catalog source:
•Amazon DynamoDB
•Microsoft Azure Cosmos DB
•Marketo
•Microsoft Dynamics CRM
•MongoDB
•IBM Netezza
•Sybase ASE (Adaptive Server Enterprise)
•Sybase IQ (Intelligent Query)
•Workday
•Apache Cassandra
•SAP SuccessFactors
•NetSuite
•SingleStore
•IBM Db2 for z/OS
To extract metadata from these source systems, download the source-specific JDBC drivers and procure appropriate licenses. For information about using the JDBC drivers and licenses to extract metadata, see the HOW TO: Use the JDBC drivers to extract metadata in Metadata Command Center Knowledge article.
Note: MongoDB and Amazon DynamoDB are NoSQL databases that do not contain views and view columns.
Objects extracted
You can use a JDBC connection to connect to any database to extract the following metadata objects:
•Database
•Schema
•View
•Table
•Column
•View Column
Prerequisites for metadata extraction and data profiling
To use a JDBC connection to extract metadata and profile data, perform the following steps:
•Use the JDBC V2 connector to configure a connection in the Informatica Intelligent Cloud Services Administrator. For information about configuring a connection in Administrator, see Connections in the Cloud Common Services help.
•Download the latest Type 4 JDBC driver version that your database supports from the third-party vendor site.
For example, if you want to use the JDBC V2 connector to connect to Aurora MySQL, download the Aurora MySQL driver. Informatica has certified Aurora MySQL driver version 8.0.27 for JDBC V2 Connector.
• Install the Type 4 JDBC driver for the database on the Secure Agent machine and perform the following tasks:
1Navigate to the following directory on the Secure Agent machine:
2Create the following folder and copy the driver based on the type of source that you want to configure:
informatica.jdbc_v2/common
You can add multiple drivers for different source systems.
3Restart the Secure Agent.
Configure permissions
To extract metadata and run profiles, you need account access and permissions to the JDBC source system.
Permissions to extract metadata
Create a user account for the Informatica user to access the JDBC source system. Grant read permission to the new user account.
Permissions to run data profiles
You do not need additional permissions to run data profiles.
Data profiling using the JDBC V2 connection
Use the JDBC V2 connection to enable Secure Agent-based data profiling for Aurora MySQL, IBM Db2, PostgreSQL, and Oracle catalog sources. To enable Secure Agent-based data profiling for these catalog sources, download the JDBC driver from the respective third-party vendor sites and complete the prerequisite steps mentioned above.
Configure data profiling to run profiles on the metadata extracted from Aurora MySQL, IBM Db2, PostgreSQL, and Oracle catalog sources using the JDBC V2 connection. You can run data profiles on the following objects:
•Table
•View
After running the catalog source job, you can view the data profiling statistics in Data Governance and Catalog.
Amazon Aurora MySQL is a fully managed, MySQL-compatible, relational database management system (RDBMS) for the cloud offered by Amazon Web Services. The data profiling task runs profiles on the following data types for Aurora MySQL objects:
•Char
•Date
•Decimal
•Double
•Float
•Integer
•Smallint
•Bigint
•Time
•Text
•Mediumtext
•Tinytext
•Longtext
•Varchar
•Character varying
The data profiling task runs profiles on the following data types for IBM Db2 objects:
•Bigint
•Char(L)
•Date
•Decimal(P,S)
•Float
•Graphic
•Integer
•Numeric(P,S)
•Smallint
•Time
•Timestamp
•Varchar
•Vargraphic
The data profiling task runs profiles on the following data types for PostgreSQL objects:
•Smallint/Int2
•Int/Int4
•Bigint/int8
•Decimal
•Numeric
•Real/Float4
•Double/Float8
•Smallserial/Int2
•Serial
•Bigserial/Serial8
•Char
•Char(n)
•Varchar
•Varchar(n)
•Text
•Date
•Time
•Timestamp
•Boolean
•Citext
Sampling type
Determine the sample rows on which you want to run the data profiling task. You can choose one of the following sampling types for a JDBC catalog source:
- All Rows
- Custom Query
Data classification for JDBC catalog sources
Configure data classification for JDBC catalog sources to classify and organize data in your organization. You can view the data classification results in Data Governance and Catalog.
Connection properties
When you configure a connection to JDBC in Administrator, you can view the connection properties for that connection on the Registration page in Metadata Command Center.
The following table describes the JDBC connection properties for the database that you configure the connection for:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Description
Description of the connection. Maximum length is 4000 characters.
Type
JDBC V2
Runtime Environment
The name of the runtime environment where you want to run tasks.
JDBC Driver Class Name
Name of the JDBC driver class.
For example, to connect to Aurora PostgreSQL, specify the following driver class name: org.postgresql.Driver
For more information about which driver class to use with specific databases, see the corresponding third-party vendor documentation.
Connection String
Connection string to connect to the database.
Use the following format to specify the connection string: jdbc:<subprotocol>:<subname>
For example, the connection string for the Aurora PostgreSQL database type is jdbc:postgresql://<host>:<port>[/dbname].
For more information about the connection string to use with specific drivers, see the corresponding third-party vendor documentation.