OS/2 Shareware BBS: 18 REXX/18-REXX.zip/vxcliser.zip/VRCS.1/DBADMIN.HLP

OS/2 Help File | 1994-12-23 | 25KB | 731 lines

ΓòÉΓòÉΓòÉ 1. General help ΓòÉΓòÉΓòÉ

The database administrator's main menu contains the following items.
Double-click on any highlighted word for more information.

Database  The Database menu contains items which let you perform operations on
          databases.

Table     The Table menu contains items which let you perform operations on
          tables in a database.

View      The View menu contains items which let you control the database
          administrator's windows.

Options   The Options menu contains items which let you configure the database
          administrator.

Help      The Help provides access to information about the database
          administrator.

For indepth information about the database administrator, see the chapter
called Using the database administrator in the VX-REXX Database Object's Guide.


ΓòÉΓòÉΓòÉ 2. Keys help ΓòÉΓòÉΓòÉ

Ctrl+O    Opens a database

Ctrl+C    Closes a database

Ctrl+Shift+A Creates a database

Ctrl+Shift+D Deletes a database

Ctrl+B    Binds a database

Ctrl+S    Opens the settings notebooks for tables

Ctrl+A    Creates a table

Ctrl+D    Deletes tables

Ctrl+I    Switches to icon view

Ctrl+E    Switches to detail view

Ctrl+M    Toggles between small and normal sized icons

Ctrl+N    Lets you set the include criteria

Ctrl+G    Arranges icons in the main window

Ctrl+R    Refreshes the list of tables

Ctrl+F    Toggles whether tables are listed after a database is opened

Ctrl+L    Sets the list box font

Ctrl+T    Starts logging

Ctrl+P    Stops logging

Ctrl+Shift+Drag Creates foreign key between source and target tables

Dragging onto shredder Deletes tables and views


ΓòÉΓòÉΓòÉ 3. The database administrator Main window ΓòÉΓòÉΓòÉ

The main window lists the tables in an open database. In Icon view, an icon
will be displayed for each table and its caption will show its fully qualified
name. In Detail view, a tabular format is used to display the names, owners,
types and comments for the tables.

If a database is open, its name will be displayed in the caption of the main
window.

See also:

Working with databases
Operating on tables
Setting the display
Configuring the database administrator
The Help menu


ΓòÉΓòÉΓòÉ 4. Working with databases ΓòÉΓòÉΓòÉ

The Database menu contains items to open, close, create, delete and bind a
database.

Opening a database
          Before you can work with a database you must open it using the Open
          menu item. You will be prompted for a database name, userid and
          password. If a database is already open, it will be closed
          automatically before you can open another one.

Closing a database
          After you have finished working with a database, you can close it
          using the Close menu item. Open notebooks will be closed and changes
          will be saved before the database is closed.

          Do not close a database if you are going to shut down the Database
          Administrator and will be working with the same database when it is
          restarted. The database will be closed automatically and reopened the
          next time you start the program.

Creating a database
          Select the Create Another menu item to create a new database. You do
          not have to open it immediately after it has been created if you want
          to continue working with a database which is already open.

Deleting a database
          Use the Delete menu item to delete a database. You do not need to
          close the database you have open if you want to delete it. It will be
          closed automatically.

Binding a database
          Use the Bind menu item to bind IBM DB2/2 databases. This must be done
          before they can be accessed by the Database administrator. This does
          not have to be done for databases created with the Database
          administrator because they are bound automatically. Note that this
          menu item does not apply and will be disabled if the data source is
          Watcom SQL.


ΓòÉΓòÉΓòÉ 5. Operating on tables ΓòÉΓòÉΓòÉ

The Table menu allows you to open the settings notebook for one or more tables.
This notebook allows you modify column definitions, the primary key, foreign
keys, indexes and privileges for a table. You can also look at the data in the
table. Tables can also be deleted and created from this menu.

Opening the Settings Notebook
          Select the Settings menu item after selecting one or more tables to
          open their notebooks. You can also double-click on a table to open
          its notebook.

Creating a table
          Use the Create another menu item to create a table. After you enter
          its name, a settings notebook will pop up allowing you to create its
          columns, foreign keys and indexes.

Deleting tables
          Select one or more tables and then choose the Delete menu item to
          delete them. You can also delete the tables by dragging them onto the
          shredder.


ΓòÉΓòÉΓòÉ 6. Setting the display ΓòÉΓòÉΓòÉ

The View menu is used to control the main window's display. You can switch
between Icon and Detail views, change the icon size and font and restrict the
tables which are listed.

The icon menu item
          Select this menu item to display the list of tables in icon view.
          Each table will have an icon whose caption will show its fully
          qualified name. You will not be able to see the comments for a table
          in this view.

The details menu item
          When this menu item is selected, the list of tables will be shown in
          detail view. You will see the names, owners, types and comments for
          the tables.

Changing the icon size
          Use the Small icons menu item to toggle between normal and miniature
          sized icons for the tables on the main window.

Sorting the list of tables
          Select the Sort menu item and then Ascending or Descending to sort
          the list of tables. In the icon view, the tables will be sorted by
          their captions. In the detail view, the current Sort field will be
          used. By default, they are sorted by name in this view. If you do not
          want any sorting, select None.

Setting the sort field
          In the detail view, the Sort field menu item can be used to control
          which field is used to sort the list of tables. Selecting Name,
          Owner, Type or Comments will sort the list by the respective field
          according to the current setting of the Sort menu item.

Restricting the list of tables
          By default, all user tables and views will be listed on the main
          window. Use the Include menu item to restrict this list. See Help for
          the Include dialog box for more information.

Arranging icons
          In the icon view, the icons for tables can be repositioned. If they
          become scattered, they can be rearranged automatically using the
          Arrange menu item.

Refreshing the table list
          Use the Refresh now menu item to regenerate the list of tables on the
          main window. The list will use the current settings on the Include
          dialog box. Tables whose notebooks are open will not be removed from
          the list even if they do not match the current include settings.


ΓòÉΓòÉΓòÉ 7. Configuring the database administrator ΓòÉΓòÉΓòÉ

The Options menu is used to set the data source and font, and also determine
when tables are listed on the main window. You can turn on the logging feature
from here as well.

Setting the data source
          Select IBM DB2/2 or Watcom SQL after choosing the Data source menu
          item to set the data source. This will indicate which type of
          databases you will be working with. If a database is already open, it
          will be closed automatically. If you select IBM DB2/2, the database
          manager will be started if it is not already running.

Fill table list on startup
          Set this menu item if you want the tables in a database to be
          automatically listed when a database is opened. If it is not set, you
          must list the tables yourself using the Include dialog box which will
          pop up when you open a database.

Changing the font
          Selecting the List box font menu item will bring up a standard font
          dialog box allowing you to set the font used by all list objects in
          the program.

Logging SQL statements
          The Database administrator will place all the SQL statements it
          executes into a file if logging is turned on. Use the Start logging
          menu item to do this. Note that only SQL statements which modify a
          database will be logged. Also, if there are any errors executing a
          statement, the error message returned by the database system will be
          logged.

Stop logging
          Use this menu item when you want to turn off logging. Logging will be
          turned off automatically if you do not it off before shutting down
          the program.


ΓòÉΓòÉΓòÉ 8. The Help menu ΓòÉΓòÉΓòÉ

Use this menu to see the Database Administrator's Guide, general help about the
program, how to use the help facility and information about key sequences used
by the program.


ΓòÉΓòÉΓòÉ 9. The logon dialog box ΓòÉΓòÉΓòÉ

The logon dialog box appears when you select Open from the Database menu, after
you create a new database using the Create another menu item and also when you
start the Database administrator if a database was open when you last shut it
down. It prompts you for information needed to open a database.

Database
          Enter the database name in this field. For Watcom SQL databases, the
          name is just an ordinary OS/2 file name and path. You can also use
          the Find button to search for available databases.

Userid
          Enter your userid in this field. If you are opening an IBM DB2/2
          database then you do not need to enter a userid if you are already
          logged on.

Password
          Type your password in this field. It will be masked. You do not need
          to type in a password if you are opening an IBM DB2/2 database and
          have already logged on.

Connection parameters
          This field only applies when opening a Watcom SQL database. It is
          used to specify the command to start the database engine. It must
          have the form

                    start=<command>

          where <command> is the command to use. For example,

                    start=dbstart -q %d

          will start the database engine in quiet mode. Note that the command
          line must contain %d to indicate where the database name should be
          placed. For more information on starting the database engine see the
          Watcom SQL manual.

Connect
          Press this button when you have finished entering the logon
          information. This will open the database. If a database is already
          open, it will be closed automatically.


ΓòÉΓòÉΓòÉ 10. Creating an IBM DB2/2 database ΓòÉΓòÉΓòÉ

The dialog box used to create an IBM DB2/2 database will appear if the data
source is IBM DB2/2 and you select Create another from the Database menu.

Database
          Enter the database name in this field.

Drive information
          Check the Use DB2/2 drive check box if you want the database to be
          created on the same drive as DB2/2. If you want to use a different
          drive, clear this check box and enter the drive in the Drive spin
          button.

Code page information
          Set the Default code page check box if you want to use the default
          code page. To use some other code page, enter its number in the
          Codepage spin button. Note that code page information is used with
          the comment for the database and therefore, is not required if no
          comment is entered.

Comments
          Enter a description of the database in this field. This information
          is not required.

Collating sequence information
          Set System if you want to use the collating sequence of OS/2 based on
          the country code. This is the default. If you do not want to use a
          collating sequence, set None. To use your own sequence, set User. The
          sequence you want to use must be defined in an OS/2 environment
          variable and you must enter the variable name in the Variable name
          field. If a command file needs to be run in order to set this
          variable, enter its name in the Command file field. Use Find to
          search for this file if necessary.

Create
          Press this button to create the database. This may take some time.
          After it has been created, it will be bound automatically so that you
          can open it immediately. The logon dialog box will come up allowing
          you to open it. The default userid and password will be userid and
          password.


ΓòÉΓòÉΓòÉ 11. Creating a Watcom SQL database ΓòÉΓòÉΓòÉ

The dialog box used to create a Watcom SQL database will appear if the data
source is Watcom SQL and you select Create another from the Database menu.

Database
          Enter the database file name in this field. Use Find to select a file
          name if necessary.

Transaction log file
          Enter the name of the log file in this field. You can use Find to
          select a name for the file. If you want the file to have the default
          name, select <default>. If you do not want to use a log file, select
          None.

Collating sequence
          You can enter the name of a collating sequence or a file name
          defining a sequence in this field. To search for the file, use Find.
          To use the default collating sequence, select <default>.

Options
          Set ANSI blank behaviour if you want string searches in the database
          to ignore trailing blanks. By default trailing blanks are not
          ignored. Set ANSI case behaviour if you want string searches in the
          database to be case sensitive. By default, string searches ignore
          case. For security, you can have the database file encrypted by
          setting Encryption. Adjust the page size using the Page size spin
          button.

Create
          Press this button to create the database. This may take some time.
          After it has been created, the logon dialog box will come up allowing
          you to open it. The default userid and password will be dba and sql.

Default
          Press this button to return to the default values for all the
          creation parameters.


ΓòÉΓòÉΓòÉ 12. Deleting a database ΓòÉΓòÉΓòÉ

Use this dialog box to delete a database.

Database
          Enter the database name in this field. Use Find to find the database
          if necessary.

Delete
          Press this to delete the database. You will be asked for confirmation
          before it is actually deleted.


ΓòÉΓòÉΓòÉ 13. Binding a database ΓòÉΓòÉΓòÉ

Use this dialog box to bind an IBM DB2/2 database. This must be done before it
can be accessed by the Database administrator. Databases created with the
administrator are bound automatically. Note that Watcom SQL databases do not
need to be bound.

The options provided by BINDDBI and DB2/2 are the same as those provided by the
database administrator. There is more indepth information on binding in the
documentation for BINDDBI and DB2/2.

Database
          Enter the database name in this field. Use Find to find the database
          if necessary.

User name
          Enter the name of the user or group whose privileges are being
          granted or revoked. If you enter <default>, PUBLIC will be used.

Grant
          Set this if privileges are to be granted to the specified user.

Revoke
          Set this if privileges are to be revoked from the specified user.

Date & time format
          Use this field to determine the data and time format used when
          datetime fields are assigned strings.  See the documentation for
          BINDDBI for more information.

Record blocking
          Use this field to specify the type of blocking and how to treat
          cursors. See the documentation for BINDDBI for more information.

Isolation level
          Use this field to specify how DB2/2 isolates data (from other
          processes) which is in use by VX-REXX.  See the documentation for
          BINDDBI for more information.

Default
          Press this to return to the default bind values.

Bind
          Press this when you are ready to perform the bind.


ΓòÉΓòÉΓòÉ 14. Finding an IBM DB2/2 database ΓòÉΓòÉΓòÉ

This dialog lists all the databases in the system catalogue. Select a database
from the list and press OK. You can also double-click on a database in the
list.


ΓòÉΓòÉΓòÉ 15. Deleting tables and views ΓòÉΓòÉΓòÉ

This dialog box appears when you select one or more tables and choose Delete
from the Database menu. It will also come up if you drag tables onto the
shredder. It will list all the tables you selected. Deselect any tables you do
not want to delete and then press Delete. You can press Cancel if you do not
want to delete any tables. Note that if any system tables or system views were
selected, they will not be listed because they cannot be deleted.


ΓòÉΓòÉΓòÉ 16. Restricting the list of tables ΓòÉΓòÉΓòÉ

Use this dialog box to restrict which tables are listed in the main window.
Enter a table mask in the Mask field. Then set the Tables, Views, System tables
and System views fields appropriately to control which types of tables are
listed. Press OK or close the dialog to apply the changes.

If the notebook for a table is open, it will remain on the main window even if
it does not match the include criteria. When a database is closed, the criteria
will be reset to the default values which list all user tables and views but no
system tables or system views.


ΓòÉΓòÉΓòÉ 17. Columns page ΓòÉΓòÉΓòÉ

The Columns page displays the column definitions for a table in a list at the
top. Name, Type, Width, Scale and Comments show the name, data type information
and comments for the columns. Other can be one of Allow nulls, Not null or
Primary key. The detailed information for the currently selected column is
displayed in the group box below.

To modify a column, select it from the list at the top and make changes to it
below. Then press Modify. You can use Undo to undo the changes you have made to
the column. To add a column, enter a new column name in the Name field and the
column information in the remaining fields. Then press Add. If you want to copy
a column definition from another table, open the notebook for that table and
drag the column you want into the list at the top of the page.

To change the order of columns, drag and drop them within the list at the top.

Name
          This field displays the name of the currently selected column. You
          can modify the name for an existing column or enter the name for a
          new column.

Data type information
          The Type field displays the data type and also lists all the data
          types supported by the database system. The Width and Scale fields
          display the remaining type information if applicable.

Comments
          Use this field to see and modify the comments for a column.

Other column information
          One of Allow nulls, Not null or Primary key will be set to indicate
          if the column disallows null values and if so, if it is part of the
          primary key.

Add
          Press this button to add a column to the table. It will be added at
          the end of the list. Note that this button only becomes enabled when
          a change has been made to the Name field.

Modify
          Press this button to change the definition of the column which is
          selected in the list at the top of the page. Note that it will only
          be enabled when a change has been made to the column definition.

Delete
          Press this button to delete the currently selected column.

Undo
          Use this to undo changes to the current column.

Apply
          The Add, Modify and Delete buttons do not actually apply the changes
          to the database itself. This button must be pressed to make the
          changes. Note that changes will also be applied automatically if you
          turn the page or close the notebook. Some changes may require the
          table to be deleted and rebuilt. You will be warned in such a case.

Reset
          Press this button to reset the page to match the database. Any
          changes you have made which haven't been applied will be lost.

See also:

Primary key page


ΓòÉΓòÉΓòÉ 18. Table page ΓòÉΓòÉΓòÉ

The Table page displays the owner, type, name and comments for a table. The
name and comments can be changed from the Name and Comments fields.

If the type is view or system view, the SQL statement used to create the view
will be displayed in the View definition field. Note that this field is read
only.

Press Apply to apply any changes you make on this page. If you turn the page or
close the notebook without applying changes, they will be applied
automatically. Note that if your data source is IBM DB2/2 and you change the
table name, the table must be deleted and rebuilt. You will be asked for
confirmation before this is done.

Press Reset to reset the page to match the database. Any changes which have not
been applied will be lost.


ΓòÉΓòÉΓòÉ 19. Primary key page ΓòÉΓòÉΓòÉ

The Primary key page is used to control which columns are part of the primary
key and also the order of these columns within the key.

The Primary key list shows which columns are in the primary key. They are
listed by their order within the key. The Available columns list shows the
remaining columns in the table. Only columns which disallow null values will be
listed here.

To add a column to the primary key, select it from the Available columns list
and press Add. To remove a column from the primary key, select it from the
Primary key list and press Remove. You can also drag and drop columns between
these lists.

If your data source is IBM DB2/2, you can reorder the columns in the primary
key by dragging and dropping them within the Primary key list. If your data
source is Watcom SQL, the primary columns will always be listed by their order
within the table because this is their order within the primary key.

Press Apply to apply changes. Changes are also applied when the notebook is
closed or the page is turned. You will be asked for confirmation before changes
are applied because making changes to the primary key deletes all foreign keys
referencing this table.

Press Reset to reset the page to match the database. Any changes which have not
been applied will be lost.


ΓòÉΓòÉΓòÉ 20. Data page ΓòÉΓòÉΓòÉ

This page lists the data in the table. Initially the page will be empty. Press
Refresh to see the data. You must also press this button whenever you want to
refresh the list to match the database.


ΓòÉΓòÉΓòÉ 21. Foreign key page ΓòÉΓòÉΓòÉ

The foreign key page lets you create, modify and delete foreign keys. The role
name, parent table name and delete rule for the foreign keys are listed at the
top of the page. If a foreign key is selected from this list, its details are
displayed below in the group box. You will be able to see the columns from the
parent table and the columns from this table (called the child table) in the
list below.

You can make changes to the role name in the Role field. Use the Parent field
to change the parent table. You can use the Find button to search for a table.
Also, tables in the main window can be dragged and dropped into Parent field.
Note that changing the parent table will remove all the columns from the
foreign key.

Use Restrict, Cascade and Set null to set the delete rule. If your data source
is Watcom SQL, Cascade and Set null will be disabled because the restrict
delete rule is the only one supported.

To remove columns from a foreign key, select an entry from the list of columns
and press Remove. You can also drag the entry onto the shredder. To add columns
to a foreign key, use the Insert button. Note that this button will be disabled
if there are no available columns in this table or the parent table.

When you have finished making changes to the currently selected foreign key,
press Modify. You can use Undo to undo the changes in the group box. To create
a foreign key, enter a role name in the Role field and the remaining
information and then press Add. The Add button will be enabled only if the role
name has been changed.

Note:  If your data source is IBM DB2/2 then the Add and Modify buttons will be
enabled only when all the columns in the primary key of the parent table have
been used.

To delete the foreign key which is selected, press Delete. You can also drag
the foreign key from the list at the top onto the shredder.

When you have finished making changes on this page, press Apply to apply the
changes to the database itself. Changes you make will also be applied to the
database if you close the notebook or turn the page. Press Reset to make the
page match the settings in the database. Any changes that have not been applied
will be lost.


ΓòÉΓòÉΓòÉ 22. Finding a table ΓòÉΓòÉΓòÉ

Enter a table name or mask in the Name field and press <Enter> or OK. Tables
which match the name you entered will be listed in the Tables list. Initially,
the Name field will be set to the mask used by the main window. If the Fill
table list on startup option is set, the tables matching this mask will be
listed immediately.

If there is only one match, it will be chosen automatically as the parent
table. Otherwise, you can double-click on a table in the list or select it and
press OK to make it the parent table.


ΓòÉΓòÉΓòÉ 23. Inserting columns into a foreign key ΓòÉΓòÉΓòÉ

Use the Insert columns dialog box to insert columns into a foreign key. Select
a column from the child table using the Child columns list. Select a column
from the parent table using the Parent columns list. Press OK to match the two
columns you have chosen and insert them into the foreign key.


ΓòÉΓòÉΓòÉ 24. Index page ΓòÉΓòÉΓòÉ

The Index page displays the name, owner, unique setting and columns of each
index in the list at the top. The columns are placed one after another, in
their order within their index. Columns which are ascending are preceded by +
and those which are descending are preceded by -.

Selecting an index from the list at the top will display its details in the
group box below. Changes can be made to the index name in the Name field. The
Owner field is read only. Use the Unique field to set the unique attribute for
the index.

The columns in the index can be reordered by dragging and dropping them within
the Columns list. Double-click on a column to toggle its sort order. To add a
column to the index use the Insert button. To remove a column from the index,
select it and press Remove. You can also remove columns by dragging them from
this list onto the shredder.

When you have finished making changes to an index, press Modify. You can use
Undo to undo the changes in the group box. To create a new index, enter its
name in the Name field and then the remaining information. Then press Add. The
owner for any index you create or modify will be set to your userid.

To delete an index, select it from the list at the top and press Delete. You
can also drag the index from the list onto the shredder.

When you have finished making changes on this page, press Apply to apply the
changes to the database itself. Changes you make will also be applied to the
database if you close the notebook or turn the page. Press Reset to make the
page match the settings in the database. Any changes that have not been applied
will be lost.


ΓòÉΓòÉΓòÉ 25. Insert a column ΓòÉΓòÉΓòÉ

Use this dialog to insert a column into an index. Columns which are available
are listed. Select the column you want to insert. Double-click on it if you
want to change its sort order. Press OK when you are done.


ΓòÉΓòÉΓòÉ 26. Privileges page ΓòÉΓòÉΓòÉ

The Privileges page displays all user ids who have access to the database in a
list at the top. It also displays the privileges they have on the table. If
your data source is IBM DB2/2 then the columns will contain Yes or No. If your
data source is Watcom SQL then the columns can also have the value Grant to
indicate that a privilege is held with the grant option.

Note:  The user ids in the list can refer to groups as well as individual
users. The privileges shown are those given directly to the user ids in the
list. If a privilege has been granted to a group but not to a member of that
group, then the list will not contain the actual privilege information for the
group member.

Selecting a user id from the list will display its privilege information in
detail below. The user name will be shown and the appropriate privilege fields
will be set. The with grant fields will be enabled only if your data source is
Watcom SQL. They are used to indicate if a privilege is held with the grant
option. The Index and Control fields will be enabled only if your data source
is IBM DB2/2.

To modify privileges, select a user id from the list, set the appropriate
fields and press Modify. You can also press Undo to undo the changes to the
current user. When you have finished making changes press Apply to apply the
changes to the database. The changes will also be applied if you close the
notebook or turn the page. Press Reset to make the page match the database. Any
changes which have not been applied will be lost.


ΓòÉΓòÉΓòÉ 27. Packages page ΓòÉΓòÉΓòÉ

The Packages page appears in the settings notebook only if your data source is
IBM DB2/2. It lists the name, owner and binder of each package in the database
which depends on the table. You can use the Refresh button to refresh this list
to match the database.


ΓòÉΓòÉΓòÉ 28. Views page ΓòÉΓòÉΓòÉ

The Views page appears in the settings notebook only if your data source is IBM
DB2/2. It lists the name, qualifier, and creator of each view in the database
which depends on the table. You can use the Refresh button to refresh this list
to match the database.