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. Very many stored procedures have a succession relationship (stored procedure calls stored procedures), assuming that the number of stored procedures is small and can be manually operated. Assuming a large amount, it's going to be crazy.

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

There is a way here, maybe it will 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;

Running the above export statement is the process of exporting a stored procedure that is below ILS to a file because there is an order by Create_time. So the order of export is also established according to the order of the original stored procedure.

Run out, should see two files in G:/procudure

1. Procudure.del

2. Procudure.del.001.lob

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvendqx2xtc3m=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

There will be procudure.del.001.lob this file, because my side has a stored procedure too large (using modified by Lobsinfile). So export to lob files, so not easy error.

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 users using xxxpassword that 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 are errors that are run once (although sorted according to creation time, but there is no guarantee that someone later changes the parent stored procedure, resulting in incorrect creation time).

The import process is shown in the figure:

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvendqx2xtc3m=/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

After opening, enter the following command

DB2 stored Procedure Migration method