let’s put db2 web query to work!

IBM DB2 Web Query for i Easy to use, powerful database queries for DB2 on IBM i

LET’S PUT DB2 WEB QUERY TO WORK!

Today’s Agenda

Introductions

What is “Business Intelligence”?

Why DB2 Web Query?

Some Helpful DB2 Web Query Product Info

Question and Answer Session


LET’S PUT DB2 WEB QUERY TO WORK! INTRODUCTIONS

ResiliEnt Business Solutions - As specialists in the delivery of high-value

Business Intelligence applications and Data Warehouses, ResiliEnt Business

Solutions has helped organizations across the United States bring their data

to life via the Web!

ResiliEnt offers a full range of professional services involving IBM’s DB2

Web Query, Cognos, WebSphere Business Events and DataStage product

lines along with expertise in a number of vertical markets such as

Manufacturing, Retail, Healthcare, Financial Services, Insurance, Sales and

more.


LET’S PUT DB2 WEB QUERY TO WORK! INTRODUCTIONS

DB2 Web Query is fairly new, but it is based on proven technology

from an IBM Business Partner with a long track record of creating

industry-leading Business Intelligence products.


LET’S PUT DB2 WEB QUERY TO WORK! WHAT IS “BUSINESS INTELLIGENCE”


LET’S PUT DB2 WEB QUERY TO WORK! WHAT IS “BUSINESS INTELLIGENCE”?

From Wikipedia, the free encyclopedia

Business Intelligence (BI) refers to skills,

processes, technologies, applications and

practices used to support decision making:

BI technologies provide historical, current, and

predictive views of business operations.

Common functions of Business Intelligence

technologies are reporting, online analytical

processing, analytics, data mining, business

performance management, benchmarking, text mining, and predictive

analytics.

Business Intelligence often aims to support better business decision-

making thus a BI system can be called a decision support system (DSS).


1. A business intelligence tool to . . .

Bring your data to life via the web with menus, parameter-driven reports,

drill through, graphs, HTML/excel/PDF output and more.

2. Funding to invest in . . .

Planning and readiness, product & product

options, development services, training.

3. A good set of partners to help you with . . .

Implementation, development, mentoring and

ongoing support.

4. Questions that you need to answer accurately and quickly, or a real

information problem you need to solve, for instance . . .

Sales reporting (e.g. Top/bottom reps, what they are selling, cost of sale),

financial reporting (e.g. revenue and expense reporting), business

performance reporting (e.g. inventory metrics, cost of labor). The

possibilities are endless!

LET’S PUT DB2 WEB QUERY TO WORK! WHAT DO YOU NEED TO GET STARTED?


LET’S PUT DB2 WEB QUERY TO WORK! WHAT DO YOU NEED TO GET STARTED?

1. A business intelligence tool . . .

IBM’s DB2 Web Query is one of the few true BI tools that runs entirely on

the i Series

2. Funding to invest in . . .

Product – IBM is assisting you with this one as DB2WQ is bundled at

no cost if Query /400 is already licensed on the platform

Consulting – Quick start program, development and staff mentoring

3. A good set of partners to help you be successful . . .

ResiliEnt + your IBM Reseller

4. Questions that you need to answer accurately and quickly, or a real

information problem you need to solve . . .

Homework assignment – Determine where to start with BI: What

questions need to be answered and what business problem needs to be

solved?


LET’S PUT DB2 WEB QUERY TO WORK! WHY DB2 WEB QUERY?


LET’S PUT DB2 WEB QUERY TO WORK! BUSINESS CASE FOR BI

Improved and more timely decision

making

Improved operational efficiency

Faster development of reports

Lower cost of development and

support

Self-service information to the end-

user community

Distribution of information via the

Web makes info available by anyone

inside or outside of the company

Aids business expansion and

growth



Monmouth County NJ Sheriff’s Office

BI Opportunity

How do you reduce cost of staffing an inmate

information system for the County while

improving service levels to the community?

BI Solution

Reduce costs by making inmate queries

available from a public website. The move

eliminated the cost of staffing a 1-800 number

for the same info and improved service to the

community by making the information

available on a 24 x 7 basis.

www.app.com



Vehicle Lighting Fixture Manufacturer

BI Opportunity

Six manufacturing plants. Each supervisor

knows how their plant is performing, but roll-

up of performance metrics at the corporate

level is not visible for 45 days after month-

close. That’s too long!!

BI Solution

Plant Management Dashboard is deployed

over a quickly formed warehouse and rollup of

performance metrics are displayed graphically

each morning. That’s a stunning

improvement!!


LET’S PUT DB2 WEB QUERY TO WORK! QUERY / 400 LIMITATIONS

Limited to green screen or print output

No graphing capabilities

No report chaining / drill-thru

Declining skill base

Expensive cost of labor

No GUI Workbench for developers

No Ad hoc assist or OLAP for end-users

No HTML/PDF/Excel output

The list goes on!!

No BI Tool Here!


LET’S PUT DB2 WEB QUERY TO WORK! QUERY / 400 GREEN SCREEN


LET’S PUT DB2 WEB QUERY TO WORK! DB2 WEB QUERY ADVANTAGES

From IBM’s DB2WQ Redbook abstract:

“The DB2® Web Query for i product is a Web-based query and report writing product that offers enhanced capabilities over the IBM® Query for iSeries® product (also

commonly known as Query/400).

IBM DB2 Web Query for i includes Query for iSeries technology to assist customers in their transition to DB2 Web Query. It offers a more modernized, Java™-based

solution for a more robust, extensible, and productive reporting solution.

DB2 Web Query provides three main components: Report Assistant, Graph Assistant, and Power Painter with the base product. Using these tools, customers can

modernize existing Query for iSeries reports while providing a foundation for building more complex business intelligence applications, such as online analytical processing

(OLAP), data mining, dash boarding, or data warehouse implementations.”



Get Better Performance

Up to 5x performance improvement

Reduce Report Maintenance

Leverage parameterized reporting, ad hoc & OLAP functions to minimize report maintenance

Remove Dependency on IT for reporting purposes and deliver INFORMATION in real time

Give the power of the data to end users in the formats they want

Upgrade into full blown Business Intelligence

Great on top of a Data Warehouse!

Green Screen to Dream Screen!!


LET’S PUT DB2 WEB QUERY TO WORK! GREEN SCREEN TO DREAM SCREEN


Key Performance Indicators

Parameter Driven (if desired)

Linked Reports

Very Customizable

No Programming Required - Use HTML Layout Tool

LET’S PUT DB2 WEB QUERY TO WORK! FINALLY YOU CAN DELIVER DASHBOARDS!



From IBM’s DB2WQ Redbook abstract:

“DB2 Web Query offers features to import Query/400 definitions and enhance their look and functionality with Power Painter or Graph Assistant.

It enables you to add OLAP-like slicing and dicing to the reports or to view reports in disconnected mode for users on the go.

It provides an interface to all data in IBM i5/OS® through either DB2 or Open Query File native adapters that automatically identify the files to be accessed and import the

metadata into DB2 Web Query.”


ADD-ON OLAP FEATURE


Standardize Field/Column Formats E.g., Use commas, set currency symbol,

suppress leading zeros

Standardize/Decompose Date Fields E.g. Integer defined as MMDDYYYY

Create Filters E.g. Define a set of countries as “Europe”

Define JOINS and Data Transforms E.g. Apply an Edit Mask to pull first three

characters from a field.

Create Business Views E.g. Organize Columns/fields for easier report

development

LET’S PUT DB2 WEB QUERY TO WORK! IMPROVE DEVELOPER PRODUCTIVITY


LET’S PUT DB2 WEB QUERY TO WORK! SOUTH CAROLINA STUDENT LOAN

Business challenge South Carolina Student Loan (SCSL) is a small non-profit organization that originates

and funds educational loans for students in South Carolina. A key challenge to SCSL

was to leverage legacy query objects while modernizing the business intelligence

environment to provide actionable information to stakeholders.

Solution

Implemented IBM DB2 Web Query for i to enable more robust, efficient reporting and

significant performance improvements. Converted Query/400 reports to DB2 Web

Query to leverage the DB2 SQL Query Engine (SQE) running on an IBM Power® 550

Express™ system.

Benefits

Enabling powerful new forecasting and reporting capabilities while eliminating the role

of IT personnel as middlemen, speeding information access from 30 days to just

seconds, and reducing report maintenance by leveraging multiple views within a single

report— enabling the company to retire up to five Query/400 reports for every new DB2

Web Query report.

“Within seconds, users are

getting information that they

previously had to wait up to 30

days for. Specific departments

have certain goals, and they are

now able to get immediate

feedback on where they stand in

reaching those goals. We do a

much better job of maintaining

compliance with DB2 Web

Query because the feedback is

real-time. No other solution

allowed us to leverage our

legacy query objects, DB2 Web

Query was the only one.”

Says Tom Dunnigan, CIO

SCSL


LET’S PUT DB2 WEB QUERY TO WORK! MOVE AT YOUR OWN PACE

You don’t have to start with a big enterprise BI

message because with DB2 Web Query you can

move at your own pace:

If all you want to do is deliver data in the formats

users want without jumping through hoops, you

can do that.

If you want to build some simple dashboards, you

can do that.

If you want to replace a ton of Query/400 reports

with small number of OLAP reports, you can do

that.


SOME HELPFUL DB2WQ INFO


LET’S PUT DB2 WEB QUERY TO WORK! PRUDENT PREPARATION

1. Product Overview:

http://www.nemug.com/handouts/Introduction%20to%20DB2%20Web%20Query%20DravtV2.pdf

2. Recorded Product Demos:

http://www.ibm.com/systems/i/software/db2/webquery (no info required to view)

https://whiteglove.on.intercall.com/confmgr/view_stored_doc.jsp?docId=91961135726736715473140969

520&docType=recording (requires name & email address to view)

3. DB2WQ FAQ:

http://www-03.ibm.com/systems/i/software/db2/webquery/

4. Product Redbook:

http://www.redbooks.ibm.com/redpieces/abstracts/sg247214.html

http://www.nemug.com/handouts/Introduction to DB2 Web Query DravtV2.pdf



























http://www.ibm.com/systems/i/software/db2/webquery









https://whiteglove.on.intercall.com/confmgr/view_stored_doc.jsp?docId=91961135726736715473140969520&docType=recording






































LET’S PUT DB2 WEB QUERY TO WORK! PRODUCT COMPONENTS – QU1

Report Assistant

The Report Assistant is an HTML-based tool that you use to create tabular

reports. You may view your reports interactively in your browser or generate

output in Excel, PDF or XML formats or traditional spool file or database file

formats.

Graph Assistant

The Graph Assistant is an HTML-based tool that guides you step-by-step

through the creation of a graph. Graph types include: line, bar, pie, scatter,

area, 3D, bubble, radar, histogram and many more.

Power Painter

Is a “WYSIWYG” tool that will enable your power users to design

compound reports. It combines reporting, graphs,

and page layout design in a single tool.


LET’S PUT DB2 WEB QUERY TO WORK! PRODUCT COMPONENTS - QU2

DB2 Web Query Developer Workbench (optional)

The DB2 Web Query Developer Workbench product is an optional add-on

Windows-based development environment for creating applications.

Developer Workbench provides intuitive GUI tools that free developers from

the hassles of coding, thereby allowing them to concentrate on interface

design, business logic, and data manipulation.

DB2 Web Query OLAP Module (optional)

The DB2 Web Query OLAP module allows slicing and dicing of data.

OLAP-enabling a report allows users to drill down to lower levels of detail.

Users can interact with their data, pivot rows and columns, visualize their

numeric fields graphically and much more.



Active Reports Module (optional)

By combining data and interactive controls into a single, self-contained

HTML file, Active Reports delivers analytic capabilities in a completely

portable and disconnected environment, with no software required other

than a browser.

For example, a salesperson might download an Active Report to his or

her PC before visiting customers and have on-the-road access to sales

data which can be sorted, selected, and drilled-down into.



ReportBroker (optional, from Information Builders)

ReportBroker is a scheduling and distribution application that centralizes

the execution and distribution of DB2 Web Query reports. In addition to

batch scheduling and execution of reports, the output can then be

emailed to users, ftp’ed to a specific PC directory or stored on the

System i.

Batch output is limited (no e.g. PDF, Excel) without ReportBroker.



Additional Database Adapters (optional, from Information Builders)

The base DB2 Web Query product includes adapters for DB2 for System i

and Query/400. Optional adapters from Information Builders enable you to

connect to dozens of other databases like MySQL, Microsoft SQL Server

and Oracle.



SDK (Software Development Kit)

Provides application integration and customization through the use of Web

Services. Primary use of the SDk is to expose DB2 Web Query output (reports and

graphs) to be integrated with non-DB2WQ Web apps (portals, dashboards, etc).

With the SDK, applications interface with DB2 Web Query using SOAP, a protocol

that exchanges information using XML-based messages. The new Web-service

functions reside in the servlet container of the IBM i integrated Web app server.

SDK requests come in as SOAP messages and are processed by these Web

services. For example, when invoked, the WebQueryRunFex Web service function

runs the report and returns its output in a structure called WebQueryReturn.

Article:

http://www.ibmsystemsmag.com/ibmi/september08/developer/21757p1.aspx

http://www.ibmsystemsmag.com/ibmi/september08/developer/21757p1.aspx


LET’S PUT DB2 WEB QUERY TO WORK! PRODUCT PRICING*CONTACT YOUR IBM RESELLER

DB2 Web Query is licensed by server and named user.

*Contact your IBM reseller for current list prices, but at this time for a

system in software tiers P05 and P10 prices are:

$1,600 and $3,200 respectively for the base server license

Plus $400 per user

But . . . If you are licensed for Query/400 and have Software

Maintenance, then you don’t pay an upgrade charge for the server

license and you receive two (P05) or four (P10) named user licenses at

no additional charge.

For systems in software tiers P20-P60 the base license has a higher

price but there are more named users included.

You could STOP with the no charge Query/400 upgrade

and you’d potentially have:

Web Based interactive queries for limited users

Many more output formats

Improved performance

Linked (Drill down) reports

But with a little EXTRA EFFORT

You could be a SUPER HERO!



Active Reports and OLAP

Both modules are licensed by system – not by user.

These two product options are very inexpensive and represent a

great buy from IBM . . . Only $400 for P05 and $800 for P10.

Disconnected Active Reports can be distributed via email without

licensing or any end-user fee . . . Terrific value and benefit to your

organization.

The Developer Workbench is licensed per user. Each Developer

Workbench user license is $995.

*Contact your IBM reseller for current pricing



Annual Product Maintenance

Annual Maintenance charge applies and is charged / priced

separately for both Query/400 and DB2WQ.

DB2WQ maintenance is a good deal – Fee is computed on the

server charge only, not the product options you buy.

Product Options Trial Software

You can try the QU02 product options on a 70 day free trial.

Existing Query/400 function is maintained even once DB2WQ is

installed. This means that you may continue to run existing Query/400

reports and create new ones using the familiar green-screen interface.


LET’S PUT DB2 WEB QUERY TO WORK! FOUR COMMON PERFORMANCE ISSUES

1. Older queries are often running under DB2WQ using CQE (older query

optimizer technology in DB2 for i) vs. SQE (SQL Query Engine - a

totally re-architected query optimizer).

SQE can be enormously more efficient, but there are some things that

still force an SQL statement down the old optimizer's code path (e.g.

SQL statement referencing a DB2 logical file). Knowing how to see if a

poor performing query is using CQE vs. SQE is step one, and then

determining why it is using CQE, and then addressing the "why", is a

skill that most i shops simply don't have for the most part.

Note: SQE is NOT available to Query/400 so that is another real benefit

or reason to move to DB2WQ.



2. Developers often write queries that are returning 100's of 1000's of

rows to an HTML output.

Reports with massive output should be run batch with the output

deposited in a library product or printed, but NOT sent back to a Web

browser.



3. Developers not pushing logic (like join logic) to DB2 and trying to do

that work in the 4GL reporting server.

In reality, you want to use as little of the 4GL in report development as

possible. Think “SQL to fetch and DB2WQ (WebFOCUS) to style”.



4. No DBA on staff to ensure tables, views and queries are optimized.

In many cases, even simple concepts are ignored such as INDEXING

columns to significantly improve performance.

Remember that you are most likely hitting tables designed for OLTP not

reporting. If reports are not performing as desired implement

techniques such as a DB2 view to reduce joins in the query, or create a

single reporting table (micro mart) for a report or group of reports which

created in an “ETL” process (Extract, Transform, Load) from multiple

tables to eliminate the join altogether.


LET’S PUT DB2 WEB QUERY TO WORK! WHAT DOES RESILIENT BUSINESS SOLUTIONS

HAVE TO OFFER?


LET’S PUT DB2 WEB QUERY TO WORK! FOUR ASSISTANCE PROGRAMS

BI Readiness Assessment for i

DB2 Web Query Quick Start Program

DB2 Web Query Development /Mentoring

Retained DBA Support

Four effective assistance programs designed to

work together or stand on their own:


LET’S PUT DB2 WEB QUERY TO WORK! WE ARE HERE TO HELP

BI doesn't have to be hard, but even with DB2 Web Query there

are still things (education, best practices, performance tuning )

that can make or break even the simplest projects.

Doug Mack

Power Systems, IBM Systems and Technology Group


LET’S PUT DB2 WEB QUERY TO WORK! QUESTION AND ANSWER SESSION

Let’s Take Your Questions.


LET’S PUT DB2 WEB QUERY TO WORK! PRUDENT PREPARATION

Onward! BI Readiness Assessment for i:

Interview Executive, Business Line and IT Managers and staff to determine

where best to put Business Intelligence to work for the Organization.

A process to inventory all RPG, COBOL and Query 400 programs, MySQL &

DB2 tables, applications etc.

Delivers a ‘road map’ to move the Client Organization from Green Screen to

the Web using DB2 Web Query.

Delivers a formal Findings Document complete with recommendations, a

high level project plan and next steps for moving forward.

The engagement includes one week onsite (E.g. Monday noon thru Friday

noon) to conduct interviews & other information gathering activities.


LET’S PUT DB2 WEB QUERY TO WORK! IMMEDIATE BENEFIT

Onward! DB2 Web Query Quick Start:

Designed to quickly unleash the power of DB2 Web Query.

Client is guided to a functional business area to apply the product.

DB2 Web Query is installed and certified,

2 existing Query 400 reports are incorporated into the product

2 new reports and/or graphs (could include drills from report to graph or

vice-versa) are developed.

All components are delivered in a Web Dashboard culminating with a full

demonstration.

The engagement is a combination of onsite and remote hours.


LET’S PUT DB2 WEB QUERY TO WORK! SUPERIOR SUPPORT

Onward! DB2 Web Query Mentoring /Development:

An attractive pay-as-you-go option to maintain and improve developer

productivity with the DB2 Web Query tool.

Provides developers with email, phone and Web support as they tackle

their daily reporting tasks with DB2 Web Query.

Great for answering “How do I do this?” questions, delivering tips &

techniques, optimizing code, etc.

Hours can also be used for development of new reports or maintenance of

existing code

160 hour draw-down (pay-as-you-go) base package (first 40 hours are pre-

billed)

Available as a remote or onsite program.


LET’S PUT DB2 WEB QUERY TO WORK! SPECIALIZED EXPERTISE

Onward! Retained DBA Support:

Provides effective and affordable access to a professional data base

administrator for code optimization, data base tuning, modifying data base

objects (tables/views/columns/etc).

Very flexible program because Client selects only the hours per month that

they feel they need.

Sold in annual contract of 4-8hr increments per week; 4hr per week

minimum; first month is pre-billed.

Unused hours in a month are rolled forward.

Remote services program with onsite support available on request.


Call Laila Samawi-Utley

@ 678-777-8666 or email at

[email protected] to

book an Onward! Strategy

Session.

LET’S TAKE THE NEXT STEP ONWARD!

let’s put db2 web query to work!

Documents