IBM InfoSphere Foundation Tools, IBM InfoSphere Information Server, Version 8.5 IBM InfoSphere Foundation Tools, IBM InfoSphere Information Server, Version 8.5The IBM® InfoSphere® DataStage® and QualityStage® Designer helps you create, manage, and design jobs. You can also use the Designer client to define tables and access metadata services.
You can import, create, and edit table definitions from many sources (for example, one source of table definitions is metadata from IBM InfoSphere Information Analyzer). When you edit or view a table, the Table Definitions window opens, as Figure 1 shows.
This window has the following pages:
InfoSphere DataStage and QualityStage Designer accesses the metadata repository to obtain live access to current metadata about integration projects and your organization's enterprise data. You access data that is generated by metadata bridges or InfoSphere Information Analyzer by using the Designer client. The following services provide designers with access to metadata:
An option in the InfoSphere DataStage and QualityStage Designer shows differences between jobs or table definitions in an InfoSphere DataStage context. Figure 2 shows a textual report with links to the relevant editor in the Designer client. You can also view differences for subsets of jobs such as shared containers and routines. This report can optionally be saved as an XML file.
When you use the Designer client, you choose the type of job to create and how to create it, as Figure 3 shows.
Different job types include parallel, mainframe, and job sequences. Job templates help you build jobs quickly by providing predefined job properties that you can customize. Job templates also provide a basis for commonality between jobs and job designers.
You use the design canvas window and tool palette to design, edit, and save the job, as shown in Figure 4.
Figure 4 shows the most basic IBM InfoSphere DataStage job, which contains three stages:
IBM InfoSphere DataStage jobs can be as sophisticated as required by your company's data integration needs. Figure 5 is an example of a more complex job.
With the Designer client, you draw the integration process and then add the details for each stage. This method helps you build and reuse components across jobs. The Designer client minimizes the coding that is required to define even the most difficult and complex integration process.
Each data source and each processing step is a stage in the job design. The stages are linked to show the flow of data. You drag stages from the tool palette to the canvas. This palette contains icons for stages and groups that you can customize to organize stages, as shown in Figure 6.
After stages are in place, they are linked together in the direction that the data will flow. For example, in Figure 4, two links were added:
You load table definitions for each link from a stage property editor, or select definitions from the repository and drag them onto a link.
Each stage in a job has properties that specify how the stage performs or processes data. Stage properties include file name for the Sequential File stage, columns to sort and the ascending-descending order for the Sort stage, database table name for a database stage, and so on. Each stage type uses a graphical editor.
The Complex Flat File (CFF) stage allows the reading and writing of data files that contain numerous record formats in a single file. Figure 7 shows a three-record join. This stage supports both fixed and variable-length records and joins data from different record types in a logical transaction into a single data record for processing. For example, you might join customer, order, and units data.
The CFF stage and Slowly Changing Dimension stage offer a Fast Path concept for improved usability and faster implementation. The Fast Path walks you through the screens and tables of the stage properties that are required for processing the stage. Help is available for each tab by hovering the mouse over the "i" in the lower left.
Transformer stages can have one primary input link, multiple reference input links, and multiple output links. The link from the main data input source is designated as the primary input link. You use reference links for lookup operations, for example, to provide information that might affect the way the data is changed, but not supplying the actual data to be changed.
Input columns are shown on the left and output columns are shown on the right. The upper panes show the columns with derivation details. The lower panes show the column metadata.
Some data might have to pass through the Transformer stage unaltered, but it is likely that data from some input columns must be transformed first. You can specify such an operation by entering an expression or selecting a transform to apply to the data, called a derivation. IBM InfoSphere DataStage has many built-in functions to use inside the derivations. You can also define custom transform functions that are then stored in the repository for reuse.
You can also specify constraints that operate on entire output links. A constraint is an expression that specifies criteria that data must meet before it can pass to the output link.
A typical design for an analytical system is based on a dimensional database that consists of a central fact table that is surrounded by a single layer of smaller dimension tables, each containing a single primary key. This design is also known as a star schema.
Star schema data is typically found in the transactional and operational systems that capture customer information, sales data, and other critical business information. One of the major differences between a transactional system and an analytical system is the need to accurately record the past. Analytical systems often must detect trends to enable managers to make strategic decisions. For example, a product definition in a sales tracking data mart is a dimension that will likely change for many products over time but this dimension typically changes slowly. One major transformation and movement challenge is how to enable systems to track changes that occur in these dimensions over time. In many situations, dimensions change only occasionally.
Figure 8 shows a typical primary key, the product sales keeping unit (PRODSKU).
The Slowly Changing Dimension (SCD) stage processes source data for a dimension table within the context of a star schema database structure. The stage lets you overwrite the existing dimension (known as a Type-1 change), update while preserving rows (known as Type 2), or have a hybrid of both types. To prepare data for loading, the SCD stage performs the following process for each changing dimension in the star schema:
In a Type-2 update, a new row with a new surrogate primary key is inserted into the dimension table to capture changes. All the rows that describe a dimension contain attributes that uniquely identify the most recent instance and historical dimensions. Figure 9 shows how the new product dimension is redefined to include the data that goes into the dimension table and also contains the surrogate key, expiry date, and the currency indicator.
Finally, the new record is written into the dimension table (with all surrogate keys), reflecting the change in product dimension over time. Although the product sales keeping unit has not changed, the database structure enables the user to identify sales of current versions versus earlier versions of the product.
Although IBM InfoSphere DataStage provides specific connectivity to virtually any database management system, the Dynamic Relational stage allows the binding of the type (for example, Oracle, IBM DB2®, or SQL Server) to be specified at run time rather than design time. The Dynamic Relational stage reads data from or writes data to a database. Figure 10 shows the general information about the database stage including the database type, name, user ID, and password that is used to connect. Passwords can be encrypted.
For developers who need to use SQL expressions to define database sources, the SQL builder utility provides a graphical interface for building simple-to-complex SQL query statements. The SQL builder supports DB2, Oracle, SQL Server, Teradata, and ODBC databases. Although ODBC can be used to build SQL that will work for a broad range of databases, the database-specific parsers help you take advantage of database-specific functionality. Figure 11 shows how the SQL builder guides developers in creating well-formed SQL queries.
IBM InfoSphere DataStage provides a graphical job sequencer in which you can specify a sequence of jobs to run. The sequence can also contain control information. For example, the sequence might indicate different actions depending on whether a job in the sequence succeeds or fails. After you define a job sequence, you can schedule and run the sequence by using the Director client, the command line, or an API. The sequence appears in the repository and in the Director client as a job.
Designing a job sequence is similar to designing jobs. You create the job sequence in the InfoSphere DataStage and QualityStage Designer, and add activities (rather than stages) from the tool palette. You then join activities with triggers (rather than links) to define control flow. Each activity has properties that can be tested in trigger expressions and passed to other activities farther down the sequence. Activities can also have parameters, which supply job parameters and routine arguments.
The job sequence has properties and can have parameters that can be passed to the activities that it is sequencing. The sample job sequence in Figure 12 shows a typical sequence that is triggered by an arriving file. The job also contains exception handling with looping and flow control.
The job sequence supports the following types of activities:
The Designer client manages the project data, enabling you to view and edit items that are stored in the metadata repository. This functionality enables you to import and export items between different IBM InfoSphere DataStage systems and exchange metadata with other tools. You can request reports on items in the metadata repository.
The Designer client provides the following capabilities:
Figure 13 shows the Designer client window for importing table definitions.
The InfoSphere DataStage and QualityStage Designer enables you to import and export components for moving jobs among IBM InfoSphere DataStage development, test, and production environments. You can import and export any component in the repository, including a job.
The export facility is also valuable for generating XML documents that describe objects in the repository. You can use a Web browser to view these documents. The Designer client also includes an import facility for importing InfoSphere DataStage components from XML documents.
This topic is also in the IBM InfoSphere Information Server Introduction.
