Metadata report - table list given job name

dataservices metadata reports
1

I need to be able to pull all temp and regular tables used in a job in a list format. Is there a quick and easy way to do this? Basically we will use this for environment builds and maintenance of our staging environments.

We are on version 12.2.3.0.

Thanks!

CILBOBuser (BOB member since 2010-12-09)

2

Yes there is a quick and easy way. Query the repo tables. ALVW_AL_USAGE maybe. Or ALVW_MAPPING. Or AL_USAGE mabye.

There is enough info on the forum and in the documentation :wink:

Johannes Vink :netherlands: (BOB member since 2012-03-20)

3

I’m trying to do something similar for documentation purposes. I found ALVW_MAPPING seemed to have the sort of results I needed but it doesn’t seem to be coming back with all the information.

We have a sizeable DS configuration and some data flows are definitely missing from the output (on SQL Server). I would guess results are hitting a limit somewhere possibly? It doesn’t help that ALVW_MAPPING is a view of tables and more views. Any one got some suggestions on dissecting the query a little.

DS is 3.2, repository is on SQL Server (2005).

Essentially I want to use the query output to produce some documentation on WF->DF->Tables (Source/Target) and Mappings, along with some implied dependencies.

DanDensley :uk: (BOB member since 2009-05-12)

4

Responding to myself :slight_smile:

Not sure what was going on before but I analysed the view definition and reworked it into a cut-down query for DF->Src Tabs & Trg Tabs …

select distinct DF_NAME, SRC_TAB_NAME, TRG_TAB_NAME
  from AL_COLMAP
  order by 1

This returns info on all DF so I’ll work my way out from there.

DanDensley :uk: (BOB member since 2009-05-12)

5

Nice of you to report this back! :+1:

Some tables are only updated when you run ‘Calculate usage dependencies’ and / or ‘Calculate column mappings’. BODS is supposed to run this by standard, but can be disabled. And BODS 4.0 is not so stable with this functionality.

Johannes Vink :netherlands: (BOB member since 2012-03-20)

6

Hi Johannes,

Ah that might explain it, thanks for the information. I kicked this off manually when playing in DS Designer so must have accidentally fixed it. I’ll look into scheduling the process.

DanDensley :uk: (BOB member since 2009-05-12)

7

Somewhere under Tools - Options you can check if it is enabled or not. I do not know of a way to schedule it… Although part is an internal job, it should be possible to schedule that one I guess…

Johannes Vink :netherlands: (BOB member since 2012-03-20)