OS/2 Shareware BBS: 10 Tools/10-Tools.zip/visdb2tk.zip/TOOLKITD.INF

OS/2 Help File | 1995-11-21 | 264KB | 1,035 lines

ΓòÉΓòÉΓòÉ 1. Version Notice ΓòÉΓòÉΓòÉ

Second Edition (October 1995)

References in this publication to IBM products, or services do not imply that
IBM intends to make these available in all countries in which IBM operates.
Any references to an IBM product, program, or service is not intended to state
or imply that only IBM`s product, program, or service may be used.  Any
functionally equivalent product, program, or service that does not infringe any
of IBM`s intellectual property rights may be used instead of the IBM product,
program, or service.

The information contained in this document has not been submitted to any formal
IBM test and is distributed AS IS.  The use of information or the
implementation of any of these techniques is a customer responsibility and
depends on the customer`s ability to evaluate and integrate them into the
customer`s operational environment.  While each item may have been reviewed by
IBM for accuracy in a specific situation, there is no guarantee that the same
or similar results will be obtained elsewhere.  Customers attempting to adapt
these techniques to their own environments do so at their own risk.

IBM may have patents or pending patent applications covering subject matter in
this document.  The furnishing of this document does not give you any license
to these patents.  You can send licence inquiries, in writing, tot he IBM
Director of Licensing, IBM Corporation, 208 Harbor Drive, Stamford, Connecticut
06904-2501, USA.


ΓòÉΓòÉΓòÉ 2. Introduction ΓòÉΓòÉΓòÉ

With Version 2 of DB2 for OS/2 and AIX, IBM has exciting, leading edge
technology, relational database products for those environments.

Version 2 provides massive enhancements of function in many areas, some of
which will radically change the way in which database applications are
developed.  Business function which would previously have been defined in a
multitude of application programs can now be defined once in the database.  New
functions which have major impact in this area include:

o User Defined Functions

o User Defined Distinct Data Types

o Triggers

o Check constraints on tables

The use of these new capabilities depends on the generation of SQL, some of
which can be complex to produce.

IBM has developed a toolkit of utilities using Visualizer which will make it
very much easier to exploit these new database capabilities.  The utilities
provide a front end from Visualizer for OS/2 and Visualizer for AIX to some of
the key new features in the databases.

All of the utilities generate SQL which may be saved to a file compatible with
the DB2 Command Line Processor.  These files can then be distributed as part of
application installation processes or the SQL included in application programs.

This document describes a demonstration of the utilities.  Working through this
demonstration will familiarise users with the utilities in the toolkit.

Three powerful utilities are included:


ΓòÉΓòÉΓòÉ 2.1. Create User Defined Distinct Type ΓòÉΓòÉΓòÉ

UDTs allow users to define distinct types which identify the business class of
the data, as well as its physical representation in the database.  Combined
with User Defined Functions, this capability provides a powerful platform for
application development.  Application developers should seriously consider the
use of UDTs in any new development, as this extra information will be exploited
by future DB2 family products.

The definition of a new type is very simple using SQL.  However, when a new
type is defined, there are no database functions available for the type.  This
utility allows new types to be defined through a GUI interface.  Then the
functions required for the type can be selected from the built-in and
user-defined functions which are compatible with the physical representation of
the new type.  This process generates the SQL to create as many functions as
required with just a few key depressions, rather than extensive manual coding.

The utility has the following features:

o Create User-Defined Distinct Types in a database

o Generate SQL to create functions for the new types

o Allow modification of the generated SQL

o Apply the generated SQL to the database

o Save the generated SQL to a file compatible with the DB2 Command Line
  Processor

o Online help


ΓòÉΓòÉΓòÉ 2.2. Create DB2 Table ΓòÉΓòÉΓòÉ

This utility provides a GUI allowing the user to easily define DB2 tables
exploiting both new and existing database function.

You can extract the definition of a table from DB2 Version 2 databases. The
definition can then be applied directly to other databases, or modified before
further use.  The complete SQL definition of the table can be saved to a file
which can be directly executed by the DB2 Command Line Processor.  The utility
can be used to generate up-to-date SQL for existing database tables.

Features of tables which are covered by the utility include:

o Database, schema and name for the table

o Table comments

o Specification of tablespaces

o Data propagator options

o Column data type definition, including UDTs

o Primary key

o Table check constraints

o Additional indexes

o Referential constraints

o Constraint names for all constraints

o Comments for columns, indexes and constraints

o Online help

All of the above information will be extracted from the database when working
with an existing table definition, and will be included when SQL is saved from
the utility, or applied to a database.


ΓòÉΓòÉΓòÉ 2.3. Create Trigger ΓòÉΓòÉΓòÉ

Database triggers allow processing to be defined which will be activated on
conditions defined within the trigger.  For example, an update to a particular
column in a table can activate a trigger which will cause rows to be updated or
inserted in other tables, all within the same unit of work.  Triggers can also
generate error conditions, so they can be used for extensive data validation.

Because of the power of the trigger mechanism, the SQL statement to define a
trigger can be long and complicated.  The Toolkit Create Trigger Utility
provides a GUI front end to allow DBAs, application developers, or auditors to
create triggers.

Features provided are:

o Provide 'Expression Builder' for a trigger

  (SQL must still be entered for the trigger conditions and actions)

o Extract existing trigger definition from any database

o Modify existing trigger definitions

o Model new triggers on existing trigger definitions

o Apply triggers rapidly to multiple databases

o Save the trigger definition as SQL which can be executed directly by the DB2
  Command Line Processor

o Online help


ΓòÉΓòÉΓòÉ 3. Installation ΓòÉΓòÉΓòÉ

The Visualizer DB2 Toolkit can be installed on personal computers running the
OS/2 operating system, with Visualizer Query for OS/2 installed, or on IBM Risc
System/6000 computers running the AIX Operating System and Visualizer for AIX.


ΓòÉΓòÉΓòÉ 3.1. Prerequisites for OS/2 ΓòÉΓòÉΓòÉ

In order to run the toolkit, you should have the following installed on your
computer.

o Either DB2/2 version 2, or CAE/2, with access to database servers running DB2
  Version 2 Common Server.

  The common server is currently available on OS/2 (DB2 for OS/2 Version
  Release 1) and AIX (DB2 for AIX Version 2 Release 1). Other implementations
  will be available during 1995.

o Visualizer Query for OS/2 Version 1 Release 2.

You must also have any products which are prerequisites for the products
listed.


ΓòÉΓòÉΓòÉ 3.2. Prerequisites for AIX ΓòÉΓòÉΓòÉ

In order to run the toolkit, you should have the following installed on your
computer.

o Either DB2/6000 version 2, or CAE/6000, with access to database servers
  running DB2 Version 2 Common Server.

  The common server is currently available on OS/2 (DB2 for OS/2 Version
  Release 1) and AIX (DB2 for AIX Version 2 Release 1). Other implementations
  will be available during 1995.

o Visualizer Query for AIX Version 1 Release 2.

You must also have any products which are prerequisites for the products
listed.


ΓòÉΓòÉΓòÉ 3.3. Installation Steps ΓòÉΓòÉΓòÉ


ΓòÉΓòÉΓòÉ 3.3.1. Install The Toolkit on OS/2 ΓòÉΓòÉΓòÉ

 1. Place the toolkit diskette in your diskette drive.

    The instructions that follow are for drive a:.  Modify the instructions if
    you are using a different drive.

 2. From an OS/2 Window or Full Screen, enter the command:

    A:

 3. In the same OS/2 Window or Full Screen, enter the command:

    INSTALL

The install routine will prompt for a directory in which to install the toolkit
objects.  The toolkit requires about 500kbytes of disk space. A folder
containing icons for the 3 utilities, the on line reference manual and the on
line version of this guide will be created on the OS/2 desktop.

Press the Cancel button to exit the installer.


ΓòÉΓòÉΓòÉ 3.3.2. Install The Toolkit on AIX ΓòÉΓòÉΓòÉ

The same installation diskette image contains the AIX and OS/2 versions of the
toolkit.  To install the toolkit on AIX

 1. Place the installation disketted in the diskette drive of your Risc
    System/6000

 2. Make your home directory the current directory.

    cd $HOME

 3. Make a temporary directory

    mkdir tktemp

 4. A shell script is provided to copy the contents of the installation
    diskette to your temporary directory.  Copy that script to your temporary
    directory:

    dosread -a tkcopy ./tkcopy

 5. Run the shell script to copy the files from the diskette to your temporary
    directory:

    . tkcopy

 6. Run the shell script tkinst to install the toolkit in the location of your
    choice:

    . tkinst

    When requested, you may enter the path in which the Query for AIX product
    files are installed. This has a default

    /usr/lpp/VQ6000

    Press ENTER to accept the default, or type the correct path and then press
    ENTER.

    You will then be prompted for a path into which to install the toolkit.
    This will default to a subdirectory named db2tools in the home directory of
    the user installing the toolkit.

    Press ENTER to accept the default, or type a path name of your own and then
    press ENTER.

    The install script will set the file permissions on the toolkit files to
    allow all users to execute the toolkit.

To be able to run the toolkit, the user must run the file tktprofile, which
will have been created in the directory you specified as the toolkit
installation directory.  This can be run from the command line as follows:

/installpath/tktprofile

where installpath is the directory to which the toolkit was installed.

Add this line to the profile (.profile) of each user who will use the toolkit.


ΓòÉΓòÉΓòÉ 4. Demonstration Preparation ΓòÉΓòÉΓòÉ

The demonstration will be done using the database DEMO, which should be created
and populated prior to starting the demonstration.

Note:  If you wish to run the demonstration in a Client/Server environment, you
should transfer the contents of the DEMO subdirectory to the server, and run
the following process on the server.  You should then catalog the database on
the client where the demonstration will be run.

From a command line :

 1. Make the directory where the toolkit was installed the current directory.
    For example:

    cd db2tools

 2. Change directory to the demo sub-directory.

    cd demo

 3. Create the demonstration database.

    db2 -v -fdemodb.sql

    Note:  The instructions above will create a DB2/2 database in the default
    location, and create tablespaces of approximately 12 Megabytes on drive C.
    You may customise this setup by editing the file demodb.sql and following
    the instructions in the lines starting -- **.  If you wish to create the
    demonstration database on a DB2/6000 server, you must customise the file
    'demodb.sql' to your environment.

    If you are using the AIX version of the toolkit, you will need to bind
    Visualizer for AIX to the new database. For a default installation, the
    steps are as follows :

    cd /usr/lpp/VQ6000/sql

    db2 connect to demo

    . ftqix

Before you start the demonstration, ensure that database manager is running.
Enter the command:

db2 connect to demo

from an OS/2 command line, to check that the database can be accessed.

The 3 parts of the demonstration must be done in order.

Throughout the demonstration you will find paragraphs in this style, which
provide material for discussion.

On completion of the demonstration, you can delete any tables, triggers and
user defined types you have created by running the command cleanup, from the
subdirectory demo under the toolkit install directory.


ΓòÉΓòÉΓòÉ 4.1. Database Privileges Required ΓòÉΓòÉΓòÉ

The demonstration was produced using the default user id USERID on a DB2 for
OS/2 standalone system.  USERID was also the instance owner.

To create the demonstration database you must have SYSADM or SYSCTRL
privileges.

To run the demonstration you should have DBADM privilege on the DEMO database.

If you use an ID other than USERID, you should use that ID wherever USERID is
used in the demonstration.


ΓòÉΓòÉΓòÉ 5. Demonstrate the Create Type Utility ΓòÉΓòÉΓòÉ

Distinct Types are a powerful feature of the database.

o They allow data types which reflect the business class of the data, not just
  the physical representation.

o Functions can be restricted to particular Distinct Types.

o User defined functions will encapsulate business logic which will only be
  appropriate to certain data types.

o When developing new applications, you should seriously consider implementing
  Distinct Types for future protection.

o In this demonstration, we create a type called AGEINMONTHS, which is used to
  represent the age of employees.


ΓòÉΓòÉΓòÉ 5.1. Demonstration steps ΓòÉΓòÉΓòÉ

 1. Start the Create Types utility, by double clicking on the appropriate icon.

 2. Select the DEMO database

    There may be some delay while the utility connects to the database, and
    checks for the existence of tables and views which are required.  These
    tables and views will be installed the first time a database is used.

    You will see a dialog stating that database has not yet been enabled for
    the utility.

 3. Select 'Yes' for automatic enablement.

    Tables and views are required to provide 'Function' information for this
    utility.  These will be installed automatically.

    Completed Create Type Form

 4. Leave Type Schema blank, to default to current authorization ID

 5. Enter AGEINMONTHS as the type name

    This may be entered in upper or lower case, but the field is case
    sensitive.

 6. Select From Source Data type INTEGER

    The details relating to the Type creation are complete. This would be easy
    in SQL.  However, when you create a new type, no functions are available.
    This utility will replicate all functions which use the base type as a
    parameter.  We have the choice of selecting the functions to replicate
    individually, or of replicating all of the functions.  We can choose to
    replicate all of the functions, then use the selection features to deselect
    some.  Using the selection capabilities, we can select all of the functions
    from a single schema at once.

 7. Choose Replicate all functions

    This will generate the SQL to replicate the functions. We can examine and
    modify the SQL for individual functions.  Let's look at the MAX function.

 8. Scroll down and double click on the MAX function.

    Here is a simple function that will tell us who the oldest employee is.

 9. Scroll back up and double click on the first occurrence of INSERT.

    Edit SQL Form for Insert Function

     This is a more complex example. The base function has 4 parameters -
    VARCHAR,INTEGER,INTEGER,VARCHAR.  We can see that the utility is generating
    three functions from this.

        VARCHAR, AGEINMONTHS, INTEGER, VARCHAR
        VARCHAR, INTEGER, AGEINMONTHS, VARCHAR
        VARCHAR, AGEINMONTHS, AGEINMONTHS, VARCHAR

10. Close the Edit SQL window by pressing the OK button

    Now let's save the SQL and see how much SQL has been generated:

11. Press the Save Text... button and save as CRETYPE.SQL.

12. From another session, open the created SQL using the enhanced editor(EPM).

    Notice the number of lines of SQL generated.  If we had user defined
    functions in the database, these would also be replicated as required.

13. Now close the editor

14. Select the CREATE button

15. Reply No to the dialog concerning warning messages from DB2.

    Replying 'Yes' to this dialog can result in a large number of warning
    messages. It is possible to use Ctrl-Break to halt processing in this case.
    Database changes will then be rolled back.

    This utility is a must for anybody wishing to work with Distinct Types.
    Applying the changes to the database will take some time.  When this is
    complete, we get the option to roll back the changes.

16. Commit the changes to the database when requested.

17. Select the Close button to exit the utility.


ΓòÉΓòÉΓòÉ 6. Demonstrate the Create Table Utility ΓòÉΓòÉΓòÉ

The create table utility is specifically designed for DB2 Version 2 Common
Server databases. It offers the following capabilities

o Specify tablespaces

o Columns with distinct types

o Specify a primary key

o Specify additional indexes

o Specify table constraints

o Specify referential constraints

o Name all of the constraints - important for error messages

The table definition can be based on an existing table in any database, or
started from scratch.  The SQL generated can be saved to a file which can be
executed directly from the DB2 command line processor.  The comments in the
saved SQL contain control information for the utility.  Saved SQL may also be
reloaded by the utility and used as the basis of other definitions.

The utility can be used to generate table creation SQL (DDL) to be included in
application programs.


ΓòÉΓòÉΓòÉ 6.1. Demonstration steps ΓòÉΓòÉΓòÉ

 1. Double Click on the Visualizer Create DB2 Table

    Select method of table creation

    A completely new table definition can be created using the Template option.
    A definition previously saved from this utility can be modified, or a table
    can be modelled on an existing database table.  The new table can be
    created in a different database from the model table.

 2. Select Model table on Existing table

 3. Select Database DEMO

    There may be a delay while the utility connects to the database and checks
    for the existence of data in system catalog tables.

 4. Select Table schema <current schema>

 5. Select Table name EMPLOYEE

 6. Press OK

    This will bring up the main Create DB2 Table panel. This panel has 3 major
    sections, used to supply all the details of the new table being created.


ΓòÉΓòÉΓòÉ 6.1.1. General Information ΓòÉΓòÉΓòÉ

Create DB2 Table - General Information

 1. Select Database DEMO

    This is the database in which the new table will be created.

 2. Leave Table Schema as USERID

    You should leave this as the default value

 3. Enter the Table name WORKERS

 4. Table Comment New table based on Employee

 5. Select Specified Tablespace

    The ability to specify tablespaces is a new feature or DB2 version 2.  If
    you have migrated a database from Version 1, all tables will be in a
    default tablespace.  You may wish to use this utility to re-create the
    tables in specified tablespaces.

 6. Choose the tablespace REGULAR from the list box

    Because REGULAR is a Database managed tablespace, you are given the options
    to select the Index Tablespace and the Long Tablespace.  Performance
    benefits may be derived by placing indexes on a separate physical drive:

 7. Choose the tablespace INDEXSPACE as Index TableSpace

 8. Choose the tablespace LONGSPACE as the Long tableSpace

 9. Select No data capture

    The data capture function is provided to facilitate propagation of changes
    to the data in the table.

10. Do note select OK but continue into Column Details.


ΓòÉΓòÉΓòÉ 6.1.2. Column Details ΓòÉΓòÉΓòÉ

Select the column details entry in the top value set control.

Create DB2 Table - Column Details

 1. Enter the name AGE in the Column name entry.

 2. Select the Type Name list.

    Note that any of the system data types may be selected.

 3. Select the Type schema USERID

    Note that the Position field has changed to 15, which will cause a new
    column to be added after the existing columns.  If you changed this value,
    the existing columns would be rearranged.

 4. Select Type Name AGEINMONTHS

    Note that this is the user defined type we created earlier.

 5. Select Accept Nulls

 6. Check With Default

    Press HELP here for an explanation. We could also have entered a Column
    Remark

 7. Select Add

 8. Do note select OK but continue into Table Constraints.


ΓòÉΓòÉΓòÉ 6.1.3. Table Constraints ΓòÉΓòÉΓòÉ

Select TABLE CONSTRAINTS from the top value set

The table constraints section is divided into 4 subsections, allowing the
specification of the different constraint types.


ΓòÉΓòÉΓòÉ 6.1.3.1. Primary Key ΓòÉΓòÉΓòÉ

The Primary Key section should already be selected.

Create DB2 Table - Primary Key Constraint

 1. Select the column EMPNO from the list of available columns

 2. Press the Add> button

    This will create a primary key of 1 column.


ΓòÉΓòÉΓòÉ 6.1.3.2. Indexes ΓòÉΓòÉΓòÉ

Select Indexes from the lower value set.

Create DB2 Table - Index Constraints

We are creating an index of two columns.  We could create any number of
secondary indexes.  Indexes are used to improve database performance, and can
also be used to ensure that indexed column values are unique.

 1. From the list of available columns, Select FIRSTNME

 2. Press the Add> button

 3. From the list of available columns, Select LASTNAME

 4. Press the Add> button

 5. Press the Add button below the list of available columns.

    The index can now be named, and a comment added.

 6. Position the cursor in the Index Name column of Row 1 of the list box at
    the top of the panel.

 7. Enter the Index Name FORENAME

 8. In the Remarks column enter Index on FIRSTNME,LASTNAME


ΓòÉΓòÉΓòÉ 6.1.3.3. Check Clauses ΓòÉΓòÉΓòÉ

Select the Check Clauses entry in the lower value set.

Check clauses provided the capability to define rules controlling the data
values that can be placed in a column.  This facility allows these rules to be
specified as part of the database, rather than in application programs.

Create DB2 Table - Check Constraints

 1. In the Check Clause control, enter the text Salary < 100000

 2. Press the Add button

 3. Position the cursor in the Constraint Name column of Row 1 of the list box
    at the top of the panel.

 4. Enter the Name MAX_SALARY

    Note that names for constraints should be chosen carefully. The constraint
    name will appear in the error message generated by an attempt to break the
    constraint rules.

 5. In the Remarks column enter Maximum Salary


ΓòÉΓòÉΓòÉ 6.1.3.4. Table References ΓòÉΓòÉΓòÉ

Select the Table References entry in the lower value set.

Table references, or referential constraints, enable the values allowed in
columns of a table to be limited to those which match primary key values in
another table.

Create DB2 Table - Table References

 1. Select referenced table schema USERID

 2. Select referenced table name DEPARTMENT

 3. Select referenced key column DEPTNO

    This is the only primary key column for the DEPARTMENT table.

 4. Select column WORKDEPT from the list of Potential matches.

    The potential matches are the columns of the tables being created that
    could be used to match against the selected key column of the referenced
    table.  Where the referenced table has more than one key column, each one
    of these must be matched.

    Delete Rule and Update Rule options can be selected.

 5. Select Add

 6. Enter a Constraint Name of Dept.

 7. Enter Remarks Referential Constraint.


ΓòÉΓòÉΓòÉ 6.1.3.5. Save and Examine the SQL ΓòÉΓòÉΓòÉ

 1. Select the Save Text... button

 2. Save as CRETAB.SQL.

 3. Examine CRETAB.SQL with the OS/2 Enhanced Editor (EPM)

    The top portion of the file contains control information which is used when
    the file is read by this utility.  Show all the statements that have been
    generated.

    This file can be executed directly by the DB2 command line processor.

 4. Close the editor and return to the main panel.

 5. Select OK to create the table

 6. Confirm that you want the changes committed when requested.

Immediate applications for which this utility may be useful.

o Add new features to tables from migrated databases.

  Specify tablespaces, name existing constraints etc.  You can quickly modify
  the created SQL to export the data to an IXF file, drop the old table, create
  the new table and re-import the data.

o Clone tables to another database

o Generate SQL to include in applications

o A table can be modified using Alter statements.  This utility will then
  produce updated DDL for the new table.


ΓòÉΓòÉΓòÉ 7. Demonstrate the Create Trigger Utility ΓòÉΓòÉΓòÉ

Triggers are probably the most obviously powerful feature of DB2 v2.  They are
an excellent feature for client/server applications, as they can be used to
minimise the distributed application logic by defining this within the
database. Use triggers for:

o Data validation

o Related updates

o Audit trail production

o Validation - A trigger can produce a customised error message

The syntax of triggers is quite difficult - we help to get this right by
minimising the SQL the user must write.


ΓòÉΓòÉΓòÉ 7.1. Demonstration steps ΓòÉΓòÉΓòÉ

 1. Double click on the Create Trigger icon.

    Select The Model for a Trigger

    As for tables, triggers can be created from an existing trigger, from a
    saved Create Trigger statement, or from an empty template.  In this
    demonstration we will work from an empty template.

 2. Select Model trigger on Template

 3. Press OK


ΓòÉΓòÉΓòÉ 7.1.1. Create DB2 Trigger Details ΓòÉΓòÉΓòÉ

Create DB2 Trigger Details

This form allows the creation of general information relating to the trigger

 1. Select the database DEMO

    There may be some delay while the utility connects to the database.

 2. Leave the trigger schema as the default.

 3. Enter a trigger name MAX_INCREASE

 4. Enter the comment Limit salary increase

 5. Select Apply trigger To each row

    A single SQL statement can update more than one row in the table.  The
    trigger can be defined so that it is actioned once for each row, or once
    for the whole statement. We have chosen to action this trigger once for
    each row.

 6. Select Apply trigger After database changes

    Triggers defined as 'Before database changes' will be applied before any
    updates in the statement activating the trigger.  The trigger actions will
    not cause any other triggers to be activated.  Triggers defined as 'After
    database changes' will be applied after the updates in the statement
    activating the trigger, and the trigger actions may activate further
    triggers.

 7. Select the Table schema USERID

 8. Select the Table name WORKERS

 9. Select the Action Update

    Triggers can be activated by Deletes, Inserts or Updates on the specified
    table.  For update actions, columns may also be specified.  If no columns
    are specifed, the trigger will be activated by updates on any column in the
    table.

10. Select the column SALARY

11. Click on the ... button to expand additional conditions

    Create DB2 Trigger Conditions

    In the trigger conditions and trigger actions section, the SQL can
    reference the values of the rows affected by the statement causing the
    trigger both before and after the updates caused by the statement.

    The values of the current row before and after the updates can be
    referenced by the Old and New correlation names.  The values of the set of
    affected rows can be referenced by the old and new temporary table names.
    Old and New correlation names can only be referenced for triggers defined
    as 'For each row'.  Old values cannot be referenced in a trigger actioned
    by Insert.  New values cannot be referenced in a trigger actioned by
    delete.

12. Enter the following in the multi line edit control:

    newcorr.salary > oldcorr.salary * 1.1

     The utility provides default correlation and temporary table names, which
    can be overridden if other names are preferred.  This trigger will only be
    actioned if salary is increased by more than 10%.

13. Select OK to close the window

14. Click on the ... button to expand SQL statements to be executed

    Create DB2 Trigger Actions

15. Enter the following values in the main control:

    Signal SQLSTATE '75001' ('Salary increase must not exceed 10%')

    This trigger will prevent salary increases of more than 10% and give a
    meaningful error message.  We could have extended the trigger to allow some
    users to make these changes.

16. Select OK to close the window

17. Select Save text...

18. Save SQL to a file CRETRIG.SQL

19. Examine CRETRIG.SQL with the OS/2 Enhanced Editor (EPM), to view the SQL
    that has been created.

20. Close the editor.

21. Press the Apply button to apply the trigger to the database.

    Note that by using Apply instead of OK we can now amend the trigger
    definition and apply it again.  We could also apply the same trigger
    definition to a different database.

22. Don't forget to run cleanup before you repeat this demonstration.

This is a very simple use of a trigger.  Triggers can be defined such that
application programs only need to update one table, with other dependent
updates being defined in triggers.

This utility eases the trigger definition process.  Triggers can be rapidly
cloned across databases.  The SQL generated can be saved to files, and executed
directly from the DB2 command line processor.

Note:  Where a trigger contains multiple 'action' statements, these must be
separated by a semicolon.  Because of this, the tilde character ю is used as
the terminator for the complete Create Trigger statement.  Use saved files from
the Command Line Processor using the td option, for example:

db2 connect to demo db2 -v -fcretrig.sql -tdю db2 connect reset

You can try the above commands, and see the SQL passed to the DB2 command line
processor.  The commands will not all be successful because the trigger already
exists.


ΓòÉΓòÉΓòÉ 8. Further Information ΓòÉΓòÉΓòÉ

For further information contact IBM EAS Product Support:

IBM Internal        EASPS at WINVMD

IEA                 GBIBM8L2 at IBMMAIL

Internet            [e-mail]

Fax                 [phone]


ΓòÉΓòÉΓòÉ 9. Printing This Document ΓòÉΓòÉΓòÉ

This document is installed as an on line document as part of the toolkit, and
can be viewed by double clicking on the 'Tutorial' icon.

You can obtain a hard copy of this document by selecting Print and then All
Sections from the Services menu of the on line document viewer.

A PostScript version of the document is also available.  This is available from
the library of the Visualizer Section of IBMDB2 Forum on CompuServe.  Download
the file TKDEMO.ZIP to your DOS or OS/2 workstation.  You must uncompress this
file using either PKUNZIP or PKUNZIP2, and then print the resulting postscript
file.

 1. Open an OS/2 Window or Full Screen

 2. Copy the file TKDEMO.ZIP to a temporary directory

 3. Make the temporary directory the current directory

 4. Uncompress the file

    PKUNZIP TKDEMO.ZIP

             or
    PKUNZIP2 TKDEMO.ZIP

 5. Copy the file to a postscript printer. If your postscript printer is on
    LPT1:

    COPY TOOLKITD.PS LPT1


ΓòÉΓòÉΓòÉ 10. How to Obtain EAS Documents ΓòÉΓòÉΓòÉ

Further copies of this document, or others in this series, are available from
the Visualizer section of the library in the IBMDB2Forum on CompuServe.

For each package the following files exist:

o PackageName.ZIP

  This ZIP file contains the full document, including illustrations, in
  postscript form.  The ZIP file also contains sample files related to the
  document and bitmaps of each document illustration.  Each package contains a
  README describing the files in that package.

o PackageName.TXT

  This file contains the document in ASCII form.  Figure descriptions are
  included, along with the file name of the bitmap for the related artwork from
  the .ZIP file. You may include the bitmaps in the text using most current
  word processors.

  or

o PackageName.INF

  As documents are updated, they will be supplied as INF files instead of ASCII
  files.  This format can be viewed using the INF viewer supplied with OS/2, or
  using Windows and AIX viewers available from the same CompuServe library as
  the documents.  INF files contain all illustrations and artwork, and can be
  printed using the capabilities of the INF viewer.

Packages Available Include:

VQM         Visualizer Query for OS/2 - hints and tips for Query Manager Users

VDB2        Using Visualizer (for OS/2 or AIX) with DB2 for MVS or DB2/VM

VDB2WS      Using Visualizer (for OS/2 or AIX) with DB2/2 or DB2/6000

VAS400      Using Visualizer (for OS/2 or AIX) with AS/400 Systems

VWAS40      Visualizer for Windows with AS/400 Systems

Instructions for obtaining materials via the IBM network are provided on the
following page.

IBM Employees may obtain the documents in tersed LIST3820 format, together with
associated files, using the following commands:

TOOLS SENDTO WINVMB TOOLS EASWGS GET PackageName PACKAGE
Packages Available Include:

VISQM       Visualizer Query for OS/2.  Hints and tips for Query Manager Users

VISDB2      Using Visualizer (for OS/2 or AIX) with DB2 for MVS or DB2/VM

VISDB2WS    Using Visualizer (for OS/2 or AIX) with DB2/2 or DB2/6000

VISAS400    Using Visualizer (for OS/2 or AIX) with AS/400 Systems

VWAS40      Visualizer for Windows with AS/400 Systems - Connectivity Guide

TOOLKIT     The Visualizer DB2 Version 2 Toolkit - Programs and Documentation

The EASWGS disk contains over 100 marketing and technical deliverables.  To
obtain a complete list, enter the following command:

TOOLS SENDTO WINVMB TOOLS EASWGS LIST * PACKAGE