Cross-platform migration of application systems based on DB2 and PHP detailed steps (i) _php tutorial

This article mainly describes how to complete the porting process of PHP application system based on DB2 from AIX platform to Linux platform. This paper includes the basic DB2 database porting, the detailed steps of porting the PHP application system in the upper layer, and the problems and solutions that may be encountered during the migration.

Task overview

The system migration is mainly divided into the following aspects:

Cross-platform migration of 1.DB2 database system

Installation and configuration of 2.Apache Server and PHP application system

Here we will be divided into 2 aspects of migration and configuration of the specific steps.

Cross-platform migration of DB2 database system

Database environment

SOURCE Environment: AIX+DB2 v8.1

Target Environment: LINUX+DB2 v8.1

The source database contains 2 databases INSTANCE:SRCDB1 and SRCDB2. In the SRCDB1/SRCDB2 database, there are hundreds of database tables, and there are many indexes, foreign KEY constraints, triggers, stored procedures, and some tables with self-increment fields (tables containing GENERATED always as IDENTITY fields). More difficult, we don't have an accurate script for creating these database objects.

Selection of migration scenarios

If the migrated source and destination systems belong to the same type of operating system, such as migration between Linux, or migration between AIX systems, the situation is relatively simple, and DB2 itself has provided relevant utilities to enable database porting between the same type of platform, such as: BACKUP and RESTORE Command. Of course, depending on the situation, you also need to have a clearer understanding of the parameters provided by the utility, such as the use of different table spaces between the source system and the target system, which involves the problem of table space redirection. Since the focus of this article is on cross-platform porting, this scenario is clearly not sufficient to meet the requirements, and is no longer discussed here.

So, how do you handle cross-platform database migration issues? Is it possible to use the utility Db2move? Db2move can migrate only the data in a table, but not the database objects such as indexes, foreign KEY constraints, triggers, and stored procedures, and there are limits to db2move for tables that contain self-increment field data. and Db2move can only import data into tables that already exist in the database and cannot display the location of the specified table space. Because in the process of system migration of the database, not only the data in the table, but also the indexes, foreign KEY constraints, triggers and stored procedures and other database objects, compared with the options in this article, the latter is more advantageous. You can use Db2move as a fallback for migrating table data only.

For export and import, only one table can be exported for import operations at a time, and you need to manually enter export and import commands and the data table names that need to be imported and exported, which might be considered when the number of database tables is not large. But it is not and is the best solution. In the case of a large number of tables in the database, this approach is largely unrealistic, and the import command does not guarantee that the data from the Increment field will be consistent with the original table data.

In this paper, based on the processing mechanism of DB2 database object, using the method of combining Db2look with DDL and DML script, and dealing with the triggers, stored procedures and foreign key constraints in the original database, a feasible scheme of porting DB2 database system is given.

Let's take SRCDB1 as an example to introduce the overall database migration process in this case. The SRCDB1 database has four database schemas, SRCDB1, ASN, db2dbg, and SQLDBA. Assume that the user name of the SRCDB1 database is user_srcdb1, password: pw_srcdb1.

Related operations on the source system (AIX)

1. Use the Db2look command to extract DDL scripts that generate database objects

Listing 1. Db2look Commands and Parameters

# db2look-d SRCDB1-E-o srcdb1.ddl-a-I user_srcdb1-w pw_srcdb1

Db2look: Generating 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: Extracts the DDL files required to replicate the database, which generates a script that contains the DDL statements

-O: Redirects the output to the given file name, and if the-o option is not specified, the output goes to stdout by default

-A: Generates statistics for all the creation programs, and if this option is specified, the-u option is ignored

-I: Specifies the user ID used when logging on to the server where the database resides

-W: Specifies the password to use when logging on to the server where the database resides

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

Because each table data in the source database is already data processed by database objects such as triggers, stored procedures, and so on, to ensure consistency and integrity of the data in the database, these database objects should be created after the data is imported to prevent database objects such as repeating triggers and stored procedures from generating error data when importing table data. Use a text editor to edit the SRCDB1.DDL generated by Db2look, create a DDL statement for the table and index, create a DDL statement for the FOREIGN KEY constraint, and create a trigger and create a stored procedure in four groups, each saved as the following four DDL scripts:

SRCDB1_TABLES.DDL SRCDB1_FORIEGNKEYS.DDL

SRCDB1_TRIGGERS.DDL SRCDB1_PROCEDURES.DDL

SRCDB1_TABLES.DDL: Contains DDL statements that create database objects such as Sequence,udf,table,view.

Listing 2. SRCDB1_TABLES.DDL statements

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 () is not NULL,

"Tab_col2" VARCHAR (+) 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 ...;

SRCDB1_FORIEGNKEYS.DDL: Contains the DDL statement that created the FOREIGN KEY constraint.

Listing 3. SRCDB1_FORIEGNKEYS.DDL statements

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 that created the 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 DDL statements that create SQL stored procedures and Java stored procedures.

Listing 5. SRCDB1_PROCEDURES.DDL statements

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 ...;

It is important to note that the DB2 V6 version of Db2look has not implemented DDL statements that extract database objects such as Udf,trigger,userspace,nodegroup,bufferpool. Starting with DB2 V7, Db2look can extract the DDL for the above object, but still cannot extract the DDL statement that created the stored procedure object. Starting from DB2 v8.2, the support of Db2look function is perfected, and the extracting function of DDL Statement of stored procedure is realized. Because of the low version of the source database system involved in this article (DB2 v8.1), it is necessary to take the above scenario to obtain DDL information for all database objects:

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

DB2 Catalog DB SRCDB1 as SRCDB1;

2). The Db2look extraction process from the DB2 v8.2 system to the SRCDB1:

db2look-d srcdb1-e-o srcdb1.ddl-a-i user_srcdb1-w pw_srcdb1;

This allows the full database object DDL information to be obtained.

3. Generate Data Export Expo

http://www.bkjia.com/PHPjc/508263.html www.bkjia.com true http://www.bkjia.com/PHPjc/508263.html techarticle This article mainly describes how to complete the porting process of PHP application system based on DB2 from AIX platform to Linux platform. The paper includes the bottom DB2 database porting, the upper PHP application System ...

