We need the ability to find which of our Data Integrator jobs use a particular table as a source. I discovered that metadata tables AL_USAGE or AL_COLMAP can be queried to get the desired results. However, this approach does not capture tables that are used in SQL Transforms.
Is there a way to find which jobs use a particular table, when that table is used in an SQL Transform?
mitchg00 (BOB member since 2008-03-17)
the SQL used in SQL Transfrom is not borken down into lineage while calculating column mapping, since the tables in the SQL Transform may not be imported in the Datastore, its a customised SQL (user defined)
you won’t be able to see that inofrmation in DI, the sql is stored as part of DF language and its not easy to extract the info from the language, you can check the AL_LANGXMLTEXT table to see if you can try to get the SQL from the XML Definition
there is another Product “Business Objects Metadata Manager”, which has DataServices Integrator that has capability to parse the SQL of the SQL Transform and give you the info that you are looking for, but in that also there are some limitation it has its own ANSI based parser if your SQL is not as per ANSI it may not be able to give you the complete info
manoj_d (BOB member since 2009-01-02)