This article mainly introduces how to complete the transplantation process of PHP application system based on DB2 from AIX platform to Linux platform. The article contains the detailed steps of transplanting the underlying DB2 database and the upper-layer PHP application system, as well as the problems and solutions that may be encountered during the transplantation process.
Task Overview
System migration work is mainly divided into the following aspects:
1. Cross-platform migration of DB2 database system
2. Installation and configuration of Apache server and PHP application system.
Below we will introduce the specific steps of migration and configuration in two aspects. .
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 database Instances: SRCDB1 and SRCDB2. The SRCDB1/SRCDB2 database contains hundreds of database tables, and has many indexes, foreign key constraints, triggers, stored procedures, and some tables with auto-increment fields (tables with GENERATED ALWAYS AS IDENTITY defined fields) . To make matters even more difficult, we don't have accurate creation scripts for these database objects.
Selection of migration plan
If the source system and destination system to be migrated belong to the same type of operating system, such as migration between Linux or migration between AIX systems, the situation is relatively simple. DB2 itself has provided relevant practical tools to achieve this. Database migration between platforms of the same type, such as BACKUP and RESTORE commands. Of course, depending on the situation, you need to have a clear understanding of the parameters provided by the utility tool. For example, if the source system and the target system use different table spaces, the problem of table space redirection will be involved. Since the focus of this article is on cross-platform transplantation, this solution obviously cannot meet the needs and will not be discussed here.
So, how to deal with cross-platform database migration issues? Is it possible to 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. Moreover, db2move also has certain limitations for tables that contain auto-increment field data. And db2move can only import data into existing database tables and cannot display the location of the specified table space. Since during the database system migration process, not only the data in the tables need to be migrated, but also database objects such as indexes, foreign key constraints, triggers, and stored procedures. Compared with the solution selected in this article, the latter has more advantages. You can use db2move only as an alternative to migrating table data.
For export and import, you can only export and import one table at a time, and you need to manually enter the export and import commands and the name of the data table to be imported and exported. When the number of database tables is not large, this This option may still be considered, but it is not the best option. In the case of a large number of tables in the database, this approach is basically unrealistic, and the import command does not guarantee that the data of the auto-incremented fields will be consistent with the original table data.
Based on DB2's processing mechanism for database objects, this article uses a method that combines db2look with DDL and DML scripts, and separately handles triggers, stored procedures, and foreign key constraints in the original database to provide a cross-platform DB2 A feasible solution for database system migration.
Let's take SRCDB1 as an example to introduce the overall database migration process in this case. There are four database modes: SRCDB1, ASN, DB2DBG and SQLDBA in the SRCDB1 database. Assume that the user name of the SRCDB1 database is user_srcdb1 and the password is pw_srcdb1.
Related operations on the source system (AIX)
1. Use the db2look command to extract the DDL script list that generates database objects
1. db2look command and parameters
# db2look -d SRCDB1 -e -o srcdb1.ddl -a -i user_srcdb1 -w pw_srcdb1
db2look: Generate DDL to re-create the 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 file required to replicate the database, this option will generate a DDL file containing Script for statements
-o : Redirect output to the given filename, if the -o option is not specified, the output defaults to stdout
-a : Generate statistics for all created programs, if this option is specified, it will be ignored -u option
-i: Specify the user ID used to log in to the server where the database is located
-w: Specify the password used to log in to the server where the database is located
2. Differentiate database object DDL scripts according to different types of objects.
Since each table data in the source database has been processed by database objects such as triggers and stored procedures, in order to ensure the consistency and integrity of the data in the database, these database objects should Create after importing data to prevent repeated execution of database objects such as triggers and stored procedures to generate erroneous data when importing table data. Use a text editor to edit srcdb1.ddl generated by db2look, divide the DDL statements that create tables and indexes, create foreign key constraints, and create triggers and stored procedures into four groups, and save them as the following four DDL script:
srcdb1_tables.ddl srcdb1_foriegnkeys.ddl
srcdb1_triggers.ddl srcdb1_procedures.ddl
srcdb1_tables.ddl: Contains ddl statements to create SEQUENCE, UDF, TABLE, VIEW and other database objects.
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(25 4),
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 …;
srcdb1_foriegnkeys.ddl: Contains the ddl statement to create foreign key constraints.
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"."SAMP LE_FK_2 " ADD ……;
……
ALTER TABLE " SRCDB1"."SAMPLE_FK_N" ADD ……;
srcdb1_triggers.ddl: Contains ddl statements to create triggers.
Listing 4. srcdb1_triggers.ddl statement
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 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
EXTERN AL 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" ……;
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 should be noted that the db2 v6 version of db2look has not yet implemented extraction such as UDF, TRIGGER , UserSpace, NodeGroup, BufferPool and other database objects ddl statements. Starting from db2 v7, db2look can extract the DDL of the above objects, but it still cannot extract the ddl statement that creates the stored procedure object. Starting from db2 v8.2, support for the db2look function has been improved, and the extraction function of stored procedure ddl statements has been implemented. Since the source database system involved in this article is of a lower version (DB2 v8.1), the above solution needs to be adopted to obtain the DDL information of all database objects:
1). From a DB2 v8.2 system to SRCDB1 (DB2 v8.1 version) perform the CATALOG operation:
db2 catalog db SRCDB1 as SRCDB1;
2). Perform db2look extraction process on SRCDB1 from DB2 v8.2 system:
db2look -d SRCDB1 -e -o srcdb1.ddl -a -i user_srcdb1 -w pw_srcdb1;
This way you can obtain the complete database Object DDL information.
3． Generate data export export script
Use a shell script to generate and export a DML script for all data and redirect it to the srcdb1_export.sql file. For users familiar with DB2, you should know that each table, view, and alias created in the database corresponds to a row of records in SYSCAT.TABLES. Therefore, all required database table information can be obtained through the corresponding database select statement. As needed, the following shell script will select the table names of all tabschema tables in SRCDB1 that are SRCDB1, ASN, SQLDBA, and DB2DBG based on the tabname field from the system table SYSCAT.TABLES, and generate corresponding export statements based on their names. Achieve the purpose of batch export. The rtrim function is used to remove the spaces on the right side of the tabname field data.
Listing 6. Generate export script
# db2 "select 'export to ' rtrim(tabname) '.ixf of ixf select * from '
rtrim(tabname) ';' from syscat.tables
where tabschema in('SRCDB1', 'ASN', 'SQLDBA', 'DB2DBG')" > srcdb1_export.sql;
Edit the generated srcdb1_export.sql, delete the statistical information displayed in the header and tail, and retain only the necessary export statements. By modifying the tabschema information contained in the above script, you can specify the range of tables that need to be exported, that is, all table names required during the migration process. The generated export export statement has the following command form:
db2 export to tablename.ixf of ixf select * from tablename;
4. Generate data import load script
Use shell script to generate load script for importing data into target system: srcdb1_load.sql
Listing 7. Generate load script
# db2 "select 'load from ' rtrim(tabname) '.ixf of ixf insert into '
rtrim( tabname) ';' from syscat.tables
where tabschema in ('SRCDB1', 'ASN', 'SQLDBA', 'DB2DBG')" > srcdb1_load.sql;
Edit the generated srcdb1_load.sql and delete the head and tail statistics. , only keep the necessary load statements. Similar to the export statement, the above shell script selects the names of all tables in SRCDB1 from the system table and generates corresponding import statements based on their names to achieve the purpose of batch import. The generated import import statement command form is as follows:
db2 load from tablename.ixf of ixf insert into tablename;