Make your database easy to read

Jim Mason

If you're like many companies your database was really an after thought. Often, the focus was on the application: file maintenance programs, transaction programs, custom reports and so on. You either built a database to support your applications in-house or bought the database as part of an application package. Either solution was expensive.

But what about the database design? In many cases, the database wasn't logically structured or easy to read for user access. You could also have "old" databases left over from the days of DDS and RPG when field names had to be restricted to six characters in length inside a program -- not very useful for users trying to create custom reports and queries.

Options to make data usable

There are many things you can do to create "smarter" databases that save users LOTS of work and provide much better information. Here are two options most companies can't afford:

• Buying six-figure database tools to restructure the database.
• Hiring a six-figure database architect to re-engineer the database. (Actually, this is the best answer, but there aren't many good ones out there.)

Option: Use DDS to redefine files

Traditionally, many iSeries shops spend a lot of time re-defining DDS source for files adding column headings, text or alias keywords to provide more meaningful names for fields in your files. Some database reporting tools such as Query/400 will use these enhanced field descriptions, making it easier to read the data when building and running reports.

Is that a good solution? Normally, it's not as good as a data mart. Converting the database is a large issue: compile a new set of files from DDS, recompile your application programs over the new files, extract, transform and load the new files from the old ones.

Option: Create simple data marts for users

Creating a separate data mart offers many advantages:

• A "reporting" data model that presents a logical data model to users
• Table column names that are easy to read for users
• The existing application data model stays in place for good performance
• Better performance for queries and reports
• Lower costs to build and maintain reports and queries using tools
• Extraction and transformation of data to use in other tools, applications

Creating a data mart is relatively easy on the iSeries. We've created some tools that help automate the process. While the details of building a data mart are beyond the scope of this tip, this is a typical data mart implementation process we've done for customers:

• Create a separate library for the data mart
• Automate replication of static and summary to the data mart
• Create SQL views to deliver critical production data real time to the data mart
• Selecte affordable, easy to use report, query and data extract tools
• Integrate user queries and reports in Web applications
• Create Web services from queries as needed
• Train the data administrator on the administration tool (iSeries Navigator)
• Train the users on the data model and the query, report and extract tools

Given the data mart is logically organized and readable, training users on where their data is in the data model should be an easy task now.

SQL views make production data readable

Let's focus on SQL views. They can do more than DDS to make production data easy to read at a lower cost. Using SQL views you can do the following:

• Create a logical data model for users from your production data files.
• Select columns from different tables and join the tables logically to create a view.
• Rename the columns to make them easier to read.

While a DDS Join Logical File can do a lot of that, it lacks all the power of an SQL View. SQL Views can be created using complex SQL Select critieria. The SQL views can be accessed for any purpose you need: reporting, data extracts, applications and more.

The nice part about the SQL view is that you don't have replication concurrency data issues. All the data is stored in one table physically. The view, like a more powerful logical file, provides a dynamic, alternate view of the data to the user. I often hear users complain they need access to the "real" data, not a copy. SQL views do this nicely.

Going from DDS to an SQL view

Below is a very simple definition of some fields in a physical file shown using the DSPFFD command. You can see the field names (AVNMID, AVA7CD, AVPZCD) are logically meaningless to a user, making database use difficult.

We'll create a simple SQL view (no joins, select criteria, etc) to make the data readable. Here's the SQL for our view, BUAVREL2. Notice a view contains an SQL Select statement, which makes it a very powerful tool for modeling a database. In addition, the columns list includes an optional FOR clause allowing the underlying (system) column name to be redefined with a more meaningful name.

Here's the SQL we generated from our tool. Notice we named the view, redefined column names to be easier to read (AVNMID becomes STUDENT_ID_NMID and so on) and based the view on a table in another library using a full Select statement.

I created the view in the data mart library (QWDU). That way, you only have to authorize user group profiles with read access to the data mart library, protecting the production database from unauthorized user access.

Running the DSPFFD command on the view above you can see the column names are much easier to read now:

Here's a simple report created using "drag and drop" report tools using the SQL view and other tables.

The nice part is this reporting tool allows me options to save the report in many ways: as an Excel spreadsheet, a PDF file, an HTML page, raw text, an XML file and more.

iSeries Navigator can define SQL views

Using IBM's iSeries Navigator's database plug-in is normally a better option for database administration. It's free and it's easy to use with drag and drop support. Here's a screen shot of iSeries Navigator's GUI interface to define a database view. This example shows a complex join of three Student files (master, name and address files) by student id. The lines linking the tables were created by dragging a column from one table and dropping it on the column from another table.

Summary on easy data access

Using SQL views, it's not hard to create data definitions that are easy for users to read. Building a data mart allows users to "get at" the real data in an easy-to-read model with powerful query tools. Using the iSeries Navigator database plug-in makes it easy to create database definitions. Good, affordable, drag and drop reporting tools make it easy for users to build their own reports on iSeries data you've published to the data mart. All of this works well on a standard edition iSeries server. There's no need for an expensive enterprise edition software package or the DB2 database extender tools.

What's the net impact of our simple data mart? Easy user access to data, no programming needed and no expensive software to buy! A win-win-win situation.

---------------------------------------