DBT544 - DB2

Outline info
Last revision date 2018-07-20 11:57:19.071
Last review date 2018-07-20 11:57:31.25

Subject Title

Subject Description
This subject provides an in-depth look at DB2 Universal Database for the AS/400, the most widely used multi-user relational database in the world. This subject will start with a brief look at the native interface which includes logical, physical and field reference files and then focus on the SQL/400 interface which includes Data Definition Language, Data Manipulation Language, Embedded SQL/400, Stored Procedures and the SQL Procedure language, Journaling and Commitment Control and ODBC. Advanced topics include Database Constraints, File Overrides, Trigger Programs, Database security, Backup and Recovery, the Universal Database and Operations Navigator.

Credit Status
1 credit in the CPA Programs.

Learning Outcomes
Upon successful completion of this subject the student will be able to:

1. Create a User Defined Function

2. Create and use field reference files

3. Create files with Data Description Specifications and SQL

4. Use Operations Navigator to manage objects in a database

5. Access Database files from a COBOL program or RPGIV program

6. Use security mechanisms to control access to a DB2/400 Database

7. Embed SQL/400 DML statements in a program

8. Code a COBOL or RPGIV trigger program

9. Preserve transaction integrity in a multi-user DB environment using table and row locking and Commit/Rollback techniques

10. Use Interactive SQL on the AS/400

11. Work with journalling, Backup and Recovery

12. Use SQL Procedural Language and Create a Stored Procedure

Cheating and Plagiarism
Each student should be aware of the College's policy regarding Cheating and Plagiarism. Seneca's Academic Policy will be strictly enforced.

To support academic honesty at Seneca College, all work submitted by students may be reviewed for authenticity and originality, utilizing software tools and third party services. Please visit the Academic Honesty site on http://library.senecacollege.ca for further information regarding cheating and plagiarism policies and procedures.

All students and employees have the right to study and work in an environment that is free from discrimination and/or harassment. Language or activities that defeat this objective violate the College Policy on Discrimination/Harassment and shall not be tolerated. Information and assistance are available from the Student Conduct Office at student.conduct@senecacollege.ca.

Accommodation for Students with Disabilities
The College will provide reasonable accommodation to students with disabilities in order to promote academic success. If you require accommodation, contact the Counselling and Disabilities Services Office at ext. 22900 to initiate the process for documenting, assessing and implementing your individual accommodation needs.


Topic Outline
Review of prerequisite material 5%

DB2/400 basic concepts 5%

Physical and Logical Files 5%

  • Field Reference Files
  • Advanced Logical File features - Deriving new fields, multi-format and join logical files
  • Accessing Database Files from High Level Languages
  • Using OPNQRYF to select and order records
SQL/400 Data Definition Language 10%
  • Creating a Collection or Schema with RUNSQLSTM
SQL/400 Data Manipulation Language 5%
  • Retrieving Rows with the Select Statement
  • The Insert, Update, and Delete Statements
  • Concurrent Updates and the Lock Table Statement
  • Transaction Integrity and Commit and Rollback Statements
Embedded SQL/400 in a COBOL & RPG-IV program 20%
  • Embedded SQL Error Handling
  • Static Statements
  • Dynamic Embedded SQL Statements
  • SQL Cursors Not scrollable, Scrollable and Dynamic
  • Host Structures and Arrays
SQL Procedure Language 10%
  • Create Procedure Statement
  • Declarations and Assignment Statements
  • Conditional processing with IF and CASE Statements
  • Loop Control Structures with LOOP, WHILE, REPEAT and FOR
  • Creating an SPL User Defined Function
Query Management 10%
  • Query Management queries
  • Passing parameters to QM queries
  • Query Management Forms
DB2/400 Advanced Features 10%
  • Level Check Considerations
  • Database Constraints
  • File Overrides
  • Object and Record Locks
  • Journals, Journal Receivers, WRKJRN, Applying and Removing Journaled changes
  • Trigger Programs (RPG & COBOL)
  • Distributed Database
  • Database Security
  • Backup and Recovery
  • Check Constraints
  • UDB and Binary Large Objects, User Defined Functions, User Defined Data Types and Data Links
Operations Navigator (iSeries Navigator) 5%
  • Managing objects in a database with operations navigator
  • Using Operations Navigator with integrity constraints, check restraints, triggers and SQL stored procedures
  • Creating tables
  • Operational control of iSeries AS400
Documenting the Data Base Model 5%
Journalling and Commitment Control 10%
  • Journals and Journal Receivers
  • Applying and Removing Journaled changes
  • Transaction Integrity and the Commit and Rollback Statements
  • Using Commit and Rollback in a RPGIV or COBOL multitransaction update

Mode of Instruction
Classroom lectures and discussions are supplemented by lab sessions with the instructor, reinforced by assigned readings and assignments between classes.

It is the student's responsibility to save documents, articles and notes that the instructor has provided on BlackBoard or in class. Students will not be able to access BlackBoard as of the last day of the student’s class.

Prescribed Texts

Reference Material
Mastering the AS/400, 3rd. Edition
by Jerry Fottral, 29th Street Press, ISBN#1-58304-070-6 

Promotion Policy

Grading Policy
A+ 90%  to  100%
A 80%  to  89%
B+ 75%  to  79%
B 70%  to  74%
C+ 65%  to  69%
C 60%  to  64%
D+ 55%  to  59%
D 50%  to  54%
F 0%    to  49% (Not a Pass)
EXC Excellent
SAT Satisfactory
UNSAT Unsatisfactory

For further information, see a copy of the Academic Policy, available online (http://www.senecacollege.ca/academic-policy) or at Seneca's Registrar's Offices.

Modes of Evaluation
Since this is a professional credit subject, marking standards reinforce professional practice by demanding legible, tidy work. Written materials should be well organized and grammatically correct, with proper spelling and punctuation.


  • Students must retain a duplicate of all assignments.
  • Computer assignments should be documented to the instructor's standards.
  • Assignments must be handed in on the scheduled due date. Late assignments are penalized.
  • For particulars, please obtain standards, dates, etc. from your instructor.

Absenteeism and Tests
  • Students should be aware that absenteeism will impact on their ability to achieve satisfactory grades.
  • If you miss a test, you must provide the reason in writing to the instructor prior to the next scheduled class. If your reason is accepted, you will be permitted to write a make-up test. Otherwise, you will be given a zero for the test. You must submit an original doctor’s certificate identifying the date, length of time of expected absence and the specific reason for your absence, or other appropriate documentation.

Term Work and Final Exam
  • Students must attain a combined grade of at least 50% on term work and the final exam. Students must pass the final exam in order to pass the subject. 
  • For further information on evaluation and academic standing, see a copy of the Academic Policy available at Seneca registration offices.

Grading is based on the following marking scheme:

Tests (minimum 2)
Assignments (minimum 2) 
Final Exam 

Approved by: Denis Gravelle