PreviousDB2 Applications (SQL Option) Regression and Conformance TestingNext"

Chapter 26: 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.

26.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).

26.2 Preparation

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. To start the SQL Server, click SQL for DB2 on the Tools menu. Then click Start server.

  4. Since you don't need to watch this window, save space on your screen by minimizing the window.

26.3 Sample Session

In this session you:

26.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 26-1.



    Figure 26-1: The Catalog Browser Dialog Box.

26.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

26.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 26-2.



    Figure 26-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 26-3.



    Figure 26-3: The Create Table Dialog Box

26.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 26-4.



    Figure 26-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.

26.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.

26.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.

26.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 26-5.



    Figure 26-5: The Pensioners Result Table

  4. Close the Result Table window.

26.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 26-6.



    Figure 26-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.

26.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.

26.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.

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

PreviousDB2 Applications (SQL Option) Regression and Conformance TestingNext"