Creating Data File objects from inside of DataStage

A seldom used object in Metadata Workbench is a “Data File”. It is not as common because it has to be manually created. Database Tables are created whenever you use a Connector or other bridge to import relational tables from a database. Data Files, however, can only be created manually, using the istool workbench generate feature, or from inside of the DataStage/QualityStage Designer.

Why create Data Files?

A Data File is the object available in the Metadata Workbench that represents flat files, .csv files or DataSets. It is able to connect to the Sequential Stage or Dataset Stage for data lineage purposes. A Data File object might be used also for pure governance reasons — a special transaction file might be defined by a particular Business Term, or you might want to assign a Steward to the Data File object — the subject matter expert on one particular file. Of course, if you are a DataStage user, you probably use regular Sequential Table Definitions all the time. Data Files are similar but are more “fixed” — they are designed to represent a specfic flat file, on a given machine, and in a particular sub-directory, as opposed to being a general metadata mapping with proper column offsets for any file that matches the selected schema.

The simplest way to create a formal Data File is to start with a DataStage Table Definition. You may already have one that was created when you imported a sequential file, or can easily create one using the “Save” button on any column list within most Stages. Once you have the Table Definition, double click on it. Review all of the tabs across the top. Pay special attention to the “Locator” Tab. Click on that one. Look at its detail properties. Values at the Locator tab control the creation of Data Files or Database Tables.

Set the pull-down option at the top to “Sequential”. If that value is not already in your pull-down list, type it in… Towards the bottom you will see an entry for the Data Collection — put in the name you want for your file. Close the Table Definition.

Now put your cursor on that Table Definition in the “tree”. Right mouse and select “Shared Table Creation Wizard”. When that dialog opens, click Next. Then open the pull-down dialog and select “create new”, and click Next. Notice the properties at this new page….you have the Filename, the Host (pick a machine or enter a new one) and Path. Make the filename the SAME as what you have hard coded in your Sequential or Dataset Stage, or the filename of any fully expanded Job Parameter default values that you are passing into it. Then set the “Path” value to the fully qualified path of the expanded Job Paramters or what you have in the same filename property. For example, if your filename in the Stage looks like this:

/tmp/myfile/#myfilename# …and #myfilename# has a default value of mySequentialFile.txt

Then use mySequentialFile.txt as the Filename and /tmp/myfile (without the final slash) for the path. Now you will have a Data File inside of Metadata Workbench that you can govern with Steward and Term assignments, and it also will stitch to the Stages that use its name in hard coded fashion or expanded Job Parameters for Design time or Operational lineage.

Ernie

Always use an Input Link AND an Output Link with the XML Stage

Another way to say this is to “avoid using the XML Stage to perform i/o.” The XML Stage is capable of reading an xml document directly (a feature in the Parser Step) and is also able to write to a new document on disk in the Composer Step. However, while it may seem simpler to do that initially, it makes your Jobs and Stage designs less flexible and less re-usable. You should have an Input link that feeds XML to the XML Stage when you are “reading” or parsing xml (and of course you will have output links that send the results downstream), and you should have an Ouput link that sends your completed XML document(s) downstream when you are writing XML (and of course you will have input links that feed in the source data).

Let’s see why.

When you are first learning the XML Stage, it seems convenient to just “put in the name of the xml document” and keep going. The Parser Step allows you to specify the filename directly (or it can be parameterized), and then you continue with the assignment of the Document Root. Similarly, when creating a new XML document, the Composer Step allows you specify the actual document to be written to disk.

Then someone comes along and says “Our application is changing. The xml documents we currently read from disk will now be coming from MQ Series…” …or maybe “…from a relational table” …or “from hadoop”…. Well, you can’t just “change the Stage type at the end of the link” in that case. You have to “add” the link, and then make what could potentially be extensive changes to your Assembly. While not especially difficult once you are familiar with the Stage, if you have moved on to other projects, or have been promoted and are no longer supporting the Job, a less experienced DataStage developer will be challenged.

So…when using the Parser Step, use one of the options that describes your incoming content as either coming in directly as content (from a column in an upstream Stage), or as a set of filenames (best use case when reading xml documents from disk, especially when you have a whole lot of them in a single sub-directory [see also Reading XML Content as a Source ] )

The same thing is true for writing XML. Send your xml content downstream — whether you write it to a sequential file, or to DB2, or to MQ Series or some other target, the logic and coding of your XML Stage remains the same! In the Composer Step, choose the “Pass as String” option and then in the Output Step, map the “composer result” to a single large column (I like to call mine “xmlContent”) that has a longvarchar datatype and some abitrary long length like 99999. While there may be times when this can’t be easily done, or when you need to use the option for long binary strings (Pass as Large Object), for many/most use cases, this will work great.

Get in the habit of always using Input and Output Links with the XML Stage. Your future maintenance and changes/adaptions will be cleaner, and you can take better advantage of features such as Shared Containers for your xml transformation logic.

Ernie

XML Stage: Establish Meaningful Link Names

…and then stick with them! Decide early what you want your Link names to be, before you even open up the Stage and begin your work on the Assembly, and then lock them in. Make a conscious decision not to change or alter them. Why? The XML Stage is not immune to Link name changes like other Stages and Connectors on the DataStage canvas.

How many of you are perfectly happy with DSLink2 and DSLink35 or other automatically generated Link names? I know I don’t spend time on every Job, running around putting on fancy Link names, especially when I’m first building it. It’s nice for documentation, and I know that I should always create meaningful names, but how many of us do?

And how often do we “go back” and edit the Link names later? That’s actually a good thing — for most Stages and Connectors. But for the XML Stage, it is something you want to avoid. Changing Link names will break your Assembly and require that you edit the stage and make changes.

Here is an example of the XML Stage reading xml documents from a subdirectory and performing validation. Valid xml will be sent down the “goodXML” Link, and rejected, invalid xml content will be send down the “badXML” link.

Notice how, inside the Assembly, these link names are used. Here in the Assembly Parser step, you see the toXML linkname used for the specification of the xml Source:

…and here, in the Assembly Output Step, you can see how the Link names are used in the Mapping:

Those screen shots illustrate how the link name becomes critical to the internals of the Assembly. If you change the link names outside the Stage, the Assembly will end up with errors (various red marks throughout the Assembly, depending on how complex it is):

Are you able to correct the Assembly when this happens? Of course…and for most scenarios, it’s not difficult…you might just need to change a setting or re-map a couple of columns. But save yourself the trouble. Decide on your Link names, set them up early (preferably before you ever enter the Stage) and then don’t touch them!

—Ernie

Best Practices and Techniques for the “New” XML Stage

Hi Everyone…

It’s been awhile since I’ve posted anything.   A certain amount of “blog” fatigue is to blame, but also because I like to post things that are (as much as possible) proven, time-honored (and not release dependent).   Many/most of the techniques I write about here are ones that I’ve spent many hours helping customers and colleagues implement in real situations.

It’s time I write about the XML Stage.  It is not-so-new anymore, but still feels new as it has had some very important xsd handling features added to it in the last few releases.   This week I will start posting suggestions and tips for using the XML Stage to read and write xml documents using DataStage.  

I’ll start with a pointer to a valuable RedBook that came out last year regarding the XML Stage.  I had the pleasure of reviewing the material as the authors put it together.  It is a great place to start when learning to work with this important Information Server capability.

XML Stage Redbook

Ernie

…and a link to the first “New” XML Stage post…

Establish Meaningful Link Names when using the XML Stage!

Are Your Kids Addicted to Minecraft?

Are your kids playing Minecraft yet? ….or should I say “Are your kids addicted to Minecraft?” This game by Mojang (www.minecraft.net) is a creative game where you put your imagination and creativity to work to build your own “world” using square blocks that you construct or dig and destroy to make tunnels, walls, caves, houses, mountains and more. The square blocks themselves represent many elements (stone, iron, coal, etc.) and in combination, lead you to the discovery or building of new things that you need to expand your empire. You find blocks of iron ore as you are “mining” (basically blowing up blocks of earth as you create new paths) and in combination with coal that makes a stove, you can obtain iron and gold utensils. Of course, to keep things interesting there are monsters and spiders that can kill you as the game turns from day to night, so you need to construct various shelters in order to survive.

I may have some of the facts incorrect — I have only watched the game, not played it, but am amazed by the phenomenon and how “catching” it has been for 10 to 13 year olds around the world. Nearly every parent I speak to has to remind their children to put down their iPad or iTouch or iPhone when they’ve played Minecraft for too many hours, or to turn off their Xbox or other computer that is running other platform versions of the game!

What makes this game so addictive? My only reference is our own creation of mini-worlds when we were kids. What kept us outside for hours, with our toys, designing private and public spaces and sharing them with our friends. For me it was Matchbox cars and trucks and Hot Wheels, with an occasional GI Joe, platoon of plastic soldiers, or Tonka truck mixed in. My best friend and I would build elaborate “villages” in and around the garden, with pachysandra posing as giant palm trees. We used sticks and pebbles to mark parking lots, driveways, and highways, and a small patch of sand at the other end of the garden served as our “remote gravel pit.” This was our own “mining” operation where our special mission trucks would go on “expeditions”. Mojang has clearly tapped into that same experience, only now it is on a touch screen, able to be played anywhere, and shared over the web between children who teach each other new things and proudly show off their new designs. It can also be played anywhere and at any time. Video games exist with far more spectacular graphics and intricate plot lines but bravo to Mojang for delivering a platform that inspires the imagination with basic simplicity while allowing for an infinite array of unique and challenging experiences.

Why write about this on “this” blog?

Watch some youngsters playing this game for awhile. It won’t be long before you are amazed at the speed at which they build/destroy/re-build/tear down and continue to evolve their world. All the time looking out for dangerous spiders or avoiding “Creepers” that will blow up and kill their game character if they step too near. Players make quick decisions about where to dig, what to build, or whether to leave a cave without knowing if it is nighttime (players learn early that monsters come out at night). How do they achieve this speed? Practice of course, but also through collaboration with their peers. They can play on the same network and play within each other’s worlds. They learn a whole new vocabulary and continually learn from others where to go (within their own groups or on the web). Some will accuse me of making a leap here, but purely for fun, this is governance in action. New terminology is shared by everyone in the Minecraft community (do you know what “Creepers” are, or how to get Glowstone and Blaze Rods out of “The Nether”?), helped along by Stewards (check out YouTube — there are 100’s of tutorials and videos out there from experienced “guides”) and metadata galore as players manage “Chests” full of artifacts collected and made, along with accurate counts of their inventory. Lineage is a bit of a stretch and not a concept you can directly apply to Minecraft, but there is a cottage industry for recording software that will let you create videos of a trip through your world or a fight with monsters.

If you are still reading this blog entry and don’t know anything about metadata, I hope you enjoy watching or playing Minecraft with your kids, providing it doesn’t push every other important activity out of the way! …and if you are into governance, I hope you had fun with the analogy and enjoyed a brief respite from metadata and governance in our technical realm.

Lineage for RDBMS “Views”

Hi Everyone…

Someone asked me yesterday about being able to perform lineage in Metadata Workbench on a database “View”. It dawned on me that I may have never created a post on this very important subject. Formal Database views, as created in the rdbms catalog via a CREATE VIEW sql statement, are fully supported for Data Lineage purposes by Information Server and the Metadata Workbench.

The key is in the method of import.

When you import your rdbms catalog information via Connector (in 8.7 Metadata Asset Manager, or in any 8.x release via DataStage, Information Analyzer, or FastTrack), the views are imported and get their own icon for display purposes within the “Hosts” tree (or Implemented Data Resources if you are in 8.7). The details of the view are available for display, and will show the SQL used in the CREATE VIEW statement to originally create it.

More importantly, when you perform “Automated Services” (a.k.a. “stitching”, or in 8.7, “Detect Associations”), Metadata Workbench will parse thru the SQL of the CREATE VIEW and establish data lineage connections to the “source tables” of the view! Once this is done, you will have lineage for the view back to its source tables, and of course, anything that is upstream from those tables, or downstream from the view itself!

Ernie

New RedBook for XML Stage is available!

The new redbook is available for the enhanced XML capabilities introduced by the “XML Stage” in Release 8.5 in October of 2010. It represents a lot of hard work by my colleagues who work with, developed, and tested this enhanced way of processing XML content in an ETL tool. Congrats to then entire authoring team, the reviewers, and the people who made publication of the Redbook possible — and congrats to the rest of of us who now have another excellent resource for reading and writing complex XML using DataStage, QualityStage, and Information Server!

You will find this new redbook here:

http://www.redbooks.ibm.com/abstracts/sg247987.html?Open

Ernie