Administration Guide

Common Tool Features

The following features are available in several tools:

• Show SQL and Show Command
• Show Related
• Help
• Generate DDL
• Filter

Show SQL and Show Command

If a tool generates SQL statements, then the Show SQL pushbutton will be available on the tool interface. Similarly, a tool that generates DB2 commands will have a Show Command pushbutton available. Clicking one of these pushbuttons allows you to:

• See the SQL statements or DB2 commands that the tool generates based on the choices you made in the graphical interface. This information helps you to understand how the interface is working.
• Save the statements or commands as a script for future reuse. This capability saves you from having to retype the SQL statements or DB2 commands if you want to run the same statements or commands again. Once the SQL statements or DB2 commands have been saved in a script, you can schedule the script, or edit the script to make changes, or create similar scripts without having to retype the statements or commands.

To show the SQL statements or DB2 commands:

1. From the Control Center, go to a window or notebook for working with an object.
2. Click on the Show SQL or Show Command push button. The appropriate window opens.

Saving SQL statements and DB2 commands is particularly helpful if the SQL statements or DB2 commands are complex.

When you use the Show Command or Show SQL feature, you can either create new scripts which you can later edit, or you can close the dialog box to return to the original dialog to make changes. If you click the Create Script pushbutton, the New Command Script window appears. There you can edit the SQL statements or the DB2 commands before saving the script.

Show Related

Show Related shows the immediate relationship between tables, indexes, views, aliases, triggers, table spaces, User Defined Functions, and User Defined Types. For example, if you select a table and you choose to show related views, you only see any views that are based directly on the table. You would not see any views that are based on the related views because those views were not created directly from the table.

Showing related objects helps you to:

• Understand the structure of the database.
• Determine what indexes already exist for a table.
• Determine what objects are stored in a table space.
• Know what other objects are related to an object and are therefore affected by any actions you may take. For example, if you want to drop a table with dependent views, Show related shows you which views will become inoperative.

To use the Show Related feature:

• From the Control Center, select an object from the Contents pane and click the right mouse button.
• Select Show Related
• Click on the tab to open the page for the related objects you want. Different related objects are listed depending on the tab you select. Only objects that are directly related to the object that you have selected are shown.

  You can click the right mouse button on a related object on the selected Page and select Show Related from the pop-up menu. The selected Page changes to show the objects related to your latest selection. You can also click on the down arrow next to the selected object to display a list of objects you previously selected to show relationships.

• Click Close to close the Show Related notebook and return to the Control Center.

Generate DDL

The Generate DDL function allows you to re-create and save in a script file the DDL and SQL statements and statistics of:

• Database objects
• Authorization statements
• Table spaces, nodegroups and buffer pools
• Database statistics

This allows you to:

• Save the DDL to create identically defined tables, databases, and indexes in another database, for example, for a database warehouse application
• Use the DDL to copy a database from the test environment to a production environment or from one system to another
• Edit the DDL to create similar objects

Clicking the Generate DDL pushbutton, brings up the Show Command window with statements generated by a utility known as the db2look utility. From the Show Command window, you can click the Save Script pushbutton to save the statements. The statements are put into a script. If you click the Generate button, the Run Script window opens.

Note:

Generating DDL statements is different when working with the Control Center for System 390. See the help information for specifics on those differences.

You can select whether you want to generate DDL statements for selected schemas or all schemas within the database. You can then edit the script if you want to make changes before you use the script in a production environment. To create identical databases using the generated DDL statements, you would simply use the script which you generated and run it in the new environment.

To generate DDL statements:

1. Highlight the object for which you want to generate DDL statements, and click the right mouse button.
2. Select Generate DDL. The Run script window appears.
3. Type a user ID and password, and click OK. A job is created with the contents of the db2look command. A DB2 message window appears with the job ID of the new job.
4. Click on OK to close the message window.
5. Use the Job History page of the Journal notebook to view the results of the job and to view the contents of a saved script associated with the job.
6. Select the job and click the right mouse button. Select Show Results from the pop-up menu. The Job Results window opens. The output of the db2look command is shown in the Job Output pane.
7. Select Create Script to create a script of the results. The New Command Script window appears.
8. Save the new script if you want to use it again.

Filter

In the Control Center, you can filter information that is displayed in the Contents pane, or you can filter information that is retrieved from a table as an actual result set. You can limit the number of objects that are displayed or the number of objects that are returned by creating filters for one or more objects. Once you have set the filter, you need to clear or delete the filter if you want to display all the objects in the tree once again.

Filtering the Display

To reduce the number of objects that appear in the Contents pane for more manageable administration:

1. Select the Filter icon from the Contents pane toolbar, located at the bottom of Control Center, or select Filter from the View menu bar.
2. Select the criteria to be used to reduce the number of objects.
3. Select the Enable filter checkbox to activate the filter.

When you later select an object to view its contents, the filter you have associated with the object limits the view according to the criteria you set earlier.

Filtering Retrieved Data

To reduce the number of rows returned in a query and improve the response time, you can define the output, or the result set that shows in the Contents pane when selecting an object.

1. Select a folder object from the tree and click the right mouse button.
2. From the pop-up menu, select Filter. The Filter window opens.
3. Use the Filter function to define a set of criteria for retrieving rows belonging to that object.

Defining a Filter to Retrieve a Specific Set of Data

To define a filter to retrieve a specific set of data:

1. From the Control Center, expand either the Databases or Subsystems folders depending on your platform.
2. Select an object for which you want to define the filter. Click the right mouse button on that object.
3. Select Filter from the popup menu. This opens the Filter notebook.
4. On the Locate page, specify the name or other descriptive filter criteria of the selected object. The result of the filter is the results set associated with the selected object shown in the Contents pane of the Control Center.
5. On the Locate page, select a radio button to specify whether to meet all the conditions selected in the fields on the Locate page or to meet at least one condition.
6. On the Advanced page of the Filter notebook, you can use additional criteria by editing the text that is shown to further limit the number of returned rows.
7. Click OK to use the filter criteria you defined.

To automatically invoke this filter notebook based on numbers of rows, select Tools from the menu bar, and select Tools Settings from the popup menu. The Select filtering when numbers of row exceeds checkbox allows you to predefine a threshold of returned rows from any selection. When the threshold is reached, the Filter notebook appears so that you can limit the current retrieval based on the defined criteria. This is especially useful when a table has grown unexpectedly and was previously unfiltered. Depending on your platform, and your data, you could be attempting to return millions of rows, when you need only a subset of rows.

Help

Extensive help information is provided with the administration tools. A help button exists on all dialog boxes as well as on the menu toolbar. You can get general help as well as help on how to fill out the fields and perform tasks. From the help menus, you can also access the index of terms or the reference information and the information provided in the product manuals.