Data Stage (Duration: 40 Hrs)

dwh DataStage TX provides support for industry standards and connectivity requirements so you can solve critical business problems in real time. DataStage TX's Solutions-Oriented Architecture is open and scalable, which means we can rapidly adapt our technology to meet specific industry needs - so you can accelerate implementation, reduce risks, and increase operational efficiencies.

A collection of links to resources for DataStage for installing, using, getting certified, optimising and administering DataStage Server Edition, Parallel Edition for old and new versions.

Working with Databases from DataStage

  1. Oracle Performance Tuning - Bulk / Direct / OCI / Updates - Here is a golden thread from the deep archives of DSXChange from Ross Leishman on Oracle performance from DataStage with some timings and techniques for the common OCI load techniques plus some out of the box thoughts on Oracle External Tables in ETL and Partition Exchange Load. Wow. Since it's a forum the thread grows with feedback and extra tips.
  2. Datastage 7x Enterprise Edition with Teradata - Essential reading if you are striking out on your first DataStage Teradata expedition as Joshy explains the differences between the different Teradata load stages. TPump may sound like something advertised in a spam email promising enlargement but it's just one of the ways to get data into Teradata very quickly. Multiload and FastLoad may not be multi or fast in certain situations.
  3. Configure DB2 remote connectivity with WebSphere DataStage - IBM DeveloperWorks article, getting DataStage to connect to remote DB2 databases from a parallel job is a bit like spending an hour assembling IKEA furniture without instructions only to discover it's two pieces of furniture, not one, and they are both missing key pieces. You need this guide. Don't even try without it. You'll never get those three weeks of your life back again.
  4. Multiple readers per node loading into ODBC - a deep analysis thread on DSXChange that examines impact of having multiple readers per node when reading a sequential file and writing to an ODBC database. A good scenario for looking at parallel config files, job debug and the impact of nodes and partitions on performance.

DataStage Enterprise Edition Online Resources

  1. Create custom operators for WebSphere DataStage and Custom combinable operators for IBM WebSphere DataStage and DataStage Parallel routines made really easy - I've put these two DeveloperWorks articles together with the Joshy George blog post, they were written many months apart but they cover the ancient and mysterious art of creating custom parallel operators for DataStage. See if you can spot the difference between custom operators and combinable operators .. then explain it to me.
  2. WebSphere DataStage Parallel Job Tutorial Version 8 - The Official DataStage tutorial from IBM. This is the one that comes on the Information Server installation and is available from the IBM publications centre and you can download it free of charge as a 1.05M PDF file. If you cannot do the tutorial you can at least read the tutorial.
  3. Modify Stage with Andy Sorrell - Andy from the DSXChange steps through using the parallel Modify Stage. There are no hints inside this stage - no drop down menus and no help screens and you can't chain Andy to your computer so the next best thing is watching his video.
  4. End of the Road to DataStage Certification - Minhajuddin has a blog with some good posts on how to prepare for and pass the exam with his own experiences and how he passed with a better score than me!
  5. DataStage Certification - how to pass the exam - My tips on passing the exam. What to study and what to expect. Keep an eye out for a DataStage 8 certification exam in the next couple months.
  6. DataStage tip: using job parameters without losing your mind and 101 uses for ETL job parameters- is my early look at DataStage parameters, since updated with version 8 Parameter Sets in more recent blog posts.
  7. Shell script to start a job using dsjob - From Ken Bland on the forum DSXChange comes a full Unix shell script for running a DataStage job from an enterprise scheduling tool. It was written for DataStage 5 but it should still work for DataStage 8! It fetches parameter values from a parameter ini table - something you could replace with a Parameter Set in the latest version, it retrieves more dynamic processing parameters from a database table. It shows how to check the status of a job after it finishes.
  8. Find and remove "orphan" Dataset files - from the DSXChange FAQ forum comes a way to get rid of unwanted datasets. These things get created by production jobs, and then the jobs get decommissioned or the dataset name changes but the old datasets stay there taking up space and polluting the ozone layer.
  9. Duke Consulting Tips & Tricks - Kim Duke Consulting brings DataStage tips and utilities. There is ETLStats - a set of DataStage components that lets you load operation DataStage metadata (job run times and stage/link row counts and parameter values) into a simple database schema. You can get the ETLStats pack and a video on how to install it. Some DataStage Server routines and Unix scripts and an XML Best Practices sample.
  10. I need a DSX-Cutter - Want to put your DataStage jobs into source control? You need a DSX-Cutter. It takes a very large DSX export file and cuts it into individual jobs for checkin. This thread of DSXChange got an amazing 48 replies and has the code for a DSX-Cutter that should be compatible with version 8. You wont need it for much longer - a future version of DataStage promises a version control API layer.
  11. A flexible data integration architecture using WebSphere DataStage and WebSphere Federation Server and Access application data using WebSphere Federation Server and WebSphere DataStage - Both these tutorials combine Federation Server and DataStage. The second one also shows the SAP R/3 Pack for DataStage. Got some scenarios for using ETL and federation together.
  12. How do I suppress a warning message? - the first time you run a parallel job you may have a heart attack from all the warning messages. Relax - it's just being picky about metadata. This DSXChange FAQ shows how to remove the warning messages.
  13. 10 Ways to Make DataStage Run Slower - my blog post using reverse psychology to find ways to use DataStage better by making it run slower.
  14. Sorts to the left of me, sorts to the right - when you get into big data and parallel jobs you need to concentrate a lot more on your sorting. Have you got secret sorts creeping into your job without you knowing? Can you pre-sort and if so how do you stop your job from re-sorting.

DataStage Real Time

It's time to get real. DataStage 7 had the Real Time Services, DataStage 8 has the Information Services Director. They both do the same thing - they turn DataStage and QualityStage jobs into an always on job - a web service - an enterprise java bean - an SOA enabled job. It's becoming a lot more important with operational Master Data Management.

  1. Getting Started with IBM WebSphere Information Services Director. A LeverageInformation technical tip via a flash video on how to turn a DataStage or QualityStage job into a web service.
  2. Transform and integrate data using WebSphere DataStage XML and Web services packs - This Developerworks article shows how to get the XML input and output stages up and running in a server job. It uses Server Jobs to read XML but it's virtually identical to how XML is used in Parallel Jobs. A Server Job finds the XML using the Folder stage to pass the data to the XML Input stage. A parallel job uses the Sequential File stage instead with a file mask option to pass XML through to the XML Input stage. Different stages but same method. The DataStage Real Time Pack or the Service Director both turn Parallel and Server jobs into web services the same way.
  3. Handling Nulls in XML Sources - the DSRealTime blog looks at nulls in XML - is it null or is it just missing and did the world really deserve XML? Google are trying to supplant XML it but Ernie helps explain it.
  4. How to Invoke Complex Web Services - this section of links would be really lame without the DSRealTime blog. In depth entry on complex web services - arrays, security, SOAP headers and embedded XML.

New to DataStage 8

This category is for people who are new to version 8 and want to know how to use the new features.

  1. Version 8.0.1 Installation - this DSXChange forum thread takes you through the eleven steps, and dozens of sub tasks, in an Information Server install on Solaris that's good reading for any Unix/Linux install. More comprehensive than the Install and Upgrade Guide.
  2. The DataStage 8 Server Edition SuperFAQ - are you on DataStage version 7 and pondering version 8? This FAQ lists the questions you might have with some answers.
  3. IBM Information Server Using Slowly Changing Dimensions in IBM WebSphere DataStage Projects - The DSXChange forum has an interesting thread about whether parallel jobs or server jobs are better at slowly changing dimensions. Check out this LeverageInformation tutorial for the new DataStage 8 parallel job stage and read the thread for the server job approach. A lot of dimensions have smaller volumes of data so Server jobs are an option.
  4. IBM Information Server: Setting up IBM WebSphere DataStage Users and IBM Information Server: Using Groups to Simplify User Administration with the Internal Directory - I've put these two LeverageInformation tutorials together for obvious reasons. You have several security options with DataStage 8 about where your users and passwords are setup and maintained.
  5. How to Create, Use and Maintain DataStage 8 Parameter Sets - This is a three part series I wrote about the new Parameter Sets in DataStage 8 and how they interact with Environment Variables and User-Defined Environment Variables. You need to know this before you start using version 8 because job parameters are a pre-requisite of a well designed job.
  6. DataStage 8 Tutorial: Using Range Lookups - From the people who brought you Parameter Sets (me) comes Range Lookups. Another walk through that will have you on the edge of your seat. Range lookup functionality is now built into the Lookup stage - finally!
  7. Navigating the Many Paths of Metadata in the Information Server and Using IBM WebSphere DataStage to Import Metadata into the IBM WebSphere Metadata Repository - In DataStage 8 you have many ways to import metadata. DataStage Designer table import, the import/export manager and the Information Analyzer console. This post tries to make some sense of it all.
  8. Using WebSphere DataStage with IBM DataMirror Change Data Capture - DataMirror and DataStage now play together. You can set up a DataMirror Transformation Server replication that feeds straight into a DataStage job for easier log based change data capture. This white paper takes a deeper look at it.
  9. DataStage 8 Tutorial: Surrogate Key State Files - looking under the covers at the new state file functionality in DataStage 8 that lets you parallel jobs increment surrogate keys across partitions and remember values between job executions.

Back to the Future - DataStage Server Edition

I've put the DataStage Server tutorials in a special category since this version is still alive and kicking and some would argue it remains better than the upstart parallel version.

  1. Video FAQ's with Ken Bland - This is a great guide for navigating around DataStage on Unix or Linux, it was written for Server Edition version 7 and earlier but it's still got some relevant information for DataStage 8 parallel edition.
  2. MQSeries € ¦ Ensuring Message Delivery from Queue to Target - a post from the DSRealTime blog about how to make sure a message from a queue makes it through a DataStage job into a database schema without losing any bits.
  3. ETL Tools Datastage tutorial and training - This is a big tutorial website with lots of HTML pages on Server Edition tasks: designing jobs, reading sequential files, performing lookups, slowly changing dimensions.
  4. Server to Parallel Transition Lab with Ray Wurlod - Some would argue the best server job tutorial is that one that lets you leave server jobs behind! This DSXChange Learning Centre tutorial looks at the difference between the versions and how to move to the newer edition.
  5. Using hash files instead of UV tables for multirow - Roll up your sleeves and get into server edition multi row lookups from hash files. A master class from Ken Bland on DSXChange. Very useful when doing slowly changing dimension lookups. Also see a similar thread Hash Files & Slowly changing dimension.
  6. Upgradation & Migration - A DSXChange forum thread where Kim Duke provides a great guide on upgrading DataStage version 7.x and earlier. Still useful for the DataStage server upgrade to version 8. It comes with a handy script for backing up crucial DataStage files before the upgrade.

It's got to be Red

IBM RedBooks have their own special category because they are so fricken huge. If all the DataStage Redbooks were stacked on top of each other they would be wafer thin since they are in digital format but if you printed them out they would be a couple thousand pages high. Inside each RedBook is a section of DataStage theory followed by a real world scenario. It's very good reading for DataStage certification.

  1. SOA Solutions Using IBM Information Server - Shows some SOA scenarios using DataStage and the Federation Server.
  2. IBM InfoSphere DataStage Data Flow and Job Design - This one comes with some DataStage design recommendations and a great retail scenario.
  3. Deploying a Grid Solution with IBM InfoSphere Information Server - This one is all about deploying DataStage onto a RedHat grid but there are some lessons in there for any type of grid.