Table Of Content®®
SSCottuuursdde eeGnnuidtte NNootteebbooookk
IBM Infosphere DataStage Essentials v11.5
PPrroocceessss IImmpplleemmeennttiinngg wwiitthh IIBBMM BBuussiinneessss
PPrroocceessss MMaannaaggeerr SSttaannddaarrdd VV88..55..55 -- IIII
CCoouurrssee ccooddee WWBB881155 // ZZBB881155 EERRCC 11..00
Course code KM204 ERC 1.0
IIBBMM TTrraaiinniinngg
Preface
November, 2015
NOTICES
This information was developed for products and services offered in the USA.
IBM may not offer the products, services, or features discussed in this document in other countries. Consult your local IBM representative for
information on the products and services currently available in your area. Any reference to an IBM product, program, or service is not intended to
state or imply that only that IBM product, program, or service may be used. Any functionally equivalent product, program, or service that does not
infringe any IBM intellectual property right may be used instead. However, it is the user's responsibility to evaluate and verify the operation of any
non-IBM product, program, or service. IBM may have patents or pending patent applications covering subject matter described in this document.
The furnishing of this document does not grant you any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive, MD-NC119
Armonk, NY 10504-1785
United States of America
The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT,
MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in
certain transactions, therefore, this statement may not apply to you.
This information could include technical inaccuracies or typographical errors. Changes are periodically made to the information herein; these
changes will be incorporated in new editions of the publication. IBM may make improvements and/or changes in the product(s) and/or the
program(s) described in this publication at any time without notice.
Any references in this information to non-IBM websites are provided for convenience only and do not in any manner serve as an endorsement of
those websites. The materials at those websites are not part of the materials for this IBM product and use of those websites is at your own risk.
IBM may use or distribute any of the information you supply in any way it believes appropriate without incurring any obligation to you. Information
concerning non-IBM products was obtained from the suppliers of those products, their published announcements or other publicly available
sources. IBM has not tested those products and cannot confirm the accuracy of performance, compatibility or any other claims related to non-IBM
products. Questions on the capabilities of non-IBM products should be addressed to the suppliers of those products.
This information contains examples of data and reports used in daily business operations. To illustrate them as completely as possible, the
examples include the names of individuals, companies, brands, and products. All of these names are fictitious and any similarity to the names and
addresses used by an actual business enterprise is entirely coincidental.
TRADEMARKS
IBM, the IBM logo, and ibm.com, InfoSphere and DataStage are trademarks or registered trademarks of International Business Machines Corp.,
registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM
trademarks is available on the web at “Copyright and trademark information” at www.ibm.com/legal/copytrade.shtml.
Adobe, and the Adobe logo, are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, and/or other
countries.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Microsoft, Windows, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
© Copyright International Business Machines Corporation 2015.
This document may not be reproduced in whole or in part without the prior written permission of IBM.
US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
© Copyright IBM Corp. 2005, 2015 P-2
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Contents
Preface ................................................................................................................. P-1
Contents ............................................................................................................. P-3
Course overview ............................................................................................... P-14
Document conventions ..................................................................................... P-15
Additional training resources ............................................................................ P-16
IBM product help .............................................................................................. P-17
Introduction to DataStage .................................................................... 1-1
Unit objectives .................................................................................................... 1-3
What is IBM InfoSphere DataStage? .................................................................. 1-4
What is Information Server ................................................................................. 1-5
Information Server backbone.............................................................................. 1-6
Information Server Web Console ........................................................................ 1-7
DataStage architecture ....................................................................................... 1-8
DataStage Administrator .................................................................................... 1-9
DataStage Designer ......................................................................................... 1-10
DataStage Director ........................................................................................... 1-11
Developing in DataStage .................................................................................. 1-12
DataStage project repository ............................................................................ 1-13
Types of DataStage jobs .................................................................................. 1-14
Design elements of parallel jobs ....................................................................... 1-15
Pipeline parallelism .......................................................................................... 1-16
Partition parallelism .......................................................................................... 1-17
Three-node partitioning .................................................................................... 1-18
Job design versus execution ............................................................................ 1-19
Configuration file .............................................................................................. 1-20
Example: Configuration file ............................................................................... 1-21
Checkpoint ....................................................................................................... 1-22
Checkpoint solutions ........................................................................................ 1-23
Unit summary ................................................................................................... 1-24
Deployment ........................................................................................... 2-1
Unit objectives .................................................................................................... 2-3
What gets deployed ............................................................................................ 2-4
Deployment: Everything on one machine ........................................................... 2-5
Deployment: DataStage on a separate machine ................................................ 2-6
Metadata Server and DB2 on separate machines .............................................. 2-7
Information Server start-up ................................................................................. 2-8
Starting Information Server on Windows ............................................................ 2-9
Starting Information Server on Linux ................................................................ 2-10
© Copyright IBM Corp. 2005, 2015 P-3
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Verifying that Information Server is running ...................................................... 2-11
Web Console Login window ............................................................................. 2-12
Checkpoint ....................................................................................................... 2-13
Checkpoint solutions ........................................................................................ 2-14
Demonstration 1: Log into the Information Server Administration Console ....... 2-15
Unit summary ................................................................................................... 2-18
DataStage Administration .................................................................... 3-1
Unit objectives .................................................................................................... 3-3
Information Server Web Console - Administration .............................................. 3-4
Web Console Login window ............................................................................... 3-5
User and group management ............................................................................. 3-6
Create a DataStage User ID ............................................................................... 3-7
Assign DataStage roles ...................................................................................... 3-8
DataStage credentials ........................................................................................ 3-9
DataStage Credentials Default Mapping ........................................................... 3-10
Logging onto DataStage Administrator ............................................................. 3-11
DataStage Administrator Projects tab ............................................................... 3-12
DataStage Administrator General tab ............................................................... 3-13
Environment variables ...................................................................................... 3-14
Environment reporting variables ....................................................................... 3-15
DataStage Administrator Permissions tab ........................................................ 3-16
Adding users and groups .................................................................................. 3-17
Specify DataStage role ..................................................................................... 3-18
DataStage Administrator Logs tab .................................................................... 3-19
DataStage Administrator Parallel tab ................................................................ 3-20
Checkpoint ....................................................................................................... 3-21
Checkpoint solutions ........................................................................................ 3-22
Demonstration 1: Administering DataStage ...................................................... 3-23
Unit summary ................................................................................................... 3-34
Work with metadata .............................................................................. 4-1
Unit objectives .................................................................................................... 4-3
Login to Designer ............................................................................................... 4-4
Designer work area ............................................................................................ 4-5
Repository window ............................................................................................. 4-6
Import and export ............................................................................................... 4-7
Export procedure ................................................................................................ 4-8
Export window .................................................................................................... 4-9
Import procedure .............................................................................................. 4-10
Import options................................................................................................... 4-11
Source and target metadata ............................................................................. 4-12
© Copyright IBM Corp. 2005, 2015 P-4
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Sequential file import procedure ....................................................................... 4-13
Import sequential metadata .............................................................................. 4-14
Sequential import window ................................................................................. 4-15
Specify format .................................................................................................. 4-16
Edit column names and types ........................................................................... 4-17
Extended properties window ............................................................................ 4-18
Table definition in the repository ....................................................................... 4-19
Checkpoint ....................................................................................................... 4-20
Checkpoint solutions ........................................................................................ 4-21
Demonstration 1: Import and export DataStage objects ................................... 4-22
Demonstration 2: Import a table definition ........................................................ 4-27
Unit summary ................................................................................................... 4-33
Create parallel jobs ............................................................................... 5-1
Unit objectives .................................................................................................... 5-3
What is a parallel job? ........................................................................................ 5-4
Job development overview ................................................................................. 5-5
Tools Palette ...................................................................................................... 5-6
Add stages and links .......................................................................................... 5-7
Job creation example sequence ......................................................................... 5-8
Create a new parallel job .................................................................................... 5-9
Drag stages and links from the Palette ............................................................. 5-10
Rename links and stages ................................................................................. 5-11
Row Generator stage ....................................................................................... 5-12
Inside the Row Generator stage ....................................................................... 5-13
Row Generator Columns tab ............................................................................ 5-14
Extended properties ......................................................................................... 5-15
Peek stage ....................................................................................................... 5-16
Peek stage properties....................................................................................... 5-17
Job parameters ................................................................................................ 5-18
Define a job parameter ..................................................................................... 5-19
Use a job parameter in a stage ......................................................................... 5-20
Add job documentation ..................................................................................... 5-21
Job Properties window documentation ............................................................. 5-22
Annotation stage properties .............................................................................. 5-23
Compile and run a job ...................................................................................... 5-24
Errors or successful message .......................................................................... 5-25
DataStage Director ........................................................................................... 5-26
Run options ...................................................................................................... 5-27
Performance statistics ...................................................................................... 5-28
Director Status view ......................................................................................... 5-29
Job log, viewed from Designer ......................................................................... 5-30
© Copyright IBM Corp. 2005, 2015 P-5
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Message details ............................................................................................... 5-31
Other job log functions ...................................................................................... 5-32
Director monitor ................................................................................................ 5-33
Run jobs from the command line ...................................................................... 5-34
Parameter sets ................................................................................................. 5-35
Create a parameter set ..................................................................................... 5-36
Defining the parameters ................................................................................... 5-37
Defining values files ......................................................................................... 5-38
Load a parameter set into a job ........................................................................ 5-39
Use parameter set parameters ......................................................................... 5-40
Run jobs with parameter set parameters .......................................................... 5-41
Checkpoint ....................................................................................................... 5-42
Checkpoint solutions ........................................................................................ 5-43
Demonstration 1: Create parallel jobs ............................................................... 5-44
Unit summary ................................................................................................... 5-56
Access sequential data ........................................................................ 6-1
Unit objectives .................................................................................................... 6-3
How sequential data is handled .......................................................................... 6-4
Features of the Sequential File stage ................................................................. 6-5
Sequential file format example ........................................................................... 6-6
Job design with Sequential File stages ............................................................... 6-7
Sequential File stage properties ......................................................................... 6-8
Format tab .......................................................................................................... 6-9
Columns tab ..................................................................................................... 6-10
Reading sequential files using a file pattern...................................................... 6-11
Multiple readers ................................................................................................ 6-12
Writing to a sequential file ................................................................................ 6-13
Reject links ....................................................................................................... 6-14
Source and target reject links ........................................................................... 6-15
Setting the Reject Mode property ..................................................................... 6-16
Copy stage ....................................................................................................... 6-17
Copy stage example ......................................................................................... 6-18
Copy stage Mappings ....................................................................................... 6-19
Demonstration 1: Reading and writing to sequential files ................................. 6-20
Working with nulls ............................................................................................ 6-32
Specifying a value for null ................................................................................. 6-33
Empty string example ....................................................................................... 6-34
Viewing data with nulls ..................................................................................... 6-35
Demonstration 2: Reading and writing null values ............................................ 6-36
Data Set stage.................................................................................................. 6-43
Job with a target Data Set stage ....................................................................... 6-44
© Copyright IBM Corp. 2005, 2015 P-6
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Data Set Management utility............................................................................. 6-45
Data and schema displayed ............................................................................. 6-46
File set stage .................................................................................................... 6-47
Demonstration 3: Working with data sets ......................................................... 6-48
Checkpoint ....................................................................................................... 6-53
Checkpoint solutions ........................................................................................ 6-54
Unit summary ................................................................................................... 6-55
Partitioning and collecting algorithms ................................................ 7-1
Unit objectives .................................................................................................... 7-3
Partition parallelism ............................................................................................ 7-4
Stage partitioning ............................................................................................... 7-5
DataStage hardware environments .................................................................... 7-6
Partitioning algorithms ........................................................................................ 7-7
Collecting ........................................................................................................... 7-8
Collecting algorithms ........................................................................................ 7-10
Keyless versus keyed partitioning algorithms ................................................... 7-11
Round Robin and Random partitioning ............................................................. 7-12
Entire partitioning ............................................................................................. 7-13
Hash partitioning .............................................................................................. 7-14
Modulus partitioning ......................................................................................... 7-15
Auto partitioning ............................................................................................... 7-16
Partitioning requirements for related records .................................................... 7-17
Partition imbalances example ........................................................................... 7-18
Partitioning / Collecting link icons ..................................................................... 7-19
More partitioning icons ..................................................................................... 7-20
Specify a partitioning algorithm ......................................................................... 7-21
Specify a collecting algorithm ........................................................................... 7-22
Configuration file .............................................................................................. 7-23
Example configuration file ................................................................................ 7-24
Adding $APT_CONFIG_FILE as a job parameter ............................................ 7-25
Editing configuration files .................................................................................. 7-26
Parallel job compilation ..................................................................................... 7-27
Generated OSH ................................................................................................ 7-28
Stage-to-operator mapping examples ............................................................... 7-29
Job Score ......................................................................................................... 7-30
Viewing the Score ............................................................................................ 7-31
Checkpoint ....................................................................................................... 7-32
Checkpoint solutions ........................................................................................ 7-33
Demonstration 1: Partitioning and collecting ..................................................... 7-34
Unit summary ................................................................................................... 7-43
© Copyright IBM Corp. 2005, 2015 P-7
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Combine data ........................................................................................ 8-1
Unit objectives .................................................................................................... 8-3
Combine data ..................................................................................................... 8-4
Lookup, Join, Merge stages ............................................................................... 8-5
Lookup Stage features ....................................................................................... 8-6
Lookup types ...................................................................................................... 8-7
Equality match Lookup stage example ............................................................... 8-8
Lookup stage with an equality match .................................................................. 8-9
Define the lookup key ....................................................................................... 8-10
Specify the output columns .............................................................................. 8-11
Lookup failure actions....................................................................................... 8-12
Specifying lookup failure actions ...................................................................... 8-13
Lookup stage with reject link ............................................................................ 8-14
Lookup stage behavior ..................................................................................... 8-15
Lookup stage output ......................................................................................... 8-16
Demonstration 1: Using the Lookup stage ........................................................ 8-17
Range Lookup stage job ................................................................................... 8-26
Range on reference link ................................................................................... 8-27
Selecting the stream column ............................................................................ 8-28
Range expression editor ................................................................................... 8-29
Range on stream link........................................................................................ 8-30
Specifying the range lookup ............................................................................. 8-31
Range expression editor ................................................................................... 8-32
Demonstration 2: Range lookups ..................................................................... 8-33
Join stage ......................................................................................................... 8-39
Job with Join stage ........................................................................................... 8-40
Join stage properties ........................................................................................ 8-41
Output Mapping tab .......................................................................................... 8-42
Join stage behavior .......................................................................................... 8-43
Inner join output ................................................................................................ 8-44
Left outer join output ......................................................................................... 8-45
Right outer join output ...................................................................................... 8-46
Full outer join .................................................................................................... 8-47
Merge stage ..................................................................................................... 8-48
Merge stage job ................................................................................................ 8-49
Merge stage properties ..................................................................................... 8-50
Comparison Chart ............................................................................................ 8-51
What is a Funnel stage? ................................................................................... 8-52
Funnel stage example ...................................................................................... 8-53
Funnel stage properties .................................................................................... 8-54
© Copyright IBM Corp. 2005, 2015 P-8
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Checkpoint ....................................................................................................... 8-55
Checkpoint solutions ........................................................................................ 8-56
Demonstration 3: Using Join, Merge, and Funnel stages ................................. 8-57
Unit summary ................................................................................................... 8-65
Group processing stages ..................................................................... 9-1
Unit objectives .................................................................................................... 9-3
Group processing stages .................................................................................... 9-4
Sort data ............................................................................................................. 9-5
Sorting alternatives ............................................................................................. 9-6
In-Stage sorting .................................................................................................. 9-7
Stable sort illustration ......................................................................................... 9-8
Sort stage Properties tab .................................................................................... 9-9
Specify sort keys .............................................................................................. 9-10
Sort stage options ............................................................................................ 9-11
Create key change column ............................................................................... 9-12
Partition sorts ................................................................................................... 9-13
Aggregator stage .............................................................................................. 9-14
Job with Aggregator stage ................................................................................ 9-15
Aggregation types ............................................................................................ 9-16
Count Rows aggregation type .......................................................................... 9-17
Output Mapping tab .......................................................................................... 9-18
Output Columns tab ......................................................................................... 9-19
Calculation aggregation type ............................................................................ 9-20
Grouping methods ............................................................................................ 9-21
Method = Hash ................................................................................................. 9-22
Method = Sort ................................................................................................... 9-23
Remove duplicates ........................................................................................... 9-24
Remove Duplicates stage job ........................................................................... 9-25
Remove Duplicates stage properties ................................................................ 9-26
Checkpoint ....................................................................................................... 9-27
Checkpoint solutions ........................................................................................ 9-28
Demonstration 1: Group processing stages ...................................................... 9-29
Fork-Join Job Design ....................................................................................... 9-39
Unit Summary................................................................................................... 9-40
Transformer stage ............................................................................ 10-1
Unit objectives .................................................................................................. 10-3
Transformer stage ............................................................................................ 10-4
Job with a Transformer stage ........................................................................... 10-5
Inside the Transformer stage ............................................................................ 10-6
Transformer stage elements ............................................................................. 10-7
© Copyright IBM Corp. 2005, 2015 P-9
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
Preface
Constraints ....................................................................................................... 10-9
Constraints example ....................................................................................... 10-10
Define a constraint ......................................................................................... 10-11
Use the expression editor ............................................................................... 10-12
Otherwise links for data integrity ..................................................................... 10-13
Otherwise link example .................................................................................. 10-14
Specify the link ordering ................................................................................. 10-15
Specify the Otherwise link constraint .............................................................. 10-16
Demonstration 1: Define a constraint.............................................................. 10-17
Derivations ..................................................................................................... 10-24
Derivation targets ........................................................................................... 10-25
Stage variables ............................................................................................... 10-26
Stage variable definitions ............................................................................... 10-27
Build a derivation ............................................................................................ 10-28
Define a derivation ......................................................................................... 10-29
IF THEN ELSE derivation ............................................................................... 10-30
String functions and operators ........................................................................ 10-31
Null handling .................................................................................................. 10-32
Unhandled nulls .............................................................................................. 10-33
Legacy null processing ................................................................................... 10-34
Transformer stage reject link .......................................................................... 10-35
Demonstration 2: Define derivations ............................................................... 10-36
Loop processing ............................................................................................. 10-44
Functions used in loop processing ................................................................. 10-45
Loop processing example ............................................................................... 10-46
Loop processing example job ......................................................................... 10-47
Inside the Transformer stage .......................................................................... 10-48
Demonstration 3: Loop processing ................................................................. 10-49
Group processing ........................................................................................... 10-55
Group processing example ............................................................................. 10-56
Job results ...................................................................................................... 10-57
Transformer logic ........................................................................................... 10-58
Loop through saved input rows ....................................................................... 10-59
Example job results ........................................................................................ 10-60
Transformer logic ........................................................................................... 10-61
Parallel job debugger ..................................................................................... 10-62
Set breakpoints .............................................................................................. 10-63
Edit breakpoints .............................................................................................. 10-64
Running a parallel job in the debugger ........................................................... 10-65
Add columns to the watch list ......................................................................... 10-66
© Copyright IBM Corp. 2005, 2015 P-10
Course materials may not be reproduced in whole or in part without the prior written permission of IBM.
