Automation of Oracle Database Creation from DB2 DDL/DML Statements
Original Publication Date: 2003-Oct-09
Included in the Prior Art Database: 2003-Oct-09
The beckend database for Content Management (CM) v8.1 is DB2 and one of feature in CM v8.2 is Oracle porting so that Oracle users can also use CM v8 product. However, in order to maintain a single codebase and scripts to create database and load initial data, an automation to transfer DB2 SQL statements like DDL/DML, which are used to create CM database and load CM initial data, and then to create Oracle database is required. With this refinement, only a single set of database statements needs be maintained so that there is no inconsistency concern. The automation is modular and easy to extend to cover new feathers. Moreover, it is easy to be used for installation utility or post-install users by "one-click and fire".
Automation of Oracle Database Creation from DB 2 DDL/DML Statements
The converting utility allows users to create Oracle database with DB2 schemas. It has five modules as described below. The module design is very generic so that it can be applied to any application/system.
(A) Network module: This is used to setup the network configuration for the new creating Oracle database.
(B) SQL statement module : This converts DB2 DDL/DML statements in Oracle ones.
(C) User input module: This takes user inputs and plug into Oracle DDL/CML statements.
(D) Preparation module : This prepares files to create Oracle database manually.
(E) Creation module: This creates Oracle database, initial administrator user, tables, views and indexes and also load initial data.
Figure 1 - The interaction between automation modules.
When a user installs CM v 8.2 with Oracle database, the CM installation utility will start from collecting user inputs like Oracle database name, the initial administrator name, network configuration, CM information, etc and send Preparation module (module D). The Preparation module generates Oracle system files required for Oracle DB creation and invokes Network module (A) and SQL statement module
The Network module (A) sets up the network configuration like Oracle Listener and Oracle TNS server. For each native Oracle database on a Oracle server, a Oracle Listener is required for receiving requests from Oracle clients. For an Oracle client, the TNS server is required as part of registration of a remote Oracle database. Therefore, the Network module configures TNS server for a Oracle client and both of Oracle Listener and TNS server for a Oracle server since a Oracle server can act as a Oracle client.
The SQL statement module (B) takes existing DB2 DDL/DML files as the input and generates Oracle DDL/DML files as the output. Within the SQL statement module, every SQL statement is treated as an object and every patten replacement is object-oriented so that it is easy to plug in a new replacement request.The principles of conversion are as the follows.
(1) Patten recognition and replacement . To find the DB2 specific keywords and replace them with Oracle specific keywords. For example, in the table definition, a binary attribute with small (compared with BLOB), fixed length N in DB2 is defined by "<AttributeName> CHAR(N) FOR BIT DATA". But in Or...