[Uruguay] DB2 Web Query for i - Hernando Bedoya

Latinamerican Roadshow 2011 – Bogota, Colombia

DB2 Web Query for i
Hernando Bedoya – DB2 for i Evangelist
hbedoya@us.ibm.com

© 2011 IBM Corporation

Acknowledgments

Thanks to Robert Andrews from IBM STG Lab Services-Rochester author
of this presentation.


IBM Systems Technical University – Prague, Czech Republic, 4 – 7 April, 2011

DB2 for i Enablers for DW
DB2 Engine Management
– On Demand Performance Center
– SQL Query Engine (SQE)
Business Intelligence
• 5x Query Performance
– DB2 Web Query
Improvement
• Real time statistics
• Autonomic Indexes
• Star Schema Awareness
– Database Parallelism
– Materialized Query Tables
– Encoded Vector Indexing
– Remote Journaling

*See detailed certified benchmark results at http://www.sap.com/solutions/benchmark/bimxl.epx

3 © 2011 IBM Corporation


Today’s Mid-market BI Landscape

Business Intelligence Remains HOT
– BI ranked #1 four straight years in Gartner CIO
survey of top priorities
– BI ranks #1 in Aberdeen Group study of
technologies with the most business impact in
the next 2-5 years

Mid-market companies have different requirements
but want similar benefits:
– Budget is smaller
– Staff is limited– Admin, DBAs
– “The full capabilities of most BI tools are
overkill for the typical SMB (Small and Medium
Business).” – Aberdeen Group

4



IBM DB2 Web Query for i : A Brief History Lesson

SQL introduced in V2R1 in 1991 (adoption was slow)
Strategic database language
Major investment in SQL
– EVIs, parallelism, SQE
No native query or BI tool to leverage SQL
What about Query/400?
– No modern interfaces
– No modern output formats
– No SQL – no ability to leverage latest
technology (SQE)
IBM and Information Builders alliance: DB2 Web Query! 5



Upgrade into BASE Product from Query/400
The BASE product (V1R1M2) web enables Query/400
– Highly intuitive, ribbon based, drag and drop report builder
(InfoAssist)
– Add additional functionality (compared to Query/400) to reports
• Link Reports by clicking on “hot spot”
• Add Conditional Styling (“if revenue less than forecast, highlight in
RED”)
• Push data directly from DB2 into Spreadsheets including pivot
tables
• Over 100 charts and graphs
• Boardroom quality PDF output
• Access remote DB2 for i data sources

– Leverages the LATEST in DB2 for i Query Optimization Technology
• Better performance, better analysis tools
• Many more techniques available to process queries



BASE Product Tools

InfoAssist
New Ribbon Based GUI for building reports,
charts, and compound documents
View what you’re building as you build it!

Report/Graph Assist
Web based tools with guided query building
more similar to building a Query/400 report
but with much more functionality

Power Painter
Web based PDF builder with canvas and
layout look and feel



InfoAssist

Create simple queries, as well as highly
complex reports, charts, and dashboards from
multiple sources using simple Ribbon-like
interface

Convert reports to charts or charts to reports or
analyze multiple reports and charts
simultaneously, tiling them to view data from
multiple perspectives

Will extend “report author” community beyond
just I/T personnel

Only available with v1.1.2
Upgrade into 1.1.2 on 5.4 and up
DB2 Web Query SW Maintenance required



Import Query/400 Definitions

Then Modify with
Report
Assistant or
execute from any
of the tools



You could STOP HERE, 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!



DB2 Web Query: Making Information Easily Accessible
Report Assist
OLAP
Analysts or
3-Click Developers
Guided Ad Hoc
Internal
2-Click
Dashboards
Business Users
1-Click And Executives

Run Time User

1-Click

Email External – Customers, Partners, Citizens
Others
0-Click



Benefits of DB2 Web Query Additional Features

Reduce Report Maintenance
– Reduce number of reports to maintain
• Through modernized reporting capabilities such as
OLAP and parameterized reports

Remove Dependency on IT to deliver information
– Empower End Users with interactive analysis (OLAP)

– Shield complexities of database from report authors
and end users with meta data layer

Extend into Business Intelligence
– Add Dashboards, self service or disconnected analytics



Build Dashboards with Rich Internet Application Layout tool

Look and feel of fat client application
on a zero footprint client

Leverage HTML Composer
component of Developer Workbench

Build KPI (Key Performance
Indicator) view of the business for
executives
Layout multiple KPIs onto single view
Link reports to common parameter
Example: allow end user to
select geography of interest and
all charts automatically reflect
selection

No programming required



Empower End Users with On Line Analytical Processing

With the OLAP feature you can replace 10’s
of Query/400 reports with a SINGLE DB2
Web Query report

A single report is a starting point for
interactive, real time multi-dimensional data
analysis
Drill down
Add or remove fields/columns by dragging and
dropping
Pivot rows and columns
Navigate across any dimension
Create charts from navigation point
Save report based on navigation point

Requires Developer Workbench license to
define dimensions and measurements
No CUBES Required
No data duplication required


Provide Analysis to Mobile Workers with Active Reports

Simply specify as an output option

Generates PC File with SNAPSHOT of
data and manipulation scripts
“Actively” work with snapshot of data
Create calculated fields
Filter data
Visualize fields/columns
Export to other formats
Word, Powerpoint
Create charts

Requires Supported Browser



Improve Productivity of Report “Authors” with Meta Data

Standardize Field/Column Formats
– Ex, use commas, set currency symbol,
suppress leading zeros

Standardize/Decompose Date Fields
– Ex: Integer defined as MMDDYYYY

Create Filters
– Ex: Define a set of countries as “Europe”

Define JOINS

Create Business Views
– Organize Columns/fields for easier report
development



Integrate DB2 and Microsoft Products

Spreadsheet Client
– Improve the experience for Excel Users
– Plugs right into spreadsheet
– Build/Run queries from within Excel
– Leverage Templates for dashboards

SQL Server Adapter
– Extend the reach of DB2 Web Query
– Support pulling data from multiple SQL
Server databases with a single adapter
– Join data from DB2 and SQL Server and
avoid data replication



Enable querying of Oracle JDEdwards application databases

Oracle JDEdwards OneWorld and EnterpriseOne adapter
– Feature of DB2 Web Query
– Does not affect SW Maintenance

Supports querying application databases running in IBM i 5.4 and up

Provides meaningful data element names, automates date conversion, converts
numeric data

The adapter preserves data integrity and security during report execution by
automatically interfacing to the application’s meta data and security layers

18


Automate Report Execution and Distribution
Automated Delivery Of Information
– On Scheduled Basis
• Through Admin GUI
• Daily, Weekly, Specific Days, exclude rules,
etc.
– On Event Basis
• Some customization required

Intelligent bursting
– Ex: Regional Sales Report

Additional output formats for batch reporting
– (HTML, PDF, Excel, Active HTML)

Delivery Destinations
– E-mail
– Printer
– Save the reports for later viewing

Notify Function
– Send notification when report is complete or fails
– Report Logging

Requires DB2 Web Query BASE Product to be
19 installed © 2011 IBM Corporation


DB2 Web Query SDK - Definition

The Web Query SDK is a collection of Web Services that allow Web
Query content to be easily exposed to any application that can call a Web
Service

Callable from many languages:
– C, C++, C#, VB.NET, PHP, Java, RPG, COBOL

Web Query customers
– Create their own front-end applications to run
Web Query reports and graphs
(Portal, Corporate Look and Feel)

ISVs
– Simple way to deliver an applications that
contains Web Query content
– Simple way to deliver an application that
controls Web Query content, e.g., Change
Management Application

20
Copyright 2007, Information Builders. Slide


21



Integrate DB2 Web Query functions with applications
Lab Services offerings that extend DB2 Web Query and
simplify integration of functions in existing or customized
applications

Application Extension
Provides a URL based interface
Removes complexity of programming
Supports any programming language

5250 Reporting Extension
Enables execution of reports from a 5250 app
Provides CL command supporting most output options
e-mail/FTP, or store a report locally

Both require Run Time Enablement feature of DB2 Web
Query for production environment
Application Extension requires 5733-QU4 for development
environment


DB2 Web Query Integration Toolkit Extension Example
Create hotspot on map that links to execution of a detailed report

http://lp12ut21:12321/wqsoa/report/?&zrdDomain=db2w
bqry%2Fdb2wbqry.htm&zrdFolder=%23visiblea56gb&zr
dReport=app%2Fproduct_revenue_state_report.fex
&STATE=California

23



DB2 Web Query 5250 Reporting Extension
RUNWQFEX command



“The most widespread technical Don’t let your project fail
problem reported by practitioners because you missed the
was slow query performance.” most critical success factor!
Survey of over 2000 companies that have implemented
Business Intelligence Applications
– The BI Survey 8 – Nigel Pendse,

DB2 for i has some great
query optimization
technology - but you need
to make sure you are using
it!



DB2 for i Center of Excellence (Lab Services)

DB2 for i web site – articles, papers, presentations
– ibm.com/systems/i/software/db2/
Briefings, consulting and guidance on demand*
DB2 Web Query Getting Started Services*
DB2 for i Modernization Workshop
DB2 for i SQL Performance Workshop
DB2 for i Performance Checkup*
DB2 for i remote database administration and engineer services

Note: Traditional Performance Analysis addresses RESOURCE
utilization, but does not address database optimization

*Use a voucher

For More Information, Contact Mike Cain (mcain@us.ibm.com) or Mark Even (even@us.ibm.com)



To Learn More: DB2 Deep Dive Webinar Series

DB2 Web Query Report Broker

Integrating DB2 Web Query with Microsoft® products

Raising the analytics bar with DB2 Web Query Online Analytical Processing (OLAP)
feature

Are you getting the maximum performance out of your DB2 Web Query reports?

Building and deploying Dashboards with DB2 Web Query Developer Workbench and
Active Dashboards

To view replays of these webinars, go to:
http://www.ibm.com/systems/i/software/db2/webquery/db2_webinars.html



For More Information: www.ibm.com/systems/i/db2/webquery
Self Running Demos
– 40 minute overview
– 75 minute deeper dive
– Links to webinar series
Helpful Getting Started Tab
– Latest Installation Instructions
– Links to our “community”
– Education and RedBook Offerings
– Recent Articles (tips and techniques)
Additional Product Information
Access to Developer Workbench trial
Papers
– FAQs
– Why i for BI
– How Run Time Enablement Works



Important Links “This forum is
SUPER helpful”
IBM developerWorks site for DB2 Web Query - DB2 Web Query
www.ibm.com/developerworks/spaces/DB2WebQuery Forum User
– Registration Recommended
– KnowledgeBase
– Forum
– Links to Additional Information
– WIKI – Repository of technical
information

DB2 Web Query home page
www.ibm.com/systems/i/db2/webquery

Getting Started with DB2
Web Query Redbook
http://www.redbooks.ibm.com/abstracts/sg247214.html
– Tutorial Driven
– Download Sample Database

29



SESSION EVALUATIONS
Please fill in the session evaluation sheets.
They can be found at the entrance of the
break-out room.
A prize winner is drawn every day from the
completed evaluations.
The winner’s name is announced at lunch
time and displayed on the plasma screens.
Prizes can be collected at the Welcome
Desk.

[Uruguay] DB2 Web Query for i - Hernando Bedoya

More Related Content

What's hot

Similar to [Uruguay] DB2 Web Query for i - Hernando Bedoya

More from IBMSSA

Recently uploaded

[Uruguay] DB2 Web Query for i - Hernando Bedoya