Standard template for internal and external presentations DB

DB 2 Web Query for i 개요

Query/400: A history Lesson • • Around since June, 1988 Lacks a graphical interface Does not support output to PDF, HTML, XML, or Excel Does not support non-DB 2 for i 5/OS databases Does not support drill down from a completed report Is not a pure SQL interface Does not use the SQL Query Engine (SQE) – SQE was introduced with V 5 R 2 – CQE (Classic Query Engine) is used by Query/400 © 2009 IBM Corporation

System i Query/Reporting Environment System i C/ ODB C JDB DB 2 Windows ©

Today’s Business Intelligence Requirements • Remove Dependency on IT for reporting purposes – Ease IT backlog of reporting requests – Parameterized Reporting • Reduce Report Maintenance • Empower End Users • Client Independence – Web Based • Reduce Software Maintenance • Multiple Viewing Options – – • Dashboards/Scorecards Spreadsheet Integration Static Reports/Intelligent Bursting Report Archival Operational or Data Warehouse – Real time or Historical Reporting • Secure the Data – Avoid Data replication – Adhere to compliance requirements © 2009 IBM Corporation

IBM DB 2 Web Query for i • Base Program Product Includes: – i 5/OS Web Reporting Server – Several Web Based authoring tools • RA, GA, Power Painter – Query/400 (5722 -QU 1) • Web Enable Query/400 Reports – BASE PRODUCT OFFERED AS NO CHARGE UPGRADE FROM QU 1 • Does not include Software Maintenance • Additional Features – Run Time User Enablement – Active Reports (Disconnected Analysis) – On Line Analytical Processing • Requires Meta Data provided with Developer Workbench – Developer Workbench • IT Tool for meta data • DB 2 Web Query Report Broker – Automated Report Execution and Distribution • DB 2 Web Query SDK – Web Services to integrate reporting functions into applications/portals http: //www. ibm. com/systems/i/db 2/webquery © 2009 IBM Corporation

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 DB 2 into Spreadsheets – Over 100 charts and graphs – Boardroom quality PDF output – Access remote DB 2 for i data sources – Leverages the LATEST in DB 2 for IBM i Query Optimization Technology • Better performance, better analysis tools • Many more functions available for query performance • Strategic optimizer © 2009 IBM Corporation

DB 2 WQ Leverages Advanced DB 2 Query Optimization • SQL Query Engine (SQE) – Better Performance – up to 10 x 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 http: //www. ibm. com/systems/i/software/db 2/awp. html © 2009 IBM Corporation

Query/400 vs. DB 2 Web Query Function Query/400 DB 2 Web Query Base + IBM Features UI for Query Developer 5250 Browser (BASE) or Windows (Developer Workbench) UI for End User 5250 Browser or Windows (Active Reports or output formats requiring Windows, e. g. , Excel) Remote DB 2 for i 5/OS Access No Via DRDA (BASE) Interactive Output 5250, Spoofile, DB 2 Table PDF, XML, Spreadsheet, HTML, PC-File Formats, Spoolfile, DB 2 Table, etc. (BASE) Batch Scheduling and Output i 5/OS Scheduler; Spoolfile, DB 2 Table YES. Basic function with BASE, advanced function (distribution) with Report Broker Traffic Lighting No Yes (BASE) Linked Reports with Drill Down No Yes (BASE) OLAP No Yes (OLAP Feature) Disconnected Analysis No Yes (Active Reports Feature) Dashboarding No Yes (requires Developer Workbench) Parameterized Reporting Prompting via 5250 RUNQRY Single Parameter Yes (BASE), advanced parameterization controls with Dev. Workbench Meta Data Layer No Yes (customization of meta data available with Dev. Workbench) © 2009 IBM Corporation

Query/400 vs. DB 2 Web Query Function Query/400 DB 2 Web Query Base + IBM Features Unlimited User Licensing Yes Named or Run Time (Concurrent, effectively unlimited) i 5/OS Support V 5 R 2 and up V 5 R 4, V 6 R 1 Report Storage No Local report storage with Report Broker Target Audience for developing reports IT IT and Business Community Portal Integration No With SDK Integration with Operational application Via Run. Qry API As callable URL from Browser, or through Web Services with SDK Web Application Server Support N/A Comes with Lightweight Web Server Compound Reports No Yes (BASE), additional capabilities with Dev. Workbench) Excel Integration No, must run Query, Download, then Import Yes, Excel is an output option with report characteristics preserved DB 2 for i 5/OS Optimization CQE Only SQE or CQE Impact Analysis and Data Profiling No Yes (Developer workbench) © 2009 IBM Corporation

DB 2 Web Query vs. Competition DB 2 Web Query Crystal Reports Y Y Native DRDA ODBC Multiple Member File Support Y N Multi record format files Y N Import Query/400 Definitions Y N Meta Data Layer Y N IBM i (Integrated) Windows DB 2 based – no need to move data Requires add’l OLAP engine in Windows Disconnected Support Y, with Active Reports N Report Distribution Y, with Report Broker Y – Windows Report Caching Y, with Report Broker Y - Windows IBM i (Integrated) Windows Cross DB 2 for i Joins Y N Report Execution from 5250 app Y N Access Data in DB 2 for i Interface to DB 2 for i Reporting Server OS support OLAP Web Server OS support © 2009 IBM Corporation

DB 2 Web Query for i 시작 © 2009 IBM Corporation

DB 2 Web Query Architecture DB 2 Web Query Servlet © 2009 IBM Corporation

PC and System i Requirements • PC – Internet Explorer v 6. 0 or higher, Firefox v 1. 5 or higher – Memory of 1 GB or more • System i – – – V 5 R 4 of i 5/OS 이상 Qshell – option 30 of the OS 5722 JV 1 – Java Runtime Environment 1. 4 or higher 5722 -SS 1 – option 33 – PASE (maybe) 5722 -JC 1 – IBM Toolbox for Java 5722 -DG 1 – IBM HTTP Server for i 5/OS © 2009 IBM Corporation

Installation Procedure (1) 1. QSECOFR 또는 *SECADM and *ALLOBJ 권한을 가진 User로 Signon. 2. Base product설치 RSTLICPGM(5733 QU 2) DEV(OPT 01) 3. 기타 additional option들을 설치. – For Active Reports, enter: RSTLICPGM(5733 QU 2) DEV(OPT 01) OPTION(1) – For OLAP Enablement option, enter: RSTLICPGM(5733 QU 2) DEV(OPT 01) OPTION(2) – For Developer Workbench, enter: RSTLICPGM(5733 QU 2) DEV(OPT 01) OPTION(3) 4. license agreement화면에서 F 14를 이용해 accept. 5. APAR II 14318 (For R 110), APAR II 14375 (For R 111)에 나열된 최신 5733 -QU 2 PTFs 설치. © 2009 IBM Corporation

Installation Procedure (2) 6. DB 2 Web Query HTTP instance를 등록: CALL QWEBQRY 76/REGWEBQRY 7. 설치 후, QWEBQRYADM User Profile의 Password를 Setting. CHGUSRPRF(QWEBQRYADM) PASSWORD(yourpassword) 8. 한글 Data Handling을 위해 NLS Configuration을 수행. a. QSH 명령창에서 아래 명령어 수행 /qibm/proddata/webquery/ibi/srv 76/home/bin/tsgult. out NLSCFG /qibm/userdata/webquery/ibi/srv 76/wfs 933 b. QWEBQRYADM User ID를 이용해 DB 2 Web Query Console에 접속. Configuration->NLS Settings에서 Client Code Page를 ‘ 949 -Korea’를 선택. http: //Server_Name: 11331/webquery_html/wfconsole. htm c. Log-in 창에서 언어 선택을 가능하도록 하기 위해서 Configuration->Dynamic Language Switch 에서 표시를 원하는 언어를 선택. d. /QIBM/User. Data/webquery/ibi/webfocus 76/WQLWI 7/lwi/conf/overrides/i 5 javaopts. javaopt File에 아래 Option을 추가 -Dfile. encoding=Cp 970 9. 아래 URL을 통해 전반적인 Configuration Properties를 확인할 수 있습니다. http: //Server_Name: 11331/webquery/properties. jsp © 2009 IBM Corporation

DB 2 Web Query관련 i/OS Objects • IFS Directories – /QIBM/PRODDATA/WEBQUERY/IBI – /QIBM/USERDATA/WEBQUERY/IBI • System Objects – – – – QWEBQRY 76 *LIB QWEBBASE *LIB QSYS/QWEBQRYADM *USRPRF QSYS/MRADMIN *USRPRF QSYS/MDUNTITLED *USRPRF QSYS/QWEBQRY *AUTL QWEBQRY 76/QWEBQRYJOB *JOBD QUSRSYS/QWEBQRYADM MSGQ © 2009 IBM Corporation

사용 User 등록 및 확인 • DB 2 Web Query를 사용할 각각의 User 들을 아래와 같이 등록. 이때, USRPFR 은 반드시 대문자로 사용. CALL QWEBQRY 76/WQADDLIC ‘USRPRF’ • 사용자를 제거할 경우; CALL QWEBQRY 76/WQRLSLIC ‘USRPRF’ • 등록된 User들을 확인하려면, WRKLICINF PRDID(5733 QU 2) 상에서 Feature 5050에 Op. 8으로 확인/ © 2009 IBM Corporation

Server Start/End 및 System Jobs • • QWEBQRYADM으로 Signon Web Query Reporting Server Start STRWEBQRY START(*ALL) Web Query Reporting Server End ENDWEBQRY OPTION(*CLEAR) DB 2 Web Query Connect는 SQL CLI (Call-Level-Interface)를 이용 – 각각의 Connection은 QSYSWRK Subsystem상의 QSQSRVR job들로 수행됨 • Reporting Server Job들은 QSYSWRK와 QHTTPSVR Subsystem에서 수행됨. – QSYSWRK • EDAPTH, EDAPLOG, EDAPGWY, TSCOM 3, JSCOM 3, HLISNK – QHTTPSVR • WQLWI 7 © 2009 IBM Corporation

Access to DB 2 Web Query • DB 2 Web Query의 Default Port: 11331

Creating Metadata • • • Report밑의 아무 폴더에서 우측마우스 Click후, Metadata선택 Data Adapter창에서 원하는 Adapter Type을 선택하여 Create Synonym선택 Synonym을 만들 대상을 선택 – Table, Alias, View, MQT, Stored Procedure • Create Synonym button을 Click하여 생 성 – 이렇게 만들어진 Synonym을 DB 2 Web Query에서 사용하게 된다. – DB Format등이 바뀌면 Synonym을 다시 Create해줘야 한다. • 생성된 Metadata를 삭제하려면, WRKLNK ‘ /QIBM/USERDATA/IBI/APPS/BASEAP P’ directory내에서 찾아 지워야 함. © 2009 IBM Corporation

DB 2 Web Query Features © 2009 IBM Corporation

Report Assistant: Drill Down © 2009 IBM Corporation

Output Reports and Graphs to: • • • HTML PDF Excel 97/2 k/2 k w formula's Active Reports Postscript • DB 2 for i table • • Excel 95 Doc with and without page breaks WK 1 DIF Lotus Tab Delimited XML © 2009 IBM Corporation

MS Excel과의 호환성 • • DB 2 Web Query는 MS Excel과의 integration을 제공 완벽히 호환되는 Excel spreadsheets로의 변환 – W/ Drilldowns, Formulas, Summations, Color-coding & more • end user productivity를 향상; errors의 감소 Formulas/Summations Color-Coding Hyperlinks/Drilldowns © 2009 IBM Corporation

Power Painter • Business people이 자신들이 원 하는 reports들 직접 Design할 수 있다. • 강력한 thin-AJAX 기반의 Report Design tool • 각자의 Business Unit에 필요한 다양한 Compound Reports 작성 • IT의 의존성 없이 쉽고 탄력적인 접근성 제공 © 2009 IBM Corporation

Optional Features from IBM • DB 2 Web Query OLAP – Slicing and dicing – Multi-dimensional functionality • DB 2 Web Query Developer Workbench – Rich client – Prerequisite for OLAP module – Customize metadata, HTML layout tool • DB 2 Web Query Active Reports – Offline reporting – Self-contained HTML file © 2009 IBM Corporation

On-Line Analytical Processing (OLAP) © 2009 IBM Corporation

DB 2 Web Query Developer Workbench • Excellent Date understanding and added functionality including Date decomposition (Year, Quarter, Month, Day) • Creation of Hierarchies and Dimension • Very good awareness of DB 2 Foreign Key constraints • Basic DB 2 Web Query metadata also includes: Column-titles, Business Descriptions, Alias, Format, Usage, Pre and Post Processing Calculations, hundreds of data manipulation functions • Federated Queries • SQL Wizard • HTML Layout painter © 2009 IBM Corporation

Active Reports: Disconnected Support • 독립적으로 수행 가능한 analytical reports 제공 – Email이나 Browser를 통해 User에게 전달가능 • System과의 연결없이 Report상의 Data를 User가 직접 가공 • 직접 수행 가능한 built-in controls – Sorting, Filtering, Visualization, Charting, Dynamic Roll-ups – Export to HTML, CSV or Excel – Export Charts to Word, Excel, or Power. Point • System i와의 연결에 어려움이 있는 Mobile sales reps 과 같은 User에게 적합 © 2009 IBM Corporation

DB 2 Web Query Report Broker – 5733 -QU 3 • 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 DB 2 Web Query BASE Product to be installed © 2009 IBM Corporation

DB 2 Web Query Software Development Kit: 5733 -QU 4 • A set of Web Services that allow invocation of DB 2 Web Query functions from within an application – Enables ISV Developers to access and integrate Web Query Content into any web application • Web Services are programming language independent – The foundation for SOA – C, C++, VB. NET, Java, etc. • Requires the BASE product to be installed (run time and development) – Developer Workbench also a development requirement – Run Time User Enablement required for Run Time • Simple to Deliver powerful BI content – Self service parameterized reports – Active reports and OLAP © 2009 IBM Corporation

Useful Links • Getting Started with DB 2 Web Query Redbook – Tutorial Driven – Download Sample Database – http: //www. redbooks. ibm. com/abstracts/sg 247214. html • Review Installation Instructions on “Getting Started” website: – http: //www. ibm. com/systems/i/db 2/webquery • Download an installation pre-req utility – NEW utilities to check for all DB 2 WQ Pre-Reqs • IBM pre req checker: http: //www. ibm. com/developerworks/wikis/display/webquery/DB 2+Web+Query+for +i+Installation • Centerfield Technologies: http: //www. centerfieldtechnology. com/wq_icv. asp © 2009 IBM Corporation