Looking at the methods for importing metadata table definitions into DataStage 8 ETL jobs.
All of the metadata import methods of DataStage 7 are in DataStage 8 and all execute in the same way. Developers familiar with previous versions will be right at home! What is tricky to come to terms with are all the new ways to get metadata into DataStage 8 and the Metadata Server. The Metadata Server can provide reporting on metadata from products outside of DataStage such as BI tools so in some cases you might be importing the metadata for reporting and not for ETL.
The list that follows covers just the techniques for importing metadata to be used by DataStage jobs.
1) Information Analyzer
2) DataStage Table ImportAll our old favourite import commands are still there! This remains the most direct way to get metadata into DataStage. Many of these import wizards use the same method to get to the data as a DataStage job stage would.
There are import commands for Cobol complex flat files, XML files, stored procedures etc. The Plug-in Meta Data Definition option lists the import brokers for the Enterprise database sources such as Oracle, DB2 and Teradata. It will also list any additional special stages you have added such as Netezza, Federation Server or DataStage TX. In this example I used the ODBC Table Definition to bring in an MS Access table.
This produces very good metadata and often brings in description fields where they are available:
The importer worked out that the Memo field needed to be defined in DataStage as a LongVarChar field and that the Yes/No flag field could be stored as a bit.
3) MITI Import bridgesDataStage comes with a bunch of metadata bridges supplied by a specialist vendor Meta Integration Technology Inc (MITI).
Meta Integration is the leading "Metadata Component Provider" to major database, data integration, business intelligence, repository, and modeling tool vendors. MITI has established itself as the " Switzerland of Metadata"
These bridges are great - they cover dozens of popular products and are up to date on the latest versions. (We have been waiting a long time for an ErWin 7 import broker).
Importing from a modeling tool is also great if you have a lot of sequential file sources where the metadata on them is stored in a data model. Good luck getting that - most projects seem to store that type of metadata in MS Office documents.
These are the modeling tools that MITI has an import bridge for:
CA ERwin Data Modeler
CA Gen (COOL:Gen)
CA COOL:BusinessTeam (GroundWorks)
CA COOL:DBA (Terrain)
CA COOL:Enterprise (ADW)
IBM Rational Rose Data Modeler & XDE
IBM Rational Data Architect
Telelogic Popkin System Architect
You get a menu listing the dozens of bridges available:
Going through all the wizard steps will turn the imported metadata into one or more table definitions.
MITI also has a cobol copybook importer but I haven't been able to get it to work on files that the DataStage importers handles. Both the complex flat file and XML importer for DataStage has quite a good preview and debugging tool that makes the job easier than via the MITI bridge.
4) Orchestrate Schema importFor example when I try to import an MS Access table with a memo field the Orchestrate import gives me this error:
Datatype not supported.
While the DataStage ODBC import works - it converts the memo field:
Once I take the memo field out and run the Orchestrate import again I get a parallel schema:
This definition caused an abort to my job as it didn't convert the MS Access Yes/No field correctly:
The DataStage ODBC import chose a Bit SQL Type and the Orchestrate import chose TinyInt and the job fell over. So the DataStage ODBC import wins that battle. Orchestrate might do a better job on Enterprise parallel data sources such as Oracle and DB2 and might avoid some metadata warning messages at run time however these minor glitches can be fixed. The lack of a description field cannot be fixed so this type of import is one I avoid.
5) Write your own
6) Manually enter data
For WHATSAPP group , drop a msg to 91-88-00-906098