in which Extraction and transformation in two sections. Third section will have a Sample project on Retail industry Scenario.This book consists of about 660 pages.
It's a whopping RedBook weighing in at 660 pages and 19.7 MB as it's chock full of screenshots. Because not all readers want to download a 19.7 MB file or wade through a PDF to find out if they want it I have taken a deeper look at a couple sections and included the full table of contents.
DataStage Standards
There are a few pages of standards and guidelines that are handy for beginner programmers and cover overall setup and specific stage setup:
StandardsAn example of some stage specific guidelines:
Development guidelines
Component usage
DataStage Data Types
Partitioning data
Collecting data
Sorting
Stage specific guidelines
TransformerWhen you add to this all the sample jobs you have a great data warehouse example. Personally I'd like to see this entire DataStage standards and guidelines section lifted out and plonked in a wiki - perhaps over on LeverageInformation.
Take precautions when using expressions or derivations on nullable columns within the parallel Transformer:
– Always convert nullable columns to in-band values before using them in an expression or derivation.
– Always place a reject link on a parallel Transformer to capture / audit
possible rejects.
Join
Be particularly careful to observe the nullability properties for input links to any form of Outer Join. Even if the source data is not nullable, the non-key columns must be defined as nullable in the Join stage input in order to identify unmatched records.
Distributed Transaction Stage
A Distributed Transaction Stage accepts multiple input links in a DataStage job representing rows of data for various database actions and makes sure they are all applied as a single unit of work. This stage is coming in release 8.1 and dsRealTime blog author Ernie Ostic talks about it in his post (and about how to achieve this in a Server Job) in MQSeries…Ensuring Message Delivery from Queue to Target :Â
Using MQSeries in DataStage as a source or target is very easy…..but ensuring delivery from queue to queue is a bit more tricky. Even more difficult is trying to ensure delivery from queue to database without dropping any messages…
The best way to do this is with an XA transaction, using a formal transaction coordinator, such as MQSeries itself. This is typically done with the Distributed Transaction Stage, which works with MQ to perform transactions across resources….deleting a message from the source queue, INSERTing a row to the target, and then committing the entire operation. This requires the most recent release of DataStage, and the right environment, releases, and configuration of MQSeries and a database that it supports for doing such XA activity….
In the example in the Redbook a series of messages are read from MQ Series queue, they are transformed ETL style and then passed to the Distributed Transaction Stage (DTS) to be written to various database tables:
This job looks like Napoleons troop movements at Waterloo but shows how the job takes a complex message from MQ, flattens it out into customer, product and store rows, does a bit of fancy shmancy transformation using DataStage stages and sends insert, update and delete commands for all three types of data to a Distributed Transaction Stage. A Unit of Work is a bundle of up to nine database commands and the removal of the message they all came from, all with a single rollback on failure.
There are some handy functions on this design:
- You can read from the queue in read only mode so the messages stay on there or in destructive mode so handled messages are removed.
- You can choose to write the messages out in the order they were placed on the queue, handy for parallel processing.
- You can configure the job to finish after reading a certain number of transactions or after a defined period of time.
- Ability to treat different messages with a shared key field as a unit.
 You can click on a link in this little map to change to the properties for that link - so you can click on Product_Delete to see the properties for the delete command on the product table and then click on Store_Update to change to a different set of properties. I wonder how many DataStage 8.1 stages are going to have this feature? Could be handy. You can also see the new look and feel of the property window which is a lot more like a standard GUI property window now - kind of what you see in tools like Visual Basic.
Slowly Changing Dimension Stage
The RedBook also gives the new 8.0.1 Slowly Changing Dimension Stage a thorough going over in a lot more detail then any of the documentation or tutorials we have seen before. The retail scenario shows a very complex series of SCD updates out of a single complex flat file source:
I've done these type of dimension loads before and before you had the SCD stage this same functionality could have taken ten jobs with up to ten stages in each. The SCD stage performs the same functionality as four stages under the old version: a surrogate key generator, a surrogate key lookup, a change data capture and a transformer for setting dates and flags and values. The SCD stage does all this in one stage so it's a lot easier for inexperienced programmers and those new to SCD functionality.
The RedBook takes a look inside the properties screen, it looks a lot like a transformer, with some extra columns to define the purpose of the special SCD tagging fields:
You can define columns as being one of Surrogate Key, Business Key, Type 1, Type 2, Current Indicator, Effective Date, Expiration Date, SK Chain (link to previous record). You can have Type 1 and Type 2 fields in the same dimension with Type 2 taking precedence.
What's good about this RedBook is the retail scenario goes into the impact on slowly changing dimensions of day 0, 1, 2 and 3 data and changes showing how the SCD stage and special properties are impacted. This is a deep level of detail into the workings of this stage.
Table of Contents
The RedBook website has a top level table of contents so I've pasted the detailed table:
Chapter 1. IBM WebSphere DataStage overview . . . . . . . . . . . . . . . . . . . . . 1
1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.2 IBM Information Server architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.2.1 Component overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.2.2 Topologies supported . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.3 IBM WebSphere DataStage within the IBM Information Server architecture
15
1.3.1 Shared components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.3.2 Runtime architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
1.4 IBM WebSphere DataStage main functions . . . . . . . . . . . . . . . . . . . . . . . 20
1.4.1 Data transformation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
1.4.2 Jobs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
1.4.3 Parallel processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
1.5 Best practices overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
1.5.1 Standards . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
1.5.2 Development guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
1.5.3 Component usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
1.5.4 DataStage Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
1.5.5 Partitioning data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
1.5.6 Collecting data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
1.5.7 Sorting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
1.5.8 Stage specific guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
7576TOC.fm Draft Document for Review May 18, 2008 5:12 pm
iv IBM WebSphere DataStage Data Flow and Job Design
Chapter 2. IBM WebSphere DataStage stages . . . . . . . . . . . . . . . . . . . . . . 35
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
2.2 Aggregator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
2.3 Complex Flat File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
2.4 Column Import. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
2.5 Column Export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
2.6 Data Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
2.7 Distributed Transaction (new in Version 8.1) . . . . . . . . . . . . . . . . . . . . . . 63
2.8 FTP Enterprise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
2.9 Funnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
2.10 Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
2.11 Lookup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
2.12 Merge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
2.13 Sequential File. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
2.14 Slowly Changing Dimension . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
2.15 Sort . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
2.16 Surrogate Key Generator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
2.17 Transformer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
Chapter 3. Retail industry scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
3.1 Retail industry scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
3.1.1 One time tasks (Day 0) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.1.2 Recurring tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
3.1.3 Recurring tasks (Day 1) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211
3.1.4 Recurring tasks (Day 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 371
3.1.5 Recurring tasks (Day 3) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401
Contents v
Draft Document for Review May 18, 2008 5:12 pm 7576TOC.fm
Appendix A. IBM Information Server setups . . . . . . . . . . . . . . . . . . . . . . 427
A.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 428
A.2 Configure IBM WebSphere Classic Federation Server for z/OS . . . . . . 429
A.2.1 Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
A.2.2 Configuration of IBM WebSphere Classic Federation for z/OS system
catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 431
A.2.3 Configuration of Classic Data Architect . . . . . . . . . . . . . . . . . . . . . 438
A.3 Create the Queue Manager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444
A.4 Set up the XA parameters on Queue Manager. . . . . . . . . . . . . . . . . . . . 451
A.5 Create the queues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455
Appendix B. Code and scripts used in the retail industry scenario. . . . 461
B.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462
Appendix C. Additional material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
Locating the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
Using the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469
How to use the Web material . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471
Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
How to get Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
