DB2 Stored Procedure Migration method

When you migrate a database, stored procedures are generally migrated in the past, but there are generally two problems:

1. Many stored procedures have a succession relationship (stored procedure calls stored procedures), if the number of stored procedures are small, but also manual operation, if the volume is large, it is crazy.

2. The stored procedure is too large (too many rows), causing the exception

There is a method here that may solve the problem.

I. Exporting a stored procedure

EXPORT to G:/procudure/procudure.del of Del MODIFIED by Lobsinfile
SELECT ' SET current SCHEMA ' | | RTrim (procschema) | | ' @ ' | | Chr (10) | | SET current PATH = Sysibm,sysfun,sysproc, ' | | RTrim (procschema) | | | Chr (10) | | text| | Chr (10) | | @ ' | | Chr (10)
From syscat.procedures WHERE procschema = ' ILS ' ORDER by Create_time;

Executing the above export statement is to export the schema as a stored procedure under ILS as a file, because there is an order by create_time, so the order of the export is established in the order of the original stored procedure.

You're done, you should see two files in G:/procudure.

1. Procudure.del

2. Procudure.del.001.lob

The Procudure.del.001.lob file appears because I have a stored procedure that is too large (using modified by lobsinfile), so it is not easy to go wrong by exporting to a lob file.

Two. Importing stored procedures

The next step, of course, is to import the stored procedures under other DB:

1. Go to g:/procudure under command line

2. Connect to the Db:connect to ilsdb user xxx password you want to import

3. DB2 [email protected]-VF Procudure.del.001.lob

OK, by the way, look at log, there is no error. There is an error, executed once (although sorted by creation time, but there is no guarantee that someone later modified the parent stored procedure, resulting in the wrong creation time).

The import process is shown in the figure:

Open and enter the following command

DB2 Stored Procedure Migration method