Face Time With Paul Zikopoulos

---

DB2 Magazine readers: I come from a mainframe background, so my DB2 Universal Database (UDB) disaster recovery plan is designed to re-create my current running environment exactly as is on a recovery site machine. From my research, DB2 UDB in the Microsoft Windows 2000 environment seems slanted more toward failover than toward re-creation. Is there a procedure possible on Windows to mimic the re-creation possible on the mainframe?

Zikopoulos: DB2 UDB on Windows gives you a boatload of flexibility with respect to planning your availability strategy. Any of the following approaches could help accomplish the task you're trying to perform:

• Use the flash copy features of DB2 UDB that leverage optimized hardware for data recovery
• Set up log shipping
• Set up a replication scenario between the tables
• Use a redirected restore.

I would suggest you look at the Data Recovery and High Availability Guide and Reference (ftp://ftp.software.ibm.com/ps/products/db2/info/vr8/pdf/letter/db2hae80.pdf) for detailed information on this subject. The article DB2 Universal Database and the Highly Available Data Store also presents a good basic introduction to the availability options in DB2 UDB for Windows.

DB2 Magazine readers: Can you tell me more about DB2 UDB version 8.1's new client/server communications mechanism, which is based on the DRDA architecture?

Zikopoulos: The good news is that even if you don't know anything about this new feature, your applications will benefit from it. In other words, you don't need to do anything but migrate your client and servers to the version 8 format. The new mechanism is officially called the DB2 UDB common client.

Figure 1 shows a Visual Basic application processing a data request to a remote DB2 UDB server in DB2 UDB version 7.2 (before the common client).

Figure 1: Processing a data request in DB2 v.7.2

The application goes through a stack of API layers and finally exits onto the DB2 UDB proprietary wire protocol DB2RA. In order to go from ADO down to CLI and onto the wire, the code goes through a lengthy code path and a number of memory transfers and context switches. This performance overhead slows down most applications. To make matters worse, trying to identify a problem involves tracing at different layers of the stack.

In DB2 UDB version 8.1, the common client implementation replaces the proprietary DB2RA protocol with the Distributed Relational Database Architecture (DRDA) protocol. DRDA is an open-standard communications protocol that IBM invented and donated to the IT community.

Using DRDA as the base-level protocol eliminates considerations for 32-bit and 64-bit interconnectivity. If you are a mainframe shop, you likely already know a lot about DRDA thanks to DB2 Connect. Despite its underlying DRDA protocol, DB2 UDB version 8.1 still requires you to license mainframe access with DB2 Connect.

The key performance benefits come from the 90o rotation of the stack such that the data access APIs write directly to the wire (See Figure 2). This change leads to an incredible performance improvement, along with a significant reduction in the CPU load for applications.

DB2 Magazine readers: When using a partitioned database, you can't use identity columns. Is there a way to get around this?

Zikoupoulos: Identity and sequence objects were implemented in DB2 UDB version 7 to facilitate the migration process from other databases (for example, Sybase) that used this feature and to remove concurrency issues surrounding "counting tables," which is an alternative to this approach. In DB2 UDB version 7.x, these features were only supported on DB2 Enterprise Edition and "child" editions (that is, DB2 Workgroup Edition, DB2 Workgroup Unlimited Edition, and DB2 Personal Edition).

With the DB2 UDB version 8.1.2 update (http://www-3.ibm.com/cgi-bin/db2www/data/db2/udb/winos2unix/support/v8fphist.d2w/report) and subsequent updates and FixPaks, these objects are supported in a partitioned database environment (which is available in DB2 UDB Enterprise Server Edition Version 8.1.2+ with the database partitioning feature).

In the partitioned implementation, each node maintains a local cache from which identity or sequence values are assigned. If this cache becomes empty, it's refilled from a central location on the catalog node via a remote request. Using a larger cache size can minimize remote requests for cache refills. However, be aware that this approach will increase the average dispersion of generated values (assigned on different database nodes), as well as the potential number of identity or sequence values that could be lost in the event of a database shutdown. For example, assigning values 1-50 versus 1-20 in a cache increases the 'gapping' between partitions.

DB2 Magazine readers: Is there an SQL command or system stored procedure that will return the data definition language (DDL) that was used to create an object such as a table, view, or procedure? I'm looking for something that can be used from a generic ODBC connection, not a tool specific to DB2 UDB. And, is there a way to change a user password (for the user that is currently connected) through an ODBC connection?

Zikopoulos: There's no single SQL command that accomplishes what you want to do. For a view, you can find definitions in the TEXT field in the SYSIBM.SYSVIEWS table. You can do the same for a stored procedure, using the SYSCAT.ROUTINES table instead.

For a table, you would need to use the DB2LOOK utility. We're looking at exposing this utility via a PSM stored procedure in the future. DB2LOOK will actually return the DDL for functions and views, and is being extended in the near future to include the DDL for procedures, too.

If you're only looking for a table or view's schema description, you can use the DB2 DESCRIBE command. That won't give you the DDL per se, but it will provide a description of the columns and their respective characteristics:

You can change the password for a user at connection time with the appropriate CONNECT statement. If a user is logged on to a database, the application can simply reconnect and change the password there.

DB2 Magazine readers: What is the difference between DB2 UDB and Oracle systems? What are the advantages of using DB2 UDB?

Zikopoulos: I could write a book about this topic but because I don't have a lot of space here, I invite you to consult the various independent analysts (http://www-306.ibm.com/software/data/pubs/tech-consult.html) who contrast and compare the DB2 UDB solution within today's competitive database market space. (These studies were not paid for by IBM.)

The studies are very complete, and there are times when some of our competitors are shown, in some specific areas, to have a slight advantage. That's where the development team gets the opportunity to better understand requirements and drive richer features into our product set.

The studies contrast and compare key features such as performance, manageability, integration, total cost of ownership, and so on. Overwhelmingly, DB2 UDB comes out as a leader in these areas and more.

At the highest level, it's all about the amount of time and effort that goes into getting value from a solution. Middleware that cuts the time to value is what the market needs, and studies show that DB2 UDB is a leader in that area.

DB2 Magazine readers: Why does the certification for DB2 UDB for z/OS Test 512 include questions on installing DB2 UDB in a Windows environment?

Zikopoulos: Exam 512, DB2 UDB version 7.1 Family Fundamentals, spans all DB2 UDB platforms. This exam tests an individual's fundamental knowledge of DB2 UDB, including installation and planning, as it relates to the various platforms. After passing this exam, you can branch out into architecture-specific administration exams for the Linux, Unix, Windows, and z/OS platforms.

That was the official IBM answer. But let me add some personal commentary, from experience, as to why the 512 exam is structured this way.

First, about 50 percent of this exam is SQL based, and that's common across that DB2 UDB family. So the number of platform-specific questions such as those you mentioned is limited.

Second, customers love the z/OS platform because of its ability to handle policy-based workload distribution and because of its availability characteristics. Even our competitors, when trying to champion their own products, refer to "mainframe levels of availability".

Many businesses are choosing to leverage distributed platforms for line of business (LOB) solutions - trading some availability characteristics, but leveraging the commoditization of the hardware. Given the current business landscape, even DB2 UDB for z/OS administrators want to understand, at least at a general level, the DB2 UDB distributed platform and, in particular, the ubiquitous Windows client, which is often used as a development platform or as a rich client where data analytics and reporting is handled. (Incidentally, .Net developers can easily develop DB2 UDB for z/OS applications and even non-database applications such as CICS, VSAM, MQSeries, and so on.)

I would suggest that you direct your question to the DB2 UDB Certification team. They're always looking for feedback about the program. You may e-mail them at: db2cert@ca.ibm.com.

Note: The comments and responses included in this forum and represent the views of the author only. Neither IBM nor the author is liable for its contents.