Descripiton

The course covers the creation and support of DB2 FUNCTIONS and STORED PROCEDURES.

Delegates will also learn how to implement database security, including adding a security administrator, SECADM user, and implement database roles to simplify security management. Db2 encryption techniques are also discussed. This course also covers how the Web Console may be used to monitor and maintain a DB2 system.

Finally, this course teaches database administrators how to plan, implement and manage DB2’s High Availability Disaster Recovery (HADR) feature. The lectures cover the processing performed for a Db2 Primary and Standby Db2 database. The Db2 database configuration options that define and control the HADR function are covered. The option to define and operate multiple HADR standby databases will be explained. The course also covers the special considerations for allowing read only access by applications to an HADR Standby database. Students will learn the Db2 commands like TAKEOVER, START HADR and STOP HADR that are used to control HADR primary and standby database activity. The monitoring for HADR status of the primary and standby databases using the db2pd commands will be presented.

Prerequisites

Knowledge of basic DBA commands is essential (such as object creation, data movement and recovery, performance, monitoring and tuning techniques).

Audience

Database Administrators

Objectives

After completing this course, participants should be able to:

• Understand DB2 Security features including LBAC, Column Permissions, Row
• Permissions and ROLES
• set up database security
• use GRANT and REVOKE
• use High Availability Disaster Recovery (HADR)
• understand Schemas and Schema Paths
• implement and maintain Functions
• implement and maintain Stored Procedures
• have a basic understanding of Stored Procedures written in SQL-PL

Topics

• HIGH AVAILABILITY DISASTER RECOVERY (HADR)
• Logging Recap
• Archive Logging
• Single / Dual Logging
• The Backup / Recover Utilities - Recap
• High Availability Disaster Recovery (HADR) Overview
• HADR Limitations / Restrictions
• HADR Setup - Step 1 - Select the Primary Database
• HADR Setup - Step 2 -Primary Database Configuration
• HADR Setup - Step 3 - Standby Database Creation
• HADR Setup - Step 4 -Standby Database Configuration
• HADR Setup - Step 5 - Specify Synchronisation Mode
• HADR Setup - Step 6 - Start HADR
• HADR States
• HADR Takeover

• SECURITY
• Connecting To DB2 - Authentication
• Authentication Methods
• Authorities And Privileges
• Instance / System Authorities
• Database Authorities
• Database Authority Summary
• Schema Privileges
• Table Privileges
• View Privileges
• Index Privileges
• Package Privileges
• The Grant Statement
• The Control Privilege
• The Revoke Statement
• Catalog Information
• Granting Using IBM Data Studio
• Privileges Required For Programming
• Grant Examples
• Revoke Examples
• Label-Based Access Control
• LBAC - Step 1 - Define The Security Label Component
• LBAC - Step 2 - Define The Security Policy
• LBAC - Step 3 - Define The Security Labels
• LBAC - Step 4 - Define The Table To Use Security Labels
• LBAC - Step 5 - Grant Security Labels To Users
• LBAC - Step 6 - Use The Table
• Label-Based Access Control - Column Protection
• LBAC Catalog Tables
• The Transfer Ownership Statement
• Introduction To Trusted Context And Roles
• Trusted Context And Roles - 3 Tier Problems
• Security Enhancement - Trusted Contexts / Roles
• Trusted Contexts
• Creating Trusted Contexts
• Roles
• Trusted Context / Role Examples
• Using A Trusted Connection In A Jdbc Application
• Creating Restrictive Databases
• Row And Column Access Control
• Scalar Functions For Row / Column Permissions
• Row Access Control - Defining Row Permissions
• Activating Row Permissions
• Column Access Control - Defining Column Masks
• Activating Column Masks
• Encryption Techniques

• WEB MONITORING
• Monitoring using the IBM Data Studio Web Console
• Web Console Access
• Web Console - Health Summary
• Web Console - View Alerts
• Web Console -Configuring Alerts
• Web Console - View Application Connections
• Web Console - Monitor Tablespaces
• Web Console - Monitor Utilities
• Web Console - Job Manager

• WHAT ARE STORED PROCEDURES?
• Overview
• Reduction in Network Traffic
• Stored Procedure Advantages
• DB2 Address Spaces
• Defining an External Stored Procedure
• Executing a Stored Procedure - the Call Statement
• Error Handling within Stored Procedures
• Execution Flow

• SCHEMAS AND PATHS
• Schemas
• The Grant Schema Statement
• Schema Path - Bind Option
• Current Path - Special Register
• Overriding the Search Path
• Set Current Schema
• Set Current Package Path

• STORED PROCEDURE DEFINITION
• The Create Procedure Statement
• Stored Procedure Parameters
• Allowable SQL Statements
• Create Procedure Example
• The Alter Procedure Statement
• Deleting a Stored Procedure Definition
• Defining a Java Stored Procedure
• Java Stored Procedures - Jar Installation

• CODING PROCEDURES IN SQL - OVERVIEW
• The SQL-PL Language
• Begin and End Statements (Compound Statements)
• Declaring Host Variables
• Assigning Values to Variables - The SET Statement
• CASE Statement
• Comments
• FOR Statement
• GET DIAGNOSTICS Statement
• GOTO Statement
• IF Statement
• Comparison Operators
• LEAVE and ITERATE Statements
• LOOP statement
• REPEAT Statement
• RETURN Statement
• WHILE statement
• Handling Errors in an SQL Stored Procedure
• Testing for Errors - SQLCode and SQLState
• The Declare Handler Statement
• Condition Handler Execution Path
• Declaring Conditions for Handlers
• Dynamic SQL statements
• Returning Result Sets
• Processing Result Sets From Other Stored Procedures
• SIGNAL Statement and Message_Text Variable
• RESIGNAL Statement
• Coding Considerations

• STORED PROCEDURE FUNCTION AUTHORITIES
• Stored Procedure Authorities
• Stored Procedure Authorisation
• Authorisation Checking when Calling a Procedure