Generating a data definition statement for a stored procedure

You can use this notebook to:

• Extract the DDL for an object before changes are made. You then have a copy available for fallback purposes.

• Extract a procedure environment to move your DB2 objects to another DB2 subsystem. Generate the DDL to create a duplicate environment that can be used in another subsystem.

Authorities and privileges

To generate a DDL statement:

1. Open the Generate DDL window.
   
   
2. From the Objects page select which objects and dependent objects that you want generated. The available choices include the Procedure and the privileges associated with the stored procedure. If you clear the check boxes of certain objects, other related objects become unavailable (including some on the Options page). You must choose at least one object.
   
   
3. From the Options page, select one or more options to change the statement from the default state.
   
   
   1. In the Overrides group, select one or more of the check boxes to alter the schema name or owner. You can:
      
      
      • A schema is a logical grouping for stored procedures. When a stored procedure is created, it is assigned to one schema which is determined by the name of the object. Two different schemas can each contain a function with the same name that have the same data types for all of their corresponding data types. However, a schema must not contain two functions with the same name that have the same data types for all of their corresponding data types. Therefore, to create a duplicate procedure you need to change the schema name.
        
        
      • The owner field is an 8 character authorization ID of the owner of the procedure. For example, if you type NEWOWN in the field, the DDL that is generated will include a "SET CURRENT SQLID='NEWOWN'" statement before the CREATE PROCEDURE statement.
   
   
   
   2. In the Options group, specify new values for the generated DDL.
      
      
      • Specify a release of DB2 for OS/390 for which you want the generated DDL to be used.
        
        
      • Specify how often you want to commit the statements.
        
        
      • Specify the default handling to be used. You can specify to use the defaults as defined in the original procedure or remove any defaults in the new procedure definition wherever possible.
        
   
   
4. From the Output page designate the file (mainframe or workstation) that will contain your new DDL statement. You can define a separate data set or file for the report and the generated statements. You must designate at least one output file name or data set name to generate the DDL.
   
   
   • In the Output report to data set field and Output DDL to data set field, type an identifier of 1 to 44 characters that identifies a cataloged name for a data set defined in the OS/390 system and used by the DB2 for OS/390 subsystem.
     
     
   • In the Output report to file field and Output DDL to file field, type a file name that will be saved on your workstation. You can also click the push button to open a Save As window to select a file name.
     
   
   
5. Click OK to generate the selected object.
   

Related information

Data sets

Data set allocation by the generate DDL function