Chapter 28: Maintaining a DB2 Database

You can use the SQL Wizard to maintain a DB2 database. You can do things such as adding and updating tables and defining queries.

You need to have read the chapter Start Here for the Tutorials and worked through the first session, Using Mainframe Express, and the session DB2 Applications (SQL Option), before you do this session.

You need to have installed SQL Option to do this session.

We assume you are familiar with SQL and with DB2 on a mainframe.

28.1 Overview

This session shows you how to use the SQL Wizard to define and maintain tables and queries in an existing DB2 database.

In this session you use the Wizard to add a Pensioner table to the supplied database called Tutorial, and to create and run a query on this table.

Although the SQL Wizard and the sample database used in this session are not related to any project, you must have a project loaded to enable the relevant IDE menu functions. It must be a project where the SQL check box was checked. We will use the project created in the chapter DB2 Applications (SQL Option).

28.2 Preparation

If you installed both SQL Option and Host Compatibility Option, ensure SQL Option is active, as described in the section Preparation in the chapter DB2 Applications (SQL Option).

This demo uses the project you created and built in the chapter DB2 Applications (SQL Option).

  1. If you have closed Mainframe Express, open it as before. Close any project windows and any other windows that are open.

  2. Open the project sqldemo.mvp in any of the ways described in the chapter Start Here for the Tutorials.

    The full path is \mfuser\projects\gsdemo\sqldemo\sqldemo.mvp. If you use Open on the File menu, you need the Files of Type field on the Open dialog box set to Project files (*.mvp) to see this file.

  3. Start the XDB Server as described in the section Starting the XDB Server in the chapter DB2 Applications (SQL Option). If you are using Windows 95, you can minimize the XDB Server window, as you don't need to be able to see it for this tutorial.

28.3 Sample Session

In this session you:

28.3.1 Start the SQL Wizard

To start the SQL Wizard:

  1. Click SQL for DB2 on the Tools menu, then click SQL Wizard.

    The SQL Wizard appears, containing a Catalog Browser dialog box as shown in Figure 28-1.



    Figure 28-1: The Catalog Browser Dialog Box.

28.3.2 Create a New Table

To create a table:

  1. Click the TABLE tab to ensure it's selected.

  2. Click Create.

  3. Enter PENSIONERS in the Table Name field.

    The bottom part of the dialog box is for defining the fields to appear in the table. We will define three fields.

  4. Click in the empty field under the column heading Field Name and enter the following:

    Field Name PensionerId
    Data Type Char
    Length 5
    Not Null Unique

  5. Press Tab until a new new field appears below the first, and enter the following:

    Field Name PensionerAddress
    Data Type Varchar
    Length 100
    Not Null True

  6. Press Tab again to add another new field, and enter the following:

    Field Name PensionerAge
    Data Type Varchar
    Length 3
    Not Null False

28.3.3 Defining the Primary Key

To define the primary key for the table:

  1. Click Create, just to the right of the Indexes and Keys field.

    The Create Index dialog box appears.

  2. Type PensionerId in the Index Name field.

  3. Click PensionerId in the Non-index Columns listbox and click

    This moves this field across to the list of Index Columns.

  4. Click Primary Key under Type.

    You should now have the dialog box shown in Figure 28-2.



    Figure 28-2: The Create Index Dialog Box for the Primary Key

  5. Click OK.

    This adds the primary key +PensionerId to the list of Indexes and Keys. You should now have the dialog box shown in Figure 28-3.



    Figure 28-3: The Create Table Dialog Box

28.3.4 Define a Secondary Index

To define a secondary index for the table:

  1. Click Create, just to the right of the Indexes and Keys field.

    The Create Index dialog box appears again.

  2. Type Altindex in the Index Name field.

  3. Click to add all the fields as Index Columns.

    You should now have the dialog box shown in Figure 28-4.



    Figure 28-4: The Create Index Dialog Box for the Secondary Index

  4. Click OK.

    This adds the index +AltIndex to the list of Indexes and Keys.

28.3.5 Viewing the SQL

To view the SQL which will be used to create this new table:

  1. Click SQL....

  2. Close this view window.

28.3.6 Finishing Creating the Table

To finish creating the table:

  1. Click OK.

  2. Click Refresh.

    The new table called PENSIONERS has been added to the list of tables in the database called Tutorial. You may have to pull the slider down to see it.

  3. Expand the table details by clicking the "+" alongside the table name PENSIONERS. Expand the Columns and Indexes in the same way.

28.3.7 Adding Data to the Table

To store some data in this table:

  1. Click PENSIONERS in the Catalog Browser, then click Open.

    The Result Table window appears. This table displays the records in the table, but there are none at present.

  2. Click Allow Editing on the Record menu.

    This enables you to enter records into the table.

  3. Enter a few record details as shown in Figure 28-5.



    Figure 28-5: The Pensioners Result Table

  4. Close the Result Table window.

28.3.8 Creating a Query

To create a query against this table:

  1. Click the QUERY tab to ensure it's selected.

  2. Click New.

    The Add Table dialog box appears.

  3. Click PENSIONERS in the Table Name field, and click Add.

    The PENSIONERS(P1) window appears.

  4. Click Done.

  5. On the PENSIONERS(P1) window, click PensionerId and PensionerAddress and see how these are added to the list.

  6. Enter 10234 in the Conditions field directly below PensionerId and press Tab.

    Notice how your entry changes

  7. Enter 10674 in the or field directly below PensionerId and press Tab.

    The produces the window shown in Figure 28-6.



    Figure 28-6: The Query Design window.

  8. Click Save As on the File menu, enter Pensionlist as the Query Name, and click OK.

  9. Close the Query Design Window.

  10. Click Refresh.

    The new query called Pensionlist has been added to the list of queries in the database called Tutorial.

28.3.9 Running the Query

To run the query:

  1. Click Pensionlist in the list, then click Batch.

    The result table appears, showing records that match the criteria you specified in the query.

  2. Close the Result Table window.

28.4 Before Continuing

Close the SQL Wizard and the SQL server.

Close the project. If you want to take a break before going on to the next session, you can close Mainframe Express.

Return to the Tutorials Map in the chapter Start Here for the Tutorials and choose which session to go on to next, depending on your interests.

Comments on the books? Click Send Us Comments.

Copyright © 2001 Micro Focus International Limited. All rights reserved.
This document and the proprietary marks and names used herein are protected by international law.