|
Hola, este tema tiene un poco de tela,
si te tienes que meter con ello, mira el documento que te adjunto, t�mbien
tiene enlaces a libros donde podras encontrar la informaci�n necesaria de
lo que estas tratando de hacer.
El enlace al documento es este:
http://www-912.ibm.com/8625680A007CA5C6/1AC66549A21402188625680B0002037E/6B58F6B5B2D399B686256DB2004CC270 IBM Support Line Technical Document Document Number: 319030739 ____________________________________________________________ Functional Area: DB2 UDB for OS/400 SubFunctional Area: SQL/400 and Query Manager SubSubFunctional Area: General ____________________________________________________________ Product: OS/400 DATABASE (5722SS1DB) Release: V5R1M0; V5R2M0 Classification: Public Use Keywords: DB ____________________________________________________________ Document Title:Recommended Stored Procedure Actions Document Description: When working with External Stored Procedures, the *PGM object and the SQL Catalogs must be synchronized. These guidelines were designed and apply to External (non-SQL) Stored procedures that were made in an ILE (non-OPM) environment and contain at least one embedded SQL statement and are not in the QSYS or QSYS2 libraries. If your stored procedures do not meet these criteria, the steps required will be increased. As with any good program change management process, we recommend that you maintain a Change Management Document for each stored procedure that documents compiles, creates, drops, deletes, and moves. This document is required to help trace why certain items happen when actions occur. |------------------------------------+-------------------------------------------------------------------------| |Action Required: |Steps Required: | |------------------------------------+-------------------------------------------------------------------------| |Create New |- Compile | | |- CREATE PROCEDURE(s) | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Delete |- DROP SPECIFIC PROCEDURE(s) | | |- DLTPGM | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Recompile |- DROP SPECIFIC PROCEDURE(s) | | |- DLTPGM | | |- Compile | | |- CREATE PROCEDURE(s) | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Restore from SystemA to SystemB |- DROP SPECIFIC PROCEDURE(s) on SystemB | | |- DLTPGM on SystemB | | |- Restore from SystemA | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Restore from SystemA LibA to SystemB|- DROP SPECIFIC PROCEDURE(s) on SystemB | |LibB |- DLTPGM on SystemB | | |- Restore from SystemA to SystemB | | |- DROP SPECIFIC PROCEDURE(s) on SystemB of LibA | | |- CREATE PROCEDURE(s) on SystemB of LibB | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Move from LibA to LibB |- DROP SPECIFIC PROCEDURE(s) on LibA | | |- Move *PGM object | | |- CREATE PROCEDURE(s) for LibB | | |- Verify SQL Catalogs | |------------------------------------+-------------------------------------------------------------------------| |Verify SQL Catalogs (CREATES) |- DSPJRN JRN(QSYS2/QSQJRN) FILE((SYSROUTINE)) JRNCDE((R)) ENTTYP(PX) | | |- SELECT * FROM QSYS2/SYSPROCS WHERE DATE(ROUTINE_CREATED) = CURRENT_DATE| |------------------------------------+-------------------------------------------------------------------------| |Verify SQL Catalogs (DROPS) |- DSPJRN JRN(QSYS2/QSQJRN) FILE((SYSROUTINE)) JRNCDE((R)) ENTTYP(DL) | |------------------------------------+-------------------------------------------------------------------------| Using *LIBL in the declarations of Stored Procedures is not recommended. If *LIBL must be used, make sure the library that contains the *PGM object is in your library list or the program object will not be updated. This goes back to the fundamental idea that the *PGM object and catalog entries for Stored Procedures are two separate entities. For more information on Stored Procedures, refer to the following publications: DB2 for AS/400 SQL Reference at: http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst.pdf Stored Procedures and Triggers on IBM� DB2 Universal Database™ for iSeries, SG24-6503, at: http://www.redbooks.ibm.com/redbooks/pdfs/sg246503.pdf
|
