Cloudscape 5.1 to DB2 migration

Document options Document options requiring JavaScript are not displayed Rate this page Help us improve this content

Level: Introductory

Hana Curtis (hcurtis@ca.ibm.com), IBM Certified Solutions Expert, IBM

30 Mar 2004

This article describes the considerations for migrating a Cloudscape 5.1 application to DB2 Universal Database for Linux, UNIX and Windows. It covers migration of the database tables and data, application objects in the database, such as stored procedures and triggers, and the application.

Get the products and tools used in this article

If you've already purchased the right to get software online or on CD through the developerWorks Subscription, you have a single user license to use DB2 Universal Database, and other DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products -- including the Eclipse-based WebSphere Studio IDE -- to develop, test, evaluate, and demonstrate your applications. If you are not a subscriber, you can subscribe today.

Back to top

Introduction

Migrating from Cloudscape 5.1 to DB2 is generally easy depending on the Cloudscape features being used. JDBC applications are used to access Cloudscape databases and DB2 also supports JDBC. Migrating a Cloudscape application and database is easy if purely SQL-92 features are used. When Java extensions of Cloudscape’s SQL-J language are being used the migration is more complex.

The steps required to migrate from Cloudscape to DB2 generally involves:

1. Migration of the schema and data
2. Migration of application objects such as stored procedures, triggers, functions
3. Migration of the application

This article is aimed at developers with a fundamental understanding of database concepts and familiarity with Cloudscape but not with DB2 UDB. Some basic DB2 concepts are covered and resources are provided to get you started with DB2.

Cloudscape uses SQL with additions from the Java language; this is called SQL-J. DB2 provides support for embedded static SQL statements in Java using SQLJ. Note that these two terms do not refer to the same concept.

Back to top

Versions of Cloudscape & DB2

The information in this document is based on the capabilities of DB2 UDB Version 8.1 and IBM Cloudscape 5.1. In this document, the term DB2 refers to DB2 UDB Version 8.1 and Cloudscape refers to IBM Cloudscape 5.1.

Back to top

DB2 Concepts Overview

DB2 UDB is available in several editions. All editions of DB2 are full function database servers. The differences between editions lie in how many connections are allowed, whether remote connections are allowed, the size of the database, and of course, cost. For example, the DB2 UDB Workgroup Server Edition (DB2 WSE) is intended for departmental or small business environments. DB2 UDB Enterprise Server Edition is designed for the mid-size to large business and includes support for partitioned databases and connectivity to other enterprise data sources. When migrating a Cloudscape application, the DB2 UDB WSE may likely be sufficient. Moving to another edition in the future is easily done if necessary. The considerations discussed here apply to all editions of DB2 UDB.

DB2 databases are managed by a DB2 instance referred to as the database manager. Depending on the platform, a DB2 instance is created at installation time (ie. Windows) or can be created using the DB2ICRT utility. Multiple DB2 instances can exist on a machine. A DB2 instance provides the environment for managing one or more databases. Databases are created within a DB2 instance using the CREATE DATABASE command.

DB2 databases always run in multiple user mode similar to Cloudscape’s client/server environment. DB2 databases can run in single user mode however this is only available for utilities like backup.

Each database has its own catalog tables that define the metadata for the database. These are similar to Cloudscape’s system tables. Since differences exist, it is recommended that information is retrieved using java.sql.DatabaseMetaData methods.

The physical space allocated to a DB2 database is logically divided into table spaces. Tables are created in table spaces. By default, three table spaces are created when the database is created: SYSCATSPACE (for catalog tables), TEMPSPACE1 (for holding temporary data for operations such as sort) and USERSPACE1 (for user tables). Additional table spaces are created by the CREATE TABLESPACE statement.

DB2 user authentication is managed outside of DB2 by the operating system or another product. Once authenticated to DB2, a user has one of the DB2 authorities: SYSADM, SYSCTRL, SYSMAINT, DBADM or LOAD. The authorities limit the operations that a user can perform within DB2 instance or database. SYSADM is the highest authority and allows access to all data and operations within the DB2 instance. Within a database, specific privileges can be granted to users for specific objects in the database.

The DB2 Control Center is a graphical user interface used for database administration. It provides facilities for configuring DB2, managing database objects, monitoring, running utilities and many other tasks. CLP (Command Line Processor) is a tool for executing SQL statements and utilities. It can be used in interactive mode to execute single commands or batch mode to execute scripts of commands.

Back to top

Migrating Data

Data Types

Migrating the database schema is usually a simple process since the similar data types exist in most cases in both Cloudscape and DB2. The table below shows the compatibility between built in data types. Some data types have a one to one correspondence while others require more consideration before migrating. DB2 does not support Java classes as a data type and some considerations must be made when migrating this type of data.

BIGINT
-9 223 372 036 854 775 808 to
+9 223 372 036 854 775 807

BIGINT
-9 223 372 036 854 775 808 to 
+9 223 372 036 854 775 807

						
BIT,
BIT VARYING,
LONG VARBINARY,
LONG BIT VARYING,
LONG BINARY

CHAR FOR BIT DATA (max 254,
VARCHAR FOR BIT DATA (max 32672),
LONG VARCHAR FOR BIT DATA (max 32700),
BLOB (max 2G)

BOOLEAN

CHAR,
LONG VARCHAR,
VARCHAR

CHAR FOR BIT DATA (max 254,
VARCHAR FOR BIT DATA (max 32672),
LONG VARCHAR FOR BIT DATA (max 32700),
CLOB,
GRAPHIC (127 double byte chars),
VARGRAPHIC (16336 double byte chars),
LONG VARGRAPHIC (16350 double byte chars)
DBCLOB (1 073 741 823 double byte chars)

DOUBLE, DOUBLE PRECISION
4.9E-324 to 1.7976931348623157E308

DOUBLE or FLOAT
2.225E-307 to 1.79769E+308
or -1.79769E+308 to -2.225E-307

INTEGER
-2 147 483 648 to +2 147 483 647

INTEGER
-2 147 483 648 to +2 147 483 647

NCHAR,
LONG NVARCHAR
NVARCHAR

CHAR (max 254),
VARCHAR (max 32672),
LONG VARCHAR (max 32700),
CLOB (max 2G)

Notes:

1. In DB2, DATE, TIME and TIMESTAMP data is stored in an internal format in the database. The format of the character representation of each is determined by the territory code of the database (see next section on how to create a database using a specific territory code). For input, various formats of data and time may be allowed for each territory code. For output, the default representation is used but can be altered by specifying the DATETIME format when the application is bound or precompiled.
2. The way that DB2 stores and orders string data is different than Cloudscape and it is important to understand these differences to determine the best way to migrate such data.
• Cloudscape international strings are stored in UNICODE and their collating sequence is based on the locale of the database. DB2 also supports storing strings in UNICODE. The collating sequence for DB2 CHAR data types is based on the territory defined for the database. (See the next section on how to create a database that uses UNICODE and specifies a territory code). The collating sequence for GRAPHIC and FOR BIT data types is always binary.
• DB2’s LONG and LOB data types are stored on separate pages from the rest of the row and therefore require additional page reads when being processed. Therefore, these data types should only be used when storing of very long strings is required.

DB2 does not support storing object data types. If the Cloudscape application uses object data types, it will need to be modified to store objects in a different way. In this case, both the table definitions and application will require changes. Some options for storing objects in DB2 are:

1. Define tables with columns matching the attributes of the object being stored. Use INSERT to store individual attributes in each column.
2. DB2 supports the storing of XML data in columns or collections of tables using the DB2 XML Extender. If the application already handles XML data, it may be appropriate to store the XML data in the database.
3. Serialize the object and store in a CHARACTER FOR BIT DATA column. The application would have to retrieve and de-serialize the object before use. You may want to take advantage of DB2 user defined data types to give a name to this type of data.
4. Use a combination of 1 and 3 to store some information in separate columns and some serialized and stored in a single column.
5. Similar to 1, however, create user-defined structured types corresponding to the objects being stored and then define tables based on the structured types. These types of tables allow you to define a table hierarchy similarly to an object hierarchy.

Limitations on identifiers

Cloudscape identifiers are based on SQL-92 rules and therefore limited to 128 characters. The limits on DB2 identifiers vary. For example, the maximum length of table, view and stored procedure names is 128, column and schema names is 30 and index, trigger and user-define function names is 18. These limits must be considered when defining objects that must be compatible between Cloudscape and DB2. For a complete list of DB2 limits, refer to Appendix A in the DB2 SQL Reference Volume 1.

Schemas

In DB2, the default schema name is the authorization ID of the current user. The default schema for a session can be change by the SET SCHEMA SQL statement.

Tables

DB2 does not support the PROPERTIES clause in any statements.

In Cloudscape, you can use a schema name to qualify a constraint. In DB2, constraint names cannot be qualified by a schema name.

In both Cloudscape and DB2, a column is by default nullable. To disallow storing of null values, NOT NULL must be specified. An exception in Cloudscape is a primary key column. When defining a primary key, it implicitly does not allow null values. However, in DB2, the NOT NULL clause must be explicitly specified for each column included in the primary key when defining a primary key.

UNIQUE constraints allow nulls in Cloudscape. In DB2, all columns included in a UNIQUE constraint must be defined as NOT NULL. However, you can create a unique index on nullable columns. A unique index on a nullable column(s) allows a single null value to be present.

DB2 provides support for automatically generated identity columns using the GENERATE ALWAYS AS IDENTITY. The AUTOINCREMENT clause is not supported in DB2. Therefore, a Cloudscape table definition that includes an AUTOINCREMENT clause such as:

CREATE TABLE records 
(id INTEGER DEFAULT AUTOINCREMENT INITIAL 1 INCREMENT 1, 
description NVARCHAR(100) )

Will need to be converted to the following statement in DB2:

CREATE TABLE records 
(id INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), 
description VARCHAR(100) ) 

Check conditions in check constraints have some different restrictions between DB2 and Cloudscape. For example, Java methods are allowed in Cloudscape check conditions but not allowed in the DB2.

DB2 does not support dropping or renaming of a column in a table.

Table spaces

By default, tables are created in USERSPACE1. You may choose to place all your tables in USERSPACE1, however, if you have a large amount of data you may want to store it in separate table spaces. Table spaces give you more control of where your data is stored on the system and more granular maintenance. The IN tablespace-name clause of the CREATE TABLE statement allow you to specify where a table will be created.

Within a table space, data is stored on pages. When a table space is created, the page size (4, 8, 16 or 32K) is defined. A row of a table (except for long data types) must fit on a page therefore the length of a row is limited by the page size. If a table has long rows, an appropriate page size must be chosen.

Moving Data

DB2 provides the import and load facilities to insert data into the database. Import is typically used for small amounts of data because it uses INSERT statements which are logged. The load utility is recommended for large volumes of data. It is faster because it writes directly to data pages in the database and performs minimal logging.

If the table design has not changed when migrating from Cloudscape to DB2, then data can be exported from the Cloudscape database and then imported or loaded into the DB2 database. However, if there are differences in the table design, an application may need to be written to read the data in the Cloudscape database and convert it before inserting into the DB2 database in a new format.

Back to top

Migrating Application Objects

Indexes

Indexes can be created in DB2 as well using the CREATE INDEX statement. Cloudscape supports a BTREE modifier to the CREATE INDEX statement; DB2 does not support the use of this keyword. Some indexes are required in support of constraints however some indexes are created to improve query performance. It may not be appropriate to migrate indexes directly as DB2 may not use indexes in the same way as Cloudscape to optimize queries. DB2 provides the Index Advisor tool to help determine the best indexes for your application. It is recommended to use this tool after the application has been migrated to help you define the best indexes.

Stored Procedures and User Defined Functions

Cloudscape allows calling of Java methods against objects in the application and in the database. This is not supported in DB2. To provide similar functionality, these methods will need to be converted to stored procedures or user defined functions (UDFs) based on the functionality needed. DB2 stored procedures and UDFs are created using the CREATE PROCEDURE and CREATE FUNCTION statements. They can be written in Java, SQL procedural language (referred to as SQL procedures) and other languages. Stored procedures are invoked using the CALL statement. UDFs are executed in the context of any SQL statement

Built-in Functions

DB2 provides compatible built-in functions for most Cloudscape built-in functions. Different built-in functions are provided in DB2 for casting values to other data types and also for manipulating datetime values.

In Cloudscape, data types are converted to other data types using the CAST function. DB2 provides specific functions for casting operations. For example, to convert the integer value, 99 to a character type, you must use CHAR(99)in the SQL statement.

Extracting parts of datetime values is also performed using specific functions. For example, to extract the month value from a timestamp, you must use MONTH(CURRENT TIMESTAMP).

Triggers

DB2 supports triggers with similar syntax, features and behavior as Cloudscape however the trigger action is more limited. Cloudscape allows java methods to be called in the trigger action. DB2 allows only a subset of SQL statements and SQL procedural statements to be called in the trigger action. Calls to stored procedures are not allowed from trigger actions (in current release of DB2, but will be allowed in the future) however, calls to UDFs are allowed. Therefore, simple trigger actions can easily be migrated to DB2 however, when calls to java methods or complex logic are present in the trigger action, then this aspect of the application may need to be redesigned.

In Cloudscape, triggers can be disabled using the SET TRIGGERS statement. DB2 does not have the ability to temporarily disable triggers. To disable, the triggers must be dropped and then recreated when needed again.

External Virtual Tables

Cloudscape provides support for External Virtual Tables (EVTs) using the Virtual Table Interface (VTI). DB2 provides similar functionality using table functions. Table functions provide the ability to present data in a flat file or other source as a relational table. Table functions can also be designed to access data in another database using federated support or Web Services.

In Cloudscape, an application can insert and delete from EVTs using the INSERT and DELETE statements (without transaction support). In DB2, there is no support for inserting and deleting directly from a table function using INSERT and DELETE statements, however, the table function itself can be written to perform inserts and deletes.

Prepared Statements

DB2 does not have the concept of prepared statements as in Cloudscape. A prepared statement in Cloudscape allows a statement to be prepared and stored in the database to be invoked later without having to be prepared. However, there are several ways to avoid re-preparing frequently used SQL statements in a Java application accessing a DB2 database.

• A JDBC preparedStatement allows you to re-execute the same statement multiple times in the application.
• The DB2 statement cache keeps recently prepared dynamic statements so that they do not have to be re-prepared. The size of the cache can be optimized for the application. See the pckcachesz database configuration parameter in the DB2 product manual Administration Guide: Performance for more details.
• DB2 provides support for SQLJ in Java applications and stored procedures. SQLJ provides support for static SQL.
• DB2 SQL procedures provide support for static SQL statements.

Back to top

Migrating the Application

Database Creation

The Cloudscape database is created using a java program. The DB2 database is created using the CREATE DATABASE command. You may want to write a CLP script to create the database and also the table spaces and tables for the application.

If you want the data in the database to be stored in Unicode, you must also specify the code set and territory when creating the database. The CODESET clause defines how CHAR data will be stored in the database and the TERRITORY clause defines the collating sequence of the data and default datetime format. You can also override the default collating sequence for a territory by the COLLATE USING clause. The CODESET and TERRITORY settings do not affect how data is stored or collated for GRAPHIC and BIT data types; the collating sequence for GRAPHIC and BIT is always binary. After the database is created, you cannot change the code set or territory.

The following command creates a Unicode database named XYZCO using the territory code for Canada.

CREATE DATABASE XYZCO USING CODESET UTF-8 TERRITORY CA

Information about creating Unicode databases can be found in Appendix B of the DB2 UDB Administration Guide: Planning.

Cloudscape allows encryption of a database. DB2 does not support encryption of the database but does provide functions to encrypt column data. The following statements set an encryption password and then insert an encrypted value into the EMP table column SNN.

SET ENCRYPTION PASSWORD = ‘hohoho’;
INSERT INTO EMP(SSN) VALUES ENCRYPT(‘000-999-000’); 

Cloudscape allows creation of Read-only database. There is no concept of a read-only database in DB2. However, if a data will be accessed mostly read, then the application may want to use isolation level Uncommitted Read (UR) to reduce the acquiring of locks.

JDBC Support

DB2 provides JDBC support through the IBM JDBC Universal Driver which is based on the open distributed protocol, Distributed Relational Database Architecture (DRDA) and is also compatible with all DB2 server platforms (UNIX, Windows, Linux, z/OS) and Cloudscape. To use the JDBC Universal Driver, the db2jcc.jar must be included in the Java class path, as well as the appropriate license jar file. The db2jcc_license_c.jar file that comes with the Cloudscape NetworkServer does not apply to DB2.

DB2 also includes the IBM Legacy JDBC Driver which is based on DB2 CLI (Call Level Interface) API. However, this driver is being deprecated so it is recommended that the IBM JDBC Universal Driver be used.

Depending on how the application connects to the database, either the getConnection call or the datasource definition will need to be modified. For example, to connect to database SAMPLE, the application would require these statements:

Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
Connection conn = DriverManager.getConnection("jdbc:db2://hcurtis:50000/sample");

For a DB2 data source, the DB2 Universal Driver provides these implementation classes:

com.ibm.db2.jcc.DB2SimpleDataSource
com.ibm.db2.jcc.DB2DataSource
com.ibm.db2.jcc.DB2ConnectionPoolDataSource
com.ibm.db2.jcc.DB2XADataSource

For more information, please refer to the DB2 product manuals Application Development Guide: Programming Client Applications and Developing Enterprise Java Applications using DB2 Version 8.

Queries: Join Statements

Some Cloudscape join syntax is not supported by DB2. The following are examples of join operations and their compatible syntax in DB2.

CROSS JOIN

• Cloudscape: SELECT * FROM tab1 CROSS JOIN tab2
• DB2: SELECT * FROM tab1, tab2

INNER JOIN

• Cloudscape: SELECT * FROM tab1 INNER JOIN tab2 USING(id)
• DB2: SELECT * FROM tab1, tab2 WHERE tab1.id = tab2.id

LEFT OUTER JOIN

• Cloudscape: SELECT * FROM tab1 LEFT OUTER JOIN tab2 USING (id)
• DB2: SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON tab1.id = tab2.id

RIGHT OUTER JOIN

• Cloudscape: SELECT * FROM tab1 RIGHT OUTER JOIN tab2 USING (id)
• DB2: SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON tab1.id = tab2.id

Data Manipulation: INSERT, UPDATE and DELETE Statements

DB2 supports INSERT, UPDATE and DELETE statements with similar syntax and behavior as Cloudscape except for the PROPERTIES clause and support for EVTs.

DB2 also provides the MERGE statement which updates a target table based on data in another table. The MERGE statement will insert a row if it does not exist in the target table or will delete or update an existing row as specified by the statement.

Concurrency Considerations (Isolation levels)

DB2 supports various isolation levels that can be set through the setTransactionIsolation method. The following table shows the DB2 isolation levels and their equivalent JDBC isolation levels.

Savepoints

DB2 does not support the use of multiple (nested) savepoints. As in Cloudscape, savepoints cannot be used within triggers.

Back to top

Summary

A Cloudscape application can be migrated fairly easily to DB2 depending on the features that it uses. Most SQL-92 features are similar in DB2 and Cloudscape or require minimal changes to be compatible. Use of Java extensions in Cloudscape, make it difficult to be compatible with DB2 without major changes.

This paper provided an overview of the issues involved in migrating a Cloudscape application to DB2.

Resources

• "A Colorful Introduction to DB2 UDB Version 8 for UNIX, Linux and Windows" (developerWorks, January 2003 year) is a good overview for developers new to DB2.
  
  
• developerWorks offers several tutorials to get started with DB2:
  • DB2 UDB Administration Fastpath (CT28C)
  • DB2 UDB Programming Fastpath (CT10)
  • DB2 Version 8 Family Fundamentals Certification Preparation Tutorials
  
  
• DB2 Technical Support provides all DB2 manuals in PDF format.
  
  
• DB2 UDB Version 8 Information Center provides access to information about DB2 and related products.
  
  
• DB2 UDB Home page is the portal for all DB2 product information and news.
  
  
• DB2 UDB V8 Java application development contains the most current information about Java application development support for DB2.
  
  
• DB2 Database Porting Zone provides information you need to port an application and its data from other database management systems to DB2 UDB.
  
  
• "Migrating to DB2 Universal Database Express Edition V8.1: A roadmap for developers and administrators" (developerWorks, August 2003)
  
  
• Browse for books on these and other technical topics.
  

About the author

		Hana Curtis is a lead member of the DB2 Functional Verification Testing team at the IBM Toronto Software Laboratory. Previously, she was a member of the DB2 Integration team working with DB2 and WebSphere, a database consultant working with IBM Business Partners to enable their applications to DB2, and a member of the DB2 development team. Hana is one of the authors of the book: DB2 SQL Procedural Language for Linux, UNIX, and Windows (Prentice Hall, 2003). She holds the following certifications: IBM Certified Solutions Expert: DB2 UDB V8.1 Database Administration for UNIX, Windows, and OS/2, IBM Certified Solutions Expert: DB2 UDB V8.1 Family Application Development, IBM Certified Specialist: DB2 V8.1 User.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top