You are previewing DB2 9 Fundamentals Certification Study Guide.
O'Reilly logo
DB2 9 Fundamentals Certification Study Guide

Book Description

Service-Oriented Architecture (SOA) is a way of organizing software. If your company's development projects adhere to the principles of SOA, the outcome will be an inventory of modular units called "services", which allow for a quick response to change.

This book tells the SOA story in a simple, straightforward manner that will help you understand not only the buzzwords and benefits, but also the technologies that underlie SOA: XML, WSDL, SOAP, XPath, BPEL, SCA, and SDO. And through it all, the authors provide business examples and illustrations, giving a practical meaning to abstract ideas.

SOA for the Business Developer:

* Gives a detailed overview of Extensible Markup Language (XML), including namespaces and XML schema.

* Describes Web Services Definition Language (WSDL) and SOAP, the standard SOA technologies.

* Gives a clear tutorial on XML Path Language (XPath), a language for deriving data from transmitted messages and other sources. XPath is useful for working with a variety of other technologies, including several described in this book.

* Gives comprehensive details on BPEL 2.0, a language that coordinates services and whose preceding version is already in numerous products. Our coverage is sufficient for most of your work with BPEL and includes a quick-reference guide.

* Introduces Service Component Architecture (SCA), a proposed standard for composing and deploying applications. You're sure to hear more of SCA, which is sponsored by 18 companies.

* Introduces Service Data Objects (SDO), a proposed standard for representing data in a single way, even if the data comes from different types of data sources. SDO is likely to accompany SCA into the limelight.

Table of Contents

  1. Copyright
    1. Dedication
  2. Acknowledgments
  3. About the Author
  4. About This Book
    1. Conventions Used
  5. Foreword
  6. Preface
  7. 1. IBM DB2 9 Certification
    1. DB2 9 Certification Roles
      1. IBM Certified Database Associate—DB2 9 Fundamentals
      2. IBM Certified Database Administrator—DB2 9 for Linux, UNIX, and Windows
      3. IBM Certified Database Administrator—DB2 9 for z/OS
      4. IBM Certified Application Developer—DB2 9 Family
      5. IBM Certified Advanced Database Administrator—DB2 9 for Linux, UNIX, and Windows
      6. IBM Certified Solution Designer—DB2 Data Warehouse Edition V9.1
    2. The Certification Process
      1. Preparing for the Certification Exams
      2. Arranging to Take a Certification Exam
      3. Taking an IBM Certification Exam
  8. 2. Planning
    1. The DB2 Family
      1. DB2 Everyplace—Database Edition and Enterprise Edition
      2. DB2 Express Edition
      3. DB2 Express-C
      4. DB2 Personal Edition
      5. DB2 Workgroup Server Edition
      6. DB2 Enterprise Server Edition
      7. DB2 Data Warehouse Server Edition
      8. DB2 Personal Developer’s Edition
      9. DB2 Universal Developer’s Edition
      10. DB2 Enterprise Developer’s Edition
      11. DB2 for i5/OS
      12. DB2 for z/OS
    2. Other DB2 Products
      1. DB2 Clients
        1. DB2 Runtime Client
        2. DB2 Client
      2. DB2 Connect
      3. DB2 Extenders
      4. A Word About XML Data
    3. DB2 9’s Comprehensive Tool Set
      1. The Control Center
      2. The Replication Center
      3. The Satellite Administration Center
      4. The Command Editor
        1. SQL Assist
        2. Visual Explain
      5. The Task Center
      6. The Health Center
        1. A Word about the SQL Performance Monitor
      7. The Journal
      8. The License Center
      9. The Configuration Assistant
      10. The Design Advisor
      11. The Command Line Processor
      12. The Developer Workbench
    4. A Word About Database Workloads
    5. Practice Questions
    6. Answers
  9. 3. Security
    1. Controlling Database Access
    2. Authentication
      1. Where Does Authentication Take Place?
      2. Trusted Clients versus Untrusted Clients
    3. Authorities and Privileges
      1. Authorities
      2. System Administrator authority
        1. System Control authority
        2. System Maintenance authority
        3. System Monitor authority
        4. Database Administrator authority
        5. Security Administrator authority
      3. Load authority
      4. Privileges
        1. Database privileges
        2. Object privileges
    4. Granting Authorities and Privileges
      1. Granting and Revoking Authorities and Privileges from the Control Center
      2. Granting Authorities and Privileges with the GRANT SQL Statement
        1. Database-level authorities and privileges
        2. Table space privileges
        3. Schema privileges
        4. Table privileges
        5. View privileges
        6. Index privileges
        7. Sequence privileges
        8. Routine privileges
        9. Package privileges
        10. Server privileges
        11. Nickname privileges
        12. Distinct type privileges (DB2 for iSeries and DB2 for zSeries only)
        13. GRANT SQL statement examples
      3. Revoking Authorities and Privileges with the REVOKE SQL Statement
        1. Database-level authorities and privileges
        2. Table space privileges
        3. Schema privileges
        4. Table privileges
        5. View privileges
        6. Index privileges
        7. Sequence privileges
        8. Routine privileges
        9. Package privileges
        10. Server privileges
        11. Nickname privileges
        12. Distinct type privileges (DB2 for iSeries and DB2 for zSeries only)
        13. REVOKE SQL statement examples
      4. Requirements for Granting and Revoking Authorities and Privileges
    5. Authorities and Privileges Needed to Perform Common Tasks
    6. Practice Questions
    7. Answers
  10. 4. Working with Databases and Database Objects
    1. Servers, Instances, and Databases
    2. Managing Instances
    3. Creating a DB2 9 Database
      1. What Happens When a DB2 9 Database Is Created
      2. The Complete CREATE DATABASE Command
      3. Creating a DB2 9 Database with the Create Database Wizard
    4. DB2’s Directory Files
      1. The System Database Directory
      2. The Local Database Directory
      3. The Node Directory
      4. The Database Connection Services (DCS) Directory
      5. Cataloging and Uncataloging a DB2 Database
      6. Cataloging and Uncataloging a Node
      7. Cataloging and Uncataloging a DCS Database
    5. Establishing a Database Connection
    6. Creating Database (Data) Objects
      1. Schemas
      2. Tables
        1. A word about declared temporary tables
      3. Views
      4. Indexes
      5. Aliases
      6. Sequences
      7. Triggers
      8. User-Defined Data Types
        1. Distinct data types
        2. Structured data types
      9. User-Defined Functions (or Methods)
      10. Stored Procedures
        1. SQL stored procedures
        2. External stored procedures
      11. Packages
    7. Deleting Database (Data) Objects
    8. Practice Questions
    9. Answers
  11. 5. Working with DB2 Data Using SQL and XQuery
    1. Structured Query Language (SQL)
    2. SQL Data Manipulation Language (DML) Statements
      1. The INSERT Statement
      2. The UPDATE statement
      3. The DELETE Statement
      4. The SELECT Statement
    3. A Closer Look at the SELECT Statement and Its Clauses
      1. The WHERE Clause
        1. Relational predicates
      2. The BETWEEN predicate
      3. The LIKE predicate
      4. The IN predicate
      5. The EXISTS predicate
      6. The NULL predicate
      7. The GROUP BY Clause
      8. The GROUP BY ROLLUP clause
      9. The GROUP BY CUBE clause
      10. The HAVING Clause
      11. The ORDER BY Clause
      12. The FETCH FIRST Clause
      13. A Word About Common Table Expressions
      14. A Word About CASE Expressions
      15. Joining Tables
        1. Inner joins
        2. Outer joins
      16. Combining the Results of Two or More Queries Using a Set Operator
    4. Obtaining Results from a Result Data Set Using a Cursor
      1. The DECLARE CURSOR Statement
      2. The OPEN Statement
      3. The FETCH Statement
      4. The CLOSE Statement
      5. Putting It All Together
    5. Transactions and Transaction Boundaries
      1. Transaction Management and Savepoints
    6. Invoking User-Defined Functions
    7. Calling a Stored Procedure
    8. Working with XML Data
    9. Practice Questions
    10. Answers
  12. 6. Working with DB2 Tables, Views, and Indexes
    1. DB2 9’s Data Types
      1. Numeric Data Types
      2. Character String Data Types
      3. Date/Time Data Types
      4. Large Object (LOB) Data Types
      5. The Extensible Markup Language (XML) Document Data Type
      6. Extenders
      7. User-Defined Data Types
    2. Understanding Constraints
      1. NOT NULL Constraints
      2. Default Constraints
      3. CHECK Constraints
      4. UNIQUE Constraints
      5. Referential Integrity Constraints
        1. The Insert Rule for referential constraints
        2. The Update Rule for referential constraints
        3. The Delete Rule for referential constraints
      6. Informational Constraints
      7. Temporarily Suspending Constraint Checking with the SET INTEGRITY SQL Statement
    3. Creating Tables
      1. Creating Tables with XML Columns
      2. Creating Tables with Identity Columns
      3. Creating Partitioned Tables
      4. Creating Tables That Are Like Existing Tables
    4. A Quick Review of Schemas
    5. Examples of the CREATE TABLE SQL Statement
      1. Example 1
      2. Example 2
      3. Example 3
      4. Example 4
      5. Example 5
      6. Example 6
    6. A Closer Look At Declared Temporary Tables
    7. Another Look At Views
    8. A Closer Look At Indexes
      1. Clustering Indexes
      2. Multidimensional Clustering (MDC) Indexes
    9. Another Look at Triggers
    10. Practice Questions
    11. Answers
  13. 7. Database Concurrency
    1. Understanding Data Consistency
    2. Isolation Levels
      1. The Repeatable Read Isolation Level
      2. The Read Stability Isolation Level
      3. The Cursor Stability Isolation Level
      4. The Uncommitted Read Isolation Level
      5. Choosing the Proper Isolation Level
      6. Specifying the Isolation Level to Use
    3. Locking
      1. Lock Attributes and Lock States
      2. How Locks Are Acquired
      3. Lock granularity and concurrency
      4. Which Locks Are Acquired?
      5. Locks and Performance
        1. Lock compatibility
        2. Lock conversion
        3. Lock escalation
        4. Lock waits and timeouts
        5. Deadlocks
    4. Practice Questions
    5. Answers
  14. A. DB2 9 Fundamentals Exam (Exam 730) Objectives
    1. Planning (14%)
    2. Security (11%)
    3. Working with Databases and Database Objects (17%)
      1. Working with DB2 Data using SQL and XQuery (23.5%)
      2. Working with DB2 Tables, Views, and Indexes (23.5%)
      3. Data Concurrency (11%)
  15. B. Sample Test
    1. Planning
    2. Security
    3. Working with Databases and Database Objects
    4. Working with DB2 Data Using SQL and XQuery
    5. Working with DB2 Tables, Views, and Indexes
    6. Data Concurrency
    7. Answers
      1. Planning
      2. Security
      3. Working with Databases and Database Objects
    8. Working with DB2 Data Using SQL and XQuery
    9. Working with DB2 Tables, Views, and Indexes
    10. Data Concurrency