The new XMLPack in 8.5….generating xsd’s….

As noted in an earlier post and outlined nicely in Vincent’s blog (new xml!), the new XML Pack is here… It is very powerful, providing new features for reading and transforming hierarchical data, performs faster (and smarter) than earlier xml technologies within DataStage, and much more. It also requires that you have an XML Schema Definition (xsd) for the import of xml metadata. Most of the time this is not an issue. The xml documents you are reading and/or writing are well defined, complying with a formal xsd developed within your organization, or perhaps by a partner, yourself or by a standards body. But sometimes, there is no xsd. You may not have access to one, it might have been lost, or it never existed. The XML might be simple enough that it was just generated by another tool without the use of an xsd (or you are asked to generate it), or the xml might be old enough to pre-date the arrival of xsd’s.

There are many ways to generate an xsd. Popular tools such as Altova XMLSpy support this capability, as do many others, including xml Max, whose link I have over on the link list to the right. A quick search on the web will invite you to try a lengthy list of possibilities. One that I’ve been very successful with is called “trang”.

http://www.thaiopensource.com/relaxng/trang.html

This little tool does more than just xsd generation, although that is the functionality that I have found most useful. I’ve tried it on Windows and on Linux. It is easy to use, well documented, has references from other bloggers across the web, and does the Job. It is command line based, and requires that you have a java run time locally installed. There may be more sophisticated tools out there, but this is sufficient for what I need to be productive with the new XML Stage.

Let me know if you find any others!

Ernie

Connecting Shared Tables to your DataStage/QualityStage Jobs

At this point you should be familiar with Shared Tables, and understand how data lineage works within, and among, multiple DataStage Jobs. Stage to Stage lineage is very useful, and may be all that you require. It’s also powerful, though to go beyond this and connect your Jobs to the tables that you have imported from various places.

Why?

One of the key reasons for including Shared Tables in lineage is for “Business Lineage”. This is a high level summary of lineage that doesn’t illustrate the lower level transformation details…just the “key” sources and targets (files, tables, reports, etc.) along the way. Another is to connect your Jobs to “external, non Information Server assets.” DataStage doesn’t write directly to a (for example) Cognos report….it writes to a table somewhere, and that table is then read by the business intelligence tool. The connection thru that table is critical for accurate data lineage reporting. Here is how Metadata Workbench makes the connection between a DataStage Job and a table…

Shared Tables have a four (4) part name: Host/Database/Schema/Tablename (see other entries in the table of contents on Shared Tables for more details on the names of Shared Tables or alternatives for importing them).

Relational Stages in DataStage typically use two (2) parts to identify a particular table. “Server” or “Database” or “DSN” name, and tablename. The Server/DSN/Database name is usually in a dedicated property within the Stage. The Tablename might be in a dedicated property, or it could also be embedded in User Defined SQL. Any of them might be hard coded or established via Job Parameters.

The first thing Metadata Workbench needs to do is to “map” the abstract “Server/Database/DSN” name to a particular “Host” and “Database” combination in the list of Shared Tables. Like any application, DataStage is just pointing to some abstract “string” when trying to find a database. An ODBC DSN, for example, “might” be the name of the database, but it could also just be “myODBCdatabase”, which really points to (in the ODBC definition) a DB2 table called HRMAIN. Even if we use the string HRMAIN in the “Server” property of the Stage, we still need a way to identify the particular host that we are considering for data lineage. This is done via the “Database Alias” link in the “Advanced” tab of the Metadata Workbench.

Go to the Advanced Tab after signing into the Workbench. Perform Automated Services for one of your projects (this could take a long time if it’s the first time you are doing it). When it finishes, click on “Database Alias”. Look carefully at the values there. These are the “strings” that are used in your various Jobs to identify databases. Pick the string that is appropriate for the Stage Type that you are working with and slide your cursor over to the right. The “Add” button will allow you to select the desired host/database combination that this abstract string should connect to. In the example noted above, I might assign the string (alias) myODBCdatabase to the host/database combination of QR2H004/HRMAIN. QR2H0004 with Database HRMAIN must be something I have already imported and is viewable in the left navigation pane of the Workbench, or in the Repository Management tab of the Information Server Web Console.

Save it (button on the lower right).

The next time you perform Automated Services, whenever that Stage type with that particular string (myODBCdatabase) is found, Metadata Workbench will use QR2H004/HRMAIN, combined with the fully qualified tablename* in the Stage, to match to a particular Shared Table that has been imported previously.

[* Note... if your tablename is NOT fully qualified, Metadata Workbench will, for most RDBMS Stage Types, pick up and use the value of the Username in order to identify your table]

Are you using Job Parameters? For Design based lineage, Metadata Workbench is smart, and will use the “default values” of those Job Parameters when finding alias “strings” and also when obtaining the fully qualified (schema.tablename) tablenames to use in the linking.

Are you using $PROJDEF? Run the ProcessEnvVars shell or bat file inside of /IBM/InformationServer/ASBNode/bin to obtain the project definitions for use in this algorithm.

Operational Metadata (a whole separate blog entry is needed to discuss OMD) is used to populate the Job Parameter values from “run time”…otherwise the same rules apply. If you are just starting, get to know lineage w/o worrying about OMD. That’s an advanced topic. Understand how database alias works by performing Automated Services, reviewing the Database Alias page in the Workbench, doing the assignment and running Automated Services again. Once it is complete, go to the actual table using the navigator frame at the left (open the Host tree and find the table) and right mouse and select “data lineage”. If it is a source, select “where does this go to”…..if it is a target, select “where did this come from”…… and validate your results.

Ernie

The new XML Stage is here!

Just announced yesterday…the new XML Stage is available for 8.5! This introduces a whole new level of XML Transformation to the Information Server platform! Among its new capabilities are the ability to read single huge documents using a new streaming methodology that avoids the need to load the document into memory, support for any type of xsd, or collection of xsd’s, to define your xml metadata, and perhaps most important, a whole new hierarchical editing mode called an “Assembly”, which provides support for the creation of complex multi-node hierarchical structures! There’s much more, such as very explicit control of xml validation, a built-in test facility to ease transformation development, and support for both EE and Server Jobs. I’ve had a chance to play with the Stage over the last few months, and will share my experiences and techniques in upcoming posts.

In the meantime, I’d like to congratulate my IBM teammates in engineering for this accomplishment! This new capability will change how we approach many transformation solutions!

You can find the new XML Transformation capability at fix pack central for application to your 8.5 installation.

Ernie