Extract data from an Excel source file using the DataStage Java Integration stage and Java pack

Brief introduction

The IBM infosphere Information Server consists of a set of data integration products that can help businesses gain business value from information that spans multiple data source systems. It helps to analyze, clean, and integrate information from multiple heterogeneous data sources in a cost-effective manner. With IBM infosphere information Server, businesses can increase their operational efficiencies with lower costs and risks.

IBM Information Server DataStage is an ETL product that enables organizations to extract, transform, and load data from heterogeneous data sources. DataStage can use its high-performance parallel framework to solve complex business problems by integrating a variety of data sources.

DataStage Java Integration Stage and DataStage Javapack plug-ins

The Java Integration Stage is a DataStage Connector that you can use to invoke a custom Java application from the Infosphere Data Stage and Quality parallel tasks. Java integration Stage is available in IBM infosphere information Server 9.1 or later. Java integration Stage can be used in the following topologies: As a source, as a target, as a translator, as a lookup phase.

The DataStage Java Pack is a collection of two plug-in phases (Java Transformer and Java Client) that can be used to invoke Java applications in DataStage. The Java Pack is available in DataStage 7.5.x or later.

The Java Transformer phase is an active phase that invokes a Java application that reads and converts input data and then writes it to the output link defined in the DataStage job. The Java Client phase is a passive phase that can be used as a source, a target, and a lookup phase. When used as a target phase, the Java Client Stage will use the data. As a lookup phase, the Java Client Stage will perform the lookup function.

You can use the Java integration Stage (unstructured data Stage) or any Java Pack stages to extract Excel data. The specifics depend on the version of the information Server that you deploy. If you are using information Server 9.1, you can use unstructured data Stage or Java integration Stage to extract the Excel data. If you are using a version prior to 9.1 (for example, 8.7.x, 8.5.x, 8.1.x, 8.0.1, and 7.5.x), you must use the Java Pack stages (Java Transformer Stage or Java Client Stage) from Extract the data from the Excel file. This article describes how to use the Java integration Stage and Java Pack Client Stage to extract Excel data.

DataStage Java Integration Stage and Java Client Stage GUI properties

Java Integration Stage GUI

Java integration Stage and Java Client Stage are available in the real time phase of DataStage Designer Palette. To use the Java integration Stage and Java Pack stages, you need to start DataStage Designer, create a new job, extend the real time Palette category in stages, and then drag and drop the phase.

Figure 1. Java Integration Stage and javapack stages in Palette

The Java integration phase supports different properties based on different usage environments (source, target, and lookup). You must configure the stage properties to define Java Virtual machine configuration and Java classes that are invoked from the Java integration phase. The input link and output link properties must be configured according to the job design.

Figure 2. Stage properties