The Changing Enterprise Application

The world of enterprise application development is rapidly changing. Most companies today have moved to an agile development methodology in an attempt to become more responsive to customers' needs. Craig Mullins, the renowned database systems development expert, was kind enough to interview me for this Enterprise Systems Media piece. We discuss how software developers can most effectively serve customers and, as product manager for CA Technologies, I explain our approach to providing DB2 database management solutions. I hope you'll give it a read.

On a related note, the new IBM Redbook, "Integration Throughout and Beyond the Enterprise," does an excellent job of explaining emerging application development patterns and how they're applied to critical tasks like supporting mobile devices and modernizing legacy applications.

Now, not every DBA is an application architect who must know every detail about how applications access the database. Nonetheless, I feel it's very important for DBAs to at least be aware of these trends and how they will impact their jobs. You may not care exactly how all these phones and tablets get to your database, but the reality is they will result in thousands of new transactions hitting your database, so you should have a clear understanding of what is going on. This Redbook doesn't tell you how to monitor and tune these applications, but it will give you an idea of the architecture being used, which can provide insight into tuning opportunities.

What are your thoughts about mobile devices, cloud computing and the impact of these new methods of application data access? Please share your thoughts in comments.

Deprecations and Other Changes in DB2 11

As I was reading the DB2 11 announcement letter (U.S. version here; EMEA version here), I noticed that SYNONYMS has been deprecated and the recommendation is to use ALIAS. This could have a huge impact on existing development environments; it will greatly affect DBAs.

In addition to SYNONYMS, these functions have been deprecated in DB2 11:

• SQL processing options NEWFUN(YES) and NEWFUN(NO): Rather than select YES/NO, you'll need to use the DB2 version number -- e.g., NEWFUN(V11) or NEWFUN(V10).

• dsnhdecp parameter values NEWFUN=YES and NEWFUN=NO: Although these values are supported in DB2 11, NEWFUN=V11 and NEWFUN=V10 should be used.

Private_Protocol, a subsystem parameter, is also deprecated in DB2 11. The functionality this parameter controls won't apply to later DB2 releases.

In addition, these utility options are deprecated:

• REORG TABLESPACE UNLOAD (ONLY, PAUSE and EXTERNAL): Use the UNLOAD utility instead.

• REORG TABLESPACE/INDEX  (INDREFLIMIT, OFFPOSLIMIT): Use the DSNACCOX stored procedure to determine if the object needs to be reorganized.

• REORG TABLESPACE/INDEX ( INDREFLIMIT,OFFPOSLIMIT) REPORTONLY.

• REORG INDEX UNLOAD ONLY: Use the DIAGNOSE utility stop the process instead.

• LOAD FORMAT UNLOAD: Use the LOAD FORMAT INTERNAL option to load data that was unloaded with UNLOAD FORMAT INTERNAL.

• COPY CHANGELIMIT: Use the DSNACCOX stored procedure to determine if the object needs to be copied.

• REPAIR VERSIONS: Use the REPAIR CATALOG utility instead.

These utility options are still supported in DB2 11, but they'll be removed in a later DB2 release.

Here are some other notable changes:

• As of DB2 11, password protection for active log and archive log data sets is no

longer supported.

• The DSNH CLIST no longer supports values of NEWFUN=V8 or NEWFUN=V9.

• The following DB2-supplied routines are removed in DB2 11 and are unavailable to callers after migration to conversion mode. A report is added to the DSNTIJPM premigration job to detect occurrences of these routines on an existing subsystem or data sharing group, and to specify that these routines are not available in DB2 11.

– SYSPROC.DSNAEXP

– AMI-based DB2 MQ functions:

DB2MQ1C.GETCOL

DB2MQ1C.MQPUBLISH

DB2MQ1C.MQREAD

DB2MQ1C.MQSUBSCRIBE

DB2MQ1C.MQUNSUBSCRIBE

DB2MQ2C.GETCOL

DB2MQ2C.MQPUBLISH

DB2MQ2C.MQREAD

DB2MQ2C.MQSUBSCRIBE

DB2MQ2C.MQUNSUBSCRIBE

• These subsystem parameters are removed in DB2 11:

ASSIST

COORDNTR

DISABSCL

DISALLOW_DEFAULT_COLLID

MSVGP

MSVGP2

OJPERFEH

OPTIOPIN

OPTIOWGT

OPTIXIO

PGRNGSCR

PTCDIO

RETVLCFK

SEQCACH

SEQPRES

SMSDCFL

SMSDCIX

STATCLUS

• The application programming default value CHARSET has also been removed. In DB2 11, you cannot use the BIND PACKAGE options ENABLE and DISABLE(REMOTE) REMOTE (location-name,...,(luname),...) to enable or disable specific remote connections. You can use the ENABLE(REMOTE) or DISABLE(REMOTE)

options to enable or disable all remote connections.

• Sysplex query parallelism is no longer supported. Packages that used sysplex query parallelism in previous releases use CPU parallelism in DB2 11.

• The DSN1CHKR utility is no longer supported. DSN18101 and DSN18161 messages are issued when the DSN1CHKR utility is invoked.

I’m thinking that the deprecation of synonyms and other functions will affect customers' development processes and production job schedules. (Here's just one consideration: Do you use REORG to unload data?) I'd greatly appreciate your input on this list of deprecations and how your processes might be impacted.

An IDUG Perspective on DB2 11

The IDUG DB2 11 Editorial Committee was formed in March. The team was comprised of volunteers from IDUG’s worldwide community of DB2 users and consultants: Ron Brown, Cuneyt Goksu, Dan Luksetich, Bjarne Nelson, Toine Michielse, Christian Molaro, Kurt Struyf, Julian Stuhler, Billy Sundarrajan and Isaac Yassin. Working alongside IBM in its formal Early Support Program (ESP), this impressive group of industry experts gained valuable insight into the new release.

The committee's findings, including practical experiences and independent evaluations of new features, are presented in this white paper.

I like how committee members organized their report by job disciplines (systems programmer, DBA, developer). This list of topics should give you an idea of the detail involved:

Highlights for systems programmers:

• Extended logging
• DRDA/DDF enhancements
• Buffer pool enhancements
• Data-sharing enhancements
• SELECT from directory pagesets
• Additional zIIP enablement
• Installation
• Migration
• Exploitation of EC12 features

Highlights for DBAs:

• Temporal table enhancements
• Online schema change enhancements
• Bind/rebind enhancements
• Insert performance improvements
• SQL compatibility features
• XML enhancements
• Utility enhancements
• Security enhancements
• Instrumentation enhancements

Highlights for developers:

• Transparent archive query
• SQL enhancements
• Analytics enhancements
• Global variables
• Variable arrays
• Declared global temporary table enhancements

Wow. Thanks to the IDUG Editorial Committee for providing their insights and sharing their experiences with these features. I encourage you to read the white paper for yourself; it's well worth the time.

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.

IBM Hosts a Big Day for Big Data

I was honored to be invited to IBM Almaden research center in San Jose, California, for a briefing on IBM’s Big Data launch earlier this month. Hundreds of DB2 community luminaries -- including Information Champions, analyst and DB2 Gold Consultants -- were brought together to absorb all this information and then share it with the IT industry through their blogs, articles and tweets.

I learned a lot about what is going on in the distributed world from the discussion of tools like Hadoop as well as the latest DB2 for LUW release. DB2 for LUW 10.5 features a technology called BLU, which supports data stored in columnar format rather than row format. That is, the data page contains data for a given column where row format is rows of data that are stored on a page of data. This column format can dramatically increase the speed of analytic queries.

Of course, with my focus on the mainframe, I wanted to learn more about big data on z/OS. The starting point here was the latest release of DB2 Analytics Accelerator (IDAA) for z/OS, IDAA V3.1.

Prior to IDAA V3.1, data had to be stored on the local host and replicated to the IDAA (Netezza). However, IDAA V3.1 features a High-Performance Storage Saver (HPSS), which allows you to store a DB2 table partition or full table of data solely on the accelerator, reducing the need for host storage. This feature removes the requirement for data to be replicated in DB2 storage and Accelerator storage. Tables can now be divided between traditional database resident partitions on DB2 for z/OS and HPSS on the IDAA.

What are your company's plans for big data? How do you see big data impacting your job as a DBA? Please share your thoughts in comments.

The Value of Early Access

I want to follow up on my recent post about the IBM DB2 11 for z/OS Early Support Program (ESP).

When I was working as a DBA I never participated in an IBM ESP, but I would have loved to have had the opportunity. I did, however, take part in a few beta programs run by other product vendors.

Getting early access to a product is hugely beneficial. First, it gives you the opportunity to test a product in your environment and ensure it doesn't negatively impact your system or applications. It also allows you to try out new features and provide feedback that helps your fellow customers. Vendors really do value and implement customer input. A number of my suggestions were acted upon, and because I'd offered constructive comments, vendors were more responsive to me when I asked for changes or additions. It's almost like a direct line to development.

I also saw internal benefits from taking part in product betas. As soon as the product was made generally available (went GA), I knew I had management buy-in, because my bosses knew that rolling out this new release wouldn't pose any negative impact on the business.

Now consider all that, and imagine getting to preview something as integral to your work life as DB2. By participating in the IBM DB2 ESP, you get the benefit of testing new features and seeing how they'll integrate with your own system and applications. You have the assurance, from testing, that the next release of DB2 will work well in your environment. From the day DB2 11 GAs, your company will immediately benefit from the new release. And finally, you can benefit professionally from your experience by sharing what you've learned at a local user group meeting or as a presenter at an IDUG conference.

As a vendor (CA Technologies), we get early access to DB2 as an IBM development partner. IBM provides us with a list of enhancements that could impact our products and we work through these changes. We also find bugs in DB2 and work with IBM to get them corrected.

While there are system requirements for companies looking to join in the DB2 ESP, keep in mind that IBM is seeking diversity. You don't have to be a large customer or a prominent vendor to take part; the goal is to test DB2 in all types of environments.

Perhaps you've participated in a past DB2 ESP. If so, it'd be great to read about your experience in Comments. What prompted your company to take part? What were the benefits?

 

Becoming a DBA

Over the years I've often been asked how one gets a job as a DBA. In my years of consulting I have, of course, talked to many DBAs who've taken all kinds of routes to where they are now. For instance, a former coworker started as a computer operator. He advanced into systems programming, and then moved into the database group. Another former coworker was a clerk doing paperwork, but she switched into IT and progressed into database administration.

Me, I was hired as a developer for a company that just got DB2. I was assigned to test a new DB2 application, a project that took about four months. When it was over, I was asked if I'd like stay on the database side. In each of these case, it was a matter of someone starting in one area of a company, building relationships and taking the opportunity when it was presented.

If you really want to be a DBA, you should naturally learn as much as you can about DB2. If possible, take a data modeling class and DBA class. But beyond technical knowledge, take the small steps to put yourself in position for the opportunity. Build a good relationship with your friends in the DBA group. If you can, learn the tools they use to monitor your applications. Have them show you when they make SQL coding changes to improve a given transaction or batch job. Doing these kinds of things will make you shine.

I'd really like to hear from the DBAs out there. How did you get your break? Please take a moment and share your story in Comments.