IBM DB2 UDB Query Manager and SQL Development Kit

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 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:

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:

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:

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:

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.

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

To draw insights and make better decisions, businesses rely on the secure, flexible and open platform of IBM Power Systems. Built with the first processor designed for big data workloads, the design of Power Systems combines the computing power, memory bandwidth and I/O in ways that are easier to consume and manage, building on strong resiliency, availability and security.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

Transform your business with Systems Software that enables virtualization, high availability, flexibility, security and compliance on Power Systems™. IBM’s integrated approach to developing Systems and Systems Software together delivers optimized results with Power Systems.

As an open innovation platform, Power Systems is optimized for big data and analytics performance and to deliver scale-out economics and security for the cloud. IBM and IBM Business Partner solutions exploit key capabilities in IBM Power Systems.

Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.