Week 5: Working with DB2 Tables, Views, and Indexes... Study material and FAQ

In preparation for the 5th session, please read DB2 9 Fundamentals exam 730 prep, Part 5 (Working with DB2 objects).
(Note: The IBM ID required to sign-in for the tutorial is different from your IBM Intranet id. If you do not already have an IBM id, follow the link on the above site to register for an IBM id.)

Please note that these sessions are structured on the assumption that you have read the material prior to attending the weekly session. If you do not read & understand the preparation material prior to attending the session, you will not be able to fully participate.
If you are unable to attend these review sessions, you must be diligent and read/review/practice this material independently.
Preparation will mean the difference between passing or failing the exam.


Additional Resources:
Here are some additional tutorials to help give you some hands-on experience with DB2. Since these tutorials are each written as stand-alone tutorials, they each include instructions for downloading DB2 Express-C software. You only need to install DB2 Express-C once, but the data files associated with each tutorial will need to be downloaded and setup with your local installation of DB2.

New to DB2:
- Hello World:
- Part 1
- Part 2
- DB2 Detective Game

Prior experience with DB2:
- SQL & XQuery Tutorial
- DB2 Business Game

On-line book:
DB2 9 Fundamentals Certification Study Guide, by Roger E. Sanders.
You can find this book on the IBM Books 24x7 site.

DB2 Certification Preparation group on ChannelDB2 (this group).

DB2 9 Certification Exam (#730) Preparation:
Part 1 (Planning)
Part 2 (Security)
Part 3 (Accessing DB2 Data
Part 4 (Working with DB2 Data)
Part 5 (Working with DB2 objects)


Summary of Dates:

Exam Date: March 24
Location: B1 - Training Centre

Objectives:
Section 5 - Working with DB2 Objects (17%) - Tests your knowledge of the different objects that are available with DB2 9 and your ability to create a DB2 9 database
A. Ability to identify and connect to DB2 servers and databases
B. Ability to construct a DB2 9 database
C. Ability to identify DB2 objects
D. Knowledge of the basic characteristics and properties of DB2 objects
E. Ability to identify DDL statements available with DB2 9
F. Ability to identify the results produced when a DDL statement is executed

Working with DB2 Objects REVIEW

• Servers, Instances, and Databases
  1. What is a DB2 data server?
  2. Describe an instance, when it's created, and what it controls
  3. Describe the relationship between servers, instances, and databases
  4. List some components of a database
• Managing instances
  1. What is the default instance?
  2. Under what conditions would you create multiple instances?
  3. What command is used to:
     • Create an instance
     • Drop an instance
     • List all instances
     • Migrate an instance
     • Update an instance
     • Start an instance
     • Stop an instance
• Creating DB2 9 databases
  1. What are the two ways to create a database?
  2. What happens when a database is created?
  3. Which subdirectories hold the containers that contain the database's data?
  4. What's a DB2EVENT?
  5. What's a SQLOGDIR?
  6. List some of the files that are needed for management, monitoring, and database recovery
  7. What's a buffer pool, when is it created?
  8. What's the default buffer pool?
  9. What are SYSCATSPACE and USERSPACE1, and what are they used for?
  10. Describe the difference between system catalog tables and database configuration files
  11. What are SYSIBM, SYSCAT, SYSTAT, and SYSFUN?
  12. What are bind files?
  13. What authorities and privileges are granted when a database is created?
  14. What are DB2's directory files and how many are there?
  15. Why do you catalog and uncatalog a database, how, and when?
  16. Why do you catalog and uncatalog a node, how, and when?
  17. What's a DCS database; how and when would you catalog and uncatalog it?
  18. How do you establish a connection to a database?
• Creating Database (Data) Objects
  1. List 10 database objects and what they're used for
  2. What is the naming convention for schemas?
  3. How are schemas implictly and explictly created?
  4. What are the different types of tables and what are they used for?
  5. What are views and what are they used for?
  6. What is the "WITH CASCADED CHECK OPTION" clause used for? Which statement does it apply to?
  7. Describe the different types of indexes and what they're used for
  8. How would you give a database a nickname?
  9. How could you generate column values automatically? Why would you need to do this?
  10. What objects are executed to enforce data integrity and how are they executed?
  11. What are "transition variables" and "transition tables"?
  12. What are "triggered actions"?
  13. What are "distinct" and "structured" user-defined data types?
  14. What's the difference between "user-defined functions" and "methods"?
  15. What's an "SQL scalar" and an "external scalar"?
  16. What's an OLE DB external table?
  17. What's the difference between a "stored procedure" and an "SQL stored procedure"?
  18. What's an "external stored procedure"?
  19. What are the FENCED and NOT FENCED claused for?
  20. What DB2 object contains the information needed to process SQL statements?
  21. Describe "binding" and "deferred binging"?
  22. How do you delete database objects?
  23. Which database objects cannot be deleted?