Wednesday 30 July 2014

How to read and write data from JDBC Connector using InfoSphere DataStage?


How to read and write data from JDBC Connector using InfoSphere DataStage?

JDBC Connector is a generic connector which comes under Connectivity component category in IBM InfoSphere Information Server. It is used by IBM InfoSphere DataStage to perform extract, load , lookup and metadata operations on Databases which provide Java Database Connectivity. This blog provides an overview of how to read and write data from database using JDBC Connector through DataStage.

To connect to database through JDBC Connector, it must have the proper driver to be loaded to connect to a particular database in order to extract, load or lookup the data. For example if we want to connect to Derby database through JDBC CC, we need to have the proper jdbc driver to be loaded. That driver class is part of a jar file. The information regarding which drivers need to be picked for the corresponding database can be found in the database JDBC documentation, for example, the derby jdbc driver can be found in the below link:
https://builds.apache.org/job/Derby-docs/lastSuccessfulBuild/artifact/trunk/out/tools/rtoolsijtools16879.html

Java Database Connectivity is very useful in many ways, for example, as it is based on java, the same driver class can be used across all platforms. This is because of Java's platform independent feature. The same is applicable to JDBC CC as well because it follows the same technology to connect to any database which provides jdbc connectivity.

Advantages of using JDBC CC:

JDBC CC uses java connectivity to connect to any data source. It only needs driver class name and the jar file which has this class file in it. The driver class file and the jar file will be same across all the platforms as it is in java, so no platform specific issues.
Another advantage is the ability to connect to data sources for which we don't have native connector and for which there is no ODBC connectivity, such as Apache Derby.  
Also, it is easy to configure.

JDBC CC Configuration:

JDBC CC comes with Information Server. JDBC Connector is easy to configure as it only needs the driver jar file to be placed on the system where we have Information Server and make the file isjdbc.config point to it, no platform specific issues as its all Java. To configure the JDBC CC the prerequisites are:

1) Install Information Server: JDBC CC is introduced on IS912.
2) Configure isjdbc.config file under Information Server Home path: This configuration file contains the name of the driver class and the jar file which has that driver class file in it. For more info please refer:
http://pic.dhe.ibm.com/infocenter/iisinfsv/v9r1/index.jsp?topic=%2Fcom.ibm.swg.im.iis.conn.jdbc.usage.doc%2Ftopics%2Fjdbc_config_driver.html



Usually, the java based data sources only have java connectivity option (JDBC), like Derby. In that case we can use JDBC CC to connect to that database to extract and load the data.

JDBC Connector as a Source:

The following figure shows a sample job which can extract the data from Derby database and put it into a Sequential File.





From the Job Canvas, Double click on JDBC Connector Stage and go to the Output tab.



For Connection:

JDBC CC needs an URL to connect to any data source. The URL syntax varies as per the data source from which we need to extract data. If we take an example of Derby database the syntax for URL looks like :

jdbc:<datasource_name>://<hostname>:<port>/<database>
jdbc:derby://locahost:1527/conn_db
localhost denotes we have derby database setup locally.
1527 is the port number where derby is running.
conn_db denotes the database name which has been created and is being used to fetch or load data.

Provide the Username and Password info and the Table Name from which you want to fetch the data.

For Column Definition:

Go to the Columns tab under Outputs tab and specify the columns of the table which you want to extract from Database.




JDBC Connector as a target:

The following figure shows a sample job which can load the data from a sequential file into Derby database.




From the Job Canvas, Double click on JDBC Connector Stage and go to the Input tab.

For Connection:

Please follow the same steps above which were performed when JDBC CC used as source.

For Column Definition:

Go to the Columns tab under Inputs tab and specify the columns of the table in which you want to load data.




Conclusion JDBC CC is one of the easy-to-learn connectors with advantages like platform independancy, one single driver can be used for all the platforms, easy to configure, provides access to databases where native connectors are not provided and comes with very specific jdbc data sources.




Disclaimer: “The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.”

No comments:

Post a Comment