db2 web query whats new

© 2012 IBM Corporation1

IBM Power Systems

DB2 Web Query for i – What’s new

Tim Rowe [email protected]


IBM Power Systems

Requirements

If you have items your business requires, Submit a requirement!

Help IBM understand how we can help you.


IBM Power Systems

Background

Designed to:

� Provide modernized reporting with extensions into analytics and Business Intelligence

� Leverage the LATEST in DB2 for i technology

� Provide BI “best practices” (e.g., meta data layer)

� Be delivered at low cost

� Allow customers to keep their data in DB2 for i while

– Improving Performance

– Reducing report maintenance

– Simplifying infrastructure (single server installation)

What is DB2 Web Query?

An IBM i Web-based query and report writing program product� First delivered in 2007 as product 5733-QU2� A powerful replacement for IBM Query for iSeries (QU1 product Query/400)


IBM Power Systems

What is Business Intelligence?

DB2 Web Query

OS/EAI-Operation Systems/Enterprise Application Integrations

Source: The Data Warehousing Institute, Smart Companies in the 21st Century, July 2003

REPORTING

WHAT HAPPENED?

MONITOR

WHAT JUST HAPPENED?

ANALYSIS WHY DID IT HAPPEN?

PREDICT

WHAT WILL HAPPEN?

Historical Data (Data Warehouses/Marts) Real-Time Data (ODS)

Data Mining Query/

Reporting OLAP Dashboards/

Scorecards

Predictive Analytics Product

© 2012 IBM Corporation

IBM Power Systems

The Challenges of Delivering Analytics with Query/400

� The most common way of accessing DB2 for i data for reporting purposes for over 30 years

� Limited functionality, product is in stable-izedmode

– Output to file, printer, or on screen

� Requires data “expert” to build reports

� Multi-step processes for simple tasks like viewing the data in a chart in Excel

� No Impact Analysis or Usage tools

– What is the impact of database changes on my Query/400 reports?

– How many queries can we get rid of because they are no longer used?

� No easy way to understand performance impact on system

– Older query optimization technology being used

– Little usage of governing functions means “run-away” queries are not uncommon

� Creates negative perception of “AS/400”


IBM Power Systems

Why do you need to TRANSFORM your data?

� Not everyone understands the data..only the original programmer knows!

– Cryptic six character RPG fields

– Data meaning buried in the field

• If the first character is an “A”, the next 5 characters mean customer number

• If the first character is a “B”, then it’s a company ID

� Not everyone understands the table/file and data relationships

– How do I join these 8 files?

– How do I more easily roll up data by city/region/country/world?

� Not everyone AGREES to the meaning of data

– Accounting: 1+1 = 2 Sales: 1+1 = 3 !

� Not everyone understands complex date functions

– Nor how every date is stored in different data TYPEs in the database

� And no one wants to define these rules in EVERY single report

6

Shield the COMPLEXITIES of the database from Report Authors and End Users opening up additional report

authoring capabilities and removing dependency on I/T


IBM Power Systems

Leverage a Meta Data Layer to Shield Complexity of Operational Databases

� 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

– Leverage relationships across tables/files

� Create Business Views

– Organize Columns/fields for easier report development

� Define Multi-Dimensional Relationships

– If I drill down on COUNTRY, should I see REGION or DISTRICT?


IBM Power Systems

Meta Data Example: Data Transformation

For credit rating >7 is “Good”,

4-6 is “Average”,<4 is “Bad”


IBM Power Systems

9

DB2 Web Query for i – Intuitive, Insightful, ExtensibleModernize Query/400, Extend into Business Intelligence

Authoring Tool“Intuitive”

Authoring Tool“Intuitive”

Reports & Graphs“Flexible delivery”

Reports & Graphs“Flexible delivery”

Dashboards“Insightful”

Dashboards“Insightful”

OLAP“Extensible”

OLAP“Extensible”


IBM Power Systems

Announcing! DB2 Web Query for i v2.1Modernize Query/400 Reporting with Smarter Analytics for IBM i

� New Version 2.1

� New Product IDs (5733-WQe/WQs)

� New Core based pricing

� Available for IBM i v6.1 and v7.1

� Simpler Packaging Options

� Express Edition

� Entry level edition

� Report development

� Great for small, get started environments

� Standard Edition

� Full function capabilities

� Virtually unlimited runtime users support

� Generally Available June 15

AnnouncedApril 24


IBM Power Systems

11

V2.1 – Simplified product structure

From current product(s)

(v1.1.2):

� BASE

� Number of Users

� OPTIONS

� Option 1 Active Reports

� Option 2 OLAP

� Option 3 Developer Workbench

� Option 4 Run Time User Enablement

� Option 5 Spreadsheet Client

� Option 6 Microsoft SQL Server Adapter

� Option 7 JD Edwards Application Adapter

� 5733QU3 Report Broker

� 5733QU4 SDK

� Lab Services 5250 Reporting Extension

To new product

(v2.1):

� Express Edition

� Number of Users

� WorkBench

� Standard Edition

� Number of Users

� WorkBench

� Runtime Users

� JD Edwards Application Adapter


IBM Power Systems

12

V2.1 – Simplified product structure

From current product(s)

(v1.1.2):

� BASE

� Number of Users

� OPTIONS

� Option 1 Active Reports

� Option 2 OLAP

� Option 3 Developer Workbench

� Option 4 Run Time User Enablement

� Option 5 Spreadsheet Client

� Option 6 Microsoft SQL Server Adapter

� Option 7 JD Edwards Application Adapter

� 5733QU3 Report Broker

� 5733QU4 SDK

� Lab Services 5250 Reporting Extension

To new product

(v2.1):

� Express Edition

� Number of Users

� WorkBench

� Standard Edition. Express plus:

� Number of Users

� WorkBench

� Runtime Users

� JD Edwards Application Adapter

Functionality mapping


IBM Power Systems

v2.1 Product enhancements

� New look and feel

– Direct links to Web Query Wiki with

Tutorials, Redbook, and Q&A forum, as

well as product page with latest offerings

and features

– New GUI (Portal) for working with

reports and folders

� Improved Mobility Support with ‘My Mobile

Favorites’

� Web based user management

– admin, dba, dev, user, scheduler, workbench

� User Role Based Security

� Web browser Meta Data editor

� Consolidated report development tool

(InfoAssist)

� Integrated OS based report scheduling*

� And lots more


IBM Power Systems






and features


reports and folders


Favorites’






(InfoAssist)


� And lots more


IBM Power Systems






and features


reports and folders


Favorites’






(InfoAssist)


� And lots more


IBM Power Systems






and features


reports and folders


Favorites’






(InfoAssist)


� And lots more


IBM Power Systems



– Direct links to Web Query Wiki with Tutorials, Redbook, and Q&A forum, as well as product page with latest offerings and features

– New GUI (Portal) for working with reports and folders

� Improved Mobility Support with ‘My Mobile Favorites’





� Consolidated report development tool (InfoAssist)


� And lots more www.ibm.com/systems/i/db2/webquery

* Available in Web Query Standard Edition


IBM Power Systems

Comparing v2.1 Editions

Express Edition� Portal user interface

� Supports all metadata and report authoring tools

� User management security center

� Mobile support

� Can order additional licensed users

� Can order Developer Workbench licenses

Standard Edition

� All capabilities of Express plus

� Ability to have virtually unlimited run time users

� Can order runtime group licenses

� Report scheduling, distribution, and bursting

� Application integration capability

� Data and application adapters:

� SQL Server (included)

� JDE adapter (optional feature)


IBM Power Systems

InfoAssist – The integrated development tool

Integrated development tool

• Create simple queries, as well as highly

complex reports, charts, and dashboards from multiple sources

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

•Generate reports in HTML, Adobe Flash/Flex, Adobe PDF, Microsoft Excel or PowerPoint, DB2 Web Query Active Reports (for offline analysis), and other formats

• Familiar Microsoft Office-like ribbon interface

• Robust yet simple to use!


IBM Power Systems

InfoAssist splash screen

� Can create a single report

– Replace Report Assistant

� Can create a single graph

– Replace Graph Assistant

� Can create a document

– Replace Power Painter

� Can start with a single report or single graph and escalate them into a document and add additional elements

– Additional reports

– Additional graphs

– Text

– Images

IBM DB2 Web Query for i – InfoAssist


IBM Power Systems

IBM DB2 Web Query for i – InfoAssist


IBM Power Systems

Feature enabled by InfoAssist

� Create report “slicers”

– Filters selected by users at run time

� Dynamic, user selected output format

– Selected by users at run time

InfoMini


IBM Power Systems

Ability to create Rich Internet Applications

New feature in HTML Composer

Benefits of a Rich Internet Application

(RIA)

• Rich – robust and intuitive

• Responsive – quickly updates

• Look and feel similar to desktop


IBM Power Systems

Active Technologies for Mobile Web Apps

� Ease of Use

� Single-tap UI paradigm

� Full gesture & screen rotation support

� Embedded data analysis and visualization

� Full offline data interactivity

� Ease of Development

� Dynamic device detection

� Build once, fit in any device

� Industry Standard

� Send data with 128-bit encryption

� Web Apps technology

� iPhone App-like UI

� Available in HTML or Flash

Download iPad app: http://itunes.apple.com/us/app/mobile-faves/id406033421?mt=8


IBM Power Systems

DB2 Web Query Application Extension

25

http://lp12ut21.rchland.ibm.com:12321/wqsoa/reports/re

port?&zrdDomain=db2wbqry/db2wbqry.htm&zrdFolder=

%23revenuerepor&zrdReport=app/product_revenue_sta

te_report.fex&STATE=California

� Provides an easy URL based interface to reports/graphs

� Based on SDK

� Greatly simplifies report integration process

� Integrate reporting and BI

functions into customized or existing

Line-of-Business applications

� DB2 Web Query content

integration without low level

programming!


IBM Power Systems

26

Based on SDK - Greatly simplifies report integration process

DB2 Web Query Application Extension






IBM Power Systems

27


DB2 Web Query Application Integration Extension





http://lp12ut21.rchland.ibm.com:12321/wqsoa/reports/report?&zrdDomain=db2wbqry/db2wbqry.htm&zrdFolder=%23revenuerepor

&zrdReport=app/product_revenue_state_report.fex

&STATE=California


IBM Power Systems

28


DB2 Web Query Application Integration Extension





http://lp12ut21.rchland.ibm.com:12321/wqsoa/reports/report?&zrdDomain=db2wbqry/db2wbqry.htm&zrdFolder=%23revenuerepor

&zrdReport=app/product_revenue_state_report.fex

&STATE=California

If no parm is passed in URL,

user is automatically prompted


IBM Power Systems

IBM DB2 Web Query Extension Offering

DB2 Web Query CL Reporting Extension

� Provides an easy way to invoke DB2 Web Query reports through CL

� Similar functionality for customers use to invoking Query/400 reports

� CL interface to run reports� send via e-mail (as inline HTML or as an attachment)� FTP it to a desired target

� v2.1 - Part of Web Query Standard Edition

� v1.1.2 - Provided by IBM STG Lab Services� Pricing, ordering and SW Maintenance

handled by Lab Services � Contact IBM STG Lab Services


IBM Power Systems



IBM Power Systems



IBM Power Systems



IBM Power Systems

IBM DB2 Web Query for i – Dedicated subsystem

Simplified work management -easily allocate/adjust system resources

Simplifies starting and stopping of DB2 Web Query jobs

Simplifies measurement and understanding of workloads being generated by DB2 Web Query jobs

One place to look for all DB2

Web Query jobs


IBM Power Systems

IBM DB2 Web Query for i – Dynamic User selected output format


IBM Power Systems

IBM DB2 Web Query for i – Dynamic user selected output format


IBM Power Systems

� Active Flash

� Active PDF **

� Excel pivot tables **

DB2 Web Query - New output formats

** InfoAssist only


IBM Power Systems

DB2 Web Query – Improved date function performance

Date functions passed to the DB2 Engine for converting and handling dates

� Optimized SQL and improved performance!

� DATECVT – Convert legacy dates

Character formats

DATECVT(DC_YYMD_CHAR, 'A8YYMD', 'YYMD')

DATECVT(DC_MDYY_CHAR, 'A8MDYY', 'YYMD')

DATECVT(DC_DMYY_CHAR, 'A8DMYY', 'YYMD')

Packed and zoned decimal formatsDATECVT(DC_MDYY_DEC, 'P8MDYY', 'YYMD')

DATECVT(DC_MDYY_DEC, 'P8MDYY', 'YYMD')DATECVT(DC_DMYY_DEC, 'P8DMYY', 'YYMD')

Integer formatsDATECVT(DC_MDYY_INT, 'I8MDYY', 'YYMD')

DATECVT(DC_MDYY_INT, 'I8MDYY', 'YYMD')

DATECVT(DC_DMYY_INT, 'I8DMYY', 'YYMD')

� DPART and HPART – Extracts components from a date fieldYEAR, QUARTER, MONTH, and DAY

� DATEADD, HADD, and DATEDIFF – Date arithmetic


IBM Power Systems

What’s the

big deal

here?

So what…Wow!!

Ex: DB2 Web Query – Improved date functions performance

Created a report that queried a 6 million row table

and converted 1 legacy date field to a “smart” date and displayed 3 other columns

Before improved date functions:

Elapsed time to run to completion and display report:

Just under 8 minutes

After improved date functions:

Elapsed time to run to completion and display report:

20 seconds!


IBM Power Systems

New Feature for Define/Compute and Filters• supports SQL scalar functions in a request

To reference an SQL function in a request, prefix the function name with SQL.

Examples:SQL.COALESCE(MARITAL_STATUS, ’UNKNOWN’)SQL.MONTHNAME(ORDERDATE)SQL.MY_UDF(CUSTOMER_NUM, REVENUE)

Direct SQL Function Calls

User Defined Function (UDF)

I could really use this.


IBM Power Systems

JD Edwards Integration

J.D. Edwards Application Adapter

• Create reports, graphs,

dashboards over JDE data

• Handles decimal and date conversions, provides meaningful column names, supports JDE UDCsand security model

• Support for JDE World and

EnterpriseOne


IBM Power Systems

JD Edwards Integration…

Without JDE Adapter With JDE Adapter


IBM Power Systems

Creating Synonyms (Metadata Layer)

CRTWQSYN CL command

• Create metadata for multiple objects programmatically

• Easy to use

• Start creating reports with less setup time


IBM Power Systems

Misc Notes

� Stay current with updates by applying the latest available Web Query group ptfs

– SF99637 (IBM i v7.1), SF99636 (IBM i v6.1), SF99635 (IBM i v5.4)

� Web Query v1.1.2 remains available for order until mid June

� Upgrades supported to both v1.1.2 and, when available, v2.1 (excludes v1.1.0)

– Must be current on DB2 Web Query SW Maintenance (SWMA)

– Order a “refresh” download from ESS Website

� Want to try new version for 70 days?

– Download 70 day trial from ESS Website

� Installation Instructions

– https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en#/wiki/W516d8b60d32c_4fc5_a811_5f3d840bf524/page/Installation


IBM Power Systems

Need help? Important Links

� IBM developerWorks site for DB2 Web Querywww.ibm.com/developerworks/spaces/DB2WebQuery

– 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 QueryRedbookhttp://www.redbooks.ibm.com/abstracts/sg247214.html

– Tutorial Driven

– Download Sample Database

� InfoAssist tutorials from developerWorks Wiki (tutorials not yet in Redbook)https://www.ibm.com/developerworks/mydeveloperworks/wikis/home/wiki/W516d8b60d32c_4fc5_a811_5f3d840bf524?lang=en

“BTW, this forum rocks and is

SUPER helpful”- Forum user


IBM Power Systems

� The DB2 for i Center of Excellence team within IBM Lab Services consists of a group of highly trained, senior level consultants with over 150 years of combined experience in helping AS/400, iSeries, System i and now IBM i on Power Systems clients with database related services.

� DB2 Web Query Getting Started Services:

– Three days of onsite assistance

– Audience specific training (targeted training modules)

Combination of presentation, demonstration and hands on labs covering DB2 Web Query, performance considerations, and critical success factors

– Getting started with Meta Data Workshop

� Query/400 Modernization Services

– Three days of onsite assistance

– Analyze current Query/400 environment using Lab Services developed discovery and analysis tools

– Develop roadmap to modernize reporting environment

– Build prototype of modernized environment with DB2 Web Query

� Use Services Voucher to cover or supplement costs of services

– For more information, contact Doug Mack at [email protected]

IBM Lab Services offerings for DB2 Web Query


IBM Power Systems


IBM Power Systems

Thank you!


IBM Power Systems

Trademarks and DisclaimersAdobe, Acrobat, PostScript and all Adobe-based trademarks are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States, other countries, or both.

Intel, Intel logo, Intel Inside, Intel Inside logo, Intel Centrino, Intel Centrino logo, Celeron, Intel Xeon, Intel SpeedStep, Itanium, and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation in the United States, other countries, or both.

IT Infrastructure Library is a registered trademark of the Central Computer and Telecommunications Agency which is now part of the Office of Government Commerce.

ITIL is a registered trademark, and a registered community trademark of the Office of Government Commerce, and is registered in the U.S. Patent and Trademark Office.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Cell Broadband Engine and Cell/B.E. are trademarks of Sony Computer Entertainment, Inc., in the United States, other countries, or both and are used under license therefrom.

Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.

Other company, product, or service names may be trademarks or service marks of others.

Information is provided "AS IS" without warranty of any kind.

The customer examples described are presented as illustrations of how those customers have used IBM products and the results they may have achieved. Actual environmental costs and performance characteristics may vary by customer.

Information concerning non-IBM products was obtained from a supplier of these products, published announcement material, or other publicly available sources and does not constitute an endorsement of such products by IBM. Sources for non-IBM list prices and performance numbers are taken from publicly available information, including vendor announcements and vendor worldwide homepages. IBM has not tested these products and cannot confirm the accuracy of performance, capability, or any other claims related to non-IBM products. Questions on the capability of non-IBM products should be addressed to the supplier of those products.

All statements regarding IBM future direction and intent are subject to change or withdrawal without notice, and represent goals and objectives only.

Some information addresses anticipated future capabilities. Such information is not intended as a definitive statement of a commitment to specific levels of performance, function or delivery schedules with respect to any future products. Such commitments are only made in IBM product announcements. The information is presented here to communicate IBM's current investment and development activities as a good faith effort to help with our customers' future planning.

Performance is based on measurements and projections using standard IBM benchmarks in a controlled environment. The actual throughput or performance that any user will experience will vary depending upon considerations such as the amount of multiprogramming in the user's job stream, the I/O configuration, the storage configuration, and the workload processed. Therefore, no assurance can be given that an individual user will achieve throughput or performance improvements equivalent to the ratios stated here.

Prices are suggested U.S. list prices and are subject to change without notice. Contact your IBM representative or Business Partner for the most current pricing in your geography.

db2 web query whats new

Technology

data relationships

viewingthe data

data transformation4

ibm query

ibm power systemsleverage

ibm understandhow

meta data layerbe

meta data example