The Power of Metadata in the IBM i OS: Part 1

Developers who work in the IBM i environment may or may not be familiar with the term “metadata.” Metadata can be defined as “data about data.”

Metadata has existed within the IBM i OS since the historic days of the System/38, when an integrated Relational Database was introduced. Consider the commands DSPFD and DSPFFD, both of which are frequently used by developers to see information about files and fields within files, respectively. The DSPFD command, when executed for a specific file name, shows information at the file level such as description, number of records, number of deleted records, length of the record , etc. The DSPFFD command goes a layer deeper to show each field in the record format and the descriptive information, including field length, type, column headings , etc. This is metadata which Db2 keeps up to date in the Db2 Catalog, a group of system files.

Similar information can be seen in Access Client Solutions (ACS), the graphical counterpart to an IBM i green-screen commands. When you look in ACS under Schemas->Library->Tables and then right click a Table object to review the definition attributes, you see columns as shown in Figure 1.

Default Metadata

When you make a table known to Web Query, you do so by creating a metadata object called a synonym. Synonyms consist of two files; one includes a definition of where the table is found, the other a definition of the fields from the table. By default, the synonym is created using whatever information the table contains. Launching the report designer, a consumer of the data in this example (Figure 2) sees fields as they were known to the ERP system.

Because Db2 Web Query for i has been developed to be both a developer and an end-user tool, it behooves us to present data in a way that is accurate, easy to work with and clear to the report designer. Looking at Figure 2, we see this data possibly lacks clarity because the database names weren’t meaningful enough. We have a choice: modify descriptions of items in the report, or edit the metadata to “improve” it. Modifying the metadata will improve all reports based on that same data source, potentially freeing the IT development staff from writing reports, and in the long run, improving the reporting experience for the business.

To some this might sound like a strictly cosmetic improvement, but as you will see, metadata can do more than simply change column appearance.

Making Data More Attractive

Now that you understand a little bit more about metadata, let’s consider metadata in the context of business intelligence (BI). The typical BI system inserts a layer of metadata between the database objects and the report generation tools. This metadata layer can be used to further define the data from the database, change field formats or calculate entirely new values in new fields to be included in reports. For example let’s suppose that an ORDERS table from your ERP system contains the following fields:

XOORD#        Decimal(9,0) - numeric containing Order

XOITM#          Decimal(9,0) - numeric containing Item within the order

XOITEM          CHAR(50)      - character field containing an product that has been ordered 

XORDAT         Numeric(8,0) - numeric requested date in format YYYYMMDD (i.e. 2021-09-01)

XOSDAT         Numeric(8,0) - numeric ship date in format YYYYMMDD (i.e. 2021-10-01)

XOQTY           Decimal(9,0) - numeric containing the Quantity ordered 

XOUPRC        Decimal(9,2) - numeric containing the unit price per item 

XOUCST         Decimal(9,2) - numeric containing the unit cost per item 

Sometimes a database table was defined using field names plus either the TEXT or COLDHGs columns to add more meaningful descriptions to the fields; but perhaps NOT! In this case, the table does not include a user-friendly column name. Therefore, we could use the metadata layer to assign meaningful names to the fields. Something like this, for example:

XOORD#        “Order Number” or “Order No.”

XOITM#          “Item Number“ or “Item No.”

XOITEMD       “Item Description”

XORDAT         “Requested Date”

XOSDAT         “Ship Date”

XOQTY           “Quantity Ordered”  or simply ‘Quantity”

XOUPRC        “Unit Price” 

XOUCST       “Unit Cost”

Here’s an excerpt of WQ default metadata showing what was initially generated when the table was made known to Web Query:

FIELDNAME='XOORD#', ALIAS='XOORD#', USAGE=P10, ACTUAL=P5,

      MISSING=ON, $

    FIELDNAME='XOITM#', ALIAS='XOITM#', USAGE=P10, ACTUAL=P5,

      MISSING=ON, $

    FIELDNAME=XOITEM, ALIAS=XOITEM, USAGE=A50, ACTUAL=A50,

      MISSING=ON, $

Here’s a modified version showing the addition of a Title value giving each field a user readable name:

FIELDNAME=XOORD#, ALIAS=XOORD#, USAGE=P10, ACTUAL=P5,

      MISSING=ON,

      TITLE='Order,No.', $

    FIELDNAME=XOITM#, ALIAS=XOITM#, USAGE=P10, ACTUAL=P5,

      MISSING=ON,

      TITLE='Item,No.', $

    FIELDNAME=XOITEM, ALIAS=XOITEM, USAGE=A50, ACTUAL=A50,

      MISSING=ON,

      TITLE='Item,Description', $

Once a Title is added to all the fields, columns seen in the Web Query report, now contain an improved heading, and because these are defined in metadata, the report author didn’t have to make such changes manually, over and over. A report using the revised metadata is shown in Figure 3.

The headings look improved but notice how the Ship Date field, based on a legacy numeric column, is formatted. This is another opportunity where metadata can help as we will show below.

Making Data More Functional

Given our example table and fields, suppose the business requests a report, that shows the total or extended price for each item in the orders file? Extended price does not exist in the Db2 database table. In the past, a report using Query/400 or an RPG program would create a “work” field, to calculate the extended price as quantity * unit price. Using our database fields we’d create XOEXTPRC, with a formula like this:

XOEXTPRC = XOQTY * XOUPRC

But, the field would exist ONLY within that query or program that calculated it, right? Therefore, when the CFO subsequently asks for another report, depicting profitability of items in the orders table, the report writer making this next report would have to repeat the logic to extend the price, and cost as well, to calculate profit as the difference between extended price and extended cost.

XOEXTPRC = XOQTY * XOUPRC

XOEXTCST = XOQTY * XOUCST

XOEXTPRFT = XOEXTPRC - XOEXTCST

This repeated effort would be a waste of time and perhaps, in the case of more complex calculations, open up an opportunity for error. How can metadata help with this situation?

Within WQ, we can use metadata to extend the original data by redefining or creating new fields. Additional fields can be created using a Web Query DEFINE statement. We will define new numeric fields by supplying a name, type and length of the field, decimal places and also telling WQ to display the field using comma as a thousands-separator. In this case, we will use packed numeric, 13-digits with 2 decimal places, showing thousands with commas (P13.2C) in our field definition. Finally, within the TITLE definition, the use of a comma in the text tells WQ that the words “Extended” and “Price” should be on two lines.

Here is an excerpt of metadata to define the three new columns:

    DEFINE XOEXTPRC/P13.2C MISSING ON ALL=XOQTY*XOUPRC;

      TITLE='Extended,Price', $

    DEFINE XOEXTCST/P13.2C MISSING ON ALL=XOQTY * XOUCST;

      TITLE='Extended ,Cost', $

    DEFINE XOEXTPRFT/P13.2C MISSING ON ALL=XOEXTPRC-XOEXTCST;

      TITLE='Extended,Profit', $

Once these DEFINES are added, whenever anyone creates a new report using this data source for the orders table, they will see extended price, extended cost and profit fields, in the same representation and column heading, even though the fields are not in the original database table. This is an incredibly powerful concept common to most BI tools!

Fixing a Problem

Other situational uses of metadata involve changing the format of database column data to some other format or solving issues that may exist in legacy tables. Recall our two date fields in numeric format in our record. Older systems created prior to the addition of DATE data types often stored dates as character or numeric fields. Remember when you had to edit the month and day portions of a date to make sure they were valid? Today, database fields of type DATE are smart, preventing you from entering a bad date. In addition to being less functional than a DATE column, these older field types often used blank, zero or some other value to indicate lack of an entry. A DATE field is more desirable, because you can do more things with it, including date arithmetic, reformatting and decomposition into the year, month, day subcomponents.

Here is an excerpt of the original default metadata for my Orders table date fields:

     FIELDNAME=XORDAT, ALIAS=XORDAT, USAGE=P9, ACTUAL=P5,

      MISSING=ON, $

    FIELDNAME=XOSDAT, ALIAS=XOSDAT, USAGE=P9, ACTUAL=P5,

      MISSING=ON, $

Below are new lines using the DEFINE in conjunction with a Web Query function called DATECVT. This function (one of many), is used to modify date information from one numeric or character format into real date fields. In this case, we are switching the original fields from a packed eight-digit number to a DATE column and also providing display names via Title tags. The original number fields are still within the metadata, but these modified columns will add columns in YYMD format:

    DEFINE REQDATE_YYMD/YYMD MISSING ON ALL=DATECVT( XORDAT , 'P8YYMD' , 'YYMD');

      TITLE=‘Requested,Ship,Date’, $

    DEFINE SHIPDATE_YYMD/YYMD MISSING ON ALL=DATECVT( XOSDAT, 'P8YYMD' , 'YYMD');

      TITLE=‘Actual,Ship,Date', $

It’s important to note that Date columns can be represented in various ways within the metadata (MDY=090121, YMD=210901, DMY=01092021, MDYY=09012021, YYMD=20210901) depending upon the functions or format string utilized. In fact, additional formatting options can be used to influence slashes, dashes and even whether to include the month name or weekday name. Metadata allows standardization of date presentation or localization based on country considerations.

A Report Based on the Metadata

Once the metadata has been edited and saved, it becomes available to a report designer. The first step in launching the InfoAssist report designer is to select a data source. You then can see the available fields. In the report designer LivePreview, seen in Figure 4, you see a windows of available fields, how they are being used to apply sorting or summation in the queried data and the potential report layout.

The Result

When executed, a report containing the combination of original fields and fields created within metadata, yield the result, shown in Figure 5.

With metadata, there are numerous benefits to be had. In my next article, I’ll discuss additional features of metadata and how they can be utilized. The takeaway? Consumers of reports and business analysts designing new reports see the consistent definitions and hopefully spend less time by spending a little effort on metadata up front.

---