Monday, February 19, 2018

To SQL or not to SQL - with Db2 Web Query

Sometimes I find myself talking out of both sides of my mouth when discussing Db2 Web Query capabilities. The term is related to contradicting yourself – maybe saying one thing to one person, and another, opposite thing to another. 

While certainly not trying to deceive anyone, the association with Db2 Web Query is about whether you need to be an SQL programmer to use it. The short answer is, ABSOLUTELY NOT. However, out the other side of my mouth, I have to say, it certainly can be an advantage if you do know SQL!

Db2 Web Query provides a graphical interface to building reports, dashboards, and BI applications. With the metadata interface simplifying the database structures for report authors, the graphical interface is easily used by those other than programmers and database experts. The “Business Analyst” is a classic power user of Db2 Web Query, building reports from scratch and never having to code at an SQL or RPG level to accomplish their goals – because under the covers Db2 Web Query generates the necessary code to access the data and provide the report logic, formatting, etc. 

While we strongly discourage you from editing any of that code, you can see what is generated by right clicking on a report or chart and choosing (if you have authority) to open with text editor. The code probably won’t make any sense to you, and you definitely DO NOT want to edit anything there as it might mess up the execution of the report. The code is stored in IBM i in something we call the repository. 

Many people have asked the Db2 Web Query team if you can write your own, or leverage existing SQL code in a report. So out the other side of my mouth I say “Absolutely!”  Reports, charts, and dashboards can contain data from many different data sources. Db2 file/tables are the most obvious data source, but did you know you can also leverage existing Query/400 definitions, SQL Views, SQL (or any HLL) stored procedures, Db2 functions (including user defined functions), and uploaded SQL scripts? You can also upload Excel spreadsheets and with Standard Edition, access non Db2 databases such as MySQL, Postgres, and just about any relational database you can get a JDBC driver for. 

A good example of leveraging the power of using SQL as a data source for reports is one I’ve been showing off lately to simplify some pretty complex reporting requirements. The examples use new IBM i 7.3 OLAP extensions to the SQL language. These new capabilities in the SQL language make it simpler to code, and will perform extremely well.

Suppose you are in Human Resources, and you want to regularly check salaries of your employees to ensure that are in line with corporate policies. For instance, maybe all employees need to be within an average for their department, or within a department you don’t want one employee to be an anomaly compared to others – either way higher or way lower. You might want to answer these kinds of questions: 

  •        What is the employee’s salary compared to the average WITHIN their department?
  •        What is an employee’s ratio of salary within their department and overall company?
  •        For each employee, can you compare their salary to the two closest behind this person’s salary, and the two closest ahead of them in salary? 

While a couple of these might be easily answered by using the graphical interface to generate the necessary code, that third bullet is really going to make you think about how you might do that. It might require the age old approach of using a work file. Db2 Web Query supports the work file concept using something called HOLD files, all handled within a single report. Within a report, you can run your query, generate a HOLD (or temporary) file, and then have the report complete by querying the HOLD file. So while do-able, it is a lot of work to build this kind of report, and the report itself will take a while to run if its having to do multiple passes of the data. 

An alternative to simplify the creation of, and speed up the execution of the report is to leverage SQL capabilities in 7.3. You can read more about these enhancements in Mike Cain’s blog on database enhancements in7.3. For my example, I used new “Partition By” syntax to create logical windows looking into the data and then performing aggregation functions or OLAP functions such as LAG and LEAD against the data in each window. 

The first step (after learning a bit about how these new 7.3 functions work) is to build the SQL to get an average of salary across each employee within each department, and secondarily, get a view of each employee’s salary compared to the two closest salaries (still within their department) both above and below theirs.

Using my favorite SQL Scripting tool, Access Client Solutions (ACS), which you can download for free here, I created these two scripts below that create VIEWs with my select statement (I could have chosen to just create the scripted select statement instead of putting it into a VIEW object, and then uploaded that into Db2 Web Query). 

To use these now in a report you need to create the metadata (called a synonym) over your view or uploaded script. The steps are pretty close to the same whether it’s a view or an uploaded script (you’d choose External SQL Scripts rather than “Tables, Views, and Other Objects").  

Once your synonym is built and tested (use the SAMPLE DATA option within the synonym editor) to run the SQL statement and validate you get the data you were expecting you’re ready to build your report, chart, or visualization:

Note that I added a filter so the “salary analyst” can select different (and multiple) departments or look at all employees. The prompt is automatically built and available when you run the report. 

Now let's create a drill down with the first SQL View I created. Here is an average salary report with an auto drill down on any of the department values to link to a chart for just that department’s employees: 

Each of these reports probably took at most about 15 minutes to develop using the new 7.3 SQL functions and Db2 Web Query. And when I run the reports the response time is sub-second. You’d be hard pressed to gain that kind of productivity and response time using the workfile approach! Also, if your SQL View ever changes, all you need to do is refresh your synonym.

There are many other examples of using SQL within Db2 Web Query if you are comfortable with it. For instance, a couple of weeks ago I was with a client that had already written some user defined functions and was interested in leveraging those to “un-tangle” data in their reports. Why re-invent the wheel?

In a recent prototyping exercise to demonstrate how you can integrate Db2 Web Query with Watson, our team leveraged an SQL Stored Procedure to build a weather augmented data mart using Db2 Web Query as the foundation for data gathering and visualization of the consolidated data.

Some customers using the old Showcase query builder tended to write SQL statements, and find they can leverage those same statements within Db2 Web Query to provide the same data access but enhance the output with the many ways to display the result set, i.e., leveraging already existing SQL!

SQL is fun and easy to learn – but you don’t have to and can let the graphical interface generate the appropriate code. No more talking out of both sides of my mouth (on this topic anyway)!