DB2 Developer’s Guide – 4th Ed

Author: Craig Mullins
Publisher: Sams (ISBN 0672318288)

Once you’ve picked up and read “DB2 Developer’s Guide” you will know why people on the DB2 List Serve forum refer to this book at the “BIBLE”. You will find that
the DB2 Developer’s Guide is a comprehensive guide for both the beginner and experienced in DB2 and relational database technology.

You will enjoy the authors writing style with the first chapter giving a somewhat humorous introduction into the kingdom of Userville and the characters within the journey. The author gives a vivid
description of the role the Wizard, otherwise known as the DBA, plays in the kingdom.

The first chapter is an introduction to the “MAGIC” of the language SQL. What’s nice about this chapter is that if you’re new to SQL you will be spoon-feed with terminology
and examples. If your one of the old timers in the DB2 world you’ll appreciate the examples of tips and tricks that have taken you years to learn. You will also appreciate the new technology
and how it simplifies old techniques. An example of this can be found on page 34 under Outer Join. Notice the SELECT , UNION, and another SELECT. If you work in manufacturing or areas that deal
with multiple languages to describe the products being sold, then you will most likely find this SQL to be common. The new use of OUTER JOIN as seen on page 35 not only simplifies the coding, but
also greatly improves the performance. The old statement required a lot of resources to process multiply passes of the data. With outer join processing you will only need to pass the data once.

Another example of how not to read the data multiple times is provided in the CASE example found on page 39. As seen in the example, each row is retrieved and a case statement returns whether the
row is for a table, view, or alias. The original query passed the data three times. Using the CASE expression the data only has to be passed once.

The next few chapters give some good guidelines to coding SQL, object naming conventions, physical design, and many more. Even if you’re considered a Wizard with DB2 you should review these
sections. You can think of exploring DB2 as a treasure hunt. The author is taking you on an adventure in the kingdom of Userville exploring DB2. A long the way you’re going to find treasures
of knowledge.

One such treasure is the tip given on page 64 under “Enable EXPLAIN for AUTO REBIND”. You can change the DB2 system parameters “ZPARM” to allow the PLAN_TABLE to be
populated for programs that go through auto rebind. This allows the identification of negative access path changes that could not be tracked before. As we learn new tricks about older functionality
we as DBA’s need to stay abreast of new features also. The DB2 Developer’s Guide is full of tips on new features.

The next few chapters will cover the new features of DB2 V6 such as USER DEFINE FUNCTIONS (UDF), Triggers, and Object Relational Database usage. As a performance specialist the new function table
DSN_FUNCTION_TABLE used by the EXPLAIN process is very interesting. So what is this new table DSN_FUNCTION_TABLE used for? This table will contain performance related information used to help debug
and improve performance of USER DEFINE FUNCTIONS “UDF”.

Now that you’ve master the SQL coding tips and tricks, the author takes you into the realm of application development. The architecture you define for your application is critical. The author
provides a comprehensive look at some traditional design options and some alternative one’s that may affect your architecture. These include static and dynamic SQL, client server using ODBC,
CLI, JDBC, or stored procedures. You will have to make the decision on your architecture use of ODBC, CLI, JDBC, SQLJ, and stored procedures. Your shop will probably standardize on one version to
simplify change management. Based on your background, or shop standards. If you ask the RDBMS community your likely to set off a heated debate. Each architecture has it’s own strengths and

If you’re a programmer you’re going to enjoy the next section titled “DB2 Application Development”. The author gives some good SQL coding standards that should be included
in all shop standards. The author continues on the journey through coding alternative, or stored procedures. The author even goes into the new community role known as procedural DBA. The procedural
DBA will support stored procedures, triggers, and UDF’s. As a database consultant I agree with the author. I see a real need for this skill set in many shops I’m brought into.

As the importance of the Internet grows so does the requirement for accessing data over the Internet. Every DBA will need to understand what WWW is and what impact it will have on them and their
jobs. An entire chapter is devoted to explaining tools available on the World Wide Web. IBM provides an easy to learn script language that uses API’s to access DB2 called Net. Data. The
author provides good programming standards and when to use Java Applets and when to use Java Scripts. When your programming in Java you can use JDBC to access DB2 with dynamic SQL or you can use
SQLJ with static SQL. The author discusses the Pro’s and Con’s of these two standards.

Hold on to your hats because the next few sections will take you on a fast ride into the heart of DB2 internals. The information on the DB2 Optimizer, Catalog and Directory Structures, Locking,
Data Sharing, Utilities, Commands, and most of all Performance Monitoring and Tuning will provide an excellent foundation for any role the reader may play in the future.

In summary I cannot say enough about the “DB2 Developer’s Guide”. It has been an exciting book to read not only for the information on new features in DB2 Version 6, but because
of the vast resource of knowledge and experience it contains. The author gives many tips and rules of thumb examples for real problems.


submit to reddit

About Troy Coleman

Troy Coleman Coleman Consulting, Inc. IBM Certified Solutions Expert - DB2 UDB V5 Database Administrator IBM Certified Advanced Technical Expert - DB2 DRDA 907 W. Dorset Ave. Palatine, IL 60067 Voice: 847-722-2698 http://www.colemanconsulting.com/~troycci