Datastage

1. Datastage Complex Flat File Stage
2. Processing Stages In Datastage

Complex Flat File Stage. CFF stage now processes Multi Format Flat (MFF) file. Constraints can be specified on the output links to filter data and/or define when a record should be sent down the link. Complex Flat File stages are used only as source stages and cannot receive input links. As a source, they read data from a flat file. Complex Flat File stages can have one or more output links, and the outputs must be linked to active stages. Sandy's DataStage Notes. Stage When you draw multiple output links for the Complex Flat File stage on the parallel canvas, the number of rows that are shown as.

Datastage

There is a new IBM draft Redbook seeking community feedback called IBM WebSphere DataStage Data Flow and Job Design with a whopping 660 pages of guidelines, tips, examples and screenshots.

An IBM RedBook IBM InfoSphere DataStage Data Flow and Job Design brings together a team of researchers from around the world to an IBM lab to spend 2-6 weeks researching a practical use of an IBM product. It's kind of like Big Brother but they are doing something useful and don't have quite as many spa parties (so I'm told). IBM is seeking peer review and feedback on this draft.

There are a few bonuses in this book:

I've heard a few complaints (some of them from me) on the lack of DataStage documentation over the years. 'Where can I download the PDFs?' 'Are there any books about DataStage?' 'Are there any DataStage Standards?' 'Where can I get example jobs?' 'Please send me the materials for DataStage Certification.' Well we can all stop complaining! You can't ask for more than over a thousand pages of documentation with screenshots and examples in this RedBook and the one from last year I profiled in Everything you wanted to know about SOA on the IBM Information Server but were too disinterested to ask. Not to mention IBM WebSphere QualityStage Methodologies, Standardization, and Matching. This one belongs on my list of The Top 7 Online DataStage Tutorials.

The team that put this one together:

• Nagraj Alur was the project leader and works at the San Jose centre.
• Celso Takahashi is a technical sales expert from IBM Brazil.
• Sachiko Toratani is an IT support specialist from IBM Japan.
• Denis Vasconcelos is a data specialist from IBM Brazil.Â

The team was supported by the DataStage development team from the Silicon Valley Labs in San Jose.
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:

0002 Standards
0002 Development guidelines
0002 Component usage
0002 DataStage Data Types
0002 Partitioning data
0002 Collecting data
0002 Sorting
0002 Stage specific guidelines

An example of some stage specific guidelines:

Transformer
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.

When 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.

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 : Counter strike 1.6 download free.

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:

Datastage Complex Flat File Stage

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.

So if you are like me you look at the job and wonder how the hell you set the properties of the DTS stage when it has nine input links and nine different sets of database commands. Well that's one of the surprises in release 8.1, they have a nifty diagram showing up in the property window (kind of like a Google map) that shows you what link you are modifying at any point in time:
Â

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:

Processing Stages In Datastage

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