How does DB2 export DDL statements for stored procedures?

The daily DBA's job is to get the SP code for analysis or transplantation to other environments. How to quickly obtain the sp ddl, DB2 provides many methods to obtain the sp ddl language for reference:

1 select routinename, text from sysibm. sysroutines;

The above is obtained through the data dictionary table query, which is equivalent to the System View query acquisition method.
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 GET ROUTINE
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' & gt; <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>

Of course, the advantage of the above method is that you can specify a specific SP name to export the DDL structure of a separate SP.

3. The-e Option of the DB2LOOK command adds the extraction of the Creation statement of the stored procedure.
Db2look-d <Database Name>-cor-e-o <output file name>

4 Use export:
Db2 "export to procudure. del OF del lobs to sp. 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 ;"

Note that the lobs to option must be added; otherwise, only part of the large stored procedure definition can be exported.

The third and fourth methods above cannot specify the SP name, which is basically all the structure of the SP in the full database. Therefore, you can select a suitable and quick method based on your work needs. In short, DB2 provides enough methods to facilitate DBA's daily work requirements.

DB2 database performance adjustment and optimization (1st and 2) PDF

DB2 database performance optimization

DB2 9.7 for Linux 5.4 Installation Steps

This article permanently updates the link address: