═══ 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   GBIBM8L2@IBMMAIL.COM 

Fax:       44 1926 410764 (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.