Test 000-302: DB2 9 Database and Application Fundamentals - Academic Initiative

An IBM DB2 Academic Associate is a member of the academic community (faculty member OR a student in a degree program) who has completed a course that covers the IBM DB2 Academic Associate CE302A course material (including all of the hands-on labs) and passed an IBM exam on the course contents.

An IBM DB2 Academic Associate is knowledgeable in fundamental concepts of RDBMS, basic administration of DB2 9.7 and understands the minimal requirements involved in the development of applications for DB2. 

The holder of this title also demonstrates that the theoretical gap between traditional database courses at college level and real hands-on experience with the DB2 data server has been closed. 

Individuals who successfully attain this title should have enough knowledge to start building application that utilizes the DB2 database, and perform basic database administration tasks.

The IBM DB2 Academic Associate Exam is offered through two IBM programs. It is offered at no charge for approved classes through the IBM CAS testing system. An equivalent exam is also offered as a DB2 Academic Certification for a fee through the IBM Certification testing system using PRIME. 

Most universities make use of the no-charge testing that is available through the IBM CAS testing system. Testing using the PRIME testing system can be used when students wish to try a second time to pass the exam or when it is critical that the student results be collected with other IBM Professional Certification information. 

Instructions for preparing for both exams are identical and are available on the IBM DB2 Academic Associate Program website (ibm.com/db2academic). When the IBM proctor (a requirement for both types of testing) requests the testing codes, they need to request them in the appropriate testing system. Faculty members providing this course for their students must be a member of the no-charge IBM Academic Initiative program (ibm.com/academicinitiative)

The rest of this information pertains to taking the IBM DB2 Academic Associate exam using the Prime testing system. 

Section 1 - Relational Database Concepts (12%)

1. Demonstrate understanding of the concepts of Data, Data Model and Information Model
2. Demonstrate knowledge of the Relational Data Model and its components
3. Demonstrate knowledge of relational database objects (tables, columns, rows, primary keys and foreign keys)
4. Demonstrate knowledge of what is a Database Management System and its advantages regarding storage of data

Section 2 - Getting Started with DB2 and Data Studio (20%)

1. Demonstrate knowledge of the different DB2 editions and features included
2. Understanding of the different licenses available for DB2 Data Server
3. Demonstrate ability to create and drop DB2 instances and databases, and how to view and modify their configuration
4. Demonstrate knowledge of DB2 storage concepts: table space, containers, buffer pools
5. Describe the different DB2 client packages
6. Demonstrate knowledge of DB2's administration tools, data movement tools and their purpose
7. Demonstrate knowledge of IBM Data Studio and IBM Data Studio
8. Demonstrate knowledge of creating, cataloguing, dropping and connecting to DB2 servers and databases

Section 3 - Working with SQL and Database Objects (20%)

1. Identify basic characteristics of DB2 database objects
2. Demonstrate ability to use DDL SQL statements to create different database objects
3. Identify the results of a given DML SQL statement
4. Demonstrate ability to use SQL to SELECT data from multiple tables using different types of join
5. Ability to use SQL to sort or group related data
6. Ability to use SQL to update, delete or insert data

Section 4 - Data Concurrency and Locking (8%)

1. Demonstrate knowledge of transactions and the ACID (Atomicity, Consistency, Isolation, Durability) properties
2. Identify the result of operations when COMMIT and ROLLBACK commands are involved
3. Demonstrate knowledge of common concurrency issues, timeouts and deadlocks
4. Ability to describe Isolation Levels in DB2 9.7 and given a situation, be able to identify the proper isolation level to be used
5. Demonstrate ability to change the isolation level for database operations

Section 5 - Database Security (8%)

1. Ability to describe DB2 authentication and authorization methods
2. Ability to assign and revoke privileges and authorities to users, user groups and roles using SQL
3. Demonstrate knowledge of what is a DB2 Trusted Context and advantages of using Trusted Connections
4. Demonstrate knowledge of Label-Based Access Control and its advantages over regular SQL privileges

Section 6 - DB2 Backup and Recovery (8%)

1. Demonstrate understanding of different logging types in DB2
2. Ability to describe available database and table space backup types in DB2: offline/online, full, incremental, incremental delta
3. Demonstrate knowledge of recovery operations for the different types of backup in DB2

Section 7 - DB2 pureXML (10%)

1. Demonstrate basic knowledge of XML, XPath and XSLT and how these technologies are related
2. Demonstrate knowledge of storing XML documents in DB2 with pureXML
3. Demonstrate ability to insert, update, delete and retrieve XML data in DB2 using SQL
4. Demonstrate ability to use IMPORT, EXPORT and LOAD utilities to move XML data
5. Demonstrate ability to identify the result of XQuery statements in DB2
6. Demonstrate knowledge of XQuery language and SQL/XML publishing functions
7. Demonstrate basic knowledge of XML indexes

Section 8 - DB2 Application Development (12%)

1. Ability to identify programming languages and APIs supported by DB2
2. Given a specific API, knowledge of which DB2 client packages are necessary for building and running applications
3. Ability to understand the differences between static and dynamic SQL
4. Demonstrate knowledge of different routine types (functions and procedures) and implementations in DB2
5. Demonstrate understanding of purpose of triggers and supported types of trigger in DB2
6. Demonstrate knowledge of DB2 tools to develop and execute routines

Section 9 - Troubleshooting (2%)

1. Understand how to apply DB2 troubleshooting techniques when a problem arises
2. Learn how to use the DB2 Information Center to understand concepts or research a problem