Tab navigation
Introduction
IBM® DB2 for i--the database manager fully integrated into the IBM i® operating system--is an advanced database server that provides access to, and protection for, your critical enterprise data. With the IBM DB2 Query Manager and SQL Development Kit for IBM i product, developers can take advantage of the richer set of features and functions only accessible from the SQL interfaces included with this powerful, built-in relational database.
DB2 Query Manager and SQL Development Kit for IBM i brings you precompilers and tools to assist in writing SQL application programs in high-level programming languages as well as an interactive query and report writing interface.
- SQL Development Kit contains SQL precompilers for developing database applications in several programming languages. This kit also includes Interactive SQL, a prompt-driven interface for testing and running SQL statements.
- Query Manager is a prompt-driven interactive interface that lets you develop reports that fit your needs. The Query Manager interface is only accessible from a 5250 emulator session. The IBM DB2 Web Query for i product offers a set of strategic graphical tools for a more robust, extensible and productive reporting solution
Conforming to the latest SQL industry standards, the DB2 for i SQL implementation allows you to define, manipulate, query, and control access to your IBM i data. It works equally well with IBM i physical files and SQL tables.
SQL access of DB2 for i databases
DB2 Query Manager and SQL Development Kit for IBM i enables SQL-based access of DB2 for i relational databases through high-level language programs and reports. The SQL precompilers for C++, C, COBOL, RPG, and PL/I are all provided in the DB2 Query Manager and SQL Development Kit for IBM i, along with support for SQL statements in REXX procedures.
It is important to understand that the DB2 Query Manager and SQL Development Kit for IBM i product must only be installed on the systems used to create high-level language programs with embedded SQL and Query Manager reports; it is not required to run them. The SQL runtime license is automatically included with every IBM i system. Any application program or query definition containing SQL, once it is created, can be run on any IBM i system, with or without this product installed.
This integrated SQL runtime support also includes support for the SQL CLI (Call Level Interface) API set. In addition, the IBM i operating system includes middleware for popular SQL programming interfaces. An ODBC driver along with ADO.NET and OLE DB providers is the available in the IBM i Access for Windows offering. The IBM Toolbox for Java and JTOpen offering includes a Type-4 JDBC driver for seamless access of DB2 for i database. A Type-2 JDBC driver is available in the IBM Developer Kit for Java.
Cross-platform portability
SQL is the acknowledged standard for accessing data from both programs and end-user queries. Applications built using SQL statements make an application much more portable between dissimilar systems, allowing richer possibilities for multi-platform installations. With the DB2 for i SQL implementation, applications can be easily ported from other database systems to IBM i. This allows you to remain competitive in delivering applications on a wide variety of computing platforms. Furthermore,because SQL is such a widely used standard, you can take advantage of existing SQL knowledge and application development skills in your enterprise.
Industry-leading advanced features
The IBM i SQL implementation conforms to the core level of the latest ANSI, ISO, and FIPS SQL standards. And beyond supporting industry standards, it offers many advanced features that set it apart from other SQL implementations. Among these features are:
- SQL stored procedures and triggers
- Support for using national languages, double-byte and Unicode character sets, and sort sequences
- Backward and forward scrollable cursors
- User-defined scalar and table functions
- Super-groups and grouping sets
- Hierarchical query support
Enhanced application performance
The DB2 for i SQL implementation provides many features such as in-memory database enablement and SSD support to enhance application performance. High-performing SQL starts with the DB2 for i cost-based query optimizer which includes advanced query rewrite capabilities and self-learning techniques to enable self-tuning of complex SQL requests. In addition, developers can use the Visual Explain tooling to graphically understand exactly how an SQL request was optimized to determine how to best change the request or system environment to improve performance.
Other key features that differentiate the performance of the DB2 for i SQL implementation from other databases include:
- Encoded vector indexes
- Options to insert and fetch data in blocks, rather than just one row at a time.
- Scrollable cursors, to allow applications and users to go backward or forward through the data for spreadsheet applications
- Automatic parallel processing with the DB2 SMP licensed feature
Easy to use interfaces, with designed-in flexibility
The Query Manager component is a powerful query and reporting tool that is an integral part of the DB2 Query Manager and SQL Development Kit for IBM i. Using an interactive interface, you can build and store queries that take full advantage of the power of the SQL language. You choose from Query Manager's two types of queries:
- Prompted queries for users unfamiliar with SQL or needing only simple query capabilities
- SQL queries for users who are familiar with or need to use the advanced features of the SQL language
You also decide whether to run queries interactively or in batch mode.
Reports for users' needs
Query Manager also enables you to develop reports in formats that are tailored to your users' needs. You use a prompt-driven interface to design report forms that are then used for formatting data returned by running a Query Manager query. Some key features include:
- Producing multiple reports with different formats using the data from a single query
- Including up to 999 rows of heading and footing text for page, break, and final level
- Embedding data values within report text headings or footings
As a bonus, you can take advantage of Query Manager's easy-to-use interface to create and maintain database information in tables.
Built-in productivity
Interactive SQL features a prototyping capability that allows you to test SQL statements before including them in an application. It should be noted that the strategic testing and scripting tool for DB2 for i is the Run SQL Scripts interface within the System i Navigator client. The Run SQL Scripts interface also enables SQL performance analysis with its Visual Explain toolset.
With these tools, you save time because you can make specific changes to the database without writing any code. For example, what if you wanted to test an SQL statement that would increase prices by seven percent in all parts in the inventory that have the word "PAINT" in the description? The following example shows how you could do this with Interactive SQL, without writing any application code:
UPDATE MYLIB.INVENTORY SET PRICE=PRICE*1.07 WHERE DESCR LIKE '%PAINT%'
Once statements have been tested, they can be embedded in an application program. The Rational Developer for Power Systems Software toolset includes wizards and coding templates that make it easy to embed SQL statements within high-level language application programs.
Your competitive edge
As today's computing environments evolve, they are becoming more and more complex. In a distributed environment, applications critical to an enterprise are spread across multiple systems. As an application provider or an application user, you need the tools to enable portable, flexible database solutions for these complex environments.
The DB2 Query Manager and SQL Development Kit for IBM i can help with this challenge by enabling applications to utilize industry standard SQL and integrated support for remote database access. Add to this the easy-to-use query and development interfaces along with the performance and advanced functionality only available to SQL interfaces. These are the qualities that set the DB2 Query Manager and SQL Development Kit for IBM i apart from the rest.
Send comments to:
rchudb@us.ibm.com.
We're here to help
Easy ways to get the answers you need.
or call us at
1-866-883-8901
Priority code:
101AR13W
Additional info
DB2 Web Query
Web based query and reporting
IBM i for BI Solution
IBM i for Business Intelligence is a packaged solution that is easy to order and easy to implement, and easy to maintain. Everything you need for an out of box analytical solution based on IBM i and DB2 Web Query.