IBM i > ADMINISTRATOR > DB2

Get a Handle on Metadata

Hands-on details for using DB2 Web Query metadata


In the September IBM Systems Magazine, Power Systems—IBM i edition cover story, “Untangling Web Query”, I explained why DB2 Web Query uses metadata and how it can actually reduce the complexity of your query and reporting environment and make life easier for your report developers. In that article, I told you about the benefits of using metadata. In this article, I want to share more details on how to take advantage of those benefits with more hands-on details.

What Is Metadata?

To refresh your memory, metadata is simply data about data. DB2 Web Query metadata is a materialized repository that contains information about your database files. Before you can create a report or graph in DB2 Web Query you must first create metadata (also referred to as synonyms) over the data sources. You can create a synonym over such database objects as tables or physical files, SQL views, DDS logical files, stored procedures and materialized query tables.

You can generate metadata in three ways:

  1. Create your own. Metadata creation wizards are available from both the Web browser via the Web Query launch page and in the DB2 Web Query Developer Workbench tool. To create metadata, you must be a Web Query administrator (member of group profile MRADMIN) or a developer in the domain. This is the most common way metadata is created.
  2. Use third-party tools. Three good examples of vendors who’ve enabled their technology for DB2 Web Query are Databorough’s x-WebQuery, Information Builders’ iWay Data Migrator and Coglin Mills’ RODIN DB2 Web Query Edition.
  3. Find an ERP or services provider. ISVs have the capability to include DB2 Web Query content as part of their solutions package. If you purchase such a distribution, the metadata will be included in the package. Similarly, a services provider may have the expertise and tools to build the metadata for you.

Synonym Editor

In the previous article, I shared that the DB2 Web Query Developer Workbench tool, a Windows PC-based tool, offers several powerful components, one of which is the Synonym Editor. The Synonym Editor contains graphical interfaces that let you:

  • Define database joins
  • Build virtual columns to centralize business logic
  • Standardize column formats
  • Convert and standardize date fields
  • Create filters
  • Create business views
  • Define online analytical processing (OLAP) dimensions

You read the benefits of these. Now let me walk you through how to use them. For more information on how synonyms affect the IFS, read the sidebar, “Two Stream Files.”

Define Database Joins

If you’ve developed reports with Query/400, you’re aware that if the query requires more than one file, you must define your joins in each query definition. Metadata lets you define your joins in one place and base all of your reports on that single, centralized definition.

Using the Synonym Editor in Developer Workbench, you can add new join segments from existing synonyms or files that simply need to be brought into the cluster (and don’t require standalone synonyms). Figure 1 shows how to add a new join segment.

Once you’ve added a join segment to a synonym, you must define the join fields by right-clicking on the new segment and selecting “Join Properties” which results in a dialog box where you can select the join fields from each segment as shown in Figure 2.

When you’re finished adding all your join segments, the Synonym Editor will group the columns under their respective segments and provide an interface from which the segments can be expanded and collapsed as shown in Figure 3.

The Synonym Editor also provides a graphical representation of the model in the Modeling View tab (see Figure 4). You can double-click on any of the segments to show the column information and sample data for that particular file.

After saving the synonym, use the DB2 Web Query development tools such as Report Assistant and select the synonym as the data source. When Report Assistant dialog is presented, all of the columns in each of the join segments can be used in the report. Because the join segments have been defined in the synonym, you won’t need to define them again in the report.

Gene Cobb is a DB2 for i technology specialist in the IBM i ERP development team.



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