Detailed steps for cross-platform migration of application systems based on DB2 and PHP (1) _ PHP Tutorial

Detailed steps for cross-platform migration of application systems based on DB2 and PHP (1 ). This article describes how to port the DB2-based PHP application system from the AIX platform to the Linux platform. This article includes the underlying DB2 database transplantation and the upper-layer PHP application system.

This article describes how to port the DB2-based PHP application system from the AIX platform to the Linux platform. This article contains the detailed procedures for migrating the underlying DB2 database, the upper-layer PHP application system, and possible problems and solutions during the migration process.

Task overview

System migration is mainly divided into the following aspects:

1. cross-platform migration of the DB2 database system

2. installation and configuration of Apache server and php application system

Next we will introduce the specific steps of migration and configuration in two aspects.

Cross-platform migration of the DB2 database system

Database Environment

SOURCE Environment: AIX + DB2 v8.1

Target Environment: Linux + DB2 v8.1

The source database contains two database instances: SRCDB1 and SRCDB2. The SRCDB1/SRCDB2 database contains hundreds of database tables, there are also many indexes, foreign key constraints, triggers, stored procedures, and some tables with self-incrementing fields (tables with generated always as identity defined fields ). Even more difficult, we do not have an accurate script for creating these database objects.

Migration Solution Selection

If the migrated source system and target system belong to the same type of operating system, such as migration between Linux or between AIX systems, the situation is relatively simple, DB2 itself has provided related utilities to Port databases between platforms of the same type, such as BACKUP and RESTORE commands. Of course, you also need to have a clear understanding of the parameters provided by the utility according to different situations. for example, if the source system and the target system use different tablespaces, it will involve the issue of table space redirection. Because the focus of this article is on cross-platform migration, this solution obviously cannot meet the requirements and will not be discussed here.

So, how to deal with cross-platform database migration? Can I use the utility db2move? Db2move can only migrate data in tables, but cannot migrate database objects such as indexes, foreign key constraints, triggers, and stored procedures. In addition, for tables that contain auto-increment field data, db2move also has certain restrictions. In addition, db2move can only import data to an existing database table and cannot display the location of the specified tablespace. In the database system migration process, you not only need to migrate the data in the table, but also database objects such as indexes, foreign key constraints, triggers, and stored procedures, or the latter is more advantageous. Db2move can be used only as an alternative for table data migration.

For export and import, only one table can be exported and imported at a time. you must manually enter the export and import commands and the name of the data table to be imported and exported, in the case of a small number of database tables, this solution may still be considered, but it is not the best solution. In the case of a large number of tables in the database, this approach is basically unrealistic, and the import command does not ensure that the data of the auto-increment field is consistent with the original table data.

Based on the DB2's processing mechanism for database objects, this article combines db2look with DDL and DML scripts, and processes triggers, stored procedures, and foreign key constraints in the original database separately, A feasible scheme for porting a cross-platform DB2 database system is provided.

Next we will take SRCDB1 as an example to introduce the overall database migration process in this case. The SRCDB1 database has four database modes: SRCDB1, ASN, DB2DBG, and SQLDBA. Assume that the username of the SRCDB1 database is user_srcdb1 and the password is pw_srcdb1.

Operations on the source system (AIX)

1. use the db2look command to extract DDL scripts for generating database objects

Listing 1. db2look commands and parameters

# Db2look-d SRCDB1-e-o srcdb1.ddl-a-I user_srcdb1-w pw_srcdb1

Db2look: generate DDL to recreate objects defined in the database

Syntax: db2look-d DBname [-e] [-u Creator] [-z Schema]

[-T Tname1 Tname2. .. TnameN] [-tw Tname] [-h] [-o Fname] [-a]

[-M] [-c] [-r] [-l] [-x] [-xd] [-f] [-fd] [-td x]

[-Noview] [-I userID] [-w password]

[-V Vname1 Vname2... VnameN] [-wrapper WrapperName]

[-Server ServerName] [-nofed]

-D: database name, required parameter

-E: extract the DDL files required for database replication. this option generates scripts containing DDL statements.

-O: redirects the output to the specified file name. If the-o option is not specified, the output is switched to stdout by default.

-A: generates statistics for all created programs. if this option is specified, the-u option is ignored.

-I: specifies the user ID used to log on to the server where the database is located.

-W: specifies the password used to log on to the server where the database is located.

2. differentiation of database object DDL scripts based on different types of objects

Since the table data in the source database is already processed by database objects such as triggers and stored procedures, to ensure data consistency and integrity in the database, these database objects should be created after data is imported to prevent repeated execution of triggers, stored procedures, and other database objects during table data import to generate error data. Use the text editor to edit the srcdb1.ddl generated by db2look, and divide the DDL statements for creating tables and indexes, the DDL statements for creating foreign key constraints, and the statements for creating triggers and creating stored procedures into four groups, save them as the following four DDL scripts:

Srcdb1_tables.ddl srcdb1_foriegnkeys.ddl

Srcdb1_triggers.ddl srcdb1_procedures.ddl

Srcdb1_tables.ddl: contains ddl statements for creating database objects such as SEQUENCE, UDF, TABLE, and VIEW.

Listing 2. srcdb1_tables.ddl statement

Create sequence "SRCDB1". "SAMPLE_SEQ_1" AS INTEGER

MINVALUE 1 MAXVALUE 9999999999

Start with 1 increment by 1;

Create function "SRCDB1". "SAMPLE _ FUNC_1 "(

VARCHAR (254 ),

VARCHAR (254 ),

VARCHAR (254)

) Returns varchar (254)

Specific sample _ FUNC_1 ......;

Create table "SRCDB1". "SAMPLE _ TAB_1 "(

"TAB_COL1" CHAR (20) not null,

"TAB_COL2" VARCHAR (70) not null );

Create table "SRCDB1". "SAMPLE _ TAB_2 "(......);

......

Create table "SRCDB1". "SAMPLE _ TAB_N "(......);

Create view SRCDB1.SAMPLE _ VIEW_1 (VIEW_COL1, VIEW_COL2) as select distinct

COL1, COL2 FROM SAMPLE_TAB WHERE ......;

Create view SRCDB1.SAMPLE _ VIEW_2 ......;

......

Create view SRCDB1.SAMPLE _ VIEW_N ......;

Srcdbconstraint foriegnkeys.ddl: ddl statement that contains the foreign key constraint.

Listing 3. srcdb1_foriegnkeys.ddl statement

Alter table "SRCDB1". "SAMPLE_FK_1"

Add constraint "SQL030903143850120" FOREIGN KEY

("FK_COL1 ")

REFERENCES "SRCDB1". "SAMPLE_TABLE"

("COL1 ");

Alter table "SRCDB1". "SAMPLE_FK_2" ADD ......;

......

Alter table "SRCDB1". "SAMPLE_FK_N" ADD ......;

Srcdb1_triggers.ddl: contains the ddl statement for creating a trigger.

Listing 4. srcdb1_triggers.ddl statements

Create trigger SRCDB1.SAMPLE _ TRIG_1 after update of col1 ON SRCDB1.SAMPLE _ TAB

Referencing new as n for each row mode DB2SQL WHEN (n. col1> 3)

BEGIN ATOMIC

Update SAMPLE_TAB

Set (col2) = anotherValue where col1 = n. col1 ;--

END;

Create trigger SRCDB1. SAMPLE_TRIG_2 ......;

......

Create trigger SRCDB1. SAMPLE_TRIG_N ......;

Srcdb1_procedures.ddl: contains the ddl statements used to create SQL Stored procedures and java stored procedures.

Listing 5. srcdb1_procedures.ddl statement

Create procedure "SRCDB1". "JAVA_PROCEDURE_1 "(

Out sqlstate character (5 ),

OUT ROWS_SUBMITED INTEGER,

IN BATCH_ID INTEGER,

In level varchar (4000)

)

Dynamic result sets 0

SPECIFIC SUBMIT_BATCH

External name Submit_batch! Submit_batch

LANGUAGE JAVA

PARAMETER STYLE JAVA

NOT DETERMINISTIC

FENCED THREADSAFE

MODIFIES SQL DATA

No dbinfo;

Create procedure "SRCDB1". "JAVA_PROCEDURE_2 "......;

......

Create procedure "SRCDB1". "JAVA_PROCEDURE_N "......;

Set current schema = "SRCDB1 ";

Set current path = "SYSIBM", "SYSFUN", "SRCDB1 ";

Create procedure SRCDB1. SQL _ PROCEDURE_1 (

IN hostname varchar (4000 ),

IN username varchar (4000 ),

OUT SQLCODE_OUT int)

SPECIFIC SRCDB1. SQL _ PROCEDURE_1

LANGUAGE SQL

-------------------------------------------------

-- SQL Stored Procedure

-------------------------------------------------

P1: BEGIN

......

END P1;

Create procedure SRCDB1. SQL _ PROCEDURE_2 ......;

......

Create procedure SRCDB1. SQL _ PROCEDURE_N ......;

Note that db2look of db2 v6 has not implemented ddl statements for extracting database objects such as UDF, TRIGGER, UserSpace, NodeGroup, and BufferPool. Since db2 v7, db2look can extract the DDL statements of the above objects, but it still cannot extract the ddl statements of the objects created in the stored procedure. Since db2 v8.2, we have improved the support for the db2look function and implemented the extraction of ddl statements in the stored procedure. Because the source database system version involved in this article is relatively low (DB2 v8.1), you need to use the above scheme to obtain the DDL information of all database objects:

1) perform the CATALOG operation on SRCDB1 (DB2 v8.1) from a DB2 v8.2 system:

Db2 catalog db SRCDB1 as SRCDB1;

2). perform the db2look extraction process for SRCDB1 from the DB2 v8.2 system:

Db2look-d SRCDB1-e-o srcdb1.ddl-a-I user_srcdb1-w pw_srcdb1;

In this way, you can obtain the complete database object DDL information.

3. Generate data Export expo

The migration process from the AIX platform to the Linux platform of the PHP application system of DB2 connector. This article contains the underlying DB2 database transplantation and the upper-layer PHP application system...