DB2 (R) Universal Database V8.1 Certification Exam 703 Study Guide
Published on 8. July 2004
Book
Paperback/Softback
352 pages
978-0-13-184049-2 (ISBN)
Description
*The definitive DB2 UDB V8.1 Family Application Development (Exam 703) preparation guide--straight from IBM *Covers database objects, SQL, embedded SQL, CLI/ODBC, JDBC, SQLJ, transactions, stored procedures, and user-defined datatypes and functions *Reviews advanced programming concepts such as compound SQL, two-phase commit, and locking *Contains extensive sample questions based on the actual exam's format and approach *Includes authoritative coverage of the DB2 exam and certification process *By Roger Sanders, best-selling DB2 certification author and active participant in IBM's exam development process Recognition as an IBM DB2 Certified Application Developer demonstrates your exceptional expertise in developing for DB2 UDB 8.1, the world's #1 enterprise database platform. If you're an IBM Certified Database Associate, you can achieve certification by passing one exam: IBM DB2 UDB V8.1 Exam 703. Straight from IBM, this is the only official Exam 703 self-study guide. Roger E. Sanders, the world-renowned DB2 expert who helped develop Exam 703, covers every objective you must master in order to pass.
Sanders walks step-by-step through basic data manipulation; core DB2 programming methods; SQL and embedded SQL; ODBC/CLI; Java and SQLJ; stored procedures; transactions, user-defined routines; and much more. Every chapter contains an extensive set of practice questions closely modeled on the actual exam, along with carefully explained answers. Coverage includes: *In-depth explanations of database objects, privileges, and constraints *SQL and DML statements for storing, manipulating, and retrieving data *Embedding SQL in high-level programming language source code *CLI/ODBC development, including conversion into executables *JDBC objects and methods for developing Java database applications *Using SQLJ to embed static SQL statements in Java applications *Transactions, stored procedures, user-defined functions, and other advanced techniques *Complete information on certification prerequisites and requirements Whether you're currently preparing for Exam 703, or you simply want to deepen your skills as a DB2 developer, you'll find this book indispensable.
Sanders walks step-by-step through basic data manipulation; core DB2 programming methods; SQL and embedded SQL; ODBC/CLI; Java and SQLJ; stored procedures; transactions, user-defined routines; and much more. Every chapter contains an extensive set of practice questions closely modeled on the actual exam, along with carefully explained answers. Coverage includes: *In-depth explanations of database objects, privileges, and constraints *SQL and DML statements for storing, manipulating, and retrieving data *Embedding SQL in high-level programming language source code *CLI/ODBC development, including conversion into executables *JDBC objects and methods for developing Java database applications *Using SQLJ to embed static SQL statements in Java applications *Transactions, stored procedures, user-defined functions, and other advanced techniques *Complete information on certification prerequisites and requirements Whether you're currently preparing for Exam 703, or you simply want to deepen your skills as a DB2 developer, you'll find this book indispensable.
More details
Language
English
Place of publication
Armonk
United States
Publishing group
Pearson Education (US)
Target group
College/higher education
Dimensions
Height: 230 mm
Width: 155 mm
Thickness: 20 mm
Weight
617 gr
ISBN-13
978-0-13-184049-2 (9780131840492)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Classification
Person
ROGER E. SANDERS, database performance engineer with Network Appliance Inc., is author of several books about DB2, including DB2 UDB V8.1 Certification Exam 700 Study Guide, DB2 Universal Database SQL Developer's Guide and DB2 Administration All-in-One Exam Guide. He has worked with DB2 for well over a decade, and holds eight IBM DB2 certifications, including IBM Certified Advanced Technical Expert (DB2 for Clusters) and IBM Certified Solutions Expert. A regular contributor to DB2 Magazine, he has authored database tutorials for IBM's DeveloperWorks website and presented at three International DB2 User Group conferences.
Content
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.
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.