IDUG Expanding Worldwide Presence

The International DB2 User Group (IDUG) has partnered with IBM to sponsor free technical seminars in India and Great Britain. The upcoming schedule currently consists of these three events:

1) IDUG DB2 Technical Workshop in Bangalore, India, on March 25.

2) IDUG DB2 Technical Workshop in Chennai, India, on March 27.

These speakers and presentations are scheduled for both events in India:

Speakers

• Chris Crone, IBM Silicon Valley
• Matt Huras, IBM Toronto Lab
• Maryela Weihrauch, IBM Silicon Valley
• Pallavi Priyadarshini, IBM India
• Steve Thomas, CA Technologies
• Mario Briggs, IBM
• Sweta Singh, IBM
• Surekha Parekh, IBM
• Jim Reed, IBM

Presentations

• DB2 for z/OS
  • DB2 for z/OS Trends & Direction and Technical Overview
  • DB2 and System z Synergy
  • The Online Schema (R)evolution
  • Continuous Availability and Scalability with DB2 for z/OS and WebSphere
  • DB2 11 Application Compatibility: What you need to know
  • Temporal Enhancements of IBM DB2 11 for z/OS
• DB2 for LUW
  • DB2 10.5 including BLU Acceleration
  • Best Practices for Boosting Java Application Performance and Availability on DB2
  • DB2 pureScale Best Practices from Customer Experiences
  • DB2 on IBM Cloud: Managed services, self service and access from cloud environments
  • DB2 Performance Tuning: Tips & techniques

3) IDUG DB2 Technical Seminar in London on April 16.

Speakers

• John Campbell, IBM
• Namik Hrle, IBM
• Ian Cook, IBM
• Oliver Bernin, IBM
• Cristian Molaro, IBM Gold Consultant
• Dualta Ó Briain, IBM

Presentations

• Track 1: DB2 for z/OS
  • DB2 11 for z/OS Technical Overview
  • IBM DB2 Analytics Accelerator Trends & Directions
  • Efficient Monitoring & Tuning of Dynamic SQL in DB2 for z/OS
  • Planning and Executing a DB2 11 for z/OS Migration
• Track 2: DB2 for LUW
  • DB2 BLU Acceleration: A Primer for sysadmin and DBAs
  • DB2 LUW HADR and pureScale demystified
  • Time Breakdowns: A novel way to analyze performance problems
  • A description of how to enable and use JSON in DB2 as part of the Big Data
  • Extend your pureScale cluster to another city: Geographically Dispersed pureScale Cluster

IDUG events feature great content and top speakers. With the addition of one-day technical seminars, IDUG and IBM are expanding the worldwide reach of DB2 education. I encourage readers in India and the U.K. to attend these events.

While I'm at it, I'll also note IDUG's 2014 technical conference schedule:

* IDUG DB2 Tech Conference in Phoenix, Arizona, U.S., May 12-16

* IDUG DB2 Tech Conference in Sydney, Australia, Sept. 10-12

* IDUG DB2 Tech Conference in Prague, Czech Republic, Nov. 9-14

Big Data and the Changing Role of DBAs

**Please note: This blog will be updated on January 7, 2014.**

In our industry it seems people are being asked to do more with less, while our employers focus on the present and near-term. Businesses aren't planning for the long-term when it comes to making employees productive and ensuring that the data being used to make critical business decisions is of the highest quality.

In the early 1990s, businesses invested in people and products to ensure that business rules were reflected in the logical database design. But it didn't end there. The physical database would also support this design as well as performing applications. There was no one person who specialized in these different disciplines. We relied on data administrators (DAs) as well as DBAs.

The DA understood the business and the rules governing the data used to run the business. Using tools such as the CA ERwin data modeler, the DA would create logical models that showed how the entities related to one another and enforced the business rules within the definition of the data. The DBA would then take this model and create the physical tables in the given RDBMS (DB2 for z/OS or DB2 for LUW).

However, after years of cost-cutting, the DA's role seems to have been disbursed to DBAs as well as business analysts. As businesses sprint toward big data and the use of low-cost Hadoop clusters, traditional DBAs are being asked to do much more. Increasingly, the DBA is being replaced with the BDA -- the big data administrator, aka the Hadoop cluster administrator.

The BDA's responsibilities combine those of traditional system administrators (install and configure software), DBAs (create and tune the database), developers (write SQL and programs) and operators (monitor system problems). You don't see the role of DA mentioned in job descriptions these days, but rest assured, the need to understand the data elements, the entities and where data is sourced and targeted is more critical than it's ever been.

Here's a job description I found for a Hadoop cluster administrator:

            What a Day in the Office Might Look Like

            You check your self-made dashboard of statistics from all sorts of data sources (database clusters) and processes to see if they are still healthy and have enough space to grow. Of course, on most days these are fine, but today you find an issue with the processing of data towards the Hadoop cluster. You discover that another process is holding a file, so you kill it and get the regular processing    routine up and running again. Of course, this is not where the job ends. Together with a few Business Intelligence developers you find a way to prevent this from happening in the future and implement this in the script. Later on, you come up with a possible (super-fast) technical solution to use real data in reports. These  should be immediately sent to commercial teams upon encountering certain           triggers in the data ...

In the past, employing separate people for each discipline allowed them to become expert enough at their responsibilities to address problems quickly. The BDA, however, will be a generalist, and as such, the BDA will need more time, research and testing to ensure that a quality product is delivered to the business.

As requests for data from the business lines inevitably increase, BDAs will find themselves overloaded, and the quality and speed at which data can be provided to the business will suffer. In response, vendors will rush to market with tools to help BDAs make higher-quality data available more quickly so that those critical business decisions can be made.

For years, businesses have tried to marginalize DAs for short-term cost savings. However, I believe that any operation that lacks DA skills will cost the business more in the long-run, because data quality due to lack of business knowledge will be sacrificed.

Are you a traditional DBA who's had to transition to the BDA role? Have you seen degrading quality in data due to the lack of data administrators? Please share your experiences in Comments.

Optimization Statistics Feedback in DB2 11

Due to the CPU costs involved, DBAs are typically very selective when it comes to collecting column-level statistics. In fact, most DBAs gather only standard or default statistics using the RUNSTATS, TABLE(ALL), INDEX(ALL), KEYCARD parameters. This is unfortunate, because when column-level statistics are collected, the optimizer has the data it needs to choose a more efficient access path.

However, with the soon to be released DB2 11, IBM is addressing this issue by externalizing information that's gathered from BIND, REBIND or PREPARE statements. During the access path calculation process, the optimizer will identify missing or conflicting statistics. This information will be written to SYSIBM.SYSSTATFEEDBACK. DB2 will also provide feedback when the EXPLAIN statement is used. This information will be written to the DSN_STAT_FEEDBACK table.

In both cases, the feedback information can be used -- either by a DBA or through tooling -- to generate RUNSTATS syntax that will collect stats needed to help the optimizer choose an efficient access path.

*This post was edited for clarity on October 21, 2013

Are DBAs Still Needed?

Since IBM unveiled its big data strategy, there's been an interesting response. Some have focused on the role of the DBA, wondering if advancements like IBM Data Analytics Accelerator for z/OS (IDAA) and Netezza, which are designed to eliminate concerns over index design when storing data, make DBAs obsolete.

IBM's Susan Visser went so far as to organize a "tweetchat" on this topic: "Is the role of the DBA dead?"

As Susan notes, the debate was informative, but ultimately, it wasn't much of a debate. That's because everyone agreed that DBAs are still absolutely needed. While I wasn't able to participate in the Twitter debate myself, here's my take: We still need DBAs to understand the target system and the best physical design for the given system. Sure, since Netezza doesn't use indexes, you don’t need to worry about what index to create on the table. However, you still need to understand the physical design techniques for the Netezza system.

For more on the enduring importance of DBAs, see this post by Bjarne Nelson.

Have you tackled a performance problem on the Netezza system? If you're a DBA with experience on Netezza, I'd like to hear from you in comments.

The Emergence of IMDB Technology

Businesses as well as consumers demand that more information be placed at their fingertips. When using mobile devices such as iPhone and Android, people expect answers to their questions in seconds rather than minutes. This reality is driving the emergence of in-memory database (IMDB) technology.

With the low cost of memory, data is increasingly being moved to IMDBs, which can provide up to 10 times faster data access than traditional disk storage devices.

Until I glanced at the above Wikipedia link, I hadn't realized that IBM acquired solidDB in 2008. The solidDB database supports both IMDB technology and disk storage access. It's used in consumer hand-held devices as well as on UNIX, AIX, Linux and Windows servers.

Once the application connects to solidDB, a subset of data from the back-end database is stored in memory. That's the role of solidDB Universal Cache, which supports DB2 for z/OS as well as DB2 for LUW, Informix, SQL Server, Oracle and Sybase. Updated data is kept in sync using IBM Infosphere CDC (Change Data Capture).

For details on solidDB 7.0, check out these links:

* The list of products included with solidDB 7.0

* An overview of the architecture

* A list of supported back-end data servers 

Another good solidDB resource is the IBM Redbook, "IBM solidDB: Delivering Data with Extreme Speed" (published May 2011).

Is your company taking advantage of IMDB technology? Please share your experiences in comments.

Managing Security in DB2 10

DB2 10 is a very significant release in terms of the new security-related features and enhancements it provides. For instance, when I was recently updating my collection of DB2 10 manuals, I scanned the latest version of the DB2 10 for z/OS Managing Security manual.

Here are the subheads for the What’s New in DB2 10 for z/OS chapter:

• Increased granularity in DB2 administrative authorities
• Separation of DB2 administrative authorities
• EXPLAIN system privilege
• Row and column access control
• Audit policy

That's quite a list. Let's dig into the first topic: increased granularity in DB2 administrative authorities. With prior DB2 versions, many enterprises felt they had no choice but to give system privilege (SYSADM) to numerous system users so they could perform various system operations for which they were responsible. SYSADM, of course, is a very powerful authority. It allows complete control over DB2, including system configuration, security management, DB2 objects management, application execution and data access control.

However, with DB2 10, a new set of privileges have been defined to split up the authority by the type of work needing to be done. Here's a quick list. The manual offers extensive details.

SECADM: With this authority, you can manage security-related objects in DB2 and control access to all database resources. However, SECADM doesn't have any inherent privilege to access any user data in those databases.

ACCESSCTRL: With this authority, you can grant explicit privileges to authorization IDs or roles by issuing SQL GRANT/REVOKE statements.

DATAACCESS: With this authority, you can access data in tables, views and materialized query tables in a DB2 subsystem. DATAACCESS also allows you to execute plans, packages, functions, and procedures.

System DBADM: With this authority, an administrator can manage all databases in a DB2 subsystem. By default, the system DBADM has all the privileges of the DATAACCESS and ACCESSCTRL authorities.

SQLADM: With this authority, you can issue the SQL EXPLAIN statements, execute the PROFILE commands and run the RUNSTATS and MODIFY STATISTICS utilities on all user databases. In addition, you can execute system-defined routines such as stored procedures or functions, and any packages that are executed within the routines.

This new granularity solves many management problems. For example, an SQL performance analyst can now be granted the authority to EXPLAIN an application program or SQL statement without having the data access control authority that's included with SYSADM.

Next week I’ll discuss the controls put around data access and the capabilities to audit these controls.

Is Classic Index-Controlled Partitioning Still Supported in DB2 10?

Back in October 2009 I said that, in the near future, all DB2 data would be stored in Universal Table Spaces (UTSs).

I hadn't given this much thought until recently. I had an old DDL script for creating a partitioned table using classic index-controlled partitioning. When I ran this script on a DB2 10 NFM environment, I received the following error:

DSNT408I SQLCODE = -662, ERROR:  A PARTITIONED INDEX CANNOT BE CREATED

         ON A TABLE SPACE, OR A TABLE SPACE CANNOT BE INDEX-CONTROLLED.

          TABLE SPACE CLASSP2, REASON 1

Reason 1:  The CREATE INDEX statement contains the PARTITION BY clause for index-controlled partitioning, but the table on which the index is created has one of the following characteristics:                

       The table is not in a partitioned table space.                

       The table is in a partition-by-growth or a range-partitioned universal table space.                                       

Initially I thought the day had finally arrived where DB2 no longer supported index-controlled partitioning. Then I wondered. It was weird that I hadn't heard any recent announcements about this. Could it just be an error in the script? 

Sure enough, when I checked the syntax diagram for CREATE TABLESPACE in the SQL Reference Guide, I noticed the change. The CREATE TABLESPACE statement now has three different specifications (partition-by-growth, partition-by-range and SEGSIZE). I thought SEGSIZE was for segmented table spaces, but once I read about each specification, I learned that SEGSIZE 0 and NUMPARTS must be specified when creating an index-controlled table space.

So my predication has yet to be realized. DB2 continues to support the classic index-controlled partitioned table space, though through a different set of parameters. Hopefully this helps those who are still creating DDL manually. For those using a catalog management tool, these parameters are automatically used in the create syntax.

Using Spatial Data

Following up on my post about on spatial data support in DB2 for z/OS, this week we'll cover how to create a table and index containing spatial data. While you read along, you might want to view this visual, a sample application delivered with ArcGIS by ESRI.

Once IBM Spatial Support for DB2 for z/OS is installed, these objects are defined in DB2:

• Stored procedures: When you request a spatial operation (e.g., importing spatial data), Spatial Support invokes the appropriate stored procedure to perform the operation.
• Spatial data types: A spatial data type must be assigned each table or view column that is to contain spatial data.
• Spatial catalog tables and catalog views.
• A spatial grid index, so that you can define grid indexes on spatial columns.
• Spatial functions: These work with spatial data in numerous ways (e.g., determining relationships between geometries, generating additional spatial data).
• Definitions of coordinate systems.
• Default spatial reference systems.
• Two schemas: DB2GSE and SYSPROC.

As a DBA, I can imagine I'd want to include spatial data in table columns or create indexes to ensure that queries are running efficiently. To learn about these and other uses of spatial data, see the IBM Spatial Support for DB2 Version 9.1 for z/OS User’s Guide and Reference.

(Notes: The DB2 LOAD utility will fail if a spatial grid index is created on the target table. Before running this utility, you must drop the spatial grid index by using the ST_drop_index stored procedure. The spatial grid index can be recreated after running the LOAD utility. Some utility support -- Reorg Tablespace , Reorg Index, Rebuild Index and Check Index -- was introduced with DB2 10. Details are here. Utility support for DB2 9 is enabled through APAR PM35200.)

A sample install verification program (IVP) job can be found in the install sample library SDSNSAMP(DSN5SCMD). This job contains statements to create tables and call sample programs that use the spatial stored procedures to create/drop indexes as well as enable/disable spatial support.

Once Spatial Support is installed, these data types can be used:

 

ST_Point

ST_LineString

ST_Polygon

ST_MultiPoint

ST_MultiLineString

ST_MultiPolygon

ST_Geometry

 

In this example, a ADDRESS table is created that includes a LOCATION column of type DB2GSE.ST_POINT:

           CREATE TABLE ADDRESS ( ..., LOCATION DB2GSE.ST_POINT, ...) ;

 

To help improve performance, a “spatial grid index” can be created on this column using the SYSPROC.ST_create_index stored procedure. As I noted in the previous post, standard DDL CANNOT be used create an index on spatial data.

This sample calls the ST_create_index stored procedure to create the LOC_IX index on the LOCATION column in the ADDRESS_TABLE table:

CALL SYSPROC.ST_CREATE_INDEX( NULL, ADDRESS_TABLE, LOCATION , NULL, LOC_IX,NULL, 10.0, 20.0, 35.0, ?, ?)

The DB2 optimizer considers a spatial index for use when a query contains one of these functions in its WHERE clause:

• ST_Contains
• ST_Crosses
• ST_Distance
• EnvelopesIntersect
• ST_Equals
• ST_Intersects
• ST_Overlaps
• ST_Touches
• ST_Within

 

Sample SQL -- List the address found in the Chicago Loop:

SELECT address_line1, City, State, Zip

FROM address_table AS a

WHERE ST_Within(a.location,:Chicago_Loop_Area) = 1

As I mentioned in the previous post, there's little available information on managing spatial data in DB2. However, this IBM developerWorks piece is very informative. It also includes links to additional resources.

 

A Big Data Primer

Recently when I attended IDUG EMEA in Berlin, Germany, I stopped in the vendor hall to visit Susan Visser, who blogs about IBM Information Management, DB2, InfoSphere and data warehousing.

As I was checking out the DB2 books she had on display and for sale, Susan recommended a free book called “Harness the Power of Big Data." I've been researching Big Data lately, so I picked it up to read on the plane ride back to Chicago. I was so impressed I checked to see if this book was available online as a PDF. Sure enough, it is.

This is a must read for anyone who's interested in Big Data and IBM’s Big Data strategy. Download it, or if you prefer, purchase a hard copy.

If you've read this book, I'd be interested in your thoughts, as well as your company's response to Big Data and data analytics. Please post in Comments.

DB2 Performance Stored Procedures

Last week I posted a lengthy list of stored procedures that can be used to help with administrative tasks. What follows is a much shorter list of some stored procedures that are used to manage performance. For more about these procedures and how to use them, see the IBM manual, "DB2 10 for z/OS: Managing Performance."

• DSNACCOX (DB2 realtime statistics) issues recommendations for maintaining DB2 databases. These recommendations include when to reorganize, image copy, or update statistics for table or index spaces. SDNACCOX also indicates when a data set has exceeded a specified threshold for the number of extents it occupies and when an object is in a restricted state.
• DSNACCOR has been deprecated and replaced by DSNACCOX.
• DSNAEXP (DB2 EXPLAIN) allows database operators to issue EXPLAIN statements within certain simple SQL statements without the authorization that's typically required to execute such statements. DSNAEXP was deprecated with DB2 10. The EXPLAIN privilege or SQLADM authority should now be used instead.

These stored procedures, which are new with DB2 10, help with automating statistics maintenance (ADMIN_UTL_MONITOR, ADMIN_UTL_EXECUTE, ADMIN_UTL_MODIFY). The "Managing Performance" manual also has details on these procedures.

• SYSPROC.ADMIN_UTL_MONITOR provides functions that enable analysis of database statistics. These functions include issuing alerts for out-of-date, missing or conflicting statistics as well as summary and detailed table-level reports that describe generated RUNSTATS statements.

• SYSPROC.ADMIN_UTL_EXECUTE solves alerts stored in the SYSIBM.SYSAUTOALERTS catalog table. The maintenance windows for these alerts are defined by the SYSIBM.SYSAUTOTIMEWINDOWS catalog table.

• SYSPROC.ADMIN_UTL_MODIFY maintains the SYSIBM.SYSAUTORUNS_HIST and SYSIBM.SYSAUTOALERTS catalog tables, removing all entries in these tables that are older than a configurable threshold. This procedure also removes all entries in SYSIBM.SYSAUTOALERTS that are in COMPLETE state.

Database administrators are always looking for ways to improve application performance. Do you use any of these stored procedures? Have you found them helpful? Please share your experiences in Comments.