═══ 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= where 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 . 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 . 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 , 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 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.