Generating DB2 maintenance commands

Throughout the change management process, specific database packages might become inoperative and statistics might become inaccurate. For example, DB2® marks packages as invalid or inoperative when you drop objects. You might need to issue rebind commands to re-create the packages based on the most current statistics, or you might need to restore the authorization for objects that are re-created. When you generate change commands from the Deployment Script Editor, you can specify that DB2 Change Management Expert automatically generate DB2 maintenance commands.
You have the option to generate the following commands:
Authorization statements
If your database changes require that objects be dropped and recreated, you might need to restore the authorizations that were granted on those objects. You might also want to specify authorizations for new objects that you add to the database manually by using the Change Command Editor.
Tip: Ensure that the script that contains these changes is displayed on the Change Commands page of the Deployment Script Editor.
Runstats commands
It is important to regenerate statistics when you make many changes to the database and also after your data has been loaded into tables. Runstats commands are generated for all the change command files in the deployment script, including the newly generated ones.
Start of changeReorg commandsEnd of change
Start of changeYou should reorganize all indexes when a table is altered. You should also reorganize all tables and indexes when a tablespace is altered. DB2 Change Management Expert will automatically generate runstats commands to refresh the statistics after a table is reorganized. You should also rebind all of your application packages to take advantage of the reorganized data.

For more information about the impact of reorg commands, see the DB2 Command Reference.

End of change
Rebind commands
You will need to rebind packages if your change commands contain the following DROP statements:
  • TABLE
  • TRIGGER
  • MQT
  • UDF
  • VIEW
  • ALIAS
  • INDEX
  • STRUCTURE TYPE

If you are making several changes, it is also recommended that you rebind your packages to improve performance. DB2 Change Management Expert generates rebind commands for all the packages in the database for the selected schemas by default. To limit the scope to only those packages with the DDL in the change command files, you can check the Limit the scope of the generated rebind commands to the selected change command files option.

Flush package cache commands
After the statistics are updated, you can flush the package cache so that the dynamic SQL statements will use the updated statistics, which will enhance the performance.

These commands should be run after the changes have been made to your database and after the data has been loaded into various tables.

To generate DB2 maintenance commands from the Deployment Script Editor, complete the following steps:
  1. On the Change Commands tab of the Deployment Script Editor, click Generate Change Commands. The Generate Change Commands wizard starts.
  2. Complete the page in the wizard to specify whether to preserve any authorities and privileges that exist on the objects and to generate runstats, rebind, and flush cache package commands. No options are checked by default.
    If you choose to preserve authorization, DB2 Change Management Expert generates the authorization statements in a separate script file from the other maintenance commands. By default, the file name is deployment_script_name_auth.chx. If you choose to generate rebind, runstats, or flush package cache commands, DB2 Change Management Expert generates these commands and puts them a single script file in the following order:
    1. Runstats commands
    2. Rebind commands
    3. Flush package cache commands

    You can use the default file name for the script file for the rebind, runstats, and flush package commands, or specify your own file name.

  3. When you return to the Change Commands page, verify that script files for the authorization statements and the other maintenance commands are displayed in the Define Change Commands field.
Related tasks
Generating change commands from the Deployment Script Editor


Feedback

Copyright IBM Corporation 2006, 2007. All Rights Reserved.