Oracle to DB2 UDB Conversion Guide2003

4.1 Preparation for porting

Before you start migrating the database using the MTK, you need to prepare the target system DB2 UDB environment. This section outlines the tasks to perform in order to have a functional and accessible DB2 UDB ESE database.

4.1.1 DB2 UDB V8 ESE installation

Regardless of the platform, you need to verify whether or not the system satisfies hardware and software requirements. In this redbook, we use both AIX and Microsoft Windows as the main platforms for installation. Installing DB2 UDB ESE on a platform such as SUN Solaris, HP-UX, or Linux requires modifying the operating system kernel parameters. System reboot is required afterwards.

DB2 UDB ESE installation on Linux

The DB2 UDB ESE installation on Linux consists of the following steps:

Verifying hardware requirements

The hardware specifications must satisfy one of the following:

• Intel® 32-bit or 64-bit

• DB2 UDB ESE requires 256 MB of RAM at a minimum. More memory is required to efficiently run other applications.

• The amount of disk space needed by DB2 UDB ESE may vary depending on the type of installation. Table 4-2 shows the disk space requirements.

Verify software requirements

The following software components must be installed on the server before attempting the DB2 installation.

• For Intel 32-bit architecture, you require a recent Linux operating system distribution with:

  • Kernel level 2.4.9 or later

  • glibc 2.2.4 or later

  • RPM 3 or later

• For Intel 64-bit architecture, the following software is required:

  • Red Hat Linux 7.2 or higher or SuSE Linux SLES-7 or higher

  • gcc 3.0.2

  • gcc3 libstdc++ runtime libraries

• The IBM Developer Kit for Java 1.3.1.

Verifying communication requirements

Ensure having the proper communication protocols installed on the server in order to achieve successful communication

• No additional software is needed if TCP/IP is used for connectivity.

Please refer to the Quick beginnings for DB2 Servers, GC09-4826 for a list of supported communication protocols.

Performing DB2 UDB ESE installation on Linux

The following steps explain the DB2 installation procedure:

1. Log on to the Linux system as root.

2. Modify kernel parameters for DB2.

3. Mount the CD-ROM using command mount /cdrom

4. Change to the CD-ROM directory cd /cdrom

5. Launch the DB2 Setup wizard with command ./db2setup which will start a graphical user interface (GUI) that will guide you through the installation process. Once the installation is successfully terminated, the DB2 server and components will be located in /opt/IBM/db2/V8.1. At this point the DBA may proceed with applying the recent FixPak and creating instances and databases. The db2setup interface also allows the user to generate a response file if a silent install is desired.

DB2 UDB ESE installation on AIX

The DB2 UDB ESE installation on AIX consists of the following steps:

Verifying hardware requirements

The hardware specifications must satisfy one of the following:

• IBM RISC/6000, eSeries or pSeries®

• DB2 UDB ESE requires 256 MB of RAM at a minimum. More memory is required to efficiently run other applications.

• The amount of disk space needed by DB2 UDB ESE may vary depending on the type of installation. Table 4-2shows the disk space requirements.

  Table 4-2: Disk space requirements on AIX

  Installation type	Disk space
  Compact	350MB–400 MB
  Typical	450MB–550 MB
  Custom	350MB–700 MB

Verify software requirements

The following software components must be installed on the server before attempting the DB2 installation:

• AIX V4.3.3 with maintenance level 9 or later (32-bits) or AIX V5.1.0 maintenance level 2 or later (32 and 64 bits)

• Java runtime environment (JRE) V1.3.1

Verifying communication requirements

Ensure having the proper communication protocols installed on the server in order to achieve successful communication:

• No additional software is needed if TCP/IP is used for connectivity.

Please refer to the Quick beginnings for DB2 Servers, GC09-4826 for a list of supported communication protocols.

Performing DB2 UDB ESE installation on AIX

The following steps explain the DB2 installation procedure:

1. Log on to the AIX system as root.

2. Mount the CD-ROM using command mount /cdrom

3. Change to the CD-ROM directory cd /cdrom

4. Launch the DB2 Setup wizard with command ./db2setup which will start a graphical user interface that will guide you through the installation process. Once the installation is successfully terminated, the DB2 server and components will be located in /opt/IBM/db2/V8.1. At this point the DBA may proceed with applying the recent FixPak and creating instances and databases. The db2setup interface also allows the user to generate a response file if a silent install is desired.

DB2 UDB ESE installation on Windows

The DB2 UDB ESE installation on AIX consists of the following steps:

Verifying hardware requirements

The hardware specifications must satisfy one of the following

• DB2 UDB ESE 32-bit requires a Pentium® level processor while 64-bit DB2 UDB ESE requires an Itaneum processor.

• DB2 UDB ESE requires 256 MB of RAM at a minimum. More memory would be required to efficiently run other applications.

• The amount of disk space needed by DB2 UDB ESE may vary depending on the type of installation. Table 4-3 shows disk space requirements on Windows.

  Table 4-3: Disk space requirements on Windows

  Installation type	Disk space
  Compact	300MB
  Typical	350MB
  Custom	300MB

Verifying software requirements

The following software components must be installed on the server before attempting the DB2 installation:

• Windows NT V4 with Service Pack 6a or higher, Windows 2000 with Service Pack 2, Windows XP or Windows .Net

• Java Runtime Environment (JRE) V1.3.1 is required. The installer will install the JRE if the DB2 graphical tools are being installed.

Verifying communication requirements

DB2 UDB ESE V8 requires TCP/IP for remote administration. No additional software is required if TCP/IP is used for connectivity. Please refer to the Quick beginnings guide for a list of supported communication protocols.

Performing DB2 UDB ESE installation on Windows

The following steps explain the DB2 installation procedure:

1. Log on to the Windows system as an administrator user.

2. Close all running applications.

3. Insert the CD-ROM into the drive and a graphical user interface DB2 Setup wizard should be launched automatically. If the auto-run feature is not enabled, you can launch the DB2 installer by executing the setup.exe program from the CD-ROM.

4. The DB2 Setup wizard will guide you through installation process. Once the installation is successfully terminated, the DB2 server and components should be located in the X:\IBM\SQLLIB directory where X is the disk drive identifier. At this point, the DBA may proceed with applying the recent FixPak and creating instances and databases.

4.1.2 Multiple partition installation

In the multiple partition environment, you need to install DB2 UDB in each physical database server. To ease the installation process, DB2 UDB provides the facility to generate an installation file call response file during installing the first machine. The file contains the installation specification and setup information, which can be used to install the reset of systems in the partitioned environment. DB2 UDB also provides you the sample response file. You can modify the sample response file to fit your system requirements, and use it to install DB2 UDB, create required a user ID, and create DB2 instance, etc. in all the systems in partitioned environment.

The basic DB2 UDB installation procedure in the multi-partitioned environment is the same as the single partition installation, except you need to specify the multiple partition database during installation. However, there are additional considerations that should be taken in environment preparation or user ID setup. For example, in an AIX or Linux environment, only one DB2 instance is created on the instance-owing machine. The home directory of the instance should be shared among all the participating servers. NFS-mounted instance home directory on the instance-owning machine is commonly used. In Windows, DB2 UDB utilizes Windows clustering technology and domain server setup is required.

The DB2 UDB Version 8 manual Quick Beginnings for DB2 Servers, GC09-4836 contains the procedures of setting up multi partitioned database. The following IBM Redbooks also provide detailed information on setting up the DB2 UDB multiple partitioned database:

• Scaling DB2 UDB on Windows Server 2003, SG24-7019

• Up and Running with DB2 for Linux, SG24-6899

4.1.3 Instance and database creation

You can have DB2 UDB create the DB2 instance automatically while installing DB2 UDB. You also can create the DB2 instance manually after the installation is completed. On Linux or AIX, the DB2 instance can be created by executing db2setup program used to install DB2 manually through the command line by issuing the db2icrt command, or by using the Control Center provided by DB2. This section discusses the first two methods on a Red Hat Linux system.

Using the db2setup/db2isetup utilities

Using the ./db2setup utility provides an easy way to create a DB2 instance. As root perform the following:

1. Launch the db2setup utility.

2. Check the Create a new DB2 instance or set up an existing DB2 instance option.

3. This screen will allow you to configure the DB2 administration server and user used as a repository for the GUI administration tools provided with DB2 such as the Control Center. The default value for this user is dasusr1 with a default home directory of /home/dasusr1

4. Click on the Instance setup option and choose the Create DB2 instance - 32 bit option.

5. For a single partition instance choose the first option.

6. On the Set User Information for the DB2 Instance Owner screen, you need to identify a system user who will be the instance owner. If you choose a new user, then specify the name of the user and his password. The default values are user db2inst1 and group db2grp1. You also have to specify the home directory for this user i.e. /home/db2inst1. By default any databases created under this instance will be created in this directory unless otherwise specified. Both the user and the home directory will be created by the installer.

7. The Set User Information for the Fenced User screen allows you to specify its username and password. The default user is db2fenc1 assigned to group db2fgrp1 in home directory /home/db2fenc1.

8. The tools Catalog screen is meant for preparing the DB2 Tools catalog on the server. Choose the Do not prepare the DB2 tools catalog on this computer if you do not need the tools catalog installed.

9. Finally, set the administrator contact information and click Finish.

As part of the instance creation, the installer will create all three users identified mainly as db2inst1, db2fenc1, and dasadm1. If you do not want to use the default user IDs, you can create the user IDs and groups ahead of time and use the IDs during creating the instance. The installer will also add the following entry to the /etc/services file in order to allow communication from DB2 clients:

db2c_db2inst1 50000

Where db2c_db2inst1 indicates the service name and 50000 indicates the port number. Subsequent instances may be created on the same server simply by invoking the /opt/IBM/db2/V8.1/instance/db2isetup utility and going through the above steps.

Using DB2 command

We can also create a DB2 instance manually by following the following steps.

1. Log on to the Linux system as root.

2. Create the necessary groups for DB2 Instance owner, administration server, and Fenced ID using the following commands:

   groupadd db2grp1 groupadd db2fenc1 groupadd dasadm1

3. Create the DB2 Instance user ID, administration server user ID, and Fenced ID and assign them to their respective groups using the following commands

   useradd -g db2grp1 -d /home/db2inst1 db2inst1 -p my_password useradd -g db2fenc1 -d /home/db2fenc1 db2fenc1 -p my_password useradd -g dasadm1 -d /home/dasusr1 dasusr1 -p my_password

4. Issue the command:

   db2icrt -u db2fenc1 db2inst1

5. Edit the /etc/services file and add the following entries:

   db2c_db2inst1 50000/tcp #DB2 port for remote clients db2idb2inst1 50001/tcp #interrupt ports for DB2 1.x clients

6. Log on as the instance owner and update the Database Manager Configuration (dbm cfg) file to reflect the service name in the /etc/services file update dbm cfg using SVCENAME db2c_db2inst1

7. Set up the default communication protocol:

   db2set -i db2inst1 -i DB2COMM=TCPIP

8. Set the instance to auto-start with the system if desired:

   db2set - i db2inst1 DB2AUTOSTART=TRUE

At this point the server is ready to create the database. To simply database connectivity test, you can create a sample database in four easy steps:

1. Log on the Linux system as the instance owner db2inst1.

2. Execute the command db2sampl located at sqllib/bin directory under the home directory of the DB2 instance. The db2sampl executable is a script that automatically creates a small database called SAMPLE.

3. Connect to the SAMPLE database by issuing the db2 connect command. In our example the command becomes:

   db2 connect to sample, this should display a the following connection confirmation on the screen

   The Database Connection Information returned:

   Database server = DB2/LINUX 8.1.3 SQL authorization ID = DB2INST1 Local database alias = SAMPLE

4. To see the results, issue a SQL query such as:

   db2 "select * from staff"

A DB2 database can either be created by the Control Center or by using the command line. In order to create a DB2 database manually you may follow the following steps:

1. Log on to the Linux system as the instance owner db2inst1.

2. Since DB2 allows for one instance to have multiple databases, it is always recommended to attach to the desired instance before the create database command is issued:

   db2 attach to instance_name

   where the instance name in our case is db2inst1.

3. Issue the create database command. The simplest create database command can take the form:

   db2 "create database my_database on /db_path"

   This command will create a database and the following three table spaces:

   • SYSCATSPACE to store system catalog tables

   • USERSPACE1 to store user defined objects

   • TEMPSPACE1 to store temporary objects

   These table spaces can be viewed by issuing the command: db2 list tablespaces

There are many options that can be included in the database command. Example 4-1 includes some of the available options. Please refer to the DB2 SQL Reference Volume 2, SC09-4845 for more details on the create database command.

Example 4-1: Create database

CREATE DATABASE my_db ON /db_path ALIAS warehouse_db USING CODESET code_set TERRITORY US COLLATE USING SYSTEM USER TABLESPACE MANAGED BY SYSTEM USING ('/user_tablespace_path') CATALOG TABLESPACE MANAGED BY SYSTEM USING ('/catalog_tablespace_path') TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('/temp_tablespace_path')

The default value for db_path is the home directory of the instance owner db2inst1 /home/db2inst1.

We have created an Oracle database ORA_EMP to demonstrate the conversion process. Before running the script created by MTK to deploy the database object, we need to create a DB2 database. Example 4-4 outlines the create database command we used to create the DB2_EMP database. Any database objects will be created in this directory. Therefore, we recommend that the db_path is explicitly set to a different value in order to prevent the /home/db2inst1from utilizing all of its allowed space which can cause an instance failure.

Example 4-2: Example of the create database command

CREATE DATABASE DB2_EMP on /db2/data CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE '/db2/data/system' 25600) user tablespace managed by database using (file '/db2/data/user' 25600) temporary tablespace managed by system using ('/db2/temp')

4.1.4 Table space planning

DB2 UDB allows for two types of table spaces, SMS and DMS. Both types of table spaces have containers or data files associated with them. In this section we discusses both types of table spaces. Table 4-4 outlines the differences between both types of table spaces. There are three categories of table space:

• Regular tablespace that can store regular, index, and long data. Nevertheless, this type of table spaces is not optimized for long type data.

• Large tablespace is designed to store long character or LOB type data.

• Temporary tablespace is designed to store temporary tables. A user cannot define a permanent table in a temporary table space.

  Note

  Only users with SYSADM or SYSCTRL authority can create table spaces.

SMS table space

This type of table space stores its containers in the form of operating system directories. Since this type of table spaces cannot be resized manually, enlarging the underlying file system would then increase the size of the table space. SMS table spaces acquire more space only when needed.

There are few advantages associated with creating SMS table spaces such as ease of creation and maintenance. The main disadvantage of an SMS table space is that it cannot separate out table indexes and large data types into their own table spaces.

DMS table space

The containers associated with a DMS table space are either operating system files or raw devices. A DMS table space can be resized manually with the ALTER TABLESPACE command using the RESIZE option. The database administrator decides the location of containers belonging to the table space and when to add containers. A DMS table space may be defined as regular, large or temporary.

Table 4-4 provides the summary of the differences between DMS and SMS.

When planning for table spaces, you should consider the table space size, type, and the placement on the physical drive. Migration time is a good time to re-design the table spaces of your database if you have been considering it. Oracle datafiles are similar to the DB2 UDB DMS table space container. You should also consider to take advantage of the DB2 UDB SMS table space type while you are planing the table spaces for your database.

Creating table spaces

The command used to create a table space can be in the following form. The outlines the use of the create tablespace command is outlined as following:

CREATE Tablespace_data_type TABLESPACE Tablespace_name PAGESIZE Integer K MANAGED BY Tablespace_type USING Container_path

Where:

• Tablespace_data_type indicates if a table space is regular, large or temporary.

• Tablespace_name indicates the name of the table space.

• Integer indicates the size of a memory page in Kbytes.

• Tablespace_type indicates either SYSTEM or DATABASE for SMS and DMS table spaces.

• Container_path indicates the path and name of a container.

There are three table spaces in the example Oracle database USER_EMP_TBS, USER_LOB_TBS and USER_LOB_TBS. We will keep the same table space names in our migration demo. Following are DB2 table space creation commands we used.

Example 4-3 shows the command to create table space USER_EMP_TBS.

Example 4-3: Create table space command

CREATE REGULAR TABLESPACE USER_EMP_TBS managed by database using ('/db2/user_data 25600').

Example 4-4, shows the syntax used to create the USER_LOB_TBS table space used to store Long objects in the DB2_EMP database.

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

CREATE LARGE TABLESPACE user_lob_tbs MANAGED BY DATABASE USING (FILE '/db2/lob/user_lobs.dbf' 25600)

Example 4-5, shows the syntax used to create the USER_IND_TBS used to stored the indexes in the DB2_EMP database.

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

CREATE TABLESPACE user_ind_tbs MANAGED BY DATABASE USING (FILE '/db2/indx/user_indx.dbf' 25600)

In order to obtain information about existing table spaces the DBA can issue the following command from the CLP:

db2 list tablespaces

If detailed information is required the following command may be issued:

db2 list tablespaces show detail

4.1.5 Security consideration

DB2 UDB uses existing operating system users as database users. On an environment like AIX, users are simply added to specific operating system groups, which provide them with the necessary rights to access the database.

DB2 provides two levels of security to users. The first is authentication which is to identify who the user is and determines if the user has any access to the database. The authentication process is done using a security facility outside of DB2 like a operating system's security facility. The second one is authorization which determines what DB2 system and object privileges the authenticated user has. Authorization is performed by DB2. There ar two types of permissions: authority and privileges. The authority level, which controls the user specific privileges on instance level and over the database in its entirety such as creating a database, creating a table space, performing backup and recovery tasks, etc. The privilege level, which allows a user to access, create or manipulate a specific database object in the database such as a table, view, or an index.

DB2 UDB authorities

An authority in DB2 UDB is defined as a Group in AIX and granting a specific user this authority simply means that this user is assigned to this group in the /etc/group file. The levels of authorities in DB2 UDB are classified as follows:

• SYSADM: Administrative authority, system administrators are given full privileges over the entire DB2 instance. SYSADM cannot be granted with a SQL statement.

• SYSCTRL: System control authority, system controllers are given full privileges for managing the system, but are not allowed access to data. SYSCTRL cannot be granted with a SQL statement.

• SYSMAINT: System maintenance authority. System maintainers are given a subset of privileges to manage the system. SYSMAINT cannot be granted with a SQL statement.

• DBADM: Administrative authority. Database administrators have control over an individual database. DBADM can be granted with a SQL statement.

• LOAD: The LOAD authority is granted in the database level. The users with LOAD authority can load data to a table, Quiesce tablespace for table, perform Runstats and List Tablespaces commands. To load data to a table, the INSERT privilege on the table is also required. Depending on the load activity, the UPDATE and DELETE privilege on the table may also needed.

DB2 UDB privileges

Database privileges are granted in the database through the SQL command GRANT. Privileges are stored in the system catalog tables within the database There are three types of privileges: ownership, individual, and implicit:

• Ownership or CONTROL privileges: In most cases the database user who creates a database object is automatically granted the CONTROL privilege. This privilege permits the user to grant other database users certain privileges on this object. The GRANT privilege can be granted through the GRANT statement.

• Individual privileges: A classic example of this type of privileges is the SELECT, INSERT, UPDATE and DELETE privileges.

• Implicit privilege: This is a sub privilege, which is automatically granted to a user when this user is granted a high level privilege.

Grant command syntax

MTK does not convert GRANTS from Oracle to DB2. You'll need to perform the security conversion manually. The syntax to use the Grant or Revoke command is as follows:

GRANT privilege ON Object_name TO USER username REVOKE privilege ON object_name FROM username

Example 4-6 includes examples of granting database privilege and table access authority to a user.

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

GRANT CREATETAB TO USER smith GRANT INSERT ON emp_table TO USER smith

4.1.6 Creating DB2 database users

The main difference between creating users in an Oracle environment and creating them in a DB2 UDB environment is that in Oracle users are created at the database level using SQL commands, where as in DB2 UDB, users are created at the operating system level using operating system commands and utilities.

For example, if we need to create a new database user called db2usr and grant him select, insert, and update privileges on table accounts on an AIX environment, we will need to perform the following steps:

1. Log on to the AIX server as root and create a group: mkgroup id=995 accttab

2. Create a user and assign him to group accttab: mkuser id=1001 pgrp=accttab groups=accttab home=/home/db2user db2user

3. Edit the .profile file for user db2usr and add the db2profile path to it, and execute the .profile in order to reflect the changes:

   . /db2/home/db2inst1/sqllib/db2profile . ./.profile

4. Log on to the AIX server as the instance owner or any authorized user and connect to the database:

   su - db2inst1 db2 connect to sample

5. Grant the desired privileges to the group: db2 "grant select, insert, update on accounts to group accttab"

6. Log on as user db2user, connect to database sample, and issue a SQL statement against table accounts:

   su - db2user db2 connect to sample db2 "select * from db2inst1.staff"