Announcing: New Version of Db2 Web Query EZ-Install with NEW EZ-Report!

This month we have updated our EZ-Install package with some new goodies along with PTF Group Level 8! If you recall, we HIGHLY encourage customers and business partners to use our simplified installation package for Db2 Web Query. This is great for new installations or for upgrading from previous versions. If you’re already running Version 2.2.1, then you don’t need EZ-Install, and can just apply the new PTF Group (more on this later)!

Remember that to request EZ-Install, send an email to QU2@us.ibm.com supplying your name, company name, and serial number where you plan to install.

In this drop of EZ-Install, we’ve added or changed some things I think you’ll really like:

•  Synonym documentation and a text search utility for reports, synonyms and more
•  New user interface for working with data (adapters, synonyms)
•  Auto generate synonym and report using EZ-Report

And a few more subtle things like the ability to specify the order of values in a prompt field. For instance, if you have 20 years of data and are using ORDERDATE_YEAR in a prompt (filter), you might want to sort in ascending or descending order. Now within InfoAssist you can specify this on the FILTER EDIT dialog box.

Synonym Documentation and Text Search

Current Db2 Web Query developers have asked if there was an easy way to document the fields (including derived fields) in a synonym or set of synonyms. Synonyms are the meta data objects that provide the perspective of your data source to report authors. The documentation in the form of a report makes it easier for end users to know which synonyms contain the fields of interest as they create new reports.

 Another request we have gotten from time to time is the desire to search a Db2 Web Query object (Synonym, the report code, an HTML page or a Schedule) for some key word or field usage. The new SEARCH function will allow a search of any text string with prompts to decide which objects you want to search. Technically speaking these two new utilities are part of the product itself (not an EZ-Install add-on) so current clients running 2.2.1 can obtain these utilities by applying the PTF Group Level 8 plus some additional PTFs (refer to the "PTFs and On-going Service" document on our wiki and look for post Group Level 8 PTFs). 

New User Interface for Working with Data

If you're familiar with Db2 Web Query, you know about the synonym editor and configuring adapters. Over time, more and more of the meta data editing functions only available in Developer Workbench have been moved to the browser interface. With PTF Group Level 8 (actually, this appeared in Level 7) you'll notice a change in the browser user interface, and I think you'll like it. This change is the inital roll out of an on-going project to provide better usability and consistency across the product line.

You will hopefully enjoy the easier navigation and wizard like "take me to the next step" approach the UI team has been implementing. The EZ-Install TEST DRIVE (the tutorial that comes with EZ-Install) is also updated to reflect how to build your first synonym with the new UI!

EZ-Report Makes it….EASY

A new utility is provided under the utilities folder within IBM i Administration Samples folder. The new EZ-Report is….EASY to generate both a synonym (required for a report) and a report itself.

Use cases for EZ-report include:

• Use SQL statements to auto create a quick synonym and report
• Build reports quickly over IBM i Services using examples in ACS
• Build a quick report over a remote file with Security Compliance Tool's “Remote File Get”
• Conversion Tools (for example, converting Showcase Strategy queries to Db2 Web Query) 

Db2 Web Query is a code generator. You use the GUI to simplify creating/editing synonyms and reports, and it will generate the necessary code (including SQL) to execute and render the report. But sometimes business analysts or developers want to just use their knowledge of SQL (or in the case of IBM i services, already prepared SQL) statements for their query/report. In this blog post I show you ways of doing that through placing your SQL into an SQL View and then creating your synonym (and subsequent reports) over that synonym. You can also save your SQL statement into a text file with .sql extension, upload it into Db2 Web Query and do the same (build your synonym or reports). 

Let's use an IBM i Service as an example of a free form SQL statement you want to build a report over. In this post I discuss all the sample reports over IBM i Services we have been including in EZ-Install. These were all done with the above process using these SQL interfaces to systems information that was previously only available via CL commands or APIs.

HELLO EZ-REPORT!  With the new EZ-Report utility you can simplify the steps of creating a synonym and report and create a report in a manner of minutes if not seconds. Let’s use an IBM i service as an example. There are a wealth of SQL coding examples over IBM i Services in Access Client Solutions (ACS). If you don’t have ACS by now, you really should. You can download it from here.

Using ACS' Run SQL Scripts we can grab any sample SQL but in this case an example of finding all users on a system with *ALLOBJ authority.

I actually edited the SQL a bit - the example was only returning 5 fields but there are over 50 so I didn't want to limit my ability to report on any of the 50. I also took out the ORDER BY because I want that to be a function of the report, not the SQL statement. Once I've tested the SQL statement in Run SQL Scripts, I simply COPY the statement and pop over to the Db2 Web Query browser window and PASTE it into the prompt for EZ-Report.

Click on the run button and under the covers EZ-report will build both the synonym (required for the report) and the report itself! And BOOM, we have a report!

Now that you have a report there are many things you can do with this, including:

• Schedule it to run on a regular basis and email it out to a distribution list or send to a network drive
• Add it to a user profile metrics dashboard
• Use the power of an analytical document to work with the data views in this report (filter, sort, hide columns, export to Excel, create a chart over a subset of the data, etc.)
• Edit the report. Add a header or footer, change the color scheme, add some prompting, etc. 
• Create links or drill downs to other reports or charts
• And most importantly, take action on the information gleaned from the report 

EZ-Report also created an underlying synonym, and there are ways you can leverage that as well such as: 

• Adding some derived fields not returned from the SQL Statement
• Build powerful visualizations over the data
• Join the result set with other data sources like the EZ-Install date dimension table 

While in the above example I used an IBM i Service, how can EZ-Report help your business users?

In this blog post concerning the use of SQL and Db2 Web Query, I used an example that is more business oriented.  There are times where leveraging the SQL language can make difficult data relationships much easier using SQL than trying to figure out how to do this in InfoAssist. The example of solving a difficult reporting problem using Db2 OLAP extensions in SQL combined with EZ-report gives you both the power of a simple SQL statement to deal with challenging data relationship issues and the ability to have a report in MINUTES! 

In this case, consider how you would deal with a request from your Human Resources department to show a report that ....for every employee shows the two salaries that are closest but UNDER the employee's salary, and the two closest but OVER the employee's salary. 

Consider how you would do that in RPG. I'm guessing some temp table approach with multiple passes through the data. Not only would the OLAP extension + EZ-Report approach be completed in a fraction of the time, but the report response time from the auto generated report will SCREAM since we're pushing the work to Db2!

EZ-Install with EZ-Report is EASY!  But we're not done yet, stay tuned for more things to come in 2020!