Tuesday, October 11, 2022

NEW Db2 Web Query Version 2.4.0 Coming Soon!

Today, Oct 11, 2022, IBM announced a new version of Db2 Web Query. Version 2.4.0 continues the on-going evolution of web query, now in its second decade of providing IBM i customers with an end-to-end analytics infrastructure – query/400 modernization, business intelligence reporting, data visualizations and dashboards, a data warehouse/data mart/data motioning tool, and more. Version 2.4.0 should be available by end of the year.

Planned availability for 2.4.0 is in mid December.  The new version 2.4.0 offers many new enhancements, some of which include:  

  • A new “Hub” home page makes it easier for users and developers to navigate to content and functions you work with. Use the toolbar icons to jump right to a specific task, or click on the big purple “+” button to get a menu to create NEW objects. 


While I'd encourage you to get to know the new HUB (EZ-Install TEST DRIVE is a great way to learn the new version), rest assured you can always revert back to the previous two versions home page if your not good at handling change! 

  •        Enhancements to the new visualization and dashboard builder called DESIGNER. Designer was introduced in 2.3.0 and provides a powerful and easy way to create highly visual interactive dashboards. Enhancements include closing the functionality gap between InfoAssist and Designer. This is NOT to suggest InfoAssist is going away!

New features include support for creating data in an excel table, adding more granularity to date picker prompts, and simplifying the laying out of containers as you’re assembling content into a dashboard.

Be sure to check out Designer video demonstrations at http://ibm.biz/db2wq-230-videos

  • Customized Portals! You now have the ability ability to create customized portals (entry points for content) for different organizations within the business, or even different clients that the business supports. Groups of (or individual) users can sign in directly to their commonly used dashboards and other content. 

         Note that customized portal function is only available in Standard Edition. 

  •  EZ-Report (available in previous versions) automatically generates a report (and an underlying “synonym” object required for the report) based on an SQL statement or over a file/table. An updated (and re-structured) EZ-Report utility makes it even easier to build a report in seconds and is now available to developers (previously only available to web query admins). A new workspace has been created to store several developer related utilities, including EZ-Report. 

  • EZ-Install is the recommended installation package to install web query for the first time, or to upgrade from previous versions (we do NOT recommend you install using the ESS DVD images). EZ-Install will be upgraded to support 2.4.0 installations and upgrade in Q1 of 2023. To request EZ-Install, send an email to QU2@us.ibm.com and including company name, serial number, and OS level. 

For a more detailed list of 2.4.0 enhancements, you will soon be able to check out the NEW FEATURES GUIDE for 2.4.0. Go to the web query wiki at http://ibm.biz/db2wqwiki, take the DOCUMENTATION link and look for New Features Guide! 

Note that version 2.4.0 is only supported on IBM i OS levels 7.4 and 7.5. And as a reminder, Version 2.2.1, that supported OS levels 7.1 through 7.4 will no longer be supported after January of 2023. In addition, IBM also announced that version 2.3.0 will go out of support a year from now (October 2023). Customers on 2.2.1 may want to consider moving directly to 2.4.0 sometime early next year to minimize multiple upgrade processes and get onto the latest version at the same time!

In addition, there are a few things that are outside of the scope of the 2.4.0 announcement but worth mentioning. 

  • IBM's Lab Services organization re-branded and merged with another IBM organization to become IBM Technology Services. The Db2 for i team within Technology Services offers expert consulting and skills transfer training around all things Db2 Web Query and Db2 for i. Check out standard offerings at http://ibm.biz/Db2TechnologyServices. Customers that want a FAST START to web query or are looking for “how-to” assistance with more gnarly issues can often leverage services vouchers for some no-charge consulting assistance. To find out if you have a voucher, send an email to QU2@us.ibm.com and include your serial number and we'll let you know. Don't leave money on the table!

  • Data Motioning - what does that mean? In a soon to come blog post I’ll focus on some interesting things we’re doing to create an automated, repeatable way to get data from Db2 for i into some external (often Cloud) service or database using the Db2 Web Query DataMigrator component. Think “data streaming to the Cloud”, with a low-cost, low-code IBM i centric architecture! Stay Tuned. 

  • Instant Insights using pre-built Machine Learning models!  If you recall, back in 2020 we previewed an ability within web query to leverage pre-built machine learning models for advanced data analysis. While it won’t make the 2.4.0 initial release, IBM plans to provide this function as part of the product some time in 2023. This is COOL STUFF. With the click of a button, send a sample of your data (securely) into a service that runs it through some pre-built ML models to provide insights you would otherwise not glean from reports. Use correlation, time-series, association and outlier type ML models to gain insights into your data in the form of auto generated charts/graphs that you can then easily add to a dashboard. We love data scientists, but…no data scientist required for Instant Insights!
Start your 2023 upgrade planning NOW and let us know if you need help with upgrade considerations! 

Tuesday, April 19, 2022

NEW White Paper on Options for Leveraging SQL in Db2 Web Query


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 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.