IBM i > ADMINISTRATOR > DB2

What is Metadata And Why Do I Need It?

DB2 on IBM i
 

DB2 on IBM i has a wealth of information about files, fields, number of records, index key fields, constraints, reads, writes, etc. We might refer to this as metadata that might be used by the query optimizer to improve DB2 performance. You can see this much of this information using the classic commands DSPFD, DSPFFD, the iNavigator DB2 Definition or Properties. For objects created with SQL DDL rather than DDS, you might find that you see more information, due to database enhancements related specifically to SQL.

The DB2 Web Query product utilizes a metadata layer, created between databases and the reporting interfaces. As Tables, Views and Procedures are described to Web Query, the metadata is produced and stored as files. The creation process captures column specific information as well as data about the database object’s location. The two metadata files are collectively referred to as a Synonym, even though two separate files are produced.

The metadata layer provides a pointer to where WQ finds the data, plus presentation information. Because WQ metadata provides field-level descriptive information, it not only facilitates reporting but also offers a potential to “clarify” definitions in the context of your company’s business. When Web Query communicates with the database, the metadata tells the system how to formulate the SQL for retrieval and how to present results when they are returned.

Web Query can be used with databases other than native DB2, provided that you obtained the appropriate IBM-Supplied adapter for that database. Adapters can also be used to deal with specific ERP systems, such as Oracle JD Edwards products World or EnterpriseOne, which might utilize specialized date formats or lookup tables. In either case, the metadata tells WQ to handle the specifics for that database. For an SQL server instance, for example, Web Query would look to that system and format the SQL syntax accordingly.

Let’s consider an example in a DB2 table named ORDERS (figure 1).

This table has a column named LINETOTAL with heading named Revenue (figure 2).

When initially creating the LINETOTAL column in this table, the developer had several fields in either DDS or SQL to further define that field. Column name, System name, Data type, Text and Headings are all very familiar to IBM i developers. Each attribute plays a role in the column’s description when generating Web Query metadata.

In this case, the DDS field or System name, shows LINEPRICE, the Alias or SQL column name shows LINETOTAL and the Heading-line-1 reads Revenue. So, which of these “titles” is the preferred way to refer to this column? Well, the answer is “it depends”, but WQ gives you the ability to specify or modify this descriptive information and more.

When creating metadata for a column WQ initially makes assumptions, based on what it finds in the table definition. The importance of the Metadata layer is that it offers the opportunity to make modifications to column headings, descriptions or data formats, promoting consistency across the organization, perhaps yielding benefits beyond those in the original ERP database.

In this instance, the Revenue column may be perfectly obvious to analysts reporting from the ORDERS table. However, sometimes a column in an ERP database is named something more obscure or abbreviated like XSINVN where XS means Extended Sales and INVN means Invoice number. In another table, invoice number might be called ARINVN, relating it to Accounts receivable. The metadata title for both of these columns might be better standardized as “Sales Invoice No.”, for clarity and consistency in reports.

Rick Flagler is an information technology consultant, teacher and mentor.



Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters