CETi 1982 Technology
Technology Driven IT Training



DB2 Technology Update Knowledge Base Computer Education CETi


DB2 Version 11

IBM DB2 11 provides enhancements in data integration with SQL: Structured Query Language, analytics, and in-database scoring technology. Application performance has been improved with SQL, SQL PL, temporal, and pureXML functions. There are intelligent statistics for optimization and query execution in dynamic workloads. Online schema changes can be used to simplify management and both reduce requirements for planned outages and execute the REORG.

DB2 11 connects to the IBM Hadoop based BigInsights platform using SQL; it allows for integrating traditional applications on DB2 for z/OS with big data analytics.


DB2 Version 10

DB2 Data Mining and Business Intelligence

DB2 provides the capability to deliver temporal and versioned data to manage the business models of organizational enterprise. In the short-run it is a feature not available on Oracle Corporation or any other competing RDBMS products. DB2 is used in IBM InfoSphere Warehouse and provides data warehouse capabilities and a BI: Business Intelligence featureset: ETL, data mining, OLAP acceleration, and in-line analytics. There are several different InfoSphere Warehouse editions available for z/OS, Linux, UNIX, and MS Windows platforms.

DB2 10 for z/OS expands the value of mainframe data server through:

  • Improved operational efficiencies for CPU savings.
  • Enhancements to XML and SQL for simplifying portability from other databases.
  • Rapid application and warehouse deployment for business growth.

In addition to improved system performance, IBM has made enhancements to DB2 Version 10 in the following areas:

  • Better information is provided to the optimizer with the benefits accruing to query types.
  • INSERT, UPDATE, and DELETE statements provide improvements in processing.
  • The online schema provides the capability to add partitions and change data definitions with ALTER statements and online unload and replace.


  • There are database design options for indexes, clustering, and materialized query tables; this includes the ability to rotate partitions which will support complex warehouses.

CETi technology partners are evaluating the IBM product announcements regarding improvements made to DB2 version 10 in these areas:

  • Standards-based capabilities for SOA requirements.
  • SQLJ and JDBC support.
  • UNICODE support.
  • WebSphere integration.

DB2 Version 9

DB2 9 allows for small LOBs: Larger Objects to be stored in-line. The smaller LOBs can be physically stored with columns in a table. This means that the LOB and the rest of the columns in the row will fit within a page size. When there is frequent access to the LOB columns, improved performance will result from the regular and LOB columns being with one physical I/O rather than two. In-line LOB's also will be kept in the bufferpool which can increase performance.

The DB2 Control Center has been deprecated and is being replaced with the new Data Center. Data Studio will be the platform from which database and development tools will be launched and integrated. In Data Studio, all database related products will have a view called the Data Source Explorer. Each database object change management or data modeling tool will be an additional view and the functions will be added to the same Data Studio launch pad.

IBM DB2 Version 9 offers the capability to reduce storage requirements by using automatic deep compression capabilities. There is a DB2 Developer Workbench for creating, editing, debugging, testing, and deploying DB2 stored procedures and user-defined functions.

The XQuery language was developed by the W3C: World Wide Web Consortium. DB2 Version 9 provides the capability to invoke the XQuery language directly and call functions that extract XML data from DB2 tables and views.


DB2 and Oracle SQL and PL/SQL Support

IBM DB2 Version 9.7 supports Oracle's SQL and PL/SQL dialects. This allows many applications written against Oracle to execute against DB2. In order for an application written for Oracle to run on unchanged on DB2 different locking mechanisms, data-types, SQL, procedural language residing on the server, and the client interfaces used by the application itself need to be aligned in syntax and semantics. All these steps have been taken in DB2.

When an application is ported from one product to another, the SQL and procedural language is translated from one SQL dialect to the other. In order to avoid the normal problems which occur, IBM provides native PL/SQL. The DB2 engine includes a PL/SQL compiler side by side with the SQL PL compiler. Both compilers produce virtual machine code for DB2's SQL Unified Runtime Engine.

PL/SQL support is available only in the Enterprise Server Edition and Workgroup Edition of DB2 9.7 for LUW.

SYS-ED and CETi Technology Partners are evaluating the degree to which Oracle applications can run unchanged under DB2. To date, the results have been impressive.

SYS-ED workshops explain and demonstrate:

  • How to manage performance for mixed workloads set by priorities.
  • Reducing risk associated with unauthorized access utilizing a label based security model.
  • Proactive management of system performance.
  • Management for accessing remote DB2 and Informix data servers as local tables.
  • The XQuery builder for creating queries against XML data.
  • How to use the Developer Workbench for developing SQLJ applications and creating, editing, and running SQL statements and XML queries.
  • How to employ table partitioning for dividing data across multiple storage objects or ranges according to values in one or more table columns.
  • MQT: Materialized query table enhancements and the improved support for designing and maintaining MQT.
  • How to utilize the larger record identifiers in order to allow for more data pages per object and more records per page for system.
  • The user temporary tables used by the database manager while performing sort and join operations.