OS/2 Shareware BBS: 10 Tools/10-Tools.zip/visdb2tk.zip/FTBEAS01.HLP

OS/2 Help File | 1995-09-27 | 39KB | 1,240 lines

ΓòÉΓòÉΓòÉ 1. The Visualizer DB2 Toolkit ΓòÉΓòÉΓòÉ

This toolkit has been developed by the IBM EAS Product Support to give ease of
access to some of the powerful new capabilities in Version 2 of IBM's Database
2 for OS/2 and Database 2 for AIX products. The toolkit provides the following
capabilities:

o   Creation of Tables

o   Creation and Maintenance of Triggers

o   Creation of User Defined Types


This toolkit should be used in conjunction with Visualizer Query for OS/2,
Version 1 Release 2 or later, or with Visualizer Query for AIX Version 1
Release 2 or later.  The toolkit can be used with any DB2 Version 2 database,
utilising the capabilities of the DB2 Client/Server Family.


ΓòÉΓòÉΓòÉ 2. Table Creation Capabilities ΓòÉΓòÉΓòÉ

The Visualizer SQLTable object provides capabilities for the creation and
copying of SQL tables within a database. The utility supplied in the toolkit
provides the following additional capabilities, to exploit new features of
Version 2 of the databases.

o   Specify Check Constraints

o   Specify User Defined Data Types

o   Specify TableSpace options

o   Rapidly create the same table definition in multiple databases

o   Name all constraints

o   Rapidly copy an existing table definition to any DB2 Version 2 database

o   Save the Create Table SQL as plain text

    Note:  These files are suitable for use as an input file to the DB2 Command
    Line Processor

o   Use either an existing table or saved SQL as input for future table
    creation

o   Create (and modify) the Data Definition Language for any existing table,
    including keys, indexes and constraints

Examples

Create DB2 Table - General


ΓòÉΓòÉΓòÉ 2.1. Table Creation Examples ΓòÉΓòÉΓòÉ

The following are ways in which you can use the utility.

1.  From an existing DB2 Version 2 table, automatically generate the SQL Create
    Table statement that would create an identical table, and save it as plain
    text for use as input to the DB2 command line processor, using the -f
    parameter.

2.  Load the table definition from an existing table, modify it and apply it
    rapidly to multiple databases, making any changes necessary to accommodate
    differences in TableSpace structures.

3.  Specify all table and column constraints which can be applied.

4.  Select a table to be used for a Referential Constraint, and pick, from
    selection lists, the compatible columns from the table being created to use
    as a Foreign Key.

5.  Specify meaningful names for all constraints which will appear in any error
    messages resulting from attempts to break the constraint rules.

6.  View, Update and Modify an SQL Create Table Statement which was generated
    by the utility.


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

This panel allow general details of a table to be set: The following
information must be selected.

Database

           Select the database in which you wish to create the new table from
           the list of locally catalogued databases.

Table Schema

           Enter the schema in which the table should be created.  This will
           default to the current authorization ID.  Unless the current
           authorization ID has DBADM authority, you should not specify a value
           here.

Table Name

           Enter the name with which the table should be created.

Table Comment

           Enter up to 254 characters of information which will represent a
           description of the table.

TableSpace Details

           You may allow the database to choose a default TableSpace.  If you
           choose to specify a TableSpace, you can select a TableSpace from the
           list box.  If the TableSpace you choose is a DMS (Database Managed
           TableSpace) you can also choose an INDEX TableSpace and a LONG
           TableSpace.  Any indexes created on the table will be located in the
           INDEX TableSpace, while VARCHAR, LONG VARCHAR and LOB data will be
           located in the LONG TableSpace.  If not specified, INDEX and LONG
           data are stored in the first TableSpace specified.

Data Capture

           You should only select DATA CAPTURE CHANGES if you wish to store
           details of changes to the table for use with a data propagator.

Panel Pushbuttons

Create DB2 Table - Column Details

Create DB2 Table - Constraints


ΓòÉΓòÉΓòÉ 2.3. Create DB2 Table - Column Details ΓòÉΓòÉΓòÉ

At least one column definition must be entered before a table can be created.
If you are modelling the new table on an existing table, or you have previously
entered column details, the list box will be populated with the details of
existing columns. You can select an existing column as the basis of your new
column definition. When you have entered the complete column definition, you
can select MODIFY, which will modify the selected column definition, or ADD to
add a new column.

Column Name

           Enter the column name.  Column names are case sensitive, and will be
           passed to DB2 exactly as typed.

Position

           Choose the relative position for the column in the table.  Note that
           when a new name is entered in the column name field, the position
           will be updated automatically to one greater than the number of
           columns already defined.  When Add or Modify is selected, the
           existing columns will be rearranged if necessary to accomodate the column.

Type Schema

           Select the appropriate schema for the data type to be used for the
           column. Built in data types are in the SYSIBM schema.  User defined
           distinct types may exist in other schemas.

Type Name

           All types in the selected schema will be displayed.  Select the
           required type.

Length

           A length can only be entered for data types where length is
           supported.  For LOBs, only enter the numeric portion of the length.

Scale

           For decimal columns, scale specifies the number of decimal places.
           For LOBs, specify one of

           K Kilobytes

           M Megabytes

           G Gigabytes

Treatment of Nulls

           A column can be specified to accept null values or reject null values.

With Default

           When this option is specified, any null data inserted into the
           column will be replaced with a default for the datatype.  (either
           spaces or zero).

For Bit Data

           This may only be set for character columns, and ensures that no code
           page translation is performed on the column.  This ensures that the
           bit values of the column remains unchanged.

Compact

           This option is only available for large objects.  Although use of
           this option saves on disk space, it reduces the possibility of
           applications being able to search the LOBs for matches.

No Logging

           DB2 logs updates made to tables.  However, with large objects this
           can be impracticable or impossible.  The maximum size of a DB2 log
           file is 1 Gigabyte.  No LOB of this size or larger can be logged.
           Logging of large LOBS can consume vast amounts of disk space and
           fill DB2 logs.

Column Remarks

           Enter up to 255 characters of remarks

Panel Pushbuttons

Create DB2 Table - General

Create DB2 Table - Constraints


ΓòÉΓòÉΓòÉ 2.4. Create DB2 Table - Constraints ΓòÉΓòÉΓòÉ

This panel is divided into 4 sections

1.  Primary Key

    The primary key section contains the following elements:

    Constraint Name

               You can supply a name for the primary key constraint, or allow
               it to default.  There is little value in supplying a name for a
               Primary Key constraint.

    Available Columns

               Select, in order of precedence, the columns to be included in
               the primary key, and press the Add> button.  Use the <Remove
               button if you select columns incorrectly.

2.  Index

               There may be many indexes defined on a single table.  Select the
               columns for inclusion in the index, and use the ASC/DSC button
               to set Ascending or Descending sequence for each column.

               You can specify whether rows within the index must be unique.
               If you specify the constraint name for the index, that name will
               appear in any error messages which result from attempts to
               violate the constraint criteria.

3.  Check Clause

               By defining check constraints on a table, you can remove the
               need for application programs to do standard error checking.
               This has the effect of encapsulating the definition of a table,
               and the rules that apply to the population of the table, within
               the database definition.  This reduces the development and
               maintenance overhead for applications using the data.

               The contents of a check constraint are similar to the WHERE
               clause of a Select statement.  It is advisable to give the check
               constraints meaningful names, as the name will appear in any
               error messages that result from attempts to violate the
               constraint criteria.

               Check Clause examples

               Rules for Check Constraints

4.  Table Reference

               Referential Constraints allow the contents of columns in a table
               to be validated against the contents of columns in another
               table.  For example, you may want to prevent employees being
               added to an Employee table unless they have a value for WORKDEPT
               which is contained in the DEPTNO column of the DEPARTMENT table.

               The table containing the valid values which you wish to
               reference is known as the parent table.

               To create a table reference, you first select the parent table.
               The columns in the primary key of the parent table will be listed.

               For each of these columns you must identify a matching column in
               the table being created.  To do this, select a key column from
               the list, and then select a match from the list of potential
               matches supplied.  Repeat this for each referenced key column.

               The columns in the list of selected matches together form a
               foreign key for the table being created.

               You can create multiple constraints from a single table to
               multiple parent tables.  It is advisable to name the
               constraints, as the constraint name will appear in the error
               message resulting from any attempt to violate the constraint.

               You can also supply a comment for the constraint by entering
               this in the appropriate cell of the listbox.

    Table Schema

               The list of table schemas includes all schemas in the database
               which contain keyed tables.  Select from the list the schema
               containing table to which you wish to create a reference.

    Table Name

               The list of table includes all tables within the selected
               schema.  The authorization ID being used to create the table
               must have REFERENCES privilege on the table selected.

    Referenced Key Columns

               When a table has been selected, the primary key columns of the
               table will be displayed in this column.  Select each of the
               columns in turn.

    Potential Matches

               When a column has been selected from the Referenced Key Columns,
               this list will include all columns in the current table with a
               data type compatible with the selected Referenced Key Column.
               If your database definition is well ordered, it is likely that
               the columns will have matching names and data types.

    Selected Matches

               When you have selected a column from the potential matches, this
               will appear in the list of selected matches, in the equivalent
               position to the matching Referenced Key Column.

    Referential Constraint Rules

    Delete Rule

               Select the appropriate rule from the list.

    Update Rule

               Select the appropriate rule from the list.

Panel Pushbuttons

Create DB2 Table - General

Create DB2 Table - Column Details


ΓòÉΓòÉΓòÉ 2.4.1. Check clause examples ΓòÉΓòÉΓòÉ

1.  Ensure that only a range of values can be entered in column DEPT of the
    table being created.

        DEPT BETWEEN 10 AND 100

2.  Ensure that the values of column JOB in the table being created are in the
    specified list.

        JOB IN ('Sales','Mgr','Clerk')

3.  The following would NOT be valid, as subqueries are not allowed

        WORKDEPT IN (SELECT DEPNO FROM DEPARTMENT)

The valid examples shown above illustrate what should be entered in the entry
field for check clauses.  The extra syntax required for the Create Table SQL
statement will be generated by the Visualizer Create DB2 Table utility.


ΓòÉΓòÉΓòÉ 2.4.2. Rules for Check Constraints ΓòÉΓòÉΓòÉ

A check-condition is a search-condition except as follows:

o   A column reference must be to a column of the table being created.

o   It cannot contain any of the following:

    -   subqueries

    -   column functions

    -   variant user-defined functions

    -   user-defined functions using the EXTERNAL ACTION option

    -   user-defined functions using the SCRATCHPAD option

    -   host variables

    -   parameter markers

    -   special registers

    -   an alias


ΓòÉΓòÉΓòÉ 2.4.3. Referential Constraint Rules ΓòÉΓòÉΓòÉ

When a referential constraint is being defined as part of table creation using
the Visualizer Create DB2 Table utility, the table being created is known as
the dependent table. A referential constraint prevents data values being placed
in the columns of the dependent table which do not match existing primary key
values in the referenced table. However, the situation can arise where changes
are made to the referenced (parent) table, which would result in values
existing in the referenced table which could not have been created in the
dependent table because of the constraint rules. This situation can exist when
one or more rows are deleted from the parent table, or when the values of
primary key column(s) for one or more rows are updated. DB2 databases provide a
choice of actions for when these circumstances arise:

o   No Action

    Do not allow the update or deletion of the row in the parent table.

o   Restrict

    Do not allow the update or deletion of the row in the parent table.

o   Set Null

    Available for DELETE only.  Set all nullable columns of the foreign key of
    the dependent table to NULL.

o   Cascade

    Available for DELETE only.  Delete the matching set of rows from the
    dependent table.

Note:  There is a subtle difference between NO ACTION and RESTRICT which is
explained in the explanation for CREATE TABLE in the SQL Reference Manual.


ΓòÉΓòÉΓòÉ 2.4.4. Panel Pushbuttons ΓòÉΓòÉΓòÉ

The main panel pushbuttons are available from all sections of the main panel.
These have the following effects:

OK

           Apply the current 'Create Table' and associated SQL statements to
           the selected database.  If this is successful, exit the panel.  If
           not successful, report the error and keep the panel open to allow
           the user to take corrective action.

Apply

           Apply the current 'Create Table' and associated SQL statements to
           the selected database.  Keep the panel open and populated with the
           current details.

           Note:  If the schema and name of a table being created are the same
           as those of an existing table, you will be prompted to overwrite the
           existing table.  You will also be given the opportunity to save the
           definition of the existing table to a text file before overwriting
           it. If the creation of the new table is unsuccessful, the previous
           trigger definition will be restored.

Save Text

           Save the current 'Create Table' and associated SQL statements to a
           text file.  The standard files dialog will be invoked to allow
           specification of the target file name.

Cancel

           End the panel.


ΓòÉΓòÉΓòÉ 3. Trigger Maintenance Capabilities ΓòÉΓòÉΓòÉ

The trigger function is a powerful new capability of DB2/2 Version 2 and
DB2/6000 Version 2.  The Visualizer Trigger Utility provides a mechanism for
creating and maintaining trigger definitions using a simple, end user
interface. This tool provides the following capabilities:

o   Create a trigger based on

    An empty template

    An existing trigger in a database

    An SQL Create Trigger statement

o   Apply a trigger to a database

o   Save a trigger in text form as

    A Visualizer SQL Statement

    A Plain text SQL Statement

    Note:  Both of these can be used as input to the DB2 command line processor

o   Rapidly apply identical or similar triggers to multiple databases

o   Use existing Visualizer SQL Statements or Queries to assist in the trigger
    definition.

Visualizer Create DB2 Trigger Utility


ΓòÉΓòÉΓòÉ 3.1. Visualizer Create DB2 Trigger Utility ΓòÉΓòÉΓòÉ

With the Visualizer Create DB2 Trigger Utility, you can create a completely new
trigger, or you can model the trigger on an existing trigger.

If you choose to model the trigger on an existing trigger, you can choose the
model trigger either from the trigger definition in a DB2/2 or DB2/6000
database, or from SQL previously saved from this utility.

To choose the source of your model trigger, select one of the values of the
value set:

o   Existing Trigger

    This option allows you to choose a trigger definition from a DB2/2 or
    DB2/6000 database.

    Having selected this option, you should then select the trigger which you
    wish to use as a model.

    Database

    An entry will appear in the list for each database catalogued on the local
    node.  Although you can select any of these databases, usable trigger
    definitions will only exist in DB2 family databases equivalent to DB2/2 and
    DB2/6000 or later. Select the database from which you want to select the
    trigger.

    Trigger Schema

    An entry will appear in the list for each trigger schema in which trigger
    definitions exist.  Select the schema for the trigger with which you wish
    to work.

    Trigger Name

    An entry will appear in the list for each trigger which exists in the
    schema you selected.  Select the name of the trigger with which you wish to
    work.

o   Saved Text File

    This option allows you to choose a file of SQL previously saved from this
    utility.  You can use this mechanism if have stored an uncompleted trigger
    definition, and wish to work with that definition.

    Having select this option, you should enter the path and file name of the
    file containing the SQL with which you wish to work.  Click on the
    pushbutton to use the files dialog to locate the file.

o   Template

    This option allows you to create a new trigger from an empty definition.

When you have created a new trigger definition using any of the methods above,
you can choose to save the trigger using any of the mechanisms supplied on the
Create DB2 Trigger Details panel, which will be started when you press OK from
this panel.


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

This panel allows you to enter the details of the trigger you wish to create.
The panel can be divided into 5 major sections. For further explanation see the
explanation of the Create Trigger statement in the DB2 Version 2 SQL Reference
which is supplied on line with DB2 products.

1.  Identification of the Trigger Definition

    This section consists of the following entry fields:

    Database

               Select the database in which you wish to create the new trigger
               from the list of locally catalogued databases.

    Trigger Schema

               Enter the schema in which the trigger should be created.  This
               will default to the current authorization ID.  Unless the
               current authorization ID has DBADM authority, you should not
               specify a value here.

    Trigger Name

               Enter the name with which the trigger definition should be
               created.

    Trigger Comment

               Enter up to 254 characters of information which will represent a
               description of the trigger.

2.  Defining How to Apply the Trigger

    This section consists of the following fields.

    A single SQL statement being executed against the database may affect more
    than one row in a table. Select:

    To each row

               if you wish the trigger conditions to be tested and actions to
               be executed once for each row.

    To each statement

               if you wish the trigger conditions to be tested and actions to
               be executed only once for the statement.

The trigger may be applied either before or after the database changes. Select:

    Before database changes

               if you wish the trigger conditions to be tested and actions to
               be executed before database changes have been made. If you
               choose this option, any updates generated by the trigger actions
               WILL NOT activate triggers which would normally be updated by
               those updates.

    After database changes

               if you wish the trigger conditions to be tested and actions to
               be executed after database changes have been made. If you choose
               this option, any updates generated by the trigger actions WILL
               activate triggers which would normally be updated by those updates.

3.  Defining the conditions that activate the trigger

    This section consists of the following entry fields.

    Table Schema

               The list provided contains all schemas in the database with
               tables.  This list may include schemas which contain tables on
               which the current authorization ID does not have the authority
               required for trigger creation.

    Table Name

               The list provided contains all table that exist in the selected
               schema. This list may include tables on which the current
               authorization ID does not have the authority required for
               trigger creation.

    Action

               Select one of the following actions:

    Insert     The trigger conditions will be tested and actions executed
               whenever a row is inserted into the specified table.

    Delete     The trigger conditions will be tested and actions executed
               whenever a row is deleted from the specified table.

    Update     The trigger conditions will be tested and actions executed when
               a row is updated in the specified table.  For this option you
               may select specified columns to activate the trigger.

    Column Names

               You may select one or more column names from the list.  If you
               select column names, the trigger conditions will be tested and
               actions executed only when the specified columns are updated.
               If no columns are selected, the trigger will be activated by
               updates to any column in the table.

    Additional Conditions

               The additional conditions that may be entered here are similar
               to those that can be used in the where clause of an SQL Select
               statement.  Click on the  button to open the Create DB2 Trigger
               Conditions panel.

               The actions to be taken when the trigger is activated

               This sections consists of the following entry fields:

    SQL statements to be executed

               The SQL statements that may be entered here are similar to those
               compatible with Execute Immediate processing. Click on the
               button to open the Create DB2 Trigger Actions panel.

4.  Action Pushbuttons

    Select one of the following pushbuttons:

    OK

               To apply the changed trigger to the database and close the
               panel.

    Apply

               To apply the changed trigger to the database and leave the panel
               open.  This option makes it easy to modify the trigger
               definition to create a different trigger, or to apply the same
               trigger to a number of databases.

               Note:  If the schema and name of a trigger being created are the
               same as those of an existing trigger, you will be prompted to
               overwrite the existing trigger.  You will also be given the
               opportunity to save the definition of the existing trigger to a
               text file before overwriting it. If the creation of the new
               trigger is unsuccessful, the previous trigger definition will be
               restored.

    Save Text

               It is recommended that all trigger definitions applied to your
               databases are stored in a saved text file.  If you select this
               option you can save the text as a Visualizer SQL Statement
               object, or as a plain text file.  The saved text can be executed
               directly from the DB2 command line processor, using -tdx option.

    Cancel

               Will prompt for confirmation if changes have been made.  This
               button causes the panel to close, and all unapplied and unsaved
               changes to be abandoned.

    Help

               Invokes this panel.

Create DB2 Trigger Conditions

Create DB2 Trigger Actions


ΓòÉΓòÉΓòÉ 3.1.1.1. Authority Required for Trigger Creation ΓòÉΓòÉΓòÉ

The privileges held by the current authorization ID must include at least one
of the following:

o   SYSADM or DBADM authority.

o   All of the following:

    -   ALTER privilege on the table on which the trigger is defined

    -   SELECT privilege on the table on which the trigger is defined, if any
        transition variables or tables are specified

    -   SELECT privilege on any table or view referenced in the triggered
        action condition

    -   Necessary privileges to invoke the triggered SQL statements specified.


ΓòÉΓòÉΓòÉ 3.1.1.2. Create DB2 Trigger Conditions ΓòÉΓòÉΓòÉ

This panel allows correlation names and temporary table names to be set.
Trigger conditions can also be entered here.

Specify a condition that is true, false, or unknown. The search-condition
provides a capability to determine whether or not a certain triggered action
should be executed for a given row or for the statement (depending on the
granularity of the trigger).

The associated action is performed only if the specified search condition
evaluates as true. If the WHEN clause is omitted, the trigger actions are
always performed.

You can drag a Visualizer SQL Statement onto the data entry area, and use all
or part of that SQL in your trigger conditions.


ΓòÉΓòÉΓòÉ 3.1.1.3. Create DB2 Trigger Actions ΓòÉΓòÉΓòÉ

One or more SQL statements can be entered here. If the trigger is a BEFORE
trigger, then a triggered SQL statement must be one of the following:

o   a fullselect

o   a SET transition-variable SQL statement

o   a signal
If the trigger is an AFTER trigger, then a triggered SQL statement must be one
of the following:

o   an INSERT SQL statement

o   a searched UPDATE SQL statement

o   a searched DELETE SQL statement

o   a signal

o   a fullselect
The triggered-SQL-statement can not reference an undefined transition variable


ΓòÉΓòÉΓòÉ 3.1.1.4. Correlation names and Temporary table names ΓòÉΓòÉΓòÉ

The use of correlation names and temporary table names allows a trigger
definition to access the rows affected by the triggering SQL operation in their
state before and after the triggering SQL operation. Correlation names may only
be used for a trigger defined as FOR EACH ROW.  The correlation names refer to
two sets of transition variables which represent all of the columns of the
table being processed.  These transition variables can be referenced as:

oldname.columname

and
newname.columname

where oldname is the old correlation name specified, newname is the new
correlation name specified and columnname is the name of the equivalent column
in the trigger subject table.

Temporary table names may be used in both FOR EACH STATEMENT and FOR EACH ROW
triggers.  The temporary tables contain the complete set of rows affected by
the triggering SQL operation in their state before and after that operation.

Rules


ΓòÉΓòÉΓòÉ 3.1.1.5. Rules ΓòÉΓòÉΓòÉ

o   None of the OLD and NEW correlation names and the OLD_TABLE and NEW_TABLE
    names can be identical.

o   Only one OLD and one NEW correlation-name may be specified for a trigger.

o   Only one OLD_TABLE and one NEW_TABLE identifier may be specified for a
    trigger.

o   The OLD correlation-name and the OLD_TABLE identifier, can only be used if
    the trigger event is either a DELETE operation or an UPDATE operation. If
    the operation is a DELETE operation, OLD correlation-name captures the
    value of the deleted row. If it is an UPDATE operation, it captures the
    value of the row before the UPDATE operation. The same applies to the
    OLD_TABLE identifier and the set of affected rows.

o   The NEW correlation-name and the NEW_TABLE identifier can only be used if
    the trigger event is either an INSERT operation or an UPDATE operation. In
    both operations, the value of NEW captures the new state of the row as
    provided by the original operation and as modified by any BEFORE trigger
    that has executed to this point. The same applies to the NEW_TABLE
    identifier and the set of affected rows.

o   OLD_TABLE and NEW_TABLE identifiers cannot be defined for a BEFORE trigger.

o   OLD and NEW correlation-names cannot be defined for a FOR EACH STATEMENT
    trigger

o   The scope of each correlation-name and each identifier is the entire
    trigger definition.


ΓòÉΓòÉΓòÉ 4. Creation of User Defined Types ΓòÉΓòÉΓòÉ

DB2/2 Version 2 and DB2/6000 version 2 provide the capability to define
Distinct Data Types.  Distinct data types must be created based on the data
types built in to the database. When a distinct type is created using this
utility, the user is offered the opportunity to create versions of all the
functions that can be used by the base type for use with the user defined
types. The utility allows user selection of the functions required, and
generates the SQL to create all of the functions. The user can tailor the SQL
for individual functions before applying the SQL to one or more databases, or
saving the complete SQL in text form. The text created can be used directly
from the DB2 command line processor, using the -f parameter.


ΓòÉΓòÉΓòÉ 4.1. Create DB2 Distinct Types ΓòÉΓòÉΓòÉ

This panel allows the general details of a user defined distinct type to be
entered.  The panel comprises the following fields:

Database

           The local alias of the DB2 Version 2 database in which the distinct
           type is to be created.

Type Schema

           The schema in which the distinct type is to be created.  If not
           specified, this will default to the authorization ID.  Only
           authorization IDs with DBADM privilege are allowed to created
           distinct types in other schemas.

Type Name

           The name of the distinct type to be created.

From Source Data Type

Name

           A user defined distinct type must be based on a built in type.
           Select the appropriate type from the selection list.

Length

           A distinct type must have a specific length property.  This
           capability allows distinct types to be created for specific types of
           data.  The length should be entered as required.

Scale

           For data types based on DECIMAL, this field represents the number of
           decimal places.  For LOB types, the scale is the unit (K -
           Kilobytes, M Megabytes, G Gigabytes) of length specified in the
           length entries.

Replicate Selected Functions

Replicate All Functions

           The functions which may be replicated by this utility are those
           which accept one or more arguments of the base type.  You may choose
           to replicate all of the functions which meet these criteria, or to
           select from the available functions those that you wish to
           replicate.

Select Functions

           If you chose Replicate Selected Functions above, this button will
           become available to select the functions you wish to replicate,
           using the Select functions panel.  Selections already made may also
           be modified.  Functions that have been selected will appear in the
           selected functions list on the main window.

Selected Functions List

           Double clicking on an entry in the list will open the Edit SQL
           panel, allowing the SQL for that function to be viewed or modified.

Create

           Select this push button to create the selected functions in the
           database.

Save text...

           Select this pushbutton to save the generated SQL to a text file.

Close

           Select the pushbutton to close the window and exit.

Help

           Invoke this help.


ΓòÉΓòÉΓòÉ 4.1.1. Select functions ΓòÉΓòÉΓòÉ

Function Schema

           An entry will appear in the list for each schema in the database
           containing function definitions available for replication for the
           type being created.  When a function schema is selected, each of the
           available functions in the schema will be listed in the listbox
           below.  You can then select the individual functions for the
           selected schema, before selecting any further schemas to process.

Select All

           This pushbutton causes all of the listed functions in the schema to
           be selected for replication.  You may then deselect individual
           functions by clicking on the list box, with the Ctrl key pressed.

Deselect All

           This pushbutton causes all of the listed functions in the schema to
           be deselected for replication.  You may then select individual
           functions by clicking on the list box.

OK

           Saves the selections you have made and closes the selection dialog.

Cancel

           Abandons the selections you have made and closes the selection
           dialog.

Help

           Invokes this help.


ΓòÉΓòÉΓòÉ 4.1.2. Edit SQL ΓòÉΓòÉΓòÉ

SQL for functions

           The panel contains a multi line editable box, which contains the SQL
           that has been generated for replication of a selected function.
           There will be one CREATE FUNCTION statement for each unique function
           generated, with the statements separated by a semi-colon(;).  You
           may edit the SQL to remove, add or alter function definitions to
           your requirements.

OK

           Saves the edited changes as the SQL to be used for the selected
           function, in place of the default SQL generated by the utility.

Validate

           Prepares the SQL against the database, and reports any errors.

Default

           Replaces the SQL that has been updated by the user with the default
           generated by the utility.  This can be done even after the changed
           SQL has been saved using OK.

Cancel

           Abandons any changes made since the panel was opened, and closes the
           panel.

Help

           Invokes this help.


ΓòÉΓòÉΓòÉ 5. Using the DB2 Command Line Processor ΓòÉΓòÉΓòÉ

If you are using DB2 Version 2, you will have access to the DB2 Command Line
Processor(CLP).  This facility allows commands and SQL to be applied to the
database directly from an operating system prompt.  The CLP is invoked using
the command:

db2

The CLP supports many options, which are documented in the DB2 Command
Reference.  However, there are some special considerations when using the CLP
with files generated by the Visualizer DB2 Toolkit.


Specifying an Input File to the DB2 CLP
Running SQL Saved from Create Type or Create Table
Running SQL Saved from Create Trigger
Other Useful DB2 CLP Options
Changing the Terminator for Create Trigger


ΓòÉΓòÉΓòÉ 5.1. Specifying an Input File to the DB2 CLP ΓòÉΓòÉΓòÉ

To use a file with one command or statement per line as input to the CLP, use
the -f option.  For example, to use the file c:\work\sqllines.sql as input to
the CLP:

db2 -fc:\work\sqllines.sql


ΓòÉΓòÉΓòÉ 5.2. Running SQL Saved from Create Type or Create Table ΓòÉΓòÉΓòÉ

SQL statements can be very long, and having the whole of a statement on a
single line can be difficult to manage, and may easily exceed the capabilities
of many text editors.  The DB2 CLP provides the -t option allows SQL to be
split over multiple lines.  This options specifies that a command or  SQL
statement may span lines, and will be terminated by the semi-colon(;)
character.  Text files saved from the Create Table or Create Type utility are
semicolon separated.  To run them use the -t option.  For example:

db2 -t -fc:\toolkit\cretab.sql


ΓòÉΓòÉΓòÉ 5.3. Running SQL Saved from Create Trigger ΓòÉΓòÉΓòÉ

The actions clause of a Create Trigger statement may contain multiple
statements.  When entered, these statement must be separated by semicolons.
SQL is generated in the following form:

....
....
begin atomic
statement1;
statement2;
end

 DB2 allows only the semicolon character to separate action statements in a
trigger.  Because of this, the trigger utility terminates the complete Create
Trigger statement with the tilde (~) character.  The tilde character must be
specified as the terminator to the CLP when you use SQL generated by Create
Trigger.  Do this using the -tdx option:

db2 -fc:\toolkit\cretab.sql -td~


ΓòÉΓòÉΓòÉ 5.4. Changing the Terminator for Create Trigger ΓòÉΓòÉΓòÉ

The terminator character (default tilde(~)) can be changed by altering entry
110 in the file TRGLANG.DAT, which will be in the directory you specified for
installation of the toolkit.  See Translating the Toolkit for further information.


ΓòÉΓòÉΓòÉ 5.5. Other Useful DB2 CLP Options ΓòÉΓòÉΓòÉ

The -v option allows you to see which commands and SQL are being processed by
the CLP.  The -s option causes the CLP to halt in the event of an error.


ΓòÉΓòÉΓòÉ 6. National Language Enablement ΓòÉΓòÉΓòÉ

Each utility has a translatable language file, containing all the text and
messages which it uses.  These files have the following names:

o   TRGLANG.DAT - Create Trigger

o   TABLANG.DAT - Create Table

o   TYPLANG.DAT - Create Type

o   TKTLANG.DAT - Toolkit Launcher (AIX Only)

For OS/2, these files are installed in the directory where you installed the
toolkit, and may be translated to any language of your choice.  If you wish to
support multiple languages concurrently, you can install multiple copies of the toolkit.

For AIX, the toolkit is implemented so that you can support multiple languages
concurrently from a single installation of the toolkit.  Because of this, the
language files are installed in a subdirectory of the toolkit install
directory, matching the locale that was effective when the toolkit was
installed.  If you installed the toolkit under locale En_US, to directory
/home/toolkit, the install process will create s directory /home/toolkit/En_US
and copy the 4 language files listed above to that directory.  The installer
will also copy the help and on line documents to the same directory.  The have
the names:

ftbeas01.INF

You can update these files using a text editor, replacing each line with it's
equivalent in the language of your choice.  Any changes will be reflected as
soon as the equivalent utility is restarted.

If you wish to translate the help text or other documentation, please contact EAS.


ΓòÉΓòÉΓòÉ 7. Definition of Terms ΓòÉΓòÉΓòÉ


ΓòÉΓòÉΓòÉ 7.1. IBM EAS Product Support ΓòÉΓòÉΓòÉ

EAS is the worldwide centre of competence on Visualizer, IBM's query and
decision support family. EAS provides technical and marketing support to IBM
countries.  For further information contact EAS via:

IBM Network EASPS at WINVMD

IBMMAIL    GBIBM8L2 at IBMMAIL

InterNet   [e-mail]

Fax:       [phone] (01 926 410764 from the UK)


ΓòÉΓòÉΓòÉ 7.2. Database 2 for OS/2 ΓòÉΓòÉΓòÉ

Database 2 for OS/2, also known as DB2/2 is the member of the Database 2 Client
Server Family which runs on IBM's OS/2 Operating System.  The product is
available either standalone or as a server.  DB2/2 Version 2 Servers can
support clients running in many environments including DOS, Windows, OS/2, AIX
on Risc System/6000, HP-UX, SUN-Solaris, OS/400, VM and MVS.


ΓòÉΓòÉΓòÉ 7.3. Database 2 for AIX ΓòÉΓòÉΓòÉ

Database 2 for AIX, also known as DB2/6000 is the member of the Database 2
Client Server Family which runs on IBM's AIX Operating System on Risc
System/6000.  The product is available either standalone or as a server.
DB2/6000 Version 2 Servers can support clients running in many environments
including DOS, Windows, OS/2, AIX on Risc System/6000, HP-UX, SUN-Solaris,
OS/400, VM and MVS.


ΓòÉΓòÉΓòÉ 7.4. Referential Constraint ΓòÉΓòÉΓòÉ

See Referential Constraint/Foreign Key.


ΓòÉΓòÉΓòÉ 7.5. Foreign Key ΓòÉΓòÉΓòÉ

See Referential Constraint/Foreign Key.


ΓòÉΓòÉΓòÉ 7.6. Constraint ΓòÉΓòÉΓòÉ

A constraint is a rule that the database manager enforces. There are two types
of constraints:

o   A referential constraint is a logical rule about values in one or more
    columns in one or more tables. For example, a set of tables shares
    information about a corporation's suppliers. Occasionally, a supplier's
    name changes. A referential constraint could be defined stating that when
    one of those tables changes the name of a supplier then the other tables
    automatically have that name changed too.

o   A table check constraint sets restrictions on data added to a specific
    table. For example, it could define the salary level for an employee to
    never be less than Ь20,000.00 when salary data was added to a table
    containing personnel information.


ΓòÉΓòÉΓòÉ 7.7. Structured Query Language ΓòÉΓòÉΓòÉ

Structured Query Language (SQL) is a standardised language for defining and
manipulating data in a relational database.


ΓòÉΓòÉΓòÉ 7.8. DB2 Version 2 ΓòÉΓòÉΓòÉ

DB2 version 2 includes DB2/2 version 2 and DB2/6000 version 2.  Version 2 of
the common server for other operating systems are also included, as these
become available.


ΓòÉΓòÉΓòÉ 7.9. Referential Constraint/Foreign Key ΓòÉΓòÉΓòÉ

A foreign key is a column or set of columns in a table whose values are
required to match at least one primary key value of a row of its parent table.
A referential constraint is the rule that the values of the foreign key are
valid only if:

o   They appear as values of a parent key (primary key), or

o   Some component of the foreign key is null.

The table containing the parent key is called the parent table of the
referential constraint, and the table containing the foreign key is said to be
a dependent of that table.

Referential constraints with a delete or update rule of RESTRICT are enforced
before all other referential constraints. Other referential constraints are
enforced in an order independent manner. That is, the order does not effect the
result of the op.

Referential constraints with a delete or update rule of NO ACTION behave like
RESTRICT in most cases. However, in a complex query there can be a difference.