Enterprise Data Catalog Scanner Configuration Guide > Configuring Database Management Resources > JDBC
  

JDBC

You can use a JDBC connection to access tables in a database.

Objects Extracted

JDBC with MySQL
Enterprise Data Catalog extracts metadata of the following schema objects from a MySQL and AS400 databases that uses JDBC for connectivity:
JDBC with AS400

Permissions to Configure the Resource

You must configure the CONNECT and SELECT_CATALOG_ROLE permissions for the database user account that you use to access the MySQL or AS400 databases.

Prerequisites

Download the JDBC driver JAR file from the Internet and copy the files to the JDBCScanner directory in the <Informatica installation directory>/services/CatalogService/ScannerJars/externalDependencies directory. You must restart the Catalog Service.
Alternatively, you can perform the following steps to complete the prerequisites:
  1. 1. Download the JDBC driver file and copy the file to the <INFA_HOME>/services/CatalogService/ScannerBinaries directory.
  2. 2. Open the <INFA_HOME>/services/CatalogService/ScannerBinaries/CustomDeployer/scannerDeployer.xml file and add the following lines in the file:
    3. <ExecutionContextProperty isLocationProperty="true" dependencyToUnpack="genericJDBCJars.zip"> <PropertyName>JDBCScanner_DriverLocation</PropertyName> <PropertyValue>scanner_miti/genericJDBC/Drivers</PropertyValue> </ExecutionContextProperty>
  3. 3. Save the scannerDeployer.xml file.
  4. 4. Recycle the Catalog Service.

Basic Information

The General tab includes the following basic information about the resource:
Information
Description
Name
The name of the resource.
Description
The description of the resource.
Resource type
The type of the resource.
Execute On
You can choose to execute on the default catalog server or offline.

Resource Connection Properties

The following table describes the connection properties for the JDBC resource:
Property
Description
Driver class
Name of the JDBC driver class. For more information on JDBC driver class examples, see Examples of JDBC Driver Classes.
URL
Connection string to connect to the database.
User
Database user name.
Password
Password for the database user name.
Agent URL
Optional. URL to the Enterprise Data Catalog agent that runs on a Microsoft Windows Server.
Note: Make sure that you specify the URL in the following format: http://<hostname>:<connector_port>/MIMBWebServices
Note: To extract metadata from multiple schemas of a source using the JDBC resource type, you can specify semicolon-separated schema names when you create the resource. You can type in the multiple schema names in the Schema field on the Metadata Load Settings page.
The following table describes the Additional and Advanced properties for source metadata settings on the Metadata Load Settings tab:
Property
Description
Enable Source Metadata
Select to extract metadata from the data source.
Catalog
Catalog name.
Note: You cannot use Catalog option for JDBC or ODBC sources.
Schema
Specifies a list of schemas to import.
Source Metadata Filter
You can include or exclude tables and views from the resource run. Use semicolons (;) to separate the table names and view names.
For more information about the filter field, see "Source Metadata and Data Profile Filter" in the Enterprise Data Catalog Scanner Configuration Guide.
Case sensitivity
Select one of the following options to specify if the database is configured for case sensitivity:
  • - Auto. If you select this option, the resource uses the JDBC API and tries to find if the database is configured for case sensitivity. If the JDBC API is not available for the resource, the resource uses the not case sensitive mode.
  • - Case Sensitive. Select this option to specify that the database is configured for case sensitivity.
  • - Case Insensitive. Select this option to specify that the database is configured as case insensitive.
View definition extracting SQL
Specifies the database specific SQL query to retrieve the view definition text.
Synonyms lineage SQL
Specifies the database specific SQL query to retrieve the synonym lineage. The following are the two columns that the query returns:
  • - Full Synonym Name
  • - Full Table Name
Optional Scope
Specifies the database object types to import, such as Tables and Views, Indexes, and Procedures.
Specify a list of optional database object types that you want to import. The list can have zero or more database object types, which are separated by semicolons. For example, Keys and Indexes, and Stored Procedures.
Import stored procedures
Specifies the stored procedures to import.
The default value is True or False whatever the case might be.
Agent Options
Specify the Enterprise Data Catalog Agent options to run the scanner job.
Memory
Specify the memory value required to run a scanner job.
Specify one of the following memory values:
  • - Low
  • - Medium
  • - High
Note: For more information about the memory values, see the Tuning Enterprise Data Catalog Performance article.
Custom Options
Custom JVM parameters that you can set to configure scanner container. Use the following arguments to configure the parameters:
  • - -Dscannerloglevel=<DEBUG/INFO/ERROR>. Changes the log level of scanner to values, such as DEBUG, ERROR, or INFO. Default value is INFO.
  • - -Dscanner.container.core=<No. of core>. Increases the core for the scanner container. Verify that the value is a number.
  • - -Dscanner.pmem.enabled.container.memory.jvm.memory.ratio=<1.0/2.0>. Increases the scanner container memory when pmem is enabled. Default value is 1.
  • - -DskipExternalDatabaseObjects=true. Excludes all external objects such as schema, table, database and view.
  • - -DcustomSampling=<Sampling Option>. Overrides the SQL query and limits or samples the number of rows read from the database. The Sampling Option of the resource is overridden with this custom option that you set for JDBC connection. See the following samples of various databases for reference:
    • - -DcustomSampling='fetch first 10000 ROWS ONLY' fetches the first 10000 rows from the PostgreSQL database.
    • - -DcustomSampling='LIMIT 10000' limits the number of rows read from the Vertica database to 10000.
    • - -DcustomSampling='TABLESAMPLE(10 PERCENT)' samples the Databricks table to 10 percent.
    Note: This argument is applicable for column profiling and data domain discovery and there is no impact on similarity discovery.
Track Data Source Changes
View metadata source change notifications in Enterprise Data Catalog.
You can enable data discovery for a JDBC resource. For more information about enabling data discovery, see "Enable Data Discovery" in the Catalog Administrator Guide.
You can enable composite data domain discovery for a JDBC resource. For more information about enabling composite data domain discovery, see "Composite Data Domain Discovery" in the Catalog Administrator Guide.