Friday, January 29, 2010

Modernize QMF/SQL to Web-Based BI - Automatically Convert!

With the 2010 January 28th announcement by IBM and Information Builders on a partnership to provide WebFOCUS as the web-based BI product for mainframe DB2 data warehousing, now is the time to retire your legacy QMF/SQL applications.

Your legacy QMF/SQL applications can now be integrated into your internal and client-facing web portals and accessed on-demand via a web browser.  QMF/SQL application output can be HTML, PDF, Excel, and other standard web-supported formats.  Your legacy reporting applications can be dramatically enhanced with web-based BI features.

Partner Intelligence's BI Consolidator for QMF makes the transition quick and easy. If you are like many mainframe shops, you have decades of legacy applications; converting those into modern web-based technologies seems like an impossible dream. No worries.  Somebody else has done the hard work for you already by creating an automated translation application.

Today, you can automatically inventory, scan, and analyze your QMF procedures, forms, and queries, along with mainframe JCL that runs them. That provides you quick information on the scope of a modernization initiative along with a generated roadmap to follow.
After that, you can automatically convert your QMF applications. Currently, we have the following features in place to translate QMF procedures into comparable WebFOCUS syntax:
  • Comments (--) are preserved and saved as WebFOCUS comments
  • RUN commands are translated into WebFOCUS EXEC commands (processing FORM and symbolic variables)
  • ERASE commands are translated into SQL DROP TABLE commands
  • SET GLOBAL commands are translated into WebFOCUS –SET commands
  • Performing SAVE DATA AS, PRINT, and DISPLAY commands with WebFOCUS features (PRINT is a work-in-progress)
  • BOTTOM commands are ignored

Thanks to WebFOCUS's SQL Pass-Through feature, the BI Consolidator for QMF can leverage your existing SQL routines. If your QMF SQL routines work effectively and efficiently today, they will continue to do so running within WebFOCUS. This dramatically reduces the risk of modernizing your QMF applications -- we keep the core processes exactly the same.
In addition, some additional tasks are being performed for the SQL queries:
  • Comments (--) are preserved and saved as WebFOCUS comments outside of the SQL query
  • SET CURRENT SQLID is being issued to establish user defaults
  • If needed, SET DBSPACE is being issued to establish default database and tablespace
  • If needed, PREPARE SQLOUT is being issued so that WebFOCUS Developer Studio can paint the answer set
  • SQL columns are parsed so that proper names can be referenced when producing output files and reports

The BI Consolidator for QMF can also translate your QMF forms into valid WebFOCUS syntax.
  • Basic form information is being saved and presented as comments within the translated program
  • If there is no QMF form, creating default print output using the SQL columns
  • Respecting the sort sequence of the 1) SQL ORDER BY and then 2) the Form BREAKs and GROUPs
  • Respecting the formatting rules in the Form
  • Converting CALC columns into WebFOCUS syntax
  • Converting most QMF aggregate functions into WebFOCUS syntax (AVERAGE, COUNT, FIRST, LAST, MINIMUM, MAXIMUM, PCT, STDEV, and SUM)
  • Handling BREAK, GROUP, and OMIT instructions
  • Handling matrix reports (BY and ACROSS)
  • Converting T 1110 column instructions
  • Converting T 1210 page heading instructions
  • Converting T 1310 page footing instructions
  • Converting T 1410 report footing instructions
  • Converting T 1402 grand total instructions
  • Converting Break instructions (QMF syntax prior to V3.1)
  • Applying stylesheet instructions to replicate LEFT, CENTER, and RIGHT instructions (issue with using multiple alignments on same line)
  • Converting QMF symbolics such as: &PAGE, &TIME, and &ID

The BI Consolidator offers some additional general features such as:
  • Interactive, one-at-a-time translation and mass, hundreds-at-a-time translation
  • Options to turn on testing features such as code display (ECHO) and turn off code execution (XRETRIEVAL)
  • Option to apply automatic page numbers
  • Applying stylesheets to the report output format
  • Adding WebFOCUS output features such as HTML, PDF, Excel, Active Reports, and runtime user selections
  • Adding pre- and/or post-process steps to the QMF job
  • When needed, adds APP commands (HOLD, PATH, and APPENDPATH)
  • Option to identify specific DB2 subsystems
  • Performs mass string changes during translation process (as an example, change DB2 output tables starting with PROD_ to TEST_ to facilitate testing)
  • Provides an application browser to view programs, stylesheets, metadata, etc.
  • Provides a facility to easily generate stylesheets

I would be remiss to not include some type of disclaimer. Currently, some QMF features might not be automatically translated into WebFOCUS. That doesn't mean that it can't be done technically -- we just have not automated the process yet.  Here are some current items that are being excluded from the translation:
  • Some aggregate functions in the QMF form, such as: CPCT, CSUM, STDEV, TCPCT, and TPCT (flagging as needing manual intervention)
  • Some symbolic variables such as: &ROW and &COUNT (waiting on actual client usage for examples)
  • Some detail formatting rules, such as on which line to place grand total
  • Embedded TSO commands in the QMF procs are currently flagged for manual intervention (no usage at clients yet; parsing/generation TBD)

No comments:

About Me

My photo

I am a project-based software consultant, specializing in automating transitions from legacy reporting applications into modern BI/Analytics to leverage Social, Cloud, Mobile, Big Data, Visualizations, and Predictive Analytics using Information Builders' WebFOCUS. Based on scores of successful engagements, I have assembled proven Best Practice methodologies, software tools, and templates.

I have been blessed to work with innovators from firms such as: Ford, FedEx, Procter & Gamble, Nationwide, The Wendy's Company, The Kroger Co., JPMorgan Chase, MasterCard, Bank of America Merrill Lynch, Siemens, American Express, and others.

I was educated at Valparaiso University and the University of Cincinnati, where I graduated summa cum laude. In 1990, I joined Information Builders and for over a dozen years served in regional pre- and post-sales technical leadership roles. Also, for several years I led the US technical services teams within Cincom Systems' ERP software product group and the Midwest custom software services arm of Xerox.

Since 2007, I have provided enterprise BI services such as: strategic advice; architecture, design, and software application development of intelligence systems (interactive dashboards and mobile); data warehousing; and automated modernization of legacy reporting. My experience with BI products include WebFOCUS (vendor certified expert), R, SAP Business Objects (WebI, Crystal Reports), Tableau, and others.