Sat, Sep
0 New Articles

DB2 11: The Ultimate Database for Cloud, Analytics, and Mobile on IBM z Systems

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times


IBM database experts delineate the analytics improvements in DB2 11 for z/OS.


Editor's Note: This article is an excerpt from the new book DB2 11: The Ultimate Database for Cloud, Analytics, and Mobile (MC Press, 2014).


In Version 11, IBM DB2 for z/OS makes it easier to bring analytic components closer to the core operational data—reducing latency, complexity, and costs while improving data quality and governance. Improved query, fast analytics, and reporting facilities can improve competitiveness, reduce risks, and aid confident decision-making with real-time data.


The analytics improvements in DB2 11 include temporal data enhancements with support for views and special registers, transparent archive query, new analytics features, and integration with big data.


Temporal Data Enhancements

DB2 11 introduces support for a period specification when a view is referenced in a FROM clause by an SQL SELECT, UPDATE, or DELETE statement. A couple of examples illustrate how that works.


The first example is a SELECT against a view on a table with a system time specification, where the SELECT specifies a system time period:


CREATE VIEW v01 (col1, col2, col3) AS SELECT * FROM stt;



   FOR SYSTEM_TIME AS OF TIMESTAMP '2010-01-10 10:00:00';


The second example references a similar sort of view, this time on a table with a business time specification; in this case, an UPDATE and a DELETE are issued against the view, specifying a portion of business time.


CREATE VIEW v8 (col1, col2, col3) AS SELECT * FROM att;



   FOR PORTION OF BUSINESS_TIME FROM '2009-01-01' TO '2009-06-01'


SET c2 = c2 + 1.10;



   FOR PORTION OF BUSINESS_TIME FROM '2009-01-01' TO '2009-06-01'

   WHERE COL1 = 12345;


To enable you to retrieve data from temporal tables without modifying existing SQL, DB2 11 adds support for two temporal special registers, CURRENT TEMPORAL SYSTEM_TIME and CURRENT TEMPORAL BUSINESS_TIME. You can set these special registers to specify a system time or a business period and then execute existing SQL statements as if they had the system time or business period specified.


Two new BIND options, SYSTIMESENSITIVE and BUSTIMESENSITIVE, are used to enable use of the new temporal special registers. To allow the special registers to modify the execution of your queries, you must set one or both of the new BIND options to YES, depending on which special registers you want to use.


Transparent Archive Query

Transparent archive query is designed for the case where only a portion of your data is active or current and is possibly dynamic and subject to INSERT, UPDATE, and DELETE processing. The rest of the data is read-only historical data and is probably referenced infrequently. What you might want to do is to store this data in two tables. The first table would be the current data table, which you'd want to have on high-performance storage with high availability. The second would be a read-only history or archive table, which you could decide to move to more economical storage and possibly offload to the IBM DB2 Analytics Accelerator (IDAA).


Transparent archive query enables applications to query both the current and archive tables with no SQL changes. In other words, the fact that there are two tables is hidden from the application, which is presented with a single table image. By default, an SQL query on the data will retrieve data only from the current or base table. A new global variable, GET_ARCHIVE, can be set to allow the same query to retrieve data from both the base table and the archive table. If you set the special global variable, DB2 automatically converts the SQL to use UNION ALL to select from both tables using dynamic plan switching.


The archiving process of moving data from the current data table to the history table can be user-controlled. However, DB2 11 provides a new global variable, called MOVE_TO_ARCHIVE, that causes a deleted base table row to be moved to the archive table.


Both the base table and the history table have to be created by the user, and the structures of the two tables must be identical. They can be connected by using a new DDL clause, ALTER TABLE ENABLE ARCHIVE.


New Analytics Features

DB2 11 provides improved support for SQL grouping sets, including ROLLUP and CUBE. Previously, DB2 has had limited grouping set support; building each grouping set required a separate query. Now, ROLLUP and CUBE allow for multiple grouping sets inside the same SQL query. ROLLUP is helpful in providing subtotals along a hierarchical dimension, and CUBE is useful for queries that aggregate columns based on multiple dimensions.


Already noted is the performance enhancement for IFI filtering of IFCID 306, used by IDAA V3 with Change Data Capture. DB2 11 also provides this support for IDAA V4:

  • DB2 changes can be propagated to the accelerator as they happen.

  • The staleness of accelerator data can be detected via Real Time Statistics (RTS).

  • Disk storage cost can be reduced by archiving data in the accelerator using the High Performance Storage Saver, maintaining high performance for analytical queries.

  • Workload Manager integration is improved, and better monitoring capabilities are provided.

  • The query offload scope can be increased by using the new special register CURRENT QUERY ACCELERATION.

  • High-performance IBM SPSS in-database scoring via the PACK and UNPACK functions is available in DB2 11. This improvement has been retrofitted to DB2 10 via APAR.

Integration with Big Data

There is no doubt that there is huge interest industry-wide in big data. DB2 11 delivers support for integration with big data by providing connectors to allow DB2 applications to access data stored in Hadoop (a distributed file system) easily and efficiently. This is done by providing new user-defined functions (UDFs) and a new generic table UDF capability.


The goal in this support is to integrate DB2 for z/OS with the Hadoop-based IBM BigInsights platform, enabling traditional applications on DB2 for z/OS to access big data analytics. Analytics jobs can be specified using the JavaScript Object Notation (JSON) query language known as Jaql and submitted to IBM InfoSphere BigInsights. The results are then stored in the Hadoop Distributed File System (HDFS), and a table UDF, HDFS_READ, reads the result from the HDFS for subsequent presentation to an SQL query.


For more information about these and other features of DB2 11 for z/OS, visit this IBM website.

John Campbell

John Campbell is an IBM Distinguished Engineer reporting to the Director for z/OS Development at the IBM Silicon Valley Lab. He has extensive experience of DB2 in terms of systems, database, and application design. John specializes in design for high performance and data sharing. He is one of IBM’s foremost authorities for implementing high-end database/transaction-processing applications.

MC Press books written by John Campbell available now on the MC Press Bookstore.

The Business Value of DB2 for z/O The Business Value of DB2 for z/OS
Find out why DB2 is still going strong and how it is helping businesses to reduce costs and grow.
List Price $17.95

Now On Sale

DB2 10 for z/OS: The Smarter, Faster Way to Upgrade DB2 10 for z/OS: The Smarter, Faster Way to Upgrade
Explore the top 10 reasons to start planning your upgrade today.
List Price $16.95

Now On Sale

DB2 11: The Ultimate Database for Cloud, Analytics, and Mobile DB2 11: The Ultimate Database for Cloud, Analytics, and Mobile
Get the DB2 11 info you need, including technical overview, migration planning, and application compatibility.
List Price $16.95

Now On Sale



Support MC Press Online

$0.00 Raised:

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: