Database (via JDBC) - Import - 7.1

Talend Data Catalog Bridges

author
Talend Documentation Team
EnrichVersion
7.1
EnrichProdName
Talend Big Data Platform
Talend Data Fabric
Talend Data Management Platform
Talend Data Services Platform
Talend MDM Platform
Talend Real-Time Big Data Platform
EnrichPlatform
Talend Data Catalog

Bridge Specifications

Vendor ISO
Tool Name Database
Tool Version SQL-92 to SQL-2011
Tool Web Site http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=53681
Supported Methodology [Relational Database] Data Store (Physical Data Model) via JDBC API
Incremental Harvesting
Multi-Model Harvesting
Remote Repository Browsing for Model Selection
Data Profiling

BRIDGE INFORMATION
Import tool: ISO Database SQL-92 to SQL-2011 (http://www.iso.org/iso/home/store/catalogue_tc/catalogue_detail.htm?csnumber=53681)
Import interface: [Relational Database] Data Store (Physical Data Model) via JDBC API from Database (via JDBC)
Import bridge: 'Jdbc' 10.1.0

BRIDGE DOCUMENTATION
This bridge establishes a JDBC connection with a physical database in order to extract the physical metadata. It is critical that the parameters are filled correctly in order to satisfy the local connection requirements on the client workstation that runs the bridge. Please refer to the individual parameter tools tips for more detailed examples by database type.


WARNING and DISCLAIMER:

The implementation of metadata access (vs data access) is very limited when utilizing the generic JDBC driver in use by this bridge. Indeed, JDBC drivers only provide access to the limited metadata (such as table names and column names) needed for data access. In order to make sense of more advanced metadata (such as Synonyms, View dependencies, Candidate keys, etc.), more advanced queries have to be made to the proprietary system tables, which are specific to each database. Furthermore, such queries have to be performed using the native drivers provided by each database vendor. Therefore, we recommend using the dedicated database import bridges to access metadata from Oracle, IBM DB2 (DB/2 UDP databases), Microsoft SQL Server, Sybase and Teradata.

In addition, this generic JDBC database bridge has only been tested with Oracle, IBM DB2 (DB/2 UDP databases only), Microsoft SQL Server, Sybase and Teradata, (and again provide less metadata than the specific database bridges), and no claims are made for metadata access when using this bridge with any other database.


FREQUENTLY ASKED QUESTIONS

Q: How does the bridge load the JDBC driver I want to use?
A: The directory or jar file pathname must be set in the 'Driver path' bridge parameter or in the system CLASSPATH environment variable before starting this bridge.


Q: Does my driver require any java security permissions in order to run?
A: Some drivers do require additional security permissions to be granted at runtime. You can grant additional permissions to the driver by editing the 'java.policy' file as below:

grant codeBase 'file:/C:/oracle/ora9/jdbc/lib/classes12.zip' {
permission java.security.AllPermission;
};

Please read your driver documentation for details.

This 'java.policy' file needs to be created in the same directory as the executable, usually ''${MODEL_BRIDGE_HOME}/java'


Driver details:
- you may use any JDBC driver that supports JDBC 4.0
- driver JARs location: ${MODEL_bridge_home}/java/Jdbc/generic


Bridge Parameters

Parameter Name Description Type Values Default Scope
Driver location
This parameter is optional.

By default all JARs are located at:
'${MODEL_BRIDGE_HOME}/java/Jdbc/generic'

However, you may use this parameter to specify different JARs folder location. All JARs will be added to the CLASSPATH from this folder.
DIRECTORY      
Driver class
The name of the java class that implements the JDBC Driver interface.

The full name (including the package name) of the class must be specified.

You may copy and paste the following examples:

IBM DB2: com.ibm.db2.jcc.DB2Driver
Microsoft Access: sun.jdbc.odbc.JdbcOdbcDriver
Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
Netezza: org.netezza.Driver
Oracle 7: oracle.jdbc.driver.OracleDriver
Oracle 8 Thin: oracle.jdbc.driver.OracleDriver
Sybase: com.sybase.jdbc3.jdbc.SybDriver
Teradata: sun.jdbc.odbc.JdbcOdbcDriver, or com.teradata.jdbc.TeraDriver if you are intending to import views
PostgreSQL: org.postgresql.Driver
STRING   sun.jdbc.odbc.JdbcOdbcDriver Mandatory
URL A JDBC database connection URL has the following syntax:

jdbc:URLSubProtocol:URLSubName

This value is driver dependent and database dependent.

Examples:

IBM DB2 8.1 Net: jdbc:db2://COMPUTER_NAME_OR_IP:PORT/DATABASE_NAME
IBM DB2 8.1 App: jdbc:db2:DATABASE_NAME

Microsoft Access: jdbc:odbc:YOUR_DSN

Microsoft SQL Server: jdbc:sqlserver://COMPUTER_NAME_OR_IP:PORT

Netezza: jdbc:netezza://COMPUTER_NAME_OR_IP:PORT/DBname

Oracle 7 Thin: jdbc:oracle:thin:@COMPUTER_NAME_OR_IP:PORT:ORACLE_SID
Oracle 8 Thin: jdbc:oracle:thin:@(description=(address=(host=DATABASE_HOST)(protocol=tcp)(port=1521))(connect_data=(sid=SID)))

SyBase: jdbc:sybase:Tds:COMPUTER_NAME_OR_IP:PORT

Teradata: jdbc:odbc:YOUR_DSN, or when using the TeraDriver (native java driver): jdbc:teradata://COMPUTER_NAME_OR_IP/TMODE=ANSI,CHARSET=UTF8

PostgreSQL: jdbc:postgresql://COMPUTER_NAME_OR_IP:PORT/DATABASE_NAME

MySQL: jdbc:mysql://COMPUTER_NAME_OR_IP/DATABASE_NAME

SAP Hana: jdbc:sap://COMPUTER_NAME_OR_IP:DATABASE_TUNNEL_PORT/?autocommit=false

Informix 12.10.xC3 and earlier:jdbc:informix-sqli://COMPUTER_NAME_OR_IP:PORT/DATABASE_NAME:INFORMIXSERVER=DATABASE_SERVER
Informix: jdbc:informix-sqli://COMPUTER_NAME_OR_IP:PORT/DATABASE_NAME
STRING   jdbc:[jdbc-prefix]:COMPUTER_NAME_OR_IP:PORT/DATABASE_NAME Mandatory
User The database user name on whose behalf the connection is being made.

Important note about permissions:
This bridge reads the metadata for a database, rather than the data. Hence, permission assignments need to be quite different than for a standard database user. The bridge requires access to the system tables, not the actual data tables, thus it is highly recommended that one engages their DB Admin staff to ensure proper permissions for the user name entered here.

We do not have any specific instructions as to what are the minimal set of permissions required as it varies from database type to database type, as well as from version to version. In order to ensure that the bridge is working properly, one must first import using the bridge with a FULL Sys Admin type user name (all permissions), i.e., the same type of user name that is used to create and maintain the DB. Once the bridge is working properly against a give DB, then the DB Admin staff may create a user with lesser privileges that still produce a successful result.

Note, this parameter is not required. In the case of Oracle and Microsoft SQL Server one can specify user credentials in the cHost bridge parameter. In addtion, many Microsoft Access databases allow one to connect without specifying user credentials.
Also, for the Microsoft SQL Server specific bridge, if this parameter is empty, then it is assumed that integrated security is being used (see http://msdn.microsoft.com/en-us/library/ms378428.aspx#Connectingintegrated). In this case, the bridge will attempt to connect with this type of signature:

jdbc:sqlserver://; integratedSecurity=true
instead of

jdbc:sqlserver://; user=userid;password=userpassword

However, in order for this to work, the user must the sqljdbc_auth.dll available on the PATH environment variable. Also the version of the library must match the version of the sqljdbc4.jar that they are using.
STRING      
Password The database user's password on whose behalf the connection is being made PASSWORD      
Catalog This option can be used as a constraint to import a particular database Catalog by specifying a catalog name.
Note, that this options will be ignored for JDBC/ODBC sources, which doesn't supports the database catalogs.
The possible values are:
- empty string: The first catalog found with the connection parameters specified will be imported.
- a name: only the tables that belong to this catalog will be imported.

Example:
catalog1 - import tables from the 'catalog1'.
STRING      
Schema This option can be used as a constraint to import a particular database Schema by specifying a list of schemas separated by semicolon.

The possible values are:
- empty string: all accessible schemas including system schemas will be imported.
- a name or names: only the tables that belong to this schema(s) will be imported.

Example:
schema1;schema2 - import tables from 'schema1' and 'schema2' from selected catalog/database
STRING      
Tables, Views Description: you can specify a list of database tables to import.
When the list is empty all available tables are imported.
The list can have one or more table names separated by semicolons (e.g. table1; table2).
You can specify table name patterns using SQL LIKE expressions syntax.
Patterns support inclusions and exclusions.
Here is an example of inclusion syntax, "A%; %B; %C%; D" that tries to get table names that: - start with A or
- end with B or
- contain C or
- equal D

Note: when a pattern has special characters, like spaces enclose it in single quote marks (e.g. "'two wo%'; onewo%;").

To exclude a pattern prefix it with NOT. Here is an example of exclusion syntax, "A%; %B; NOT %SYS; NOT 'SYS%'" that
contributes to the following SQL filter: "where (name like A% or name like %B) and (name not like %SYS) and (name not like 'SYS%')"
STRING      
Case sensitivity Specify the Database Case sensitivity:

When the parameter is set to Auto the bridge tried to use the JDBC API to find out if the database is set into the case sensitive mode. If API is not implemented by the driver the bridge assumes the database is Case Insensitive.
ENUMERATED
Auto
Case Sensitive
Case Insensitive
Auto  
View definition extracting SQL Database specific SQL query to retrieve view definition text. If it is left empty, no relationships between views and their source tables will be built. This query expects the first parameter to be the view name and, optionally, the second parameter to be the schema name. Actual query text varies depending on the source database.

- For IBM DB2 it is:
SELECT CREATOR, NAME, TEXT FROM SYSIBM.SYSVIEWS WHERE CREATOR IN (?)

- For IBM DB2 AS400 it is:
SELECT TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION FROM QSYS2.SYSVIEWS WHERE TABLE_SCHEMA IN (?)

- For Microsoft SQL Server such query would be:
SELECT s.name, o.name, t.definition FROM sys.sql_modules t INNER JOIN sys.objects o ON (t.object_id=o.object_id) INNER JOIN sys.schemas s ON (o.schema_id=s.schema_id) WHERE o.type IN ('V') AND s.name IN (?)

- For Netezza it is:
SELECT CURRENT_SCHEMA, VIEWNAME, DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW'

- For Oracle it is:
SELECT OWNER, VIEW_NAME, TEXT FROM ALL_VIEWS WHERE OWNER IN (?)

- For Teradata it is:
SELECT d.DatabaseName, m.TVMName, CASE WHEN m.CreateText IS NULL THEN m.RequestText ELSE m.CreateText END FROM DBC.TVM m INNER JOIN dbc.dbase d ON(m.DatabaseId=d.DatabaseId) WHERE m.TableKind IN('V') AND d.DatabaseName IN (?)

- For PostgreSQL it is:
SELECT schemaname, viewname, definition FROM pg_views WHERE schemaname IN (?)

- For MySQL it is:
SELECT '',TABLE_NAME,VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS

- For SAP Hana it is:
SELECT SCHEMA_NAME, VIEW_NAME, DEFINITION FROM VIEWS
STRING      
Synonyms lineage SQL Database specific SQL query to retrieve synonym. This query must returns two columns: full synonym name and full table name.
The Full Name must contains schema name and table name
Actual query text varies depending on the source database.

Some examples:

- For IBM DB2 it is:
Not supported.

- For Microsoft SQL Server:
SELECT s.name, a.name, null, a.base_object_name FROM sys.schemas AS s, sys.synonyms AS a WHERE s.schema_id = a.schema_id

Result is a result set with two columns in format:
Schema.Synonym; Catalog.Schema.Table ;

- For Oracle:
SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME FROM SYS.DBA_SYNONYMS [WHERE OWNER='SCOTT']
Please, note the query text included in '[' and ']' is optional.

Result is a result set with two columns in format:
Schema.Synonym; Schema.Table ;

- For Teradata it is:
Not supported.

- For MySQL it is:
Not supported.

- For SAP Hana it is:
SELECT SCHEMA_NAME, SYNONYM_NAME, OBJECT_SCHEMA, OBJECT_NAME FROM SYNONYMS
STRING      
Import indexes Specifies if the Indexes should be imported or not.

- 'False' : Indexes are not imported(default)
- 'True' : Indexes are imported
BOOLEAN
False
True
False  
Stored procedure details Specify what stored procedure details you would like to include

- 'Signature' - name and parameters of stored procedures (default)
- 'Code, signature' - above plus code
- 'Lineage, code, signature' - above plus data lineage derived from the code
- 'None' - stored procedures are not included
ENUMERATED
None
Signature
Code, signature
Lineage, code, signature
Signature  
Procedures text SQL Database specific SQL query to retrieve stored procedures text. This query must returns two columns: full synonym name and full table name.
The Full Name must contains schema name and table name
Actual query text varies depending on the source database.

Some examples:

- For IBM DB2 it is:
SELECT procschema, procname, text FROM syscat.procedures WHERE procschema IN (?) UNION ALL SELECT FUNCSCHEMA, FUNCNAME, BODY FROM syscat.functions WHERE FUNCSCHEMA IN (?)
Result is a result set with three columns in format:
Schema; Procedure; Code;

- For Microsoft SQL Server:
SELECT s.name, o.name, t.definition FROM sys.sql_modules t INNER JOIN sys.objects o ON (t.object_id=o.object_id) INNER JOIN sys.schemas s ON (o.schema_id=s.schema_id) WHERE o.type IN ('TF','P','FN','IF') AND s.name IN (?)

Result is a result set with three columns in format:
Schema; Procedure; Code;

- For Oracle:
SELECT s.owner, s.name, s.text FROM sys.dba_source s INNER JOIN sys.dba_procedures p ON (p.owner=s.owner AND p.object_name=s.name AND p.object_type=s.type) WHERE s.type IN ('PROCEDURE', 'FUNCTION') AND s.owner IN (?) ORDER BY s.name, s.line
Result is a result set with three columns in format:
Schema; Procedure; Code;

- For Teradata it is:
Not supported.

- For MySQL it is:
SELECT '', ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES

- For SAP Hana it is:
SELECT SCHEMA_NAME, PROCEDURE_NAME, DEFINITION FROM PROCEDURES
STRING      
Miscellaneous Specify miscellaneous options identified with a -letter and value.

For example, -m 4G -f 100 -j -Dname=value -Xms1G

-f the database driver fetch size in number of rows (e.g. -f 100)

-zip exportFile to store DDL create tables statements (e.g -zip c:/temp/ddl/export.zip)

-m the maximum Java memory size whole number (e.g. -m 4G or -m 2500M ).
-s import system objects that are skipped by default (e.g. -s)
-v set environment variable(s) (e.g. -v var1=value -v var2="value with spaces").
-j the last option that is followed by Java command line options (e.g. -j -Dname=value -Xms1G).

-diff difference detection level (e.g. -diff ikm for disabling checking indexes, keys, classifier and feature maps)

-synonyms disable import of all synonyms
STRING      

 

Bridge Mapping

Meta Integration Repository (MIR)
Metamodel
(based on the OMG CWM standard)
"Database (via JDBC)"
Metamodel
Jdbc
Mapping Comments
     
Name Name  
Attribute Table Column  
Comment Comments on the column If JDBC driver supports
InitialValue Default value  
Name Name  
Optional Based on the nullable property For Access databases, set to false only if the attribute is in a Unique Index
PhysicalName Name  
Position Position If position is not provided, the order in which the attributes are retrieved is used.
BaseType Types  
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Maximum scale  
Class Table of type "TABLE"
Comment Comments on the table If JDBC driver supports
CppClassType   Set to ENTITY
CppPersistent   Set to True
Name Name  
PhysicalName Name  
ClassDiagram Schema A class diagram is created for each package and contains all the elements of the package
DerivedType Column Table column, stored procedure column SQL View column or type
DataType Data Type See datatype conversion array
Length Size  
Name   The name is computed from the datatype
PhysicalName Name  
Scale Decimal digits  
UserDefined   True for Type
DesignPackage Schema A Package is created for each retrieved schema. If there is no schema a default package is created.
Name Name Set to "Schema" if there is no schema or the schema has no name.
SQLViewAttribute View Column  
Comment Comments on the column If JDBC driver supports
Name Name  
PhysicalName Name  
Position Ordinal position  
SQLViewEntity Table of type "VIEW"
Comment Comments on the table If JDBC driver supports
Name Name  
PhysicalName Name  
StoreModel Catalog The model is built using the elements contained in the catalog (e.g. the database for MS SQL server)
Name Name Set to "Catalog" if the catalog has no name.
Synonym Table Synonym If JDBC driver supports
Name Name