Home > Data Services > Extracting Metadata from ODBC Sources

Extracting Metadata from ODBC Sources


Metadata is essential to well documented ETL processes. Ideally, this metadata starts with the source system table and column descriptions and other system specific information. When this information is not available directly inside of Data Services, retrieving it manually can fill in the gaps and help your users get a complete map of how data arrives in the warehouse.

Generally, the lack of metadata is caused by the absence of a native driver to the source database. For example, using ODBC to access a DB2 source on an AS400, the ideal solutions is to use DB2 Connect, a native driver, but that option can be price prohibitive for some clients. Additionally, some third-party vendors only allow connectivity via ODBC.

How do you go about getting the metadata? Most databases have system tables that contain the data we are seeking. A quick Google search for your source database and platform should provide you the system table name(s) you need. For this version of DB2, the table is called SYSIBM.SQLCOLUMNS. Below is a data flow to copy SQLCOLUMNS to the ODS. Qry_Set_Desc just picks out the metadata columns we are interested in.

Extracting Metadata 1

Then I use a second data flow to limit to just the tables I am interested in and output the results into a format that makes them easy to transport to our target database. In this case I have created a 4 column file, although you could get by with just the last one if your prefer.

To download PDF and Continue Reading…

ernie-phelpsAbout Ernie Phelps
Ernie has 15+ years of experience in EIM with client in many verticals, including aviation, construction, finance, and healthcare. He is also a certified SAP instructor. At Decision First Technologies, Ernie specialized in SAP Data Services

Categories: Data Services
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: