List of Examples

---

Chapter 4: Porting with MTK

Example 4-1: Create database

Example 4-2: Example of the create database command

Example 4-3: Create table space command

Example 4-4: Creating a table space of type Large

Example 4-5: Creating a table space to store indexes

Example 4-6: Granting Create table privilege to user smith

Example 4-7: Trigger InsertEmployee Oracle source code

Example 4-8: Trigger InsertEmployee DB2 conversion code

Example 4-9: Trigger ManagersChange Oracle source

Example 4-10: Trigger ManagersChange DB2 code

Example 4-11: Alternative DB2 conversion of trigger ManagersChange

Example 4-12: Trigger UpdateDepartments Oracle source code

Example 4-13: DB2 conversion of trigger UpdateDepartments

Example 4-14: InsertEmployee.db2

Example 4-15: EmployeeDynamicQuery Oracle source code

Example 4-16: Converted DB2 code of Procedure EmployeeDynamicQuery

Example 4-17: SelectRow Oracle source

Example 4-18: SelectRow DB2 conversion

Chapter 5: Conversion Reference

Example 5-1: Simple Oracle trigger

Example 5-2: Simple DB2 trigger

Example 5-3: Oracle trigger with DML command

Example 5-4: DB2 trigger with DML command

Example 5-5: PL/SQL procedure with usage of DBMS_SQL

Example 5-6: PL/SQL procedure with usage of native dynamic SQL

Example 5-7: SQL PL procedure with native dynamic SQL

Example 5-8: Dynamic UPDATE with EXECUTE IMMEDIATE

Example 5-9: Dynamic UPDATE with EXECUTE and PREPARE

Example 5-10: Java User Defined Function with dynamic SQL

Example 5-11: Oracle procedure with explicit cursor

Example 5-12: DB2 store procedure with cursor

Example 5-13: Function with an explicit cursor in Oracle

Example 5-14: Conversion using a FOR LOOP in DB2

Example 5-15: Oracle code using nested table

Example 5-16: DB2 UDB code using DGTT

Example 5-17: Efficient DB2 UDB code using DGTT

Example 5-18: DB2 UDB code using INSERT INTO

Example 5-19: PL/SQL procedure returns nested table

Example 5-20: SQL Procedure returns multiple rows using CURSOR WITH RETURN

Example 5-21: DB2 Store procedure calls AccountPackage.AccountList

Example 5-22: Condition handler - SQL EXCEPTION

Example 5-23: Condition handler - handle a long value

Example 5-24: Condition handler - SIGNAL SQLSTATE

Example 5-25: Oracle package with initialization

Example 5-26: Oracle package with initialization as procedure

Example 5-27: Definition of global variables in Oracle

Example 5-28: Temporary table with global variables

Example 5-29: Oracle hierarchical query

Example 5-30: Computing of direct child data

Example 5-31: Hierarchical query with entry point

Example 5-32: Compute hierarchy level

Example 5-33: Sample use of hierarchical query

Example 5-34: Oracle outer joins

Example 5-35: DB2 outer join conversion

Example 5-36: Oracle code using RETURNING INTO

Example 5-37: DB2 code using SELECT INTO

Example 5-38: DB2 CODE using SELECT

Example 5-39: DB2 dummy view for system information

Example 5-40: Oracle function with a REF cursor

Example 5-41: Conversion to a procedure with a Result Set in DB2

Example 5-42: Oracle procedure with Local function

Example 5-43: DB2 Conversion of Oracle Procedure with Local Function

Example 5-44: Stored Procedure in C

Example 5-45: UDF Java source

Chapter 6: Data Conversion

Example 6-1: The data_unload.sh script

Example 6-2: The desc.awk script

Example 6-3: The count.awk script

Example 6-4: Procedure to export_ table data

Chapter 7: Application Conversion

Example 7-1: Passing data to store procedure

Example 7-2: Oracle JDBC connection

Example 7-3: DB2 JDBC connection

Example 7-4: Java call of Oracle or DB2 UDB procedure with input parameter

Example 7-5: Java call of Oracle procedure with result set

Example 7-6: Java call of DB2 UDB procedure with result set

Example 7-7: Java call of Oracle procedure with input parameter and result set

Example 7-8: Java call of DB2 UDB procedure with input parameter and result set

Example 7-9: Java of Oracle function with input parameter and result set

Chapter 8: Script Conversion

Example 8-1: SQL*Loader control file with fixed-format fields

Example 8-2: DB2 UDB Load file for table ACCOUNTS

Example 8-3: SQL*Loader control file with variable-length fields

Example 8-4: DB2 load commend with variable-length fields

Example 8-5: SQL*Loader control file with conditions for table ACCOUNTS

Example 8-6: Export script in Oracle

Example 8-7: BACKUP database script in DB2

Chapter 9: Testing

Example 9-1: Sample IMPORT messages

Example 9-2: LOAD messages

Example 9-3: Turning integrity checking back on.

Example 9-4: Table for storing number of rows (Oracle)

Example 9-5: Table for storing number of rows (DB2)

Example 9-6: Sample table CK_ROW_COUNTS contents

Example 9-7: PL/SQL program that generates scripts for counting rows

Example 9-8: Explaining error codes

Example 9-9: Example of db2diag.log file

Example 9-10: Example snapshot

Example 9-11: Resetting snapshot monitor counters

Example 9-12: Displaying monitor switches

Example 9-13: Updating monitor switches at instance level

Example 9-14: Updating monitor switches at application level.

Example 9-15: Database manager snapshot

Example 9-16: Lock snapshot.

Example 9-17: Table snapshot

Example 9-18: Table space and buffer pool snapshots

Example 9-19: Dynamic SQL snapshot

Example 9-20: Sample snapshot table function

Example 9-21: Sample snapshot table function

Example 9-22: Sample snapshot table function

Example 9-23: Storing snapshot data in a table

Example 9-24: Creating sample event monitor

Example 9-25: Generating table syntax for specified event monitor

Example 9-26: Enabling event monitor

Example 9-27: Creating event monitor with file option

Example 9-28: Formatting event monitor output files

Example 9-29: Checking for current page allocation status

Example 9-30: Enabling multi page allocation

Example 9-31: Relocation of logical logs

Example 9-32: Updating IO related processes

Example 9-33: Increasing buffer pools

Example 9-34: Resizing the transactional log

Example 9-35: Invoking snapshot for locks on database sample

Example 9-36: Lock escalation message in db2diag.log file

Example 9-37: Current usage of log space by applications

Example 9-38: Checking log I/O activity

Example 9-39: Autoconfigure command

Example 9-40: Finding indexes for particular query

Appendix D: Converter for SQL*Loader

Example 9-41: Conversion of Oracle control file to DB2 load command

Example 9-42: Generation of additional DB2 update commands

---