Four Methods for exporting all DB2 stored procedures

Is it feasible to export all the DB2 stored procedures? The answer is yes. The following describes four methods for exporting all DB2 stored procedures. We hope this will help you learn about DB2 stored procedures.

1)
Select from the DB2 Directory table SYSCAT. ROUTINES:
In the SYSCAT. ROUTINES system directory table, a field named TEXT is defined as CLOB and the length is 2M2097152 bytes. To get the definition of all stored procedures, you can use a SELECT statement similar to the following and output the definition to a file.
DB2 select char (ROUTINESCHEMA, 20), CHAR (ROUTINENAME, 20), text from syscat. ROUTINES> <output file name>

The disadvantage of this method is that when text> 32767, the exported stored procedure is incomplete.

2)
For DB2 after v8.2, the-e Option of the DB2LOOK command adds the extraction of the Creation statement of the stored procedure. You can execute the following command:
Db2look-d <Database Name>-e-o <output file name>
Find the "DDL statements for Stored Procedures" section in the output file to obtain statements for creating all stored procedures.

It is said that the Stored Procedure exported by db2look in db2 v8 is incomplete.

3)
Run the get routine command to complete the following tasks:

Because get routine can only perform a single stored procedure after the name of the stored procedure is known, two steps are required:
1. Get the names of all stored procedures:
DB2 select char (ROUTINESCHEMA, 20) as schema, CHAR (ROUTINENAME, 20) as name from syscat. routines where substr (VARCHAR (TEXT),) = 'create processure'> <output file name>
2. perform the following operations on each stored procedure based on the name of the stored procedure in the output file to output the complete creation statement to the specified file:
DB2 get routine into <output file name> from procedure <Stored PROCEDURE Mode Name>. <Stored PROCEDURE Name>

This is tedious.

4)

Use export:

Db2 "export to procudure. del OF del modified by lobsinfile select 'set CURRENT scheme' | rtrim (procschema) | '@' | chr (10) | 'set current path = SYSIBM, SYSFUN, SYSPROC, '| rtrim (procschema) |' @ '| chr (10) | text | chr (10) |' @ '| chr (10) FROM syscat. procedures order by create_time ;"

This is recommended.
 

How to Implement online DB2 Import

Common db2 stored procedure statements

Usage of the DB2 create server statement

Usage of DB2 Merge statements

Principle of DB2 cursor