Week 3: Working with Databases and Database Objects... Study material and FAQ
In preparation for the 3rd session, please read DB2 9 Fundamentals exam 730 prep, Part 3 (Accessing DB2 Data) prior to the review session.
(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. Statistics from prior preparation sessions shows that only 2 in 5 students enrolled in these sessions will ultimately pass the test. The good news is ... 70% who prepared and took the test ... passed.
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
- 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.
See: DB2 Certification Preparation (this group) on ChannelDB
Objectives:
Section 3 - Working with Databases and Database Objects (17%)
A. Ability to identify and connect to DB2 servers and databases
B. Ability to identify DB2 objects
C. Knowledge of basic characteristics and properties of DB2 objects
D. Given a DDL SQL statement, knowledge to identify results (ability to create objects)
What makes up a DB2 database?
What are the logical, physical, and performance features of a database?
Describe each of these objects:
Describe the DB2 storage model
Describe tables, indexes, long fields, and table spaces
Describe DMS and SMS table spaces
Describe DMS versus SMS versus Automatic Storage
Creating your first database
List the objects DB2 created when creating a database.
Describe the CREATE DATABASE command, including:
Describe how to manipulate DB2 objects with the Control Center:
(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. Statistics from prior preparation sessions shows that only 2 in 5 students enrolled in these sessions will ultimately pass the test. The good news is ... 70% who prepared and took the test ... passed.
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.
See: DB2 Certification Preparation (this group) on ChannelDB
WORKING WITH DATABASES AND DATABASE OBJECTS REVIEW
Objectives:
Section 3 - Working with Databases and Database Objects (17%)
A. Ability to identify and connect to DB2 servers and databases
B. Ability to identify DB2 objects
C. Knowledge of basic characteristics and properties of DB2 objects
D. Given a DDL SQL statement, knowledge to identify results (ability to create objects)
What makes up a DB2 database?
What are the logical, physical, and performance features of a database?
Describe each of these objects:
- Tables, views, indexes, schemas
- Locks, triggers, stored procedures, packages
- Buffer pools, log files, table spaces
Describe the DB2 storage model
Describe tables, indexes, long fields, and table spaces
Describe DMS and SMS table spaces
Describe DMS versus SMS versus Automatic Storage
Creating your first database
List the objects DB2 created when creating a database.
Describe the CREATE DATABASE command, including:
- how to specify database location
- storage options
- code pages and collating sequences
- table space definitions Describe cataloging DB2 databases:
- why catalog a database
- how to catalog a database at the client
- how to use automated configuration using discovery
- how to automate configuration using access profiles
- manual configuration
- how to use the CA to catalog a database
- which method to use
- which communication protocol to use
- communication and database details
- ODBC settings
- Node, system, and security options
Describe how to manipulate DB2 objects with the Control Center:
- how to create and manipulate databases
- how to create user, catalog, and temp tables
- how to create and access basic DB2 objects
Replies to This Discussion
-
Permalink Reply by Dave Corbett on -
Here's a challenge for everyone:
First, create a table with the following columns: first name, last name, employee number, supervisor's employee number. Add any other columns you see fit; but remember to follow DB2's naming syntax rules. Make the person's employee number the unique identifier for that person. Add a constraint to the table which requires that every employee must have a supervisor, and a constraint which requires that every supervisor is also an employee.
(Something to think about: Who supervises the boss?)
Next, create a view on your table which returns the following information: Each employee's first and last name, and that employee's supervisor's first and last name.
This will test your understanding of database design, data structure design, DDL statements and DML statements. I intend this strictly as a practice exercise, which is to say that if your solution works, you can consider it to be correct; if you want a hint or two, just let me know.
FULL DISCLOSURE: I have already done this sort of thing in both Oracle and PostgreSQL.
© 2010 Created by channeldb2.
