O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

IBM DB2 Web Query for i: The Nuts and Bolts

Book Description

Abstract

Business Intelligence (BI) is a broad term that relates to applications that analyze data to understand and act on the key metrics that drive profitability in an enterprise. Key to analyzing that data is providing fast, easy access to it while delivering it in formats or tools that best fit the needs of the user.

At the core of any BI solution are user query and reporting tools that provide intuitive access to data supporting a spectrum of users from executives to “power users,” from spreadsheet aficionados to the external Internet consumer.

IBM® DB2® Web Query for i offers a set of modernized tools for a more robust, extensible, and productive reporting solution than the popular IBM Query for System i® tool (also known as IBM Query/400). IBM DB2 Web Query for i preserves investments in the reports that are developed with Query/400 by offering a choice of importing definitions into the new technology or continuing to run existing Query/400 reports as is. But, it also offers significant productivity and performance enhancements by leveraging the latest in DB2 for i query optimization technology.

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 the ability to query or build reports against data that is stored in DB2 for i (or Microsoft SQL Server) databases through browser-based user interface technologies:


  • Build reports with ease through the web-based, ribbon-like InfoAssist tool that leverages a common look and feel that can extend the number of personnel that can generate their own reports.

  • Simplify the management of reports by significantly reducing the number of report definitions that are required through the use of parameter driven reports.

  • Deliver data to users in many different formats, including directly into spreadsheets, or in boardroom-quality PDF format, or viewed from the browser in HTML.

  • Leverage advanced reporting functions, such as matrix reporting, ranking, color coding, drill-down, and font customization to enhance the visualization of DB2 data.


  • DB2 Web Query offers features to import Query/400 definitions and enhance their look and functions. By using it, you can add OLAP-like slicing and dicing to the reports or view reports in disconnected mode for users on the go.

    This IBM Redbooks® publication provides a broad understanding of what can be done with the DB2 Web Query product. This publication is a companion of DB2 Web Query Tutorials, SG24-8378, which has a group of self-explanatory tutorials to help you get up to speed quickly.

    Table of Contents

    1. Front cover
    2. Notices
      1. Trademarks
    3. Preface
      1. Authors
      2. Now you can become a published author, too!
      3. Comments welcome
      4. Stay connected to IBM Redbooks
    4. Chapter 1. The cycle of Business Intelligence and IBM DB2 Web Query for i
      1. 1.1 The process of implementing Business Intelligence with DB2 Web Query for i
      2. 1.2 Step 1: Identifying the requirements
      3. 1.3 Data source analysis
      4. 1.4 Designing the solution
      5. 1.5 Building and deploying reports and dashboards
      6. 1.6 Tuning, monitoring, and auditing
      7. 1.7 Starting the cycle again
    5. Chapter 2. Working with IBM DB2 Web Query for i
      1. 2.1 Videos and important websites
      2. 2.2 Simple reports
        1. 2.2.1 Revenue and gross summary report
        2. 2.2.2 Top 10 products by margin
        3. 2.2.3 Revenue summary with subtotals
        4. 2.2.4 Simple revenue report group by year
      3. 2.3 Date functions in reports
        1. 2.3.1 Dates using DB2 Web Query for i functions
        2. 2.3.2 Dates using an SQL prefix
        3. 2.3.3 Dates using a date table
      4. 2.4 Various report features and formatting
        1. 2.4.1 Table of Contents
        2. 2.4.2 Accordion
        3. 2.4.3 Pages on Demand
        4. 2.4.4 Stack Measures
        5. 2.4.5 Format choices
      5. 2.5 Charting
        1. 2.5.1 Bar chart: on time delivery by product type
        2. 2.5.2 Vertical stacked area: orders backlog by period
        3. 2.5.3 Gauge chart: on time delivery overall
        4. 2.5.4 Map: revenue by state
      6. 2.6 Filters
        1. 2.6.1 Multiple filters in reports and charts
        2. 2.6.2 InfoMini slicers
      7. 2.7 Drilling down
      8. 2.8 Documents and dashboards
        1. 2.8.1 Documents and dashboards in InfoAssist
        2. 2.8.2 Developer Workbench HTML Composer
        3. 2.8.3 Spreadsheet Client
      9. 2.9 Mobile
      10. 2.10 Scheduling and distribution of reports
      11. 2.11 Wizard
      12. 2.12 Services
      13. 2.13 IBM i Compliance and Reporting Tool
    6. Chapter 3. Installation and server operations
      1. 3.1 Installation and setup
        1. 3.1.1 Installing DB2 Web Query for i
        2. 3.1.2 Authorizing and verifying users
        3. 3.1.3 License keys
        4. 3.1.4 Dynamic Language Switching
        5. 3.1.5 Sample database
      2. 3.2 Requirements
        1. 3.2.1 IBM i requirements
        2. 3.2.2 Web browser requirements
        3. 3.2.3 PC requirements for Developer Workbench
      3. 3.3 Administrative commands
      4. 3.4 Subsystem and jobs
      5. 3.5 Port usage
      6. 3.6 Running at subcapacity
      7. 3.7 Running QU2 and WQX concurrently
    7. Chapter 4. Defining metadata
      1. 4.1 What is metadata
        1. 4.1.1 Benefits of metadata
      2. 4.2 Creating metadata
        1. 4.2.1 DB2 Web Query for i Metadata interfaces
        2. 4.2.2 IBM DB2 Web Query for i DataMigrator ETL extension
      3. 4.3 Editing and enhancing metadata
      4. 4.4 Example: Creating metadata with Developer Workbench
    8. Chapter 5. Date and time functions
      1. 5.1 The importance of dates in reporting
      2. 5.2 Handling legacy dates
        1. 5.2.1 Modernizing your database
        2. 5.2.2 Using DB2 Web Query for i functions to convert to smart dates
        3. 5.2.3 Using SQL functions and views to convert dates
        4. 5.2.4 Using a date dimension table
        5. 5.2.5 Comparing the legacy date conversion techniques
        6. 5.2.6 Example: Converting date fields in the Oracle JD Edwards World application
        7. 5.2.7 Converting other common legacy date formats
      3. 5.3 Using dates and date components
        1. 5.3.1 Date decomposition
        2. 5.3.2 Date formatting
        3. 5.3.3 Changing the date format
      4. 5.4 DB2 Web Query for i date built-in functions
        1. 5.4.1 DPART: Extracting a date component from a date field
        2. 5.4.2 DATEADD: Adding or subtracting a date unit to or from a date
        3. 5.4.3 DATEDIF: Calculating the difference between two dates
        4. 5.4.4 DATEMOV: Moving the date to a significant point
      5. 5.5 Example: Dynamic Date Range report
      6. 5.6 Date and time system variables
      7. 5.7 Date format
        1. 5.7.1 Date format display options
        2. 5.7.2 Controlling the date separator
        3. 5.7.3 Using date fields
        4. 5.7.4 Date fields in arithmetic expressions
    9. Chapter 6. IBM DB2 Web Query for i Security Center
      1. 6.1 Security architecture and concepts
        1. 6.1.1 Security architecture
        2. 6.1.2 Security concepts
      2. 6.2 IBM i security
      3. 6.3 Groups and their capabilities
      4. 6.4 Top-level folders and subfolders
        1. 6.4.1 Top-level folders
        2. 6.4.2 Subfolders
        3. 6.4.3 Controlling accessibility to subfolders and reporting objects
        4. 6.4.4 Controlling order of elements
      5. 6.5 The Register DB2 Web Query for i User (REGWQUSR) command
    10. Chapter 7. Accessing additional data sources
      1. 7.1 Connecting to DB2 for Linux, UNIX, or Windows or z/OS
        1. 7.1.1 Preparing the DB2 for Linux, UNIX, or Windows or z/OS environment
        2. 7.1.2 Configuring the adapter for DB2 for Linux, UNIX, or Windows or z/OS
        3. 7.1.3 Creating metadata
      2. 7.2 Using the adapter for Microsoft SQL Server
        1. 7.2.1 Preparing the Microsoft SQL Server environment
        2. 7.2.2 Establishing a connection to your Microsoft SQL Server
        3. 7.2.3 Creating metadata
      3. 7.3 Using the adapter for MySQL
        1. 7.3.1 Preparing the MySQL environment
        2. 7.3.2 Configuring the adapter for MySQL
        3. 7.3.3 Creating metadata
      4. 7.4 Using the adapter for PostgreSQL
        1. 7.4.1 Preparing the PostgreSQL environment
        2. 7.4.2 Configuring the adapter for PostgreSQL
        3. 7.4.3 Creating metadata
      5. 7.5 Using the generic adapter for JDBC
        1. 7.5.1 Setting up JDBC environment
        2. 7.5.2 Configuring the adapter for JDBC
        3. 7.5.3 Creating metadata
      6. 7.6 Using adapters for JD Edwards
        1. 7.6.1 Areas to consider when connecting to a JD Edwards database
        2. 7.6.2 Using a JD Edwards adapter to connect to a remote JD Edwards database
        3. 7.6.3 JD Edwards dictionary files that are needed on local system to access remote JD Edwards databases
        4. 7.6.4 Using the adapter for JD Edwards World
        5. 7.6.5 Using the adapter for JD Edwards EnterpriseOne
    11. Chapter 8. Managing data with DataMigrator
      1. 8.1 Product overview
      2. 8.2 Product components
        1. 8.2.1 Data Management Console
        2. 8.2.2 The DataMigrator server
      3. 8.3 Product installation
      4. 8.4 Product examples: Flows
      5. 8.5 Product examples: Data profiling
      6. 8.6 Summary and more information
    12. Chapter 9. Integrating web data into your custom application
      1. 9.1 Initial setup
      2. 9.2 Using the extension
        1. 9.2.1 Modes of use
        2. 9.2.2 Direct mode
      3. 9.3 Report parameter considerations
      4. 9.4 Authentication methods
        1. 9.4.1 Basic authentication
        2. 9.4.2 URL parameter authentication
        3. 9.4.3 Static authentication
        4. 9.4.4 Application authentication
      5. 9.5 Dynamic Run Time Environment support
      6. 9.6 Restrictions
      7. 9.7 Properties file and options
        1. 9.7.1 Server
        2. 9.7.2 Port
        3. 9.7.3 Basic authentication
        4. 9.7.4 User and password
        5. 9.7.5 Browse mode
        6. 9.7.6 Cache timeout
        7. 9.7.7 Token timeout
        8. 9.7.8 Template heading
        9. 9.7.9 Template footing
        10. 9.7.10 Parameter prompt limit
        11. 9.7.11 Result schema
        12. 9.7.12 Enable statistics
        13. 9.7.13 Debug mode
        14. 9.7.14 Parameter prompt
        15. 9.7.15 About link
        16. 9.7.16 WQRAX_USER fex
        17. 9.7.17 Timeout redirect URL
        18. 9.7.18 Email output
        19. 9.7.19 SMTP server
        20. 9.7.20 Sender
    13. Appendix A. DB2 Web Query for i amper variables
      1. System variables
      2. Dynamic prompting variables
    14. Back cover