From IBM Press!
* First book to market covering the new DB2 UDB 8.1 Family Application Development Test 703 Certification Exam
* Contains sample exams, answers and explanations -- all content is specifically focused on the exam, no additional material to study
* Written by the DB2 expert who was responsible for reviewing the Version 8 exams
Table of Contents
top
Foreword.
Preface.
I. DB2 UDB CERTIFICATION.
1. IBM DB2 Universal Database Certification.
DB2 Universal Database Certification Roles.
IBM Certified Database Associate-DB2 Universal Database V8.1 Family.
IBM Certified Database Administrator-DB2 Universal Database V8.1 for Linux, UNIX, and Windows.
IBM Certified Database Administrator-DB2 Universal Database V8.1 for z/OS and OS/390.
IBM Certified Application Developer-DB2 Universal Database V8.1 Family.
IBM Certified Advanced Database Administrator-DB2 Universal Database V8.1 for Linux, UNIX and Windows.
IBM Certified Solution Designer-Business Intelligence V8.1.
The Certification Process.
Preparing for the Certification Exams.
Arranging to Take a Certification Exam.
Taking an IBM Certification Exam.
II. DB2 UDB FAMILY APPLICATION DEVELOPMENT.
2. Database Objects and Programming Methods.
DB2 UDB Objects.
Database (or Data) Objects.
A Closer Look at Tables.
Creating Tables.
Table Constraints.
Controlling Data Manipulation with Referential Constraint Rules.
Identity Columns.
A Word about Declared Temporary Tables.
A Closer Look at Views.
Controlling Database Access.
Schema Privileges.
Table Privileges.
View Privileges.
Package Privileges.
Routine Privileges.
How Privileges Are Granted.
DB2 UDB's Special Registers.
A Word about SQL Procedures.
Creating a Stored Procedure.
Calling a Stored Procedure.
Developing DB2 UDB Applications.
DB2 UDB Programming Interfaces.
Embedded SQL.
Call Level Interface (CLI)/Open Database Connectivity (ODBC).
Java Database Connectivity ( JDBC) and SQLJ.
Administrative Application Programming Interface (API) Functions.
Microsoft Data Access Objects.
Practice Questions.
Answers.
3. Data Manipulation.
Structured Query Language (SQL) Revisited.
Data Manipulation Language (DML) Statements.
The SELECT Statement and Its Clauses.
The WHERE Clause.
The GROUP BY Clause.
The HAVING Clause.
The ORDER BY Clause.
The FETCH FIRST Clause.
Joining Tables.
Combining Two or More Queries with a Set Operator.
Using SQL Functions to Transform Data.
Common Table Expressions.
Retrieving Results from a Result Data Set Using a Cursor.
The DECLARE CURSOR Statement.
The OPEN Statement.
The FETCH Statement.
The CLOSE Statement.
Putting It All Together.
Retrieving a Single Row of Data.
The SELECT INTO SQL Statement.
The VALUES INTO SQL Statement.
Transactions.
Transaction Management and Savepoints.
Practice Questions.
Answers.
4. Embedded SQL Programming.
An Introduction to Embedded SQL.
Static SQL.
Dynamic SQL.
The SQL Statements Available and How They Can Be Used in an Application.
Parts of an Embedded SQL Application.
Prologue.
The Body.
Epilogue.
Putting It All Together.
Diagnostics and Error Handling.
The Get Error Message API.
A Word about SQLSTATEs.
Creating Executable Applications.
Precompiling Source Code Files.
Compiling Source Code Files.
Linking Object Modules.
Creating and Binding Packages.
Practice Questions.
Answers.
5. CLI/ODBC Programming.
An Introduction to ODBC and CLI.
Differences Between Embedded SQL and CLI Applications.
Parts of a CLI/ODBC Application.
Initialization.
Transaction Processing.
Freeing Allocated Statement Handles.
Termination.
Putting It All Together.
Obtaining Information about a Driver and Controlling Driver Attributes.
Environment Attributes.
Connection Attributes.
SQL Statement Attributes.
Diagnostics and Error Handling.
SQLSTATE Values.
Obtaining Diagnostic Information.
Obtaining SQLCA Information.
A Diagnostics/Error Handling Example.
Creating Executable Applications.
Practice Questions.
Answers.
6. Java Programming.
An Introduction to Java and JDBC.
JDBC Drivers.
Common JDBC Classes and Methods.
A Word about the JDBC DataSource Interface.
DB2 SQL and JDBC Data Types.
JDBC Application and Applet Basics.
JDBC Driver Registration.
Establishing a Data Source Connection.
Transaction Processing.
Cleaning up Resources.
Putting It All Together.
Diagnostics and Error Handling.
Creating Executable JDBC Applications and Applets.
An Introduction to SQLJ.
SQLJ Language Elements.
A Word about Host Expressions.
SQLJ Application and Applet Basics.
JDBC Driver Registration.
Connecting to a Data Source.
Executing SQL Statements.
Retrieving and Processing Results.
Terminating the Current Transaction.
Cleaning up Resources.
Putting It All Together.
Creating Executable SQLJ Applications and Applets.
Practice Questions.
Answers.
7. Advanced Programming.
Embedded SQL Revisited.
Static SQL.
Dynamic SQL.
Host Variables.
Indicator Variables.
Compound SQL.
Dynamic SQL and Parameter Markers.
Replacing Parameter Markers with Values.
Using CLI/ODBC Descriptors.
Obtaining and Setting Descriptor Information.
Working with Batches of SQL Statements in CLI/ODBC Applications.
CLI/ODBC's Extended Cursors.
Block Cursors.
Scrollable Cursors.
Controlling Cursor Characteristics.
Managing Database Connections.
Coordinating Transactions across Multiple Database Connections.
Two-phase Commit Processing.
Understanding Data Consistency.
Isolation Levels.
The Repeatable Read Isolation Level.
The Read Stability Isolation Level.
The Cursor Stability Isolation Level.
The Uncommitted Read Isolation Level.
Choosing the Proper Isolation Level.
Specifying the Isolation Level to Use.
Locking.
How Locks Are Acquired.
Practice Questions.
Answers.
8. User-defined Routines.
B2 Universal Database's Data Types.
User-defined Data Types.
User-defined Functions.
Sourced Functions.
SQL Functions.
External Scalar Functions.
External Table Functions.
OLE DB External Table Functions.
Why Use User-defined Functions?
Stored Procedures.
Developing and Registering Stored Procedures.
Calling a Stored Procedure.
The Development Center.
Practice Questions.
Answers.
Appendix A: DB2 UDB V8.1 Family Application Development Certification Exam (Exam 703) Objectives.
Database Objects and Programming Methods (13%).
Data Manipulation (26%).
Embedded SQL Programming (11%).
ODBC/CLI Programming (13%).
Java Programming (13%).
Advanced Programming (16%).
User-defined Routines (8%).
Index.
Preface
top
Preface
One of the biggest challenges that computer professionals face today is keeping their skill sets current with the latest changes in technology. When the computing industry was in its infancy, it was possible to become an expert in several different areas because the scope of the field was relatively small. Today, our industry is both widespread and fast paced, and the skills needed to master a single software package can be quite complex. Because of this complexity, many application and hardware vendors have initiated certification programs to evaluate and validate an individual's knowledge of their technology. Businesses benefit from these programs because knowing an individual has professional certification gives them confidence that the person has the expertise needed to perform a specific job. Computer professionals benefit because professional certification allows them to deliver high levels of service and technical expertise and can lead to advancement and/or new job opportunities within the computer industry.
If you've bought this book (or if you are thinking about buying this book), chances are you have already decided you want to acquire one or more of the IBM DB2 Universal Database (UDB) V8.1 Professional Certifications available. As an individual who holds eight IBM DB2 UDB professional certifications, let me assure you that the exams you must pass to become a certified DB2 UDB professional are not easy. IBM prides itself on designing comprehensive certification exams that are relevant to the work environment that an individual holding a particular certification will have had some exposure to. As a result, all of IBM's certification exams are designed with the following items in mind:
* What are the critical tasks that must be performed by an individual who holds a particular professional certification?
* What skills must an individual possess in order to perform each critical task?
* What is the frequency with which each critical task must be performed?
You will find that to pass a DB2 UDB certification exam, you must possess a solid understanding of DB2 Universal Database. (For some of the more advanced certifications, you must understand many of its nuances, as well.)
Now for the good news. You are holding in your hands what I consider to be the best tool you can use to prepare for the DB2 UDB V8.1 Family Application Development exam (Exam 703). When IBM learned that I was planning to write a series of study guides for the DB2 UDB V8.1 certification exams, they invited me to participate in the exam development process. Among other things, I had the opportunity to evaluate several of the beta DB2 UDB V8.1 exams before they went into production. Consequently, I have seen every exam question you are likely to encounter and I know what concepts you will be tested on when you take the DB2 UDB V8.1 Family Application Development exam (Exam 703). Using this knowledge, along with copies of the beta 703 exams, I developed this study guide, which covers not only every DB2 UDB concept you must know in order to pass the DB2 UDB V8.1 Family Application Development exam (Exam 703) but also the exam process itself and the requirements for each DB2 UDB V8.1 certification role available. In addition, at the end of each chapter, I have included sample questions that are worded just like the exam questions. In short, if you see it in this book, count on seeing it on the exam; if you don't see it in this book, chances are that it won't be on the exam.
About This Book
This book is divided into two parts:
* Part 1--DB2 UDB Certification (Chapter 1)
* This section consists of one chapter (Chapter 1), which is designed to< introduce you to the DB2 UDB Professional Certification Program that is available from IBM. In this chapter, you will learn about the certification roles available, along with the basic prerequisites and requirements for each role. This chapter also explains what is involved in the certification process and includes a tutorial on the IBM Certification Exam testing software, which you will encounter when you take a DB2 UDB V8.1 certification exam.
* Part 2--DB2 UDB Family Application Development (Chapters 2-8)
* This section consists of seven chapters (Chapters 2 through 8), which are designed to provide you with the concepts you will need to master before you can pass the DB2 UDB V8.1 Family Application Development exam (Exam 703). Chapter 2 is designed to introduce you to the database objects available with DB2 UDB and to provide you with an overview of the various privileges that are required in order to perform specific operations against those objects. In this chapter, you will learn about the objects (such as tables, indexes, and views) that DB2 UDB uses to facilitate data storage and you will learn when NOT NULL, default, check, unique, referential integrity, and informational constraints should be used. You will also learn how authorities and privileges determine what a user can and cannot do while working with a database and you will be introduced to the programming interfaces that are used to construct DB2 UDB database applications. Chapter 3 is designed to introduce you to the SQL statements that can be used to manipulate user data stored in DB2 UDB database objects. In this chapter, you will learn what SQL is, as well as which SQL statements are classified as Data Manipulation Language (DML) statements. You will also learn how DML statements are used to store, manipulate, and/or retrieve data stored in database objects; how data stored in a result data set (produced in response to a query) can be retrieved within an application program; and how transactions are used to define points of consistency within a database. Chapter 4 is designed to introduce you to embedded SQL application development. In this chapter, you will learn how SQL statements can be embedded in high-level programming language source code files and you will be introduced to some of the components that are used to facilitate the movement of data and return code values between an application and a database. You will also learn how simple embedded SQL applications are coded and how source code files containing embedded SQL statements are converted into executable database applications. Chapter 5 is designed to introduce you to CLI/ODBC application development. In this chapter, you will learn what CLI/ODBC is and you will explore many of the CLI/ODBC functions that are used to develop database applications. You will also learn how simple CLI/ODBC applications are coded and how source code files containing CLI/ODBC function calls are converted into executable database applications. Chapter 6 is designed to introduce you to JDBC and SQLJ application and applet development. In this chapter, you will learn what JDBC is and you will explore many of the JDBC objects and methods that are used to develop Java-based database applications and applets. In addition, you will learn what SQLJ is and how SQLJ is used to embed static SQL statements in Java applications and applets. You will also see how both JDBC and SQLJ applications and applets are converted to Java bytecode that can be interpreted by the Java Virtual Machine. Chapter 7 is designed to introduce you to advanced programming concepts such as parameter marker utilization, compound SQL, transaction processing, two-phase commit processing, isolation levels, and locking. In this chapter, you will learn how static SQL differs from dynamic SQL and you will see how parameter markers can be used in place of constants and expressions in a dynamic SQL statement. You will also learn how to use compound SQL statements, manage transactions across multiple database connections, and how two-phase commit processing is performed. Additionally, you will learn what isolation levels are, which isolation levels are available, and how DB2 UDB provides concurrency control through the use of isolation levels and locking. Chapter 8 is designed to introduce you to user-defined data types, userdefined functions, and stored procedures. In this chapter, you will learn what user-defined data types, user-defined functions, and stored procedures are and how each are developed and registered with a database. You will also learn how stored procedures are invoked once they have been created and you will get a quick tour of the Development Center (a GUIbased tool that can be used to develop user-defined data types, userdefined functions, and stored procedures).
This book is written primarily for IT professionals who have already taken and passed the DB2 UDB V8.1 Family Fundamentals exam (Exam 700) and want to take (and pass) the DB2 UDB V8.1 Family Application Development exam (Exam 703). However, any individual who would like to learn the basics of DB2 UDB V8.1 application development will benefit from the information found in this book.
A Word about the Sample Programs Provided
Throughout this book, you will see example programs that illustrate how various types of DB2 UDB applications are constructed. All of these programs are designed to interact with the SAMPLE database that is provided with DB2 UDB (which can be created by executing the command db2sampl from the DB2 Command Line Processor) and have been tested and work as designed. In many cases, error checking was kept to a minimum to save on space and to make the program logic easier to follow.
