Beyond Query/400: Introduction to

sunfloweremryologistData Management

Oct 31, 2013 (3 years and 5 months ago)

67 views

Beyond Query/400: Introduction to
IBM DB2 Web Query for i

Richard Sitt

Information Builders


June 16, 2010

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


2

IBM DB2 Web Query for System
i

Powered By Information Builders



Base Program Product


Order via
econfig

and AAS

(5733
-
QU2)


Will require IBM i
V5R4 at a minimum


Named user based pricing


Minimal Users included in BASE


Modernize
Query/400 Reports


Additional Chargeable IBM Features


Active Reports (Disconnected)


On Line Analytical Processing


Developer’s Workbench


Run Time User Licensing


Spreadsheet Client


SQL Server Adapter


Additional DB2 Web Query PRODUCTS


Automated Report Distribution with

Report
Broker


Application Integration with the Software
Development Toolkit

3

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

Upgrade into BASE Product from Query/400


The BASE product:


Web enables Query/400


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


Over 100 charts and graphs


Boardroom quality PDF output


Access remote DB2 for i5/OS data sources


Leverages the LATEST in DB2 for IBM
i

Query Optimization Technology


Better performance, better analysis tools


Many more functions available for query performance


Strategic optimizer

4

Leverage Advanced Query Optimization


SQL Query Engine (SQE)


Better Performance


up to 10x over older query engine


Self
-
learning query optimizer


Self
-
adapting query engine



SQL Plan Cache


Real time analysis of Query performance


Index (traditional and EVI) advice (recommendations and wizards)



Materialized Query Tables (MQTs)


Optimizer “aware” summary tables for extreme performance boost



Non
-
SQL interfaces (Query/400, OPNQRYF, QQQQRY API)


DO NOT LEVERAGE ANY OF THE ABOVE

5

http://www.ibm.com/systems/i/software/db2/awp.html

BASE Product Tools


Report Assistant



Graph Assistant



Power Painter


(PDF Builder)


6

Import Query/400 Definitions

Then Modify with

Report

Assistant

7

DB2 Web Query

8

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 an IT SUPER HERO!

Benefits of DB2 Web Query Extensions



Reduce Report Maintenance


Reduce number of reports by 90% through
modernized reporting capabilities such as
OLAP and parameterized reports



Remove Dependency on IT


Empower End Users with interactive
analysis (OLAP)


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



Upgrade into a solution that offers
Business Intelligence features


Add Dashboards, real time or
disconnected analytics


Build data warehouses with DB2 Web
Query enabled extract/transformation/load
(ETL) tools


Information Builder’s Data
Migrator

www.ibi.com


Coglin

Mill’s Rodin www.coglinmill.com





9

Develop Dashboards


Key Performance Indicators



Parameter Driven (if desired)



Linked Reports



Very Customizable



No Programming Required


Use HTML Composer Tool

10

Empower End Users with OLAP (On Line Analytical
Processing)

11

Active Reports
-

Provide Analysis to Mobile Workers

12

“Build Up” Metadata Using Developer Workbench


Improves productivity of Report
“Authors”


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



IBM Systems magazine cover story:

Untangling Web Query: How metadata can
reduce query and report complexity

http://www.ibmsystemsmag.com/ibmi/september09/coverstory/26214p1.aspx

13

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

14

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
installed

15

Copyright 2007, Information Builders. Slide
16

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



16

Integration with PHP

DB2 Web Query Integration from PHP
-

Leveraging DB2 Web Query web
services from IBM
i

applications




http://ibm.com/partnerworld/wps/whitepaper/i/php_db2/webquery

17

18

DB2 Web Query for IBM
i

SOA Toolkit

19

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

&STATE=California

Greatly simplifies report integration process

DB2 Web Query for IBM
i

SOA Toolkit


What is the SOA toolkit??


Web Application allowing Remote calls for DB2 Web Query
Reports/Graphs


HTML/
.Net
/PHP/Java Servlet/JSP/ASP


Rich Client, RGP, COBOL, C, C++, JAVA


E
-
Mail/IM


What are the advantages of using the toolkit??


Ease of deployment


simply install and start plugging in DB2 Web Query
reports to your applications with a simple <
iframe
> URL reference



Saves development cost/time


Faster adaptation!


LWI application server will allow the toolkit to be called from any Web
Interface capable of calling an URL


Ability to email a link to a specific report to run real
-
time!!!!




Contact: Mark Even (even@us.ibm.com)


DB2 Web Query for IBM
i

SOA Toolkit


What is provided with the toolkit??


WAR file containing
Servlet

classes (no source) capable of running
and displaying DB2 Web Query .FEX reports


Documentation


Support and Upgrades of code will be provide if customer signs the
support contract with STG Lab Services (Annual Cost)


DB2 Web Query for IBM
i

SOA Toolkit


Other features


Runs in any application server (Normally Integrated Application Server (IAS)
on IBM
i
)


Runs/Displays Reports in all formats


PDF


Excel


HTML


Active Report


Runs/Displays Graphs in all formats


Drill Down Support


Auto Parameter Prompting


Ability to pass developer/user known parameter values




Coming Soon


Report Broker Capabilities


Customized CSS Look and Feel for Parameters and Navigation


Multiple IBM
i

Systems Support


Ability to say which system you wish to run
against

DB2 Web Query for IBM
i

SOA Toolkit

Easy Integration!

Graphs

Reports

Links

DB2 Web Query

Making Information Easily Accessible

24

Email (Report
Broker and/or SDK)

0
-
Click

Run Time User

1
-
Click

Dashboards

1
-
Click

Guided Ad Hoc

2
-
Click

Report Assist

OLAP

3
-
Click

Analysts or

Developers

Business
Users

And
Executives

Internal

External


Customers, Partners, Citizens

All People

Others

Demo!

25

Education Links


Getting Started with DB2 Web Query

Redbook


Tutorial Driven


Download Sample Database


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



Distance (Web) Learning




Getting Started (OV440)


http://www
-
304.ibm.com/jct03001c/services/learning/ites.wss/us/en?pageType=course_search&sortBy=5&searchType=1&sor
tDirection=9&includeNotScheduled=15&rowStart=0&rowsToReturn=20&maxSearchResults=200&language=en&c
ountry=us&searchStri ng=ov440


Implementation Workshop (OV550)


http://www
-
304.ibm.com/jct03001c/services/learning/ites.wss/us/en?pageType=course_search&sortBy=5&searchType=1&sor
tDirection=9&includeNotScheduled=15&rowStart=0&rowsToReturn=20&maxSearchResults=200&language=en&c
ountry=us&searchStri ng=ov550


E
-
Learning Modules


Self paced, downloadable end user training


http://education.informationbuilders.com/edu/courses/elearning/systemi.jsp


DB2 SQL and Query Performance Workshop


Building the DB2 for i Database Administration (DBA) skills


Classes scheduled for 2010


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

26

Lab Services and Training

DB2 Web Query Services


“Getting Started” Services


Installation & configuration help and support


Skills transfer / education


Implementation Services


Build report/graph/dashboard applications


Tune and improve performance


Create complex SQL for advance query reporting


Help design/document/deliver business intelligence applications


Data
-
Marts


OLAP


KPI dashboards


DB2 Web Query SOA Toolkit


Built and supported by Lab Services


Integration services


Enterprise Edition Services Vouchers


Vouchers entitle holder to a FREE amount of services
(**based on hardware purchase)


Contact: stgls@us.ibm.com

Website:
http://www.ibm.com/systems/services/labservices