Make your database easy to read

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:

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:

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 i

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Web Development Migrating from RPG to EGL on IBM i Groovy programming on IBM i Running PHP open source applications: NOBODY needs authority Zend Web software teams up with IBM System i The best technologies and tools for System i programmers in 2009 Seven IBM i project lessons learned in 2008 AS/400 lessons from the past, present, and future: A holiday tale Application modernization strategies for System i RPG application modernization for i5 Web skills crucial to iSeries programmer professional development Web Servers System i no longer the stepchild of IBM's world Connecting WebSphere to AS/400 for image retrieval Weaving in WebSphere SOA enhancements drive IBM WebSphere feature packs SOA means money for IT workers Enable J2EE app on WAS to access DB2 Securing Apache: Keeping patches current IBM runs USOpen.org on Power boxes MoMA's IT makeover a mix of old and new How does RPG talk to a browser? Web Leverage IBM's Enterprise Identity Mapping (EIM) Build Web apps using free utility -- WOW! Q&A: Pluta touts benefits of RPG over Java Take their money and run: Processing Internet credit card payments with Java, WebSphere Three tips for using WebFacing WebFacing v5.1.2 may save a lot of money Get the lowdown on Eclipse and WebSphere, including pros and cons Optimizing Tomcat for Web applications Insourcing beyond outsourcing Building dynamic JDBC components and utilities

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary WebSphere Development Studio Client (WDSC)  (Search400.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

s a typical data mart implementation process we've done for customers:

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:

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.

Display File Field Description
Input parameters                                          
  File  . . . . . . . . . . . . . . . . . . . :  BUAVRELW 
    Library . . . . . . . . . . . . . . . . . :  MESCMFIL 
File Information                                          
  File  . . . . . . . . . . . . . . . . . . . :  BUAVRELW 
    Library . . . . . . . . . . . . . . . . . :  MESCMFIL 
  File location . . . . . . . . . . . . . . . :  *LCL     
  Externally described  . . . . . . . . . . . :  Yes      
  Number of record formats  . . . . . . . . . :      1    
  Type of file  . . . . . . . . . . . . . . . :  Physical 
  SQL file type . . . . . . . . . . . . . . . :  TABLE    
  File creation date  . . . . . . . . . . . . :  09/12/04 
  Data        Field  Buffer    Buffer        Field    Column
  Field      Type       Length  Length  Position        Usage    Heading
  AVNMID     ZONED        9  0       9         1        Both     AVNMID
    Allows the null value                                              
  AVA7CD     CHAR            2       2        10        Both     AVA7CD
    Allows the null value                                              
    Coded Character Set Identifier  . . . . . :     37                 
  AVPZCD     CHAR            2       2        12        Both     AVPZCD
    Allows the null value                                              
    Coded Character Set Identifier  . . . . . :     37                 

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.

"CREATE VIEW BUAVREL2 (STUDENT_ID_NMID FOR AVNMID, " +
" OPEN_AMOUNT_CENB FOR AVCENB) " +
"AS SELECT BUAVRELX.AVNMID, BUAVRELX.AVCENB FROM MESCMFIL.BUAVRELW BUAVRELX";

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:

Display File Field Description   
Input parameters                                             
  File  . . . . . . . . . . . . . . . . . . . :  BUAVREL2    
    Library . . . . . . . . . . . . . . . . . :  *LIBL       
File Information                                              
  File  . . . . . . . . . . . . . . . . . . . :  BUAVREL2    
    Library . . . . . . . . . . . . . . . . . :  QWDU        
  File location . . . . . . . . . . . . . . . :  *LCL        
  Externally described  . . . . . . . . . . . :  Yes         
  Number of record formats  . . . . . . . . . :      1       
  Type of file  . . . . . . . . . . . . . . . :  Logical     
  Field Level Information                                                           
  Data        Field  Buffer    Buffer        Field    Column           
Field      Type       Length  Length  Position        Usage    Heading          
AVNMID     ZONED        9  0       9         1        Both     STUDENT_ID_NMID  
Alternative name  . . . . . . . . . . . . :                                   
STUDENT_ID_NMID                                                           
Allows the null value                                                         
AVCENB     PACKED      11  2       6        10        Both     OPEN_AMOUNT_CENB 
Alternative name  . . . . . . . . . . . . :                                   
OPEN_AMOUNT_CENB                                                          
Allows the null value                                                          

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.

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