A couple of years ago I wrote a blog post on “To SQL or not to SQL,” related to the fact you can leverage SQL statements to define a data set to be consumed by a Db2 Web Query report or dashboard, or an ETL data flow. If you know SQL, and/or are comfortable enough with coding up SQL statements, you can add to your portfolio of hints and tips to tackle more difficult data processing, speed up performance, or convert from other SQL generation tools. By using SQL, you’re pushing the heavy data access lifting into Db2 and you can also create (or use existing) Db2 objects (views, stored procedures, user defined functions) that can be used by any application.
IBM Lab Services has used these SQL approaches to help customers migrate from other SQL based reporting tools. For some customers, migration of the queries into web query preserves their existing environment as they, in tandem, learn how to build new reports. This has even led us to create tools such as the Showcase Strategy conversion tool to simplify the migration of queries to Web Query. If you are interested in learning more about this, reach out to us at Qu2@us.ibm.com.
A new white paper has been posted to the Web Query Wiki called “Creating Synonyms and Report in Db2 Web Query using SQL Statements.”
The direct link to the white paper is here.
As with most things Web Query, there is often (almost always?) a couple/three ways to approach any “how do I” type question. In this case, it is at least FOUR ways to do this! It might seem like an overwhelming set of choices and the paper tries to articulate which one might be right for you based on circumstances or requirements.
The following depicts the various methods covered in the white paper.
A brief description of each follows. We recommend using Access Client Solutions’ (ACS) Run SQL Scripts function to create/test and save your SQL. Its formatter, syntax checker, and “insert from examples” content can really help to first make sure your SQL is working like you’d expect, and THEN bring it into Web Query in one of the manners noted below.
EZ-Report
EZ-Report is a utility within the IBM Db2 Web Query Information workspace (note this workspace location could change in the future) that provides a very fast way to auto-generate a Synonym and Report from an SQL Statement (or over a file/table). See video demonstration of it at at this link.
With EZ-Report you automate the creation of a Web Query text file containing the SQL statement, the creating of a synonym over that text file, and a report. EZ-Report basically automates the process described in the next method “SQL Text File Approach” described below.
Within minutes (seconds?) EZ-Report has created a report and associated Synonym (meta data object). You can use the report as is, or choose to enhance it (change color scheme, add a prompt, give the user option of output format at run time, schedule it to run in the background and sent out, etc.).
Along with the white paper noted, there also exists a user’s guide for EZ-Report here.
Bring SQL into Web Query Text File
The SQL Text File approach is more manual in nature than ez-report, although the manual steps to create a synonym and report are fairly simple and quickly accomplished. The objects created in this approach are the same as what EZ-Report creates for you, but you have more flexibility in how you create the synonym and report.
With this approach you should still consider using ACS to develop/test your SQL, but once you are comfortable its working as designed you can open up a text file editor in Web Query and paste your SQL statement there.
The White Paper walks you through steps to create a synonym and report.
What if you need to alter your SQL statement created with EZ-Report or with this Text File approach? You can come into this web query editor and make your changes here. Depending on the change (for instance, adding a new field) you may need to REFRESH the synonym you built (or was built for you with EZ-Report) to pick up the new field in your report.
With EZ-Report and the SQL Text file approach, all your work is being done (and saved) in Db2 Web Query. This can be beneficial for some shops that might not allow Web Query users to create Db2 objects for security or version control reasons.
SQL View Approach
The SQL View approach is very similar to the SQL text file approach. The real difference is you are creating a Db2 VIEW object that contains your SQL statement (rather than putting your SQL into a Web Query text file) and then building a synonym over your view.
An obvious (?) benefit of this is that you can share this view with other applications if desired, and you can maintain it as part of your IBM i change/version control packages if that is a factor.
We would again recommend using ACS to develop/test your SQL statement as well as wrapping that SQL statement with a CREATE OR REPLACE VIEW to actually create the view object in Db2 Here is an example: