NonStop dba/m Database Manager for NonStop SQL/MP User’s Guide Abstract This manual describes dba/m Database Manager, a client-server product designed to manage Compaq Nonstop SQL/MP databases. This manual provides procedures for using dba/m Database Manager to manage database operations as well as database objects such as catalogs, tables, views, indexes, partitions, and programs. Product Version dba/m 6.
Document History Part Number Product Version Published 422966-001 dba/m 4.0 426957-001 dba/m 4.1 522632-001 dba/m 5.0 December 2001 522632-003 dba/m 5.0 May 2002 522632-004 dba/m 6.0 August 2002 522632-005 dba/m 6.
Copyright Information Copyright 1996-2002 by Genus Software, Incorporated. All rights reserved. Trademark Acknowledgments NonStop dba/m Database Manager for NonStop SQL/MP, Himalaya and NonStop SQL/MP are trademarks of HP Computer Corporation. Microsoft is a registered trademark of the Microsoft Corporation. Windows is a trademark of Microsoft Corporation.
PREFACE Welcome to dba/m Database Manager, a client-server product designed to manage NonStop SQL/MP databases. This manual provides procedures for using dba/m Database Manager to manage database operations as well as database objects such as catalogs, tables, views, indexes, partitions, and programs. How This Manual Is Organized The dba/m Database Manager User’s Guide contains procedures for using dba/m Database Manager, and managing database operations and database objects.
Conventions and Definitions Used in This Manual Most dba/m Database Manager tasks are executed by completing the fields in a full-screen window. Window names are capitalized, for example, the Create Table window. A pane is the portion of a widow that contains related fields. The fields in a pane are often grouped inside a box and a name. Pane names are capitalized, for example, the Security pane. Fields in a window or pane are identified by names or by icons.
Variable names are italicized and enclosed in brackets, for example, . Substitute the appropriate name for your installation. Technical Support For technical assistance, Contact GCSC by calling 1-800- 282-6672 or by going to HP Support site http://support.himalaya.compaq.com/ References This manual does not provide information on HP’s NonStop SQL/MP.
CONTENTS AT A GLANCE PART I: USING dba/m Database Manager Chapter 1. Chapter 2. Chapter 3. Chapter 4. Chapter 5. Invoking dba/m Database Manager Using the Main Window Using the Object Tree Managing dba/m Database Manager Profiles Using dba/m Database Manager Commands PART II: MANAGING DATABASE OPERATIONS Chapter 6. Chapter 7. Chapter 8. Chapter 9. Chapter 10.
CONTENTS CHAPTER 1. INVOKING dba/m Database Manager.......................... 1-1 Logging On .......................................................................................... 1-1 Setting.................................................................................................. 1-3 Logging Off (Exit)................................................................................ 1-5 Licensing Issues ...................................................................................
Managing the dba/m Database Manager Profile.................................... 4-2 Change dba/m Database Manager Profile .......................................................... 4-2 Add Nodes........................................................................................................ 4-3 Delete Nodes .................................................................................................... 4-3 Managing User Profiles ...................................................................
Choose a Query ................................................................................................ 7-3 Display Plan Details .......................................................................................... 7-4 Display Statistics............................................................................................... 7-5 Table Statistics.................................................................................................. 7-6 Simulate Production Statistics.............
Delete History ...................................................................................... 9-3 Display Commands .............................................................................. 9-3 CHAPTER 10. MANAGING ITEMS AND BATCHES .................. 10-1 Managing Items.................................................................................. 10-1 Create an Item ................................................................................................ 10-1 Edit an Item............
Define File Attributes...................................................................................... 12-3 Choose Table Format...................................................................................... 12-4 Choose Partition Array: .................................................................................. 12-4 Add Columns.................................................................................................. 12-5 Change Column Attributes ...................................
Managing Table Statistics..................................................................12-30 Choose the Tables......................................................................................... 12-31 Display the Statistics ..................................................................................... 12-31 Display Dependencies ................................................................................... 12-33 Update the Statistics ......................................................
Save New View ............................................................................................ 13-15 Renaming a View ..............................................................................13-15 Choose the View ...............................................................................13-16 Display Dependencies .......................................................................13-16 Change the Name....................................................................................
Dropping an Index.............................................................................. 14-8 Choose an Index ............................................................................................. 14-9 Display Dependencies ..................................................................................... 14-9 Drop the Index.............................................................................................. 14-10 Renaming an Index...................................................
Choose a Table ............................................................................................. 15-14 Select the Partition........................................................................................ 15-15 Assign Name and Location............................................................................ 15-15 Assign File Attributes.................................................................................... 15-16 Display Dependencies .........................................
Drop Partitions ..................................................................................16-11 Choose Partition: .......................................................................................... 16-12 Display Dependencies ................................................................................... 16-12 Drop the Partitions........................................................................................ 16-13 Perform a Simple Move..............................................
Renaming Programs ..........................................................................17-13 Choose the Program ..................................................................................... 17-14 Enter the Name............................................................................................. 17-14 Save the Program Name................................................................................
PART I: USING dba/m Database Manager Part I is organized as follows: • Chapter 1, Invoking dba/m Database Manager. Describes how to log on to and off of dba/m Database Manager. • Chapter 2, Using the Main Window. Describes the dba/m Database Manager command buttons and menu buttons. • Chapter 3, Using the Object Tree. Describes the Object Tree and explains how to display nodes, volumes, and objects in the Object Tree. Describes how perform database administration functions using the Object Tree.
CHAPTER 1. INVOKING dba/m Database Manager To invoke dba/m Database Manager, double-click on the dba/m Database Manager icon in the DBAM program group. To perform any dba/m Database Manager functions, you must establish a session with the Nonstop SQL/MP database. Logging On When you invoke dba/m Database Manager, the Logon window is displayed, as shown in Figure 1-1. Figure 1-1. Logon Window. To establish a session 1. In the User ID field, enter your Guardian user ID in the format .
2. In the Password field, enter your password. Asterisks are displayed as you type, instead of the actual password. 3. Choose a refresh mode by clicking on one of the following buttons: • Refresh: dba/m Database Manager retrieves SQL-object information stored on the NonStop node. The refresh operation might take a long time, based on the number of objects on the node. • NoRefresh: dba/m Database Manager retrieves SQL-object information from files stored on the workstation.
Figure 1-2. Loading Object Tree Window Setting When user click on Setting button Setting window will appear as shown below.
Figure 1-3 Logon Settings Window User can enter following information as a part of settings User Information Setting 1. IP Address. Enter IP-Address of system. 2. Port. Enter Port on which dba/m pathway servers are configured. 3. Reply buffer size. Enter reply buffer size. 4. Batch Retention Time. Enter Batch Retention time. 5. Monitor Polling time. Enter Monitor Polling time interval here. Notifier Information 1. Email id. Enter default user id 2. Password . Enter password here. 3. Port.
Logging Off (Exit) To end a dba/m Database Manager session, click on Exit at the top left corner of the Main window. When you log off, the following events occur: • If you are monitoring disk-space usage, the monitor process ends. • If you are working within an active transaction, you are asked to abort (roll back) or end (commit) the transaction. Refer to Chapter 6, Managing Transactions, for more information. • All batches are executed as scheduled.
1-6 Invoking dba/m Database Manager
CHAPTER 2. USING THE MAIN WINDOW When you successfully establish a session, dba/m’s Main window is displayed, as shown in Figure 2-1. The Main window has two sets of buttons: • Command buttons, displayed across the top of the window • Menu buttons, displayed down the left side of the window Figure 2-1 dba/m Database Manager Main Window. Using dba/m Database Manager Commands Command buttons are displayed across the top of the Main window.
• Custom Tree. Displays a customized version of the Object Tree. You may perform most of the database administration functions on the objects in the Custom Tree from this display. Refer to Chapter 3, Using the Object Tree, for detailed instructions. • DirectCmd. Displays a window that allows you to enter and execute most of the commands processed by HP’s SQLCI utility. • CreateBatch. Submits commands generated by dba/m Database Manager as batches.
When you click on Error, the Error window is displayed, as shown in Figure 2-2. Figure 2-2. Error Message Window. Use the Error Code Input pane to get detailed information on error codes. To get an explanation of your error code 1. Enter the error code in the Error Number field. 2. Choose the source of Subsystem reporting the error by clicking on one of the buttons in the Error Type pane at the top of the window.
Find Button Use the Find command button in the Main window to find the object in the database. This is usual find menu item as shown in Figure below 2.3. Press Help button for getting help on this item. Figure 2-3. Find Pane. Using the dba/m Database Manager Menu dba/m Database Manager menu buttons are located on the left side of the dba/m Database Manager Main window. Clicking on menu buttons displays additional menu buttons from which you can access functional windows.
• Custom Tress. . Displays a customized version of the Object Tree. You may perform most of the database administration functions on the objects in the Custom Tree from this display. User can create multiple custom trees. Refer to Chapter 3, Using the Object Tree, for detailed instructions. • Catalogs. Displays catalog-management menu buttons that allow you to create, alter, drop, and upgrade catalogs. Refer to Chapter 11, Managing Catalogs, for detailed instructions. • Tables.
CHAPTER 3. USING THE OBJECT TREE When you log on to dba/m Database Manager for the first time, all SQL Objects in your environment are retrieved from the Nonstop SQL/MP database and stored on the client in the form of an Object Tree. To display the Object Tree, click on Object Tree in the Main window. The Object Tree consists of • A graphic representation of nodes with their volumes and database objects such as catalogs, tables, views, indexes, partitions, and programs.
Figure 3-2. Table Names in the Object Tree. Displaying Items in the Object Tree You can display all of the volumes in a node, catalogs in a volume, or objects in a catalog. You can also erase the display of all volumes, catalogs, or objects. Volumes If the volumes in a node are not displayed double click on the Node icon to view all of the volumes in the node. To erase the display of the volumes, double click on the Node icon.
Tables If the tables in a catalog are not displayed, click on the Tables icon under the catalog icon. The names of all the tables in the catalog are displayed in tabular form, as shown in Figure 3-2. To erase the display of all table names, click on the volume or catalog icon. Views If the views in a catalog are not displayed, click on the Views icon under the catalog icon. The names of all the views in the catalog are displayed in tabular form.
• Monitor disk-space use of volumes, tables, indexes, and partitions. You must select an object before you can work with it. To select an object, click on the object icons for nodes, volumes, and catalogs as shown in Figure 3-1. When an object icon is selected, the background color is blue.
Manage Catalogs You can refresh catalogs in the Object tree, or you can perform catalogmanagement functions such as alter, create, alter, drop, and upgrade catalogs. If the catalog is not already selected, click on the Catalog icon in the Object Tree. The following buttons in the Object window are highlighted: Refresh, Create, Alter, Drop and Upgrade. Perform one of the following: • To retrieve the objects from the catalog and update the Object Tree, click on Refresh.
Manage Views Using the Object Tree, you can perform all view-management functions such as create, alter, drop, invoke, or rename a view. You can also add, change, or delete help text, view comments, and column comments for a view. 1. If the View icon under the Catalog icon that contains the view is not already selected, click on the View icon. The names of all views in the catalog are displayed. 2.
Manage Programs Using the Object Tree, you can perform the program management functions such as alter, drop, compile, rename, copy, or move a program. 1. If the Program icon under the Catalog icon that contains the program is not already selected, click on the Program icon. The names of the programs in the catalog are displayed. 2. Select one of the program and right click on it, and the following buttons are highlighted in the Object window: Alter, Drop, Compile, Rename, Copy/Move. 3.
Figure 3-3. Custom Tree Selection Option Window To create a new custom tree select option, “Create New Custom Tree”, and click OK command button. The Custom Tree Selection Window is displayed as shown in Figure 3-4. Figure 3-4. Custom Tree Selection Window Add an Object To add an object to your Custom Tree 1. Click on the custom Trees, Then Click on Open. 2. Custom Tree Selection window will appear. 3. Select existing custom tree if any or Select create new custom tree and click Ok. 4.
5. Repeat Step 1 to include additional objects in the Custom Tree. 6. Click on Save button in custom Trees menu to save custom tree. Delete custom tree To delete custom tree Click on delete button in Custom Trees.
CHAPTER 4. MANAGING dba/m Database Manager PROFILES For ease of use, dba/m Database Manager provides windows for super users to manage dba/m Database Manager configurations. A super user is any user with a group ID of SUPER.
Managing the dba/m Database Manager Profile The dba/m Database Manager profile contains configuration parameters. Use the dba/m Database Manager Profile window to change the dba/m Database Manager profile and add or delete nodes. You must be a super user to access the dba/m Database Manager Profile window. To display the dba/m Database Manager Profile window, click on Profile in the Main window, then click on Options. The dba/m Database Manager Profile window is displayed, as shown in Figure 4-1.
1. Click on Change Profile at the bottom of the Profile window. 2. Enter a value in the SQL Operation Cost field. 3. Click OK. Add Nodes You can add nodes to dba/m Database Manager using the System Profile Attributes pane, shown in Figure 4-1. The primary node is the node on which dba/m Database Manager was installed. The new node is accessible from the dba/m Database Manager primary node. 1. Click on Add Node at the bottom of the Profile window. 2. Enter the name of the node in the Node Name field. 3.
Figure 4-2. User Profile Window. To add a user 1. In the Mode pane at the bottom of the User Profile window, click on Add. 2. In the Guardian UserID field, enter the user’s user-group name and user name in the format .. 3. In the Default Volume.Subvolume field, enter the user’s default volume and subvolume in the format <$volumename>.. 4. In the Default Catalog field, enter the user’s default catalog in the format <\nodename>.<$volumename>.. 5.
Change Users You must be logged on to dba/m Database Manager as a super user to modify a user profile other than your own, or to add or delete entries in the Nodes field. To display the User Profile window, click on Profile in the Main window, then click on User. The User Profile window is displayed, as shown in Figure 4-2. To change a user profile 1. In the Mode pane at the bottom of the User Profile window, click on Change. 2.
then click on User. The User Profile window is displayed, as shown in Figure 4-2. To delete a user 1. In the Mode pane at the bottom of the User Profile window, click on Delete. 2. Use one of the following methods to choose a user: • Enter the user-group name and user name in the Guardian UserID field, in the format .. • Choose the user from the drop-down list in the Guardian UserID field. 3. To delete the user, click OK.
Display Attributes Use the Volume tab of profile window to get the present disk volume usage. In Display attribute there are 4 option buttons. 1. Space used. This will show space used by each disk volume. 2. Space Free. This will show available space for each disk volume 3. Used/Free. This will show used as well as free space. 4. % Free. This will show % Free space for each disk volume. Display Order Display Order has 3 option buttons 1. Volume Name. It displays disk volume name. 2. Ascending.
Figure 4-4. Notifier window. Email information. Email information has username and emailed. You can add this to list by clicking option button Add to TO list (this is default). You can add this to CC list by clicking option button Add to CC list. Pager Information. Pager number can be added in textbox Pager Number. Page me Anytime is default. User can enter start time and End time. Notification Interval. User can set notification interval by entering values in textbox hour and minutes.
Add, Edit, Remove User. User can Add, Edit or Remove notifier by using Add, Edit or Delete buttons respectively. SQL Inventory This will give SQL Inventory at the present time. Click on Profile in the main window and then click on SQL_Inventory to get SQL Inventory display as shown in Figure below 4.5 Figure 4-5. SQL Inventory Default Logon To set default logon use Default Logon screen. 1.
2. Click on DefaultLogon. DefaultLogon screen will appear as shown below 3. Enter username and password 4. Click on Update 5. Click on Ok. Figure 4-6. Default Logon Map Defines Use the Map Defines window shown in Figure 4-7 to add, change, or delete map defines. To display Map Defines window, 1. Click on Query Analysis in Main window 2. Click on Adhoc Query 3. On Adhoc Query window, click on Define command button.
Figure 4-7. Map Defines window. Add Map Defines Use the Map Define window to add map defines. To display the Map Defines window, click on Query Analysis in the Main window, then click on Adhoc Query. Then Click on Query_Analysis. Query Analysis window will appear. Click on Define button get Define window. The Map Define window is displayed, as shown in Figure 4-7. 1. In the Mode pane at the bottom of the Map Defines window, click option button Add.
Change Map Defines You can change only the to the map define. click on Query Analysis in the Main window, then click on Adhoc Query. Then Click on Query_Analysis. Query Analysis window will appear. Click on Define button get Define window The Map Define window is displayed, as shown in Figure 4-7. To change the file name 1. In the Mode pane at the bottom of the Map Defines window, click on Change. 2. In the Define Name field, click on the name of the map define. 3.
NonStop dba/m Database Manager for NonStop SQL/MP User’s Guide 522632-005 4-1
CHAPTER 5. USING dba/m Database Manager COMMANDS When you work with the object management windows described in this manual, dba/m Database Manager generates commands to perform the functions you request. You can view, save, execute and queue commands. You can retrieve save commands to execute or queue items. These operations are executed by clicking on the corresponding button, as follows: • SaveCommand saves the command so that you can use it later.
3. To save the command, click OK. To cancel the save command, click Cancel. Note: Refer to your Windows documentation for more information on how to use this window. Figure 5-1. Save Command As Pane. Displaying Commands To view the SQL commands generated by dba/m Database Manager, click on Show in the window in which you are working. The SQL Command window is displayed, as shown in Figure 5-2. Click Close to exit from the SQL Command window.
Figure 5-2. SQL Command Window. Executing Commands To execute the SQL commands generated by dba/m Database Manager, click on Execute. To execute the command, click OK. The commands shown in the window are executed immediately on the node. You cannot perform any other tasks on the client while the SQL commands are being executed. When the commands have been executed, a message is displayed indicating whether the commands failed or succeeded. To cancel a command, click Cancel.
Queuing Commands You can queue SQL commands generated by dba/m Database Manager by clicking on Queue. The Quick Batch window is displayed, as shown in Figure 7-3. You can either execute the command immediately, or you can queue it for execution at a later time. Figure 5-3. Queue Batch Window. To submit the command for immediate execution 1. Enter the name of the command in the Queue Command Name pane 2. Click on Submit. To execute the command later 1.
2. Click on Queue. NOTE: You can change the name of the queue command by entering a new name in the Queue Command Name pane. You can change the parameters of the queue command in the Batch Parameter pane. Retrieving Commands Use the RetrieveCmd command button in the Direct Command or the Adhoc Query Analysis windows to retrieve SQL commands you saved using the SaveCommand button. When you click on RetrieveCmd, the Retrieve Command window is displayed, as shown in Figure 5-4. To retrieve a command 1.
5-6 Using dba/m Database Manager Commands
PART II: MANAGING DATABASE OPERATIONS The chapters in Part II explain how to use dba/m Database Manager to perform higher-level management tasks, such as monitoring disk-space usage, analyzing and tuning query performance, and creating and scheduling batches. Part II is organized as follows: • Chapter 6, Monitoring Space Usage. Describes how to display disk space used by volume. Includes procedures for monitoring objects, setting alarm thresholds, and displaying alarms.
CHAPTER 6. MONITORING DISK-SPACE USAGE Using dba/m Manager, you can monitor disk-space usage by volumes, tables, indexes, or partitions. When monitoring disk-space usage, you can set alarm thresholds. If disk-space usage exceeds the thresholds you set, dba/m Database Manager generates audible and visual alarms to notify you. Displaying Volume Usage You can display a volume’s total disk space, amount of space used, amount of free space available, and percentage of free space available.
Customizing the Volume Display You can display the amount of used disk space, free disk space, the combination of free and used disk space, or the percentage of free space. You can also display the volumes in ascending order of their names or in ascending or descending order by one of the attributes selected for display. Use the Display Attribute and Display Order panes to choose the display attributes and order you want, as shown in Figure 6-2. Figure 6-2. Display Attribute and Display Order Panes.
Choosing Objects To choose the objects to monitor, use the Object Tree window, as shown in Figure 6-3. To display the Object Tree window, click on Object Tree in the Main window. Figure 6-3. Object Tree Window. Choose Volumes To choose volumes to monitor 1. If the volume you want to monitor is not displayed, double-click on the Node icon that contains the volume. 2. Click on Volume. 3. Right Click mouse button and it will display on Monitor option.
Figure 6-4. Monitor Window. Choose Tables To choose tables to monitor 1. Choose the catalog that contains the tables you want to monitor as follows: • If the volume that contains the catalog is not displayed, click on the Node icon that contains the catalog • Click on the Catalog icon that contains the tables. 2. Click on the Tables icon displayed under the Catalog icon. All the tables in the catalog are displayed in the Tables, Indexes, or Partition Names pane, as shown in Figure 6-5. 3.
4. Right Click mouse button and menu will appear , select Monitor. The Monitor window is displayed, as shown in Figure 6-4. 5. To select additional tables, click object tree icon at the top of the window. The Object Tree window is displayed. 6. Repeat Steps 1 through 5 to select additional tables. Figure 6-5. Tables , Indexes, or Partition Names. Choose Indexes To choose indexes to monitor 1.
selected index. The Monitor button is also highlighted. To deselect an index, click on the row header again. 4. Right Click mouse button and menu will appear , select Monitor. The Monitor window is displayed, as shown in Figure 6-4. 5. To select additional indexes, click Close in the Object Monitoring window. The Object Tree window is displayed. 6. Repeat Steps 1 through 5 to select additional indexes. Choose Partitions To choose the partitions to monitor 1.
To control these monitoring functions, use the Object Monitoring window, as shown in figure 6-4. Choose Objects You must choose objects to start monitoring the objects, stop monitoring the objects or set alarm thresholds for the objects. Use the Selected Objects pane in the Monitor window to choose the objects, as shown in Figure 6-6. Use one of the following methods to choose objects: • To choose one object at a time, double-click on the name of the object in the list of objects.
Setting Alarm Thresholds Each object you choose is associated with a horizontal slide bar and a meter: • The position of the red marker on the slide bar identifies the threshold of percentage usage at which to raise an alarm. The numeric value is shown in the % Threshold field. • The percentage value shown by the blue meter indicates the current usage of the disk by that object.
want to look at the free space available over a period of time and predict the future disk space needs based on historical trend. Figure 6-7. Historical and Predicted Data. Displaying Alarms If alarm thresholds are exceeded by objects you have chosen to monitor, the Alarm button in the Main window turns red and an audible alarm is generated. To display the alarms you have set, click on Alarm. The Alarms pane is displayed, as shown in Figure 6-8.
Figure 6-8. Alarms Pane.
CHAPTER 7. ANALYZING AND TUNING QUERY PERFORMANCE Query performance depends on two major factors: the structure of the queries, and the statistics of the tables referred to in the queries. Using dba/m Database Manager, you can correlate the query to the statistics of tables that are stored in NonStop SQL catalogs. Using dba/m Database Manager, you also can study the impact of the structure of the query using the methods identified by NonStop SQL’s explain function to retrieve data.
Figure 7-1. Program Query Performance Analysis Window. Choose a Program Use the Catalogs and Programs pane to choose the program for analysis, as shown in Figure 7-2. Figure 7-2. Catalogs and Programs To choose a program 1. In the Catalogs field, double-click on the name of the catalog that contains the program. All of the programs in the selected catalog are displayed in the Programs field. 2. In the Programs field, double-click on the name of the program that contains the query or queries.
Figure CHAPTER 7-3. Versions Pane. Choose a Query To choose a query 1. In the Versions pane, double-click on the version number of the program you want to analyze. A list of source files in the program are displayed in the Source field. 2. double-click on the name of the source file that contains the SQL statement. The SLT index of the SQL statements and the corresponding cost of the query are displayed in the SLT field and the TotalCost field. 3.
Figure CHAPTER 7-4. SQL Statement Pane. The step number, operation number, operation type, and operation cost are displayed in the Operations/Steps pane, as shown in Figure 7-5. Figure 7-5. Operations/Steps Pane. Display Plan Details Use the Operations/Steps pane to display the details of each operation. To display plan details, click on an operation number in the Operations/Steps pane, and then click on Details command button.
displayed: step number, operation number, operation type, operation cost, and the query plan as created by the Explain function. Figure 7-6. Operation Details Pane. Display Statistics You can display the statistical characteristics of the tables used in the query by clicking on Statistics in the Program Query Analysis window. The Table Statistics window is displayed, as shown in Figure 7-7.
Figure 7-7. Table Statistics Window. Table Statistics Table statistics for all of the tables used in the query are displayed in the Table Statistics pane, as shown in Figure 7-8. Figure 7-8. Table Statistics Pane. Column Statistics To display column statistics of a table, double-click on the name of the table in the Table Statistics pane. The column statistics of the selected table are displayed in the Column Statistics pane, as shown in Figure 7-9.
Figure 7-9. Column Statistics Pane. Index Statistics To display index statistics of a table, click on Index statistics Tab. The Index Statistics pane, id shown in Figure 7-10. Figure 7-10. Index Statistics Pane. Partition Statistics To display partition statistics of a table, click on Partition statistics Tab. The Index Statistics pane, id shown in Figure 7-11.
Figure 7-11. Partition Statistics Pane. Update Statistics If the statistics on any of the tables have not been updated recently, you may perform Update Statistics on any of the tables. To update statistics on a table 1. Click on the cell identified by the name of the table and the Update Statistics column. The cell will display the character “y.” You may deselect a table by clicking on the cell again. 2. Click on Update Statistics. 3.
• To display all objects, including programs, that depend on the table you want to modify, click on the Objects button. Simulate Production Statistics You can manually update statistics to simulate different production environments. To simulate production statistics, your copy of dba/m Database Manager must be licensed to update table statistics. Check with the NonStop Himalaya System administrator to determine if you have the appropriate license.
Change Index Statistics You can update the number of index levels for an index in the Index Statistics pane. To choose the index, click on the name of the index in the Index field. Place the cursor in its Index Level field and edit the field. Update Catalogs Click on Manual Stats Update at the bottom of the Table Statistics window to update the table, column, and index statistics stored in system catalogs.
Modify the Query The SQL statement is displayed in the SQL Statement pane, as shown in Figure 7-13. The total cost of the original query is displayed in the Original Total Cost field. Edit the query to create a new query. Figure 7-13. SQL Statement Pane. Modify Control Options You can change the control options for tables and queries in the Current Control Options pane as shown in Figure 7-14. To view the active control options, click on Execute Cmd.
Figure 7-14. Current Control Options pane. The Current Control Options pane shown in Figure 7-14 displays the active control options for tables and queries. You can change these control options using the Control Query, Control Executor, or Control Table buttons. To change control table options 7-12 • Click on the Control Table button in the Adhoc Query Performance Analysis window, Press the tab Control_Table to display the Control Table window as shown in Figure 7-15.
Figure 7-15. Control Table Window. To change control query options • Click tab Ctrl Query button in the Adhoc Query Performance Analysis window to display the Control Options window as shown in Figure 7-16. • Edit the control options in the Current Control Options pane. To change control executor options, click on the Ctrl Executor button.
Figure 7-16. Control Query Window. View Explain Plans Once you have modified the query and/or Control Options, click on Execute Cmd. The query plan will be displayed in the Query Plan pane. Execute the Query Click on the Query Results button to execute the query. The query will be executed and the data retrieved will be displayed in the Query Analysis pane.
Figure 7-17. Query Analysis Window. Analyzing Ad Hoc Queries Use the Query Performance Analysis window to display the Explain output for ad hoc queries. You can enter queries in this window, retrieve queries stored in files, or move queries from the Program Query Analysis window. To display the Query Analysis window, click on QueryAnalysis in the Main window, then click on AdhocQuery. The Query Analysis window is displayed, as shown in Figure 7-17.
Figure 7-18. SQL Statement Pane. Modify Control Options You can change the control options for queries and the Executor in the Current Control Options pane as shown in Figure 7-14. To view the active control options, click on Execute Cmd. The active control options are shown in the Current Control Options window. View Explain Plan Click on Execute to execute the query. The Explain output is displayed in the Explain output window, as shown in Figure 7-19.
Figure 7-19. Explain Output Window. Display Statistics Click on the Statistics button to display the statistics stored in the catalogs. Refer to the Display Statistics section of “Analyzing Queries in Programs” at the beginning of this chapter for instructions on displaying table, column, and index statistics. Simulate Production Statistics You can manually update statistics to simulate different production environments.
Execute Query Click on the Query Results button to execute the query. The query will be executed and the data retrieved will be displayed in the “Query Results” pane. Save the Query You can save the ad hoc query for future use by clicking on SaveCommand.
CHAPTER 8. MANAGING DATA Using dba/m Database Manager, you can • Query a database • Copy data to or from Enscribe files and SQL tables, appending the data to the files or tables • Load data in an existing database, overwriting the existing data • Display the contents of a file You can perform these functions by accessing windows from the Data button in the Main window. The menu hierarchy is shown in Figure 10-1. Figure 8-1. Diagram of Data Management Menus.
To view the Query Data window, click on Data - Query in the Main window. An example of the Query Data window is shown in Figure 8-2. Figure 8-2. Query Data Window. Querying a Database To query a database, you choose the tables or views, then choose the columns and search criteria. Choose Tables or Views To choose tables or views to query, use the Catalogs, Tables, and Views pane, as shown in Figure 8-3. Figure 8-3. Catalogs, Tables, and Views Pane.
1. In the Catalogs field, double-click on the name of the catalog. Tables located in the catalog are displayed in the Tables field. Views located in the catalog are displayed in the Views field. • To choose a table, double-click on its name in the Tables field. • To choose a view, double-click on its name in the Views field. 2. The columns in the table or view are displayed in the Columns pane, as shown in Figure 8-4.
Figure 8-5. Select Text Pane. Specify Search Criteria If you want to specify search criteria, you can add WHERE statements to the Select Text pane shown in Figure 8-5. Using search criteria is optional. 1. Click on Predicate Tab. 2. Type the search criteria in Predicate pane. Figure 8-6. Predicate Pane. Execute the Query To execute the query, click on Execute.
If the query cost to retrieve the rows from the selected table is higher than the cost configured in the user profile, a warning message appears. You can continue or cancel the query. The progress of the selection of rows is displayed in the Query Status window, as shown in Figure 8-7. In this window, you do one of the following: • To terminate execution of the query, click Cancel. • To stop the retrieval to view the data, click on Stop.
Copying Data You can copy data to or from Enscribe files and SQL tables. The data you copy is appended to the existing data. You can also display the contents of a file or table. Use the Copy window to add data. To display the Copy window, click on Data in the Main window, then click on Copy. The Copy window is displayed, as shown in Figure 8-8. Figure 10-6. Copy Window. Define Source and Destination Define the source and destination objects in the Source-Destination pane, as shown in Figure 8-9.
1. In the Source field, double-click on the type of source object. 2. Enter the name of the source object in the Source field. 3. In the Target field, double-click on the type of destination object. 4. Enter the name of the destination object in the Target field. Specify Control Options Specify the control options for the copy operation in the Control Options pane, as shown in Figure 8-10. Figure 8-10. Control Options Pane. To specify the control options 1.
7. To copy null values from Enscribe files to null values in an SQL table, click on Use SQL Nulls. 8. In the First pane, click on one of the following: • OrdinalRecNum. Then enter the number of records to be skipped in the OrdinalRecNum field. • RecordSpec. Enter the primary-key value for the starting record of an unstructured, relative, or entry-sequenced file in the RecordSpec field. • KeyValue. Then enter the approximate position of the starting record for key-sequenced files in the KeyValues field.
7. Enter the maximum length of input records, in bytes, in the RECin field. Define Output-File Options Set output-file options using the Out Option pane, as shown in Figure 1012. Not all options are applicable to all object types. To define output-file options 1. Enter the output block length in the Blockout field. 2. Enable translation to EBCDIC by clicking in EBCDICout. 3. Allow dividing of long input records into multiple records by clicking on Fold. 4.
Options tab the top of the Copy window. The Move Options pane is shown in Figure 8-12. Not all options are applicable to all object types. Figure 8-12. Move Options Pane. To specify move options 1. In the Source Dictionary field, enter the name of the subvolume that contains the DDL dictionary or DEF definition for an Enscribe input file. 2. In the Source Record field, enter the name of the DDL record definition for an Enscribe input file. 3.
Perform the Copy To copy the data, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later. Loading Data When you copy data using the Load window, you can copy data to or from Enscribe files and SQL tables. The data you copy overwrites any existing data. You can also display the contents of a file or table.
Define Source and Destination Define the source and destination objects in the Source-Destination pane, as shown in Figure 8-14. The source can be an Enscribe file or an SQL table. The destination can be an Enscribe file or an SQL table. Figure 8-14. Source-Destination Pane. To specify the source and destination of a copy operation 1. In the Source field, double-click on the type of source object. 2. Enter the name of the source object in the Source field. 3.
4. In the Replace spaces pane, specify whether spaces should be replaced with zeroes or with defaults by clicking either on With Zeroes or on With Defaults. 5. Turn the unstructured-disk-file option on or off by clicking on Unstructured. 6. Turn the up shift option on or off by clicking on Upshift. 7. To copy null values from Enscribe files to null values in an SQL table, click on Use SQL Nulls. 8. In the First pane, click on one of the following: • OrdinalRecNum.
2. Set the compact or no compact option by clicking on Compact. 3. Enable translation to EBCDIC by clicking on EBCDICin. 4. Enter the maximum length of input records, in bytes, in the RECin field. 5. To open an input field in shared exclusion mode, click on Share. 6. Set the trim-character option by clicking on Trim; then enter the trim character in the Trim field. 7. Set the variable length option by clicking on Varin.
7. Enter the minimum percentage of space to be left in index blocks in the Islack field. 8. Enter the minimum percentage of space to be left in index and data blocks in the Slack field. Enable Parallel Execution To enable parallel execution and specify configuration files and indexes, use the Parallel Execution pane, as shown in Figure 8-18. This option is applicable only for indexes. To enable parallel execution, click on On.
Figure 8-19. Move Options Pane. To specify move options 1. In the Source Dictionary field, enter the name of the subvolume that contains the DDL dictionary or DEF definition for an Enscribe input file. 2. In the Source Record field, enter the name of the DDL record definition for an Enscribe input file. 3. In the Move fields, enter the source column names and corresponding target column names. 4. Choose one of the move types, as follows: • To move by name, click on MoveByName.
Load the Data To copy the data and overwrite existing data, click on one of the following in the Copy window: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
CHAPTER 9. AUDITING COMMAND EXECUTION All operations performed in dba/m Database Manager are logged, so you can display a history of user operations. SQL operations performed outside of dba/m Database Manager are not tracked by dba/m Database Manager. dba/m Database Manager supports three types commands: on-line commands, batch commands, and direct commands. • On-line commands are executed using the Execute in dba/m Database Manager windows.
Figure 9-1. Command History Window. Choose Users You can display commands executed by all users, a single user, or a single object. To choose users 1. Click on one of the following: • All Users. Displays commands executed by all dba/m Database Manager users. • Single User. Displays commands executed by a single dba/m Database Manager user. 2. If you selected the Single User option, enter the group name in the Group Name field, and the user name in the User Name field.
1. Click on Single Object. 2. Enter the Object Name to see commands executed by that object. You may also enter partial names of objects. Choose Period You can display only the commands executed after a specific date. To choose period Enter the date and time in the Date and Time field. Display Audit Trail To display the history, click on Get History at the bottom of the window. Delete History You can delete the history of all commands by date and time.
Figure 9-2. Direct Command Window.
CHAPTER 10. MANAGING ITEMS AND BATCHES Using dba/m Database Manager, you can manage items and batches: • An item is a unit of work that consists of SQL commands and operations, such as SQLCOMP. You create items in windows such as Create Table, Create Index, and Compile Programs. An item is executed as a single transaction. • A batch is a collection of one or more items that you execute in a user defined sequence. A batch is also executed as a single transaction.
Figure 10-1. Create Batch Window. To select an item and edit its commands 1. In the Batch Item pane, choose the item by clicking on the item number. Use the > button to move the items to the Selected Batch Item pane. The commands in the item are displayed in the Batch Item Edit pane. 2. Choose the command you want to edit by placing the cursor on the command line in the Batch Item Edit pane. 3. Edit the command. 4. Click OK to save the changes, or click Cancel to cancel the changes.
• Stop a batch from execution • Refresh the status of a batch To display the Create Batch window, click on CreateBatch in the Main window. The Create Batch window is displayed, as shown in Figure 10-1. Create a Batch Use the Batch Items pane to combine queued items into a batch, as shown in Figure 10-2. Figure 10-2. Batch Items Pane. To create a batch 1. Select the item name or number of each item you want to include in the batch in the Batch Items pane. 2.
Attribute Name Description Usage Batch name Provides identification in addition to the batch number in the Batch Results window. Optional CPU # Number of the CPU under which the batch should be executed. Required Priority Process priority for batch execution. Required Execution date/time Date and time that the batch should be executed. Required Spooler destination/location Spool file location where the output of the batch should be stored for later review.
Figure 10-4. Batch Attributes Window. Delete a Batch Use the Batches pane, shown in Figure 10-3, to delete a batch. 1. To select a batch, click on the batch name for the batch you want to delete. 2. Click on DelBatch. 3. Repeat Steps 1 and 2 to delete additional batches. Submit a Batch Use the Batches pane, shown in Figure 10-3, to submit a batch for execution. 1. To select a batch, click on the batch name. 2. Click on Submit. 3. Repeat Steps 1 and 2 to submit additional batches for execution.
Stop a Batch You can stop execution of a batch only if the batch has not started execution. Check the status of a batch in the Batch window, in the Status field.
Figure 10-5. Results Window. Display Results You can check the status of a batch (created, scheduled, errored, completed) by displaying the details of the batch execution. To choose the batches you want to display, specify the beginning and ending date and time in the Select Date Range pane. Any batches submitted within the specified range are displayed, as shown in Figure 10-6. The default dates displayed in the Select Date Range pane are today’s plus three previous days, or a total of four days.
Figure 10-6. Select Date Range Pane. Display Batch Output To display detailed batch results, click on the row of the batch for which you want details. The Batch Results window is displayed, as shown in Figure 10-5. Note. Only results for batches which have executed will be displayed. Click on Batch Results to view the results. Batch Results Delete Batch If the batch has been created, scheduled, or has executed with errors, you can delete it by clicking on the Delete Batch button.
Resubmit a Batch After viewing the results, you may resubmit a batch. To resubmit a batch 1. Select the batch to be replicated. 2. Click on Replicate. 3. The Quick Batch window is displayed. 4. Modify the commands or batch parameters 5. Enter a new name in the Queue Command pane if desired. 6. Choose either Submit or Queue.
PART III: MANAGING SQL Objects Part III is organized as follows: • Chapter 11, Managing Catalogs. Describes procedures for managing catalogs. • Chapter 12, Managing Tables. Describes procedures for managing tables. • Chapter 13, Managing Views. Describes procedures for managing views. • Chapter 14, Managing Indexes. Describes procedures for managing indexes. • Chapter 15, Managing Table Partitions. Describes procedures for managing table partitions. • Chapter 16, Managing Index Partitions.
CHAPTER 11. MANAGING CATALOGS dba/m Database Manager provides tools for managing user catalogs. Using dba/m Database Manager, you can • Create new user catalogs in the current node • Alter the attributes of an existing catalog • Drop user catalogs in a node • Upgrade user catalogs in a node You cannot use dba/m Database Manager to manage system catalogs. You must manage system catalogs using the SQLCI utility.
Catalog Menu Create Catalog Alter Catalog Drop Catalog Upgrade Catalog Figure 11-1. Diagram of Catalog Management Menus. Creating a User Catalog To create a user catalog you must assign the catalog name, location, and security string. To display the Create Catalog window, click on Catalogs in the Main window, then click on Create. The Create Catalog window is displayed, as shown in Figure 11-2.
Figure 11-2. Create Catalog Window. Assign a Name and Volume To create a new user catalog 1. Type the new catalog name in the first field next to the Catalogs icon. 2. To choose the volume and node where the catalog is located, do one of the following: • Enter the node name and volume in the second field next to the Catalogs icon. • Click on Volumes at the bottom of the window to display the Select Volume window, as shown in Figure 11-3.
• Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted later in a batch. Altering a User Catalog You can alter the following attributes of a user catalog: • Security string • Owner and group IDs • Clear-on-purge flag • Date and time after which the catalog can be purged To display the Alter Catalog window, click on Catalogs in the Main window, then click on Alter.
Change the Security Use the Security pane to change security. Change the security string using the drop-down list in the R, W, E, and P fields. Change the group ID in the first Owner ID field, and the user ID in the second Owner ID field. Change the Purge Attributes 1. Type an expiration date in the NoPurgeUntil field. 2. Click on ClearOnPurge to change the clear-on purge flag. Save the Catalog To save changes to a catalog, click on one of the following: • Execute. Executes the command immediately.
Note: If the catalog is not empty, the delete command will fail if you do not specify the cascaded-delete option. Figure 11-5. Drop Catalog Window. Drop the Catalog To drop a catalog, click on one of the following: • Execute. Executes the command immediately • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
Figure 11-6. Upgrade Catalog Window. Choose the Catalog To choose a catalog to upgrade 1. Enter the name of the catalog in the Catalog Name field. 2. Enter the new catalog-version number in the Catalog Version field. Upgrade the Catalog To upgrade the catalog, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
CHAPTER 12.
• Define file attributes • Define the table columns • Define table partitions To display the Create Table window, click on Tables in the main window, then click on Create. The Create Table window is displayed, as shown in Figure 12-1. Figure 12-1. Create Table Window. Assign Name and Location As you assign the name and location of the table you are creating. To assign the table’s name and location 1. Enter the name of the new table in the Tables field. 2.
4. Enter the name of the subvolume in the Subvolume Icon field. 5. In the Catalogs field, click on the arrow to display all catalogs. Click on the name of the catalog in which you want the table to be created. Assign Security Assign security using the drop-down lists in the R, W, E, and P fields in the Security pane. Although Owner ID and group ID are displayed, you cannot alter them. After you have created the table, use Alter Table to alter the owner ID or group ID.
5. Enter the maximum number of extents in the Max Extents field. 6. Enter the data and time until which the table cannot be purged in the NoPurgeUntil field. 7. Enter the numeric table code in the Table Code field. 8. Enable or disable audit by licking on Audit. 9. Enable or disable audit compression by clicking on Audit Compress. 10. Enable or disable buffered disk-writes by clicking on Buffered. 11. Enable or disable clear-on-purge by clicking on Clear On Purge. 12.
Add Columns After you have defined the new table’s attributes, you must add columns to the table. When you add a column you assign the following to the column: • Column name and heading • Data type • Data-value attributes • Primary key To add a column to the new table, click on Columns Tab in the Create Table window. The Columns window is displayed, as shown in Fig. 12.2 Figure 12-2. Columns Window. Assign the Name and Heading Enter the column name in the Column Name field.
Assign a Data Type Specify the data type for column using the Type pane, as shown in Figure 12-3. Figure 12-3 Data Type Pane. To assign a data type 1. Choose the data type for the column by clicking on one of the following: Char, PicX, Varchar, DateTime, Interval, Decimal, Numeric, Pic9, Smallint, Integer, Largeint, or Float. Depending on the data type you choose, additional attributes of the data types can be specified.
2. Choose the type of default value by clicking on one of the following: System, User, Current, or No Default. 3. If you click on User, enter the default value in the User field. Assign a Primary Key Use the Key pane shown in Figure 12-5 to specify the primary key for the column. Figure: 12-5. Key Pane. To assign a primary key 1. Click on Key. 2. Choose the order of the key by clicking on Ascending or Descending. Note: Key-sequence numbers are generated automatically as you add new columns.
1. In the Columns pane, click on the name of the column in the list in the Column Name field. The attributes of the column are displayed in the Edit Columns window. 2. Change any of the column attributes. 3. Click on Change to display the column’s new attributes in the Column pane. Note: If you change the primary key attribute, the key-sequence number of all primary-key columns will be changed. 4. Repeat steps 1 through 3 to change the attributes of the any other columns in the new table. 5.
Creating Table Partitions Click on partition in the Create Table window. The Add Partition window is displayed. See Add Partition in Managing Table Partitions for detail. Creating Table Constraints If you want add constraints to a table, click on Table menu in Main window, click on constraint menu, and then click on the Create Constraint window shown in Figure 12-6 is displayed. Figure 12-6. Create Constraint Window.
• Place the cursor in the Constraint Text field and enter the constraint text, or • Click on the name of an existing constraint in the Constraints Name field to display the text in the Constraint Text field. Edit the constraint text to create a new one. 3. To save the new constraint, click on Execute. Alter a Constraint If you want alter a constraint, click on Table menu in Main window, click on constraint menu, and then click on Alter menu, the Alter Constraint window shown in Figure 12-7 is displayed.
Drop a Constraint If you want drop a constraint, click on Table menu in Main window, click on constraint menu, and then click on Drop menu, the Drop Constraint window shown in Figure 12-8 is displayed. Figure 12-8. Drop Constraint Window. To drop a constraint, you choose the constraint, then delete it. In the Constraint pane click on the name of the constraint you want to delete. The constraint text is displayed in the Constraint Text pane, click on Execute.
• Queue. Queues the command to be submitted in a batch later. Altering a Table The alter-table function allows you to • Change a table’s file attributes or security • Add columns To display the Alter Table window, click on Tables in the Main window, then click on Alter. The Alter Table window is displayed, as shown in Figure 12-9. Figure 12-9. Alter Table Window. Choose a Table Use the Catalogs and Tables pane to choose a table to modify.
1. In the Catalogs field, click on the name of the catalog that contains the table you want to modify. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the table you want to modify. The attributes for the selected table are displayed. Display Dependencies Before altering a table, you can analyze the impact of the changes on other objects: • To display all programs that depend on the table, click on Dependent Programs.
11. Enable or disable serial writes by clicking on Serial Writes. 12. Enable or disable verified writes by clicking on Verified Write. 13. Enable or disable similarity check by clicking on Similarity Check. Choose Table Format Choose Format of a table by clicking either Format1 or Format2 in Format Pane. Choose Partition Array: You can choose anyone partition Array by clicking any of the option buttons in Partition Array Pane.
Change the owner ID by typing a new group ID in the first Owner ID field, and a new user ID in the second field. Add Columns To add columns, click on Columns Tab of the Alter Table window. The Columns window is displayed. Refer to Add Columns for instructions on adding columns to a table. Dropping a Table Use the Drop Table window to delete the definition of a table. Before dropping a table, you can analyze the impact of dropping the table by displaying all of the objects that depend on the view.
Choose the Table Use the Catalogs and Tables pane to choose the table to drop. To choose the table to drop 1. In the Catalogs field, click on the catalog that contains the table you want to drop. The tables contained in the catalog you selected are displayed in the Tables field. 2. In the Tables field, Click on the table you want to drop. The attributes of the table are displayed in the Drop Table window.
To display the Rename Tables window, click on Tables in the Main window, then click on Rename. The Rename Table window is displayed, as shown in Figure 12-12. Figure 12-12. Rename Table Window. Choose a Table Use the Catalogs and Tables pane to choose a table to rename. To choose a table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the table you want to rename.
• To display all programs that depend on the table, click on Dependent Programs. • To display all objects, including programs that depend on the table, click on Dependent Objects. Enter the New Name Use the Rename pane to change the table name, as shown in Figure 12-13. To rename the table, type the new name in the Rename field. Figure 12-13. Rename Pane. Rename the Table To rename the table, click on one of the following: • Execute. Executes the command immediately. • Show.
1. Click on Tables in the Main window. 2. Click on Constraint. 3. Click on Create. The Create Constraint window is displayed, as shown in Figure 12-14. Figure 12-14. Create Constraint Window. Choose a Table Use the Catalogs and Tables pane to choose a table to add constraints. To choose the table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table.
Assign a Name Assign a name for the constraint using the Constraint Name pane. Enter the constraint name in the Constraint Name field. Enter the Constraint Use the Constraint Text pane to enter the constraint. To enter the constraint, place the cursor in the Constraint Text field. You can • Enter the text of the new constraint, or • Click on a constraint displayed in the Constraints field. The constraint text is displayed in the Constraint Text field. Edit the constraint text as necessary.
Figure 12-15. Alter Constraint Window. Choose a Table Use the Catalogs and Tables pane to choose a table. To choose the table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table. The constraints for the table are displayed. Choose a Constraint Use the Constraint Name pane to choose the constraint to modify.
Save the Constraint To save the constraint, click on one of the following: • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Execute. Executes the command immediately. • Queue. Queues the command to be submitted in a batch later. Dropping Constraints Use the Drop Constraint window to delete constraints. To display the Drop Constraint window 1. Click on Tables in the Main window. 2. Click on Constraint. 3. Click on Drop.
Choose a Table Use the Catalogs and Tables pane to choose a table. To choose the table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table. The constraints for the table are displayed. Choose a Constraint Use the Constraint Name pane to choose a constraint to drop. Click on the constraint in the Constraint Name field.
Figure 12-17. Comments for Table Constraints Window. Choose a Table Use the Catalogs and Tables pane to choose a table. To choose a table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table. The constraints for the table are displayed. Choose a Constraint Use the List of Constraints pane to choose a constraint.
To change a comment, click on the line you want to modify. The cursor is placed on the line; edit the comment as desired. To delete a comment, click on the line of comment you want to delete, then click on Delete Line. Save the Constraint Comment To save the constraint, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
Figure 12-18. Help Text for Tables Window. Choose the Column Use the Catalogs, Tables, and Columns pane to choose the column. To choose the column 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table. The columns in the table are displayed in the Column Name field. 3. In the Column Name field, click on the column.
Managing Table Comments You can add, change, or delete comments in a table. Table comments consist of one or more lines of text. To display the Table Comment window, click on Tables in the Main window, then click on Table Comment. The Table Comment Text window is displayed, as shown in Figure 12-19. Figure 12-19. Table Comment Window. Choose a Table Use the Catalogs and Tables pane to choose a table. To choose the table 1. In the Catalogs field, click on the name of the catalog that contains the table.
1. Click on Add Line. The cursor is placed on the next available line in the Comment Text pane. 2. Enter the comment. 3. Repeat steps 1 and 2 to add additional comments. To change table comments, click on the line you want to modify. The cursor is placed on the line; edit the table comment as desired. To delete table comments, click on the line of text you want to delete, then click on Delete Line.
Figure 12-20. Comments for Table Columns Window. Choose the Column Use the Catalogs, Tables, and Columns pane to choose the columns of a table. To choose a column 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table. The columns in the table are displayed in the Column Name field. 3. In the Column Name field, click on the name of the column.
To change column comments, click on the line you want to modify. The cursor is placed on the line; edit the column comment as desired. To delete column comments, click on the line of text you want to delete, then click on Delete Line. Save the Column Comment To save new or modified column-comments, or to delete them, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue.
Figure 12-21. Table Statistics Window. Choose the Tables Use the Catalogs and Tables pane to choose one or more tables. To choose tables 1. In the Catalogs field, click on the name of the catalog that contains the tables. The tables in the catalog are displayed in the Tables field. 2. In the Tables field, choose tables using one of the following methods: • Choose one or more tables by clicking on each table, or • Choose all tables in the catalog shown by clicking on SelectAllTables.
• Table statistics. Table statistics are automatically displayed for all selected tables. • Column, partition, and Index statistics. To display column and index statistics, double-click on the name of the table. The column statistics are displayed in the Column Statistics pane, as shown in Figure 12-22. The index statistics are displayed in the Index Statistics pane, as shown in Figure 12-23. Figure 12-22. Column Statistics Pane. Figure 12-23. Index Statistics Pane.
Display Dependencies Before updating table statistics, you can analyze the impact that the changes might have on other objects; • To display all programs that depend on the statistics, click on Dependent Programs. • To display all objects, including programs, that depend on the statistics, click on Dependent Objects. Update the Statistics Using the Table Statistics pane, you can update the statistics of a subset of tables. Choose Tables To choose tables 1.
Figure 12-24. Update Statistics Window. Choose Update Options Use the Update Statistics window to update the statistics on a set of tables as shown in Figure 14-23. The selected tables are shown in the Table Name field. The Update Statistics field for each table is set to Y (for yes). If you do not want to update statistics for a table, click on the corresponding Update Statistics field. The value of the field changes to N (for no), indicating that the table’s statistics will not be updated.
• To disable the option for all tables, click on SetRecompileOptionToNo. The Recompile changes to N in all tables. 2. Enable or disable the All Columns option: • To enable or disable the option for one table, click on the All Columns field for the table. The value of the All Columns field changes to Y if it was N and vice versa. • To enable the option for all tables, click on SetAllColumnsOptionToYes. The All Columns field changes to Y in all tables.
Figure 12-25. Invoke Table Window. Choose a Table Use the Catalogs and Tables pane to choose a table. To choose a table 1. In the Catalogs field, click on the name of the catalog that contains the table. The tables in the selected catalog are displayed in the Tables field. 2. In the Tables field, click on the name of the table you want to invoke. Set Invoke Options Use the Options pane to specify invocation options, as shown in Figure 1226. All of the options do not apply to all of the languages.
Figure 12-26. Options Pane. To set invocation options 1. In the Format field, click on the arrow to display the target languages. Click on the name of the target language. 2. Enter the name of the record in the As Recordname field. 3. Choose the date format by clicking on Default, European, or USA. 4. Enter the prefix-indicator variable name in the Prefix field. 5. Enter the suffix-indicator variable name in the Suffix field. 6.
Figure 12-27. Output File Pane. To specify output file attributes 1. Enter the name of the node in the Node field. 2. Enter the name of the volume in the Volume field. 3. Enter the name of the subvolume in the Subvolume field. 4. Enter a file name in the File field. 5. Enter the host identifier to be used in the SECTION directive in the Section field. 6. Use the Clear check-box to indicate whether the invoke output replaces the contents of the output file or is appended to it.
Figure 12-28. Invoke Output Window You can use the Edit and Print menus in the Invoke Output window to work with the results, as follows: • To select all or part of the output, use the Select option in the Edit menu. • To copy the selected text to the MS Windows clipboard, use the Copy option in the Edit menu. • To define printer options, use the Printer Setup option in the Print menu. • To print the selected text, use the Print option in the Print menu.
Figure 12-29. Recreate Tables Window. Recreating Tables The Recreate Table function allows you to create an SQL script that will create the table, partitions of the table, and indexes on the table as it exists on the NonStop machine at the time you execute this function. You can save the script for later use by clicking Save Command. To recreate a table 1. Select the table to be recreated from the Catalogs and Tables pane 2. Commands, columns names and statistics for the table selected will be displayed 3.
CHAPTER 13. MANAGING VIEWS Managing views is simple with dba/m Database Manager. Using the procedures in this chapter you can • Create views • Alter views • Drop views • Invoke views • Recreate view • Rename views • Add, change, or delete help text • Add, change, or delete view comments • Add, change, or delete column comments in a view • Create-Like views You manage views by accessing windows from the Object Tree button, the Custom Tree button, or the Views button.
Help Text Invoke View Comment Column Comment Creating a View When you create a view, you define the view name, location, type, and security string. Then you choose the base table and table columns and define WHERE clauses for the view. Click on Views in the Main window, then click on Create. The Create View window is displayed, as shown in Figure 13-1. Figure 13-1. Create View Window. Assign Name and Location Use the first portion of the Create View window to assign the new view’s name and location.
Figure 13-2. Name and Location Pane. To assign the name and location of the view 1. Type the name of node in the Node field. 2. Type the name of the volume in the Volumes field. 3. Type the name of the subvolume in the Subvolumes field. 4. Type the name of the new view in the Views field. Choose the Type of View Use the Name and Location pane in Figure 13-2 to specify the type of view. To choose the view type, click on Protection or Shorthand.
Figure 13-3. Security Pane. Choose a Base Table Use the Base Table Catalog and Base Table panes to choose base tables for the view, as shown in Figure 13-4. Figure 13-4. Base Table Catalogs and Base Tables Pane. 1. In the list of catalogs shown in the Catalog field, double-click on the catalog that contains the base table for the view. The tables in the catalog are displayed in the Table field, as shown in Figure 15-5. 2. Double-click on the table you want to use as a base table.
The fields shown in the Base Tables and Columns pane are defined as follows: • Table Name, name of the base table. • Alias, correlation name used in the view definition. • Table Column Name, name of the column in the base table. • Type, data type of the columns in the base table. • View Column Name, name of the column in the view. • Heading, the heading text for the column in the view. Display Indexes on Base Tables You display the indexes on the base table by clicking on the Show Index button.
Figure 13-6. View Text Pane. Delete Columns To delete columns from the view, perform the following steps using the Base Tables and Columns pane. 1. In the View Column Name field, click on the row header of the column you want to delete. The corresponding Table Column Name field is highlighted. To deselect a column, click on the row header again. 2. In the Table Column Name field, click on the name of the column. The column is deleted from the View Column Name field. 3. Click on Projection.
1. Select the column you want to change by clicking on the column name shown in the View Column Name field. The corresponding Table Column Name field is highlighted. 2. Place the cursor in the View Column Name field to edit the name of the column. 3. Place the cursor in the Heading field to enter the name of the column. 4. Click on Projection. The View Text pane displays the new columns list.
Altering a View The Alter View function allows you to change the name, column headings, or security of a view. You can also analyze the impact of the changes on the view by displaying the objects that depend on the view. To display the Alter View window, click on Views, then click on Alter. The Alter View window is displayed, as shown in Figure 13-7. Choose the View To choose a view, perform the following steps. In the Catalogs field, double-click on the catalog that contains the view you want to alter.
Figure 13-8. Catalog and View Pane. Display Dependencies Before altering a view, you can analyze the impact of altering it: • To display the programs that depend on the table, click tab Dependent Programs. Programs which would be invalidated by moving the partition are displayed. • To display all objects, including programs, that depend on the table, click tab Dependent Objects. All SQL objects that depend on the table are displayed.
2. For a shorthand view, change the group ID in the first Owner ID field, and the user ID in the second Owner ID field. Figure 13-10. Security Pane. Change Similarity Check You can choose Similarity Check by clicking in the Similarity Check field in the View pane. Save the View After you have altered a view, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager. • Queue.
Figure 13-11. Drop View Window. Choose the View Use the Catalogs and Views pane to choose the view to drop. To choose the view to drop 3. In the Catalogs field, double-click on the catalog that contains the view you want to drop. The views contained in the catalog you selected are displayed in the Views field. 4. In the Views field, Double-click on the view you want to drop. The attributes of the view are displayed in the Drop View window.
Drop the View When you are sure you want to drop a view, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager. • Queue. Queues the command to be submitted in a batch later. Invoke View To invoke a view, click on Invoke in the Views menu in the Main window. The Invoke View window, as shown in Figure 13-12, will be displayed. Figure 13-12. Invoke Window. Choose the View 1.
3. Edit the Generations Options List, Date, Indicator Designation and Levels panes. 4. When you have finished, click Execute to execute the new command, or click Close to cancel. Recreate the View To recreate the view, click on Recreate in the Views menu in the Main window. The Recreate window, as shown in Figure 13-13, is displayed. Figure 13-13. Recreate View Window. Once the view is selected, dba/m Database Manager will display the script used to create the view.
Create Like You can edit the script used to create a similar view with the Create Like feature. To create a new script, select Create Like from the View menu in the Main window. The Create Like window, as displayed in Figure 13-14, will be shown. Figure 13-14. Create Like Window. Choose the View 1. In the Catalogs field, double-click on the catalog that contains the view you want to use. The views contained in the catalog you selected are displayed in the Views field. 2.
• To display the programs that depend on the script, click on Dependent Programs. Programs that would be invalidated by the change are displayed. • To display all objects, including programs that depend on the script, click on Dependent Objects. All SQL objects that depend on the script are displayed. Create New View You may edit the script shown to create a new view. Save New View After you have defined the new view, click on one of the following: • Execute. Executes the command immediately.
Figure 13-15. Rename View Window. Choose the View Use the Catalog and View pane shown in Figure 13-16 to choose a view. Figure 13-16. Catalog and View Pane. To choose the view 1. In the Catalogs field, double-click on the catalog that contains the view you want to rename. The list of views in the catalog is displayed in the Views field. 2. Double-click on the view in the Views field. The attributes of the view are displayed in the Rename View window.
• To display the programs that depend on the table, click on Dependent Programs. Programs that would be invalidated by moving the partition are displayed. • To display all objects, including programs, that depend on the table, click on Dependent Objects. All SQL objects that depend on the table are displayed. Change the Name In the Rename pane, type the new view name in the Rename field. Figure 13-17 shows an example of the Rename pane. Figure 13-17.
Figure 13-18. View Help Text Window Choose the View Use the Catalogs and Views pane to choose the column for which you want to modify the help text, as shown in Figure 13-19. Figure 13-19. Catalogs and Views Pane. To choose the view 1. In the Catalogs field, double-click on the catalog that contains the view you want to modify. The list of views in the catalog is displayed in the Views field. 2. In the Views field, double-click on the view you want to modify.
Choose the Column Use the Column Attributes pane to choose the column for which you want to modify the help text. An example of this pane is shown in Figure 13-20. In the Column Name field, click on the name of the column to select it. If the column has help text, the help text is displayed in the Help Text pane, as shown in Figure 13-21. Figure 13-20. Column Attributes Pane. Add, Change, or Delete Help Text Refer to Figure 13-21 for an example of the Help Text pane. Figure 13-21. Help Text Pane.
Save the Help Text To save new or changed help text, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager. • Queue. Queues the command to be submitted in a batch later. Managing View Comments You can add, change, or delete comment text in a view. Comments consist of one or more lines of text. To display comment text, click on Views in the Main window, then click on View Comment.
Choose the View Use the Catalogs and Views pane to choose a view. Figure 13-23 is an example of the Catalogs and Views pane. Figure 13-23. Catalogs and Views Pane. To choose the view 1. In the Catalogs field, double-click on the catalog that contains the view you want to modify. The list of views in the catalog is displayed in the Views field. 2. Double-click on the view you want to modify. If the view has any comment text, the comment text is displayed in the Comment Text pane, as shown in Figure 13-24.
To change help text, click on the line you want to modify. The cursor is placed on the line; edit the help text as desired. To delete help text, click on the line of text you want to delete, then click on Delete Line. Save the View Comment To save changes to the comment text, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager. • Queue. Queues the command to be submitted in a batch later.
Choose the Columns Use the Catalogs, Views, and Columns pane to choose the columns of a view. Figure 13-26 shows an example of this pane. Figure 13-26. Catalogs, Views and Columns. To choose the columns of a view 1. In the Catalogs field, double-click on the catalog name that contains the view you want to modify. The list of views in the catalog is displayed in the Views field. 2. Double-click on the view you want to modify. The columns in the view are displayed in the Column Icon field. 3.
To change help text, click on the line you want to modify. The cursor is placed on the line; edit the help text as desired. To delete help text, click on the line of text you want to delete, then click on Delete Line. Save the Column Comment To save changes to the comment text, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager. • Queue. Queues the command to be submitted in a batch later.
Figure 13-28. Invoke View Window. Choose a View Use the Catalogs and Views pane to choose a view, as shown in Figure 13-29. Figure 13-29. Catalogs and Views Pane. To choose a view 1. In the Catalogs field, double-click on the name of the catalog that contains the view. The views in the selected catalog are displayed in the Views field. 2. In the Views field, double-click on the name of the view you want to invoke.
1. In the drop-down list in the Format field, double-click on the target language. 2. Enter the name of the record in the As Recordname field. 3. Choose the date format by clicking on Default, European, or USA. 4. Enter the prefix-indicator variable name in the Prefix field. 5. Enter the suffix-indicator variable name in the Suffix field. 6. To specify a structure declaration for data and null indicators, click on Null Structure. 7. Enter the base level in the Base Level field. 8.
4. Enter the host identifier to be used in the SECTION directive in the Section field. 5. Click on Clear to indicate whether the invoke output replaces the contents of the output file or is appended to it. A blank box means invoke output appends the output file. A checked box means invoke output replaces the contents of the output file. Invoke the View To invoke the view immediately, click on Execute.
CHAPTER 14. MANAGING INDEXES Using dba/m Database Manager index-management windows, you can • Create indexes • Alter indexes • Drop indexes • Rename indexes • Add comments to an index • Display index statistics You can manage indexes by accessing windows from the Object Tree, the Custom Tree, or the Indexes button. The procedures in this chapter only describe how to access index management windows from the Index button.
Figure 14-1. Create Index Window. Assign Name and Location You must assign a name to the new index. You can use the default location or assign a location. If you do not assign a location, the index is created in your default node, volume, subvolume, and catalog. The default location is displayed in the Name and Location pane. To assign the name and location of the index 1. Enter the name of node in the Node field. 2. Enter the name of the volume in the Volumes field. 3.
Choose the Table Use the Catalogs and Table pane to choose the table. 1. In the Catalog pane, double-click on the catalog that contains the table, the tables in the catalog are displayed. 2. Double-click on the name of the table. The columns of the base table are displayed in the Columns pane. Assign Index Columns Use the Column pane to choose the index columns. To define index columns 1. Choose the first column of the index by clicking on the name of the column. 2.
3. Enter the maximum number of extents in the Max Extents field. 4. Enter the earliest date and time that the index can be purged in the NoPurgeUntil field. 5. Enter the numeric table code in the Table Code field. 6. Enter the minimum percentage of space to be left in a data block in the DSlack field. 7. Enter the minimum percentage of space to be left in an index block in the ISlack field. 8. Enter the minimum percentage of space to be left in data and index blocks in the Slack field. 9.
To assign miscellaneous attributes 1. Enter the key tag in the Key Tag field.. 2. Enable or disable the invalidate-programs option by clicking on Invalidate. 3. Enable or disable parallel execution by clicking on Parallel Execution. If enabled, enter the configuration file name in the Configuration field. 4. Enable or disable shared access by clicking on Shared Access. Save the Index To save the index, click on one of the following: • Execute. Executes the command immediately. • Show.
Figure 14-2. Alter Index Window. Choose an Index To choose an index to alter 1. In the Catalogs field, double-click on the name of the catalog that contains the index. The indexes in the catalog are displayed in the Catalogs and Indexes pane. 2. In the Indexes field, double-click on the index you want to alter. The attributes of the index are displayed in the File Attributes pane.
Change Security To change the security of an index, use the drop-down lists in the R, W, E, and P fields in the Security pane. You cannot change the owner ID or group ID of an index. Change File Attributes You can changes any of the attributes in the File Attributes pane. To change the file attributes of an index 1. To change extent allocation, perform one of the following: • To allocate extents, click on Allocate, then enter the initial number of extents in the Allocate field.
Partitions The name of partitions for the altered index is displayed in the Partitions pane. Choose Index Format Choose Format of a index by clicking either Format1 or Format2 in File Format Pane. Save the Index To save the modified index attributes, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
Figure 14-3. Drop Index Window. Choose an Index Use the Catalogs and Index pane to choose the index for deletion. To choose an index to drop 1. In the Catalogs field, double-click on the name of the catalog that contains the index. The indexes in the catalog are displayed in the Indexes field. 2. In the Indexes field, double-click on the name of the index you want to drop. The attributes of the index are displayed.
Drop the Index To drop the index, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. Queue. Queues the command to be submitted in a batch later. Renaming an Index Use the Rename Index window to rename an index. You can also analyze the impact of changing the index’s name by displaying all objects that depend on the index.
Choose an Index Use the Catalogs and Indexes pane to choose an index to rename. To choose an index 1. In the Catalogs field, double-click on the name of the catalog that contains the index. The indexes in the selected catalog are displayed in the Indexes field. 2. Double-click on the index you want to rename. The attributes of the selected index are displayed.
Figure 14-5. Index Comments Window. Choose an Index Use the Catalog and Index pane to choose an index. To choose the index 1. In the Catalogs field, double-click on the name of the catalog that contains the index. The indexes in the catalog are displayed in the Indexes field. 2. Double-click on the name of the index. If there is a comment associated with the index, the comment text is displayed in the Comment Text pane. Add, Change, or Delete Index Comments To add index comments 1. Click on Add Line.
To change index comments, click on the line you want to modify. The cursor is placed on the line; edit the index comment as desired. To delete index comments, click on the line of text you want to delete, then click on Delete Line. Save the Index Comment To save new or modified index comments, or to delete them, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue.
To display indexes 1. In the Catalogs field, double-click on the catalog that contains the indexes. The indexes in the catalog are displayed in the Indexes field. 2. Use one of the following methods to display index statistics: • To display statistics for one or more indexes, double-click on the name of each index • To display statistics for all indexes in the catalog, click on SelectAllIndexes. Statistics for the selected indexes are automatically displayed in the Index Statistics pane. 3.
CHAPTER 15. MANAGING TABLE PARTITIONS A partition is a portion of the table residing on a volume different from the volume on which the table was initially created. When creating partitions of a table, you specify the number of partitions and the volume, catalog, and primary-key values for each partition.
Figure 15-1. Partition Selection window. Choose Base Table Use the Base Table Catalog and Base Table panes to choose base tables. 1. In the list of catalogs shown in the Base Table Catalog field, doubleclick on the catalog that contains the base table for the partition. The tables in the catalog are displayed in the Base Table field. 2. Double-click on the table you want to use as a base table. Choose Table Partition Use the Base Table Catalog and Base Table panes to choose base tables. 1.
selected. User can select base partition for partonly by clicking partonly checkbox. Choose Partonly To design individual partition of a table or Base partition of a table, use Partonly option by clicking checkbox partonly. Partonly option is only for Auto Design of a table partition. Manual Design To create partitions with an unequal number of rows, click on Manual Design, as shown in Figure 15-2. Auto Design To create partitions with the same number of rows across all partitions 1.
Figure 15-2. Manual Design Window. To redesign the table partitions manually 1. Specify the new number of partitions wanted in the Number of Partitions field. 2. Specify the new sample rate in the Sample Rate field. 3. Specify the new primary-key values in the Primary-Key pane. The New Row button allows you to add more primary-key values to the partitions. 4. Click on Recompute to calculate and display the new number of rows and percentages in each partition.
the sampling rate and dba/m Database Manager will compute the primary key values when you press the Recompute button. Figure 15-3. Graphical Window. Automatic Design To have dba/m Database Manager automatically design table partitions 1. Specify the number of partitions in the Number of Partitions pane in the Auto Design pane as shown in Figure 15-1. 2. Specify the sample rate. 3. If you want Partonly then Click Partonly option. 4. Click on Auto Design.
Figure 15-4. Auto Design Window. Display Dependencies Before implementing a newly-designed partition in a table, you can analyze the impact of your proposed design. • To display the programs which depend on the table, click on Dependent Programs. Programs which would be invalidated by redesigning the partition are displayed. • To display all objects, including programs, which depend on the partitions, click on Objects. All SQL objects that depend on the partitions are displayed.
Choose Base Table Use the Base Table Catalog and Base Table panes to choose base tables. 1. In the list of catalogs shown in the Base Table Catalog field, doubleclick on the catalog that contains the base table for the partition. The tables in the catalog are displayed in the Base Table field. 2. Double-click on the table you want to use as a base table. Assign Sample Rate Enter the sample rate in the Sample Rate field. Compute Row Distribution Click on Compute.
Figure 15-5. Add Partition Window. Assign the Number of Partitions Enter the number of partitions you want in the table in the Number of Partitions field. Note: The number you enter should be one less than the total number of partitions, because the primary partition is automatically assigned when the table is created. Assign Volumes To choose the volumes where you want the partitions to be located Click on Volumes to choose the volumes where your partitions are going to be located.
Figure 15-6. Select Volume Window. 1. To select volumes for the partition, click on the name of the volume from the Volume Name field. Select as many volumes as there are new partitions, and click OK. 2. The Select Volumes window closes and the Add Partition window is displayed again. Partition names are assigned based on the selected volume and the name of the table. The partition names are displayed in the partition pane.
Assign File Attributes Use the Partition File Attributes pane, to assign file attributes for the partition 1. Choose a partition by clicking on its name. 2. Select the Format Type for the partition by clicking either Format1 or Format2. 3. In the Extent in Pages pane, perform one of the following: • To enter the primary and secondary extent sizes, click on Primary, then enter the primary extent size in the Primary field and the secondary extent size in the Secondary field.
Altering Partitions Use the Alter partition pane to modify the file attributes of a partition. To alter a partition, click on Table Partitions in the Main window, then click on Alter. The Alter Partitions window will be displayed, as shown in Figure 15-7. Figure 15-7. Alter Partitions Window. To alter a partition 1. Click on the name of the partition you want to alter in the partition name and catalog pane. 2.
4. Enter the maximum number of extents in the Max Extents field. 5. Enable or disable the Reset-Broken flag by clicking on Reset Broken. Display Dependencies Before altering partitions to a table, you can analyze the impact of the proposed changes: • To display the programs which depend on the table, click on Dependent Programs. Programs which would be invalidated by adding partitions are displayed. • To display all objects, including programs, which depend on the partitions, click on Dependent Objects.
Figure 15-8. Drop Partition Window. Choose Partition: To choose a partition, click on the name of the partition you want to drop in the Partition Name column.. Display Dependencies Before dropping partitions from a table, you can analyze the impact of the changes: • To display the programs which depend on the partitions, click on Dependent Programs. Programs which would be invalidated by dropping the partitions are displayed.
• Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later. Perform a Simple Move To perform a simple move of a table partition, click on Table Partitions in the Main window, then click on Simple Move. The Partition Selection window will be displayed, as shown in Figure 15-9. Figure 15-9. Partition Selection Window. Choose a Table 1.
Figure 15-10. Table Partition-Simple Move Window. Select the Partition Select the partition to be moved by clicking on the partition name in the Partition Name pane. Assign Name and Location Use the First Target Specification pane to assign a name and location for the partition you are moving. 1. Enter the name of the target partition in the partition field or click on the Volume button to select the volume from the Volume window. 2. Click on the arrows in the Catalog field to display a list of catalogs.
Assign File Attributes Use the First Target Specification pane to specify the partition’s file attributes. To assign the file attributes 1. In the Extent in Pages pane, perform one of the following: • To enter the primary and secondary extent sizes, click on Primary, then enter the primary extent size in the Primary field and the secondary extent size in the Secondary field. • To enter the number of extents, click on Extents, then enter the number of extents in the Extents field. 2.
• Queue. Queues the command to be submitted in a batch later. Performing a One-Way Split To perform a one-way split of a table partition, click on Table Partitions in the Main window, then click on One-Way Split/ Move. The One-Way Split window will be displayed, as shown in Figure 15-11. Figure 15-11. One Way Split. Choose Table 1. In the list of catalogs shown in the Base Table Catalog and Base Tables panes, double-click on the catalog that contains the base table for the partition.
1. Enter the name of the target partition in the partition field or click on the Volume button to select the volume from the Volume window. 2. Click on the arrows in the Catalog field to display a list of catalogs. 3. Double-click on the name of the catalog. Assign File Attributes Use the First Target Specification pane to specify the partition’s file attributes. To assign the file attributes 1.
Move the Partition To perform the one-way move, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later. Performing a Two-Way Split To perform a two-way split, click on Table Partitions in the Main window, then click on Two-Way Split. The Two-Way Split window will be displayed, as shown in Figure 15-12. Figure 15-12.
Choose Table 1. In the list of catalogs shown in the Base Table Catalog and Base Tables panes, double-click on the catalog that contains the base table for the partition. The tables in the catalog are displayed in the Base Table field. 2. Double-click on the table you want to use as a base table. 3. Click on Two Way/Split. Assign Names and Locations Use the First Target Specification pane to assign a name and location for the first partition you are moving.
Assign Primary-Key Values Use the Primary-Key pane to specify primary-key values for both partitions. To assign primary-key values 1. In the Column Name field, choose a column of the primary key by clicking on the name from the list of column names. 2. Place the cursor in the First Key field for the selected column and enter the value for that column. 3. Repeat Steps 1 and 2 for all other columns of the primary-key.
CHAPTER 16. MANAGING INDEX PARTITIONS A partition is a portion of the index residing on a volume different from the volume on which the index was initially created. When creating partitions of an index, you specify the number of partitions and the volume, catalog, and primary-key values for each partition.
Figure 16-1 Partition Selection window Choose Base Index Use the Base Index Catalog and Base Index panes to choose base indexes. 1. In the list of catalogs shown in the Base Index Catalog field, double-click on the catalog that contains the base index for the partition. The indexes in the catalog are displayed in the Base Index field. 2. Double-click on the index you want to use as a base table.
2. Double-click on the index you want to use as a base index for partition. The Partitions in the index are displayed in the Index Partition list box. 3. Select the partition by double clicking the partition. Selected partition will be displayed in the Text field. Also Partonly option will be clicked. User will not be able to use Manual design option, once partition of a index is selected. User can select base partition for partonly option by clicking partonly checkbox.
Manual Design When you click on Manual Design in the Partition Selection window, the Manual Design window is displayed, as shown in Figure 16-2. Figure 16-2. Manual Design Window. To redesign the index partitions manually 1. Specify the new number of partitions wanted in the Number of Partitions field. 2. Specify the new sample rate in the Sample Rate field. 3. Specify the new primary-key values in the Primary-Key pane. The New Row button allows you to add more primary-key values to the partitions. 4.
Note: You may switch to Auto Design mode at any time by clicking on the Auto Design button. In this mode, you specify the number of partitions and the sampling rate and dba/m Database Manager will compute the primary key values when you press the Recompute button. Automatic Design To have dba/m Database Manager automatically design index partitions 1. Specify the number of partitions in the Number of Partitions pane in the Auto Design pane as shown in Figure 18-1. 2. Specify the sample rate. 3.
Figure 16-3. View Partition Window. Choose Index Use the Index Catalog and Index panes to choose indexes. 1. In the list of catalogs shown in the Index Catalog field, double-click on the catalog that contains the base index for the partition. The indexes in the catalog are displayed in the Index field. Double-click on the index you want to use as a index. Assign Sample Rate Enter the sample rate in the Sample Rate field. Compute Row Distribution Click on Compute.
Adding Partitions To add partitions to an index, click on Index Partitions in the Main window, then click on Add. The Add Partition window will be displayed, as shown in Figure 16-4. Figure 16-4. Add Partition Window. Assign the Number of Partitions Enter the number of partitions you want in the index in the Number of Partitions field. Note: The number you enter should be one less than the total number of partitions, because the primary partition is automatically assigned when the index is created.
Figure 16-5. Select Volume Window. 2. To select volumes for the partition, click on the name of the volume from the Volume Name field. Select as many volumes as there are new partitions, and click OK. 3. The Select Volumes window closes and the Add Partition window is displayed again. Partition names are assigned based on the selected volume and the name of the index.
1. Choose a partition by clicking on its name in the Partitions pane. 2. Assign a catalog to the partition by double-clicking on a catalog in the list shown in the Catalogs pane. 3. Repeat Steps 1 and 2 to assign catalogs to all partitions. Assign File Attributes Use the Partition File Attributes pane, to assign file attributes. To assign file attributes for the partition 1. Choose a partition by clicking on its name. 2. Select the Format Type for the partition by clicking either Format1 or Format2 . 3.
• Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later. Altering Partitions Use the Alter partition pane to modify the file attributes of a partition. To alter a partition, click on Index Partitions in the Main window, then click on Alter. The Alter Partitions window will be displayed, as shown in Figure 16-6. Figure 16-6. Alter Partitions Window.
• To allocate extents, click on Allocate then enter the number of extents in the Allocate field • To deallocate unused extents, click on Allocate to remove the checkmark. 3. In the Extent in Pages pane, perform one of the following: • To enter the primary and secondary extent sizes, click on Primary, then enter the primary extent size in the Primary field and the secondary extent size in the Secondary field.
When you click on Drop , Drop partition screen will appear as shown below in Figure 16-7. Figure 16-7. Drop Partition Window. When you click on Drop , Drop partition screen will appear as shown below Choose Partition: To choose a partition, click on the name of the partition you want to drop in the Partition Name column..
• To display all objects, including programs, which depend on the index partitions, click on Dependent Objects. All SQL objects which depend on the index partitions are displayed. Drop the Partitions To drop the partitions from the index, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
Figure 16-8. Simple Move Window. Choose a Index 1. From the list of catalogs shown in the Index Catalog field, double-click on the catalog that contains the index. The indexes in the catalog are displayed in the Index field. 2. Double-click on the index you want to use. 3. Click on Simple Move. The Index Partition-Simple Move window will be displayed as shown in Figure 16-8. Select the Partition Select the partition to be moved by clicking on the partition name in the Partition Name pane.
• To enter the number of extents, click on Extents, then enter the number of extents in the Extents field. 2. Enter the maximum number of extents in the Max Extents field. 3. Enter the minimum percentage of space to be left in a data block in the Dslack field. 4. Enter the minimum percentage of space to be left in an index block in the Islack field. 5. Enter the minimum percentage of space to be left in data and index blocks in the Slack field.
Figure 16-9. One Way Split. Choose Index 1. In the list of catalogs shown in the Index Catalog and Indexes panes, double-click on the catalog that contains the base index for the partition. The indexes in the catalog are displayed in the Index field. 2. Double-click on the index you want to use as a index. Assign Name and Location Use the First Target Specification pane to assign a name and location for the partition you are moving. 1.
Assign File Format Select the Format Specification for the partition by clicking either Format1 or Format2 option button. Assign File Attributes Use the First Target Specification pane to specify the partition’s file attributes. To assign the file attributes 1. In the Extent in Pages pane, perform one of the following: • To enter the primary and secondary extent sizes, click on Primary, then enter the primary extent size in the Primary field and the secondary extent size in the Secondary field.
Move the Partition To perform the one-way move, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later. Performing a Two-Way Split To perform a two-way split, click on Index Partitions in the Main window, then click on Two-Way Split. The Two-Way Split window will be displayed, as shown in Figure 16-10. Figure 16-10.
Choose Index 1. In the list of catalogs shown in the Index Catalog and Indexes panes, double-click on the catalog that contains the index for the partition. The indexes in the catalog are displayed in the Index field. 2. Double-click on the index you want to use. 3. Click on Two Way/Split. Assign Names and Locations Use the First Target Specification pane to assign a name and location for the first partition you are moving. Then complete the Second Target Specification pane for the second partition. 1.
Assign Primary-Key Values Use the Primary-Key pane to specify primary-key values for both partitions. To assign primary-key values 1. In the Column Name field, choose a column of the primary key by clicking on the name from the list of column names. 2. Place the cursor in the First Key field for the selected column and enter the value for that column. 3. Repeat Steps 1 and 2 for all other columns of the primary-key.
CHAPTER 17. MANAGING PROGRAMS Using dba/m Database Manager, you can • Alter the attributes of a program • Drop a program • Copy or move a program to a new location • Rename a program • Compile programs You can manage programs by accessing windows from the Object Tree button, the Custom Tree button, or the Programs button, in the Main window. The procedures in this chapter only describe how to access program management windows from the Programs button.
Figure 17-2. Alter Program Window. Choose a Program Use the Catalogs and Programs Pane to choose a program. To choose a program 1. In the Catalogs field, double-click on the name of the catalog that contains the program. The programs in the selected catalog are displayed in the Programs field. 2. Choose the program to alter by double-clicking on the program name. The security attributes of the program are displayed. Change the Security Change the security attributes of the program in the Security Pane.
3. Use the Clearonpurge check-box to set the clear-on-purge attribute. If you check this field, the program file is not deleted when the program is dropped. 4. The Program Format Version is displayed in the Program Format Version field. Save the Program To save the program, click on one of the following: • Execute. Executes the command immediately. • Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
Choose a Program To choose a program to drop 1. In the catalogs field, double-Click On the name of the catalog that contains the program. The programs in the selected catalog and some its attributes are displayed in the Program Attributes pane. 2. Choose the program to drop by clicking on the name of the program. A delete marker appears in the row header of the selected program. 3. Repeat step 2 to select additional programs.
Figure 17-4. Compile Program Window. Compiling Old Programs Choose Programs To choose an old or existing program to compile 1. In the Catalogs field, double-click on the name of the catalog that contains the program. The programs in the catalog are displayed in the Program pane. 2. Use the following methods to choose the programs you want to compile. The selected programs are displayed in the Program Name pane. • To choose one program at a time, double-click on the name of the program.
• To remove one program from the selected list of programs, click on the row header of the program to be deleted. • To delete all selected programs, click on DeselectAll. You can deselect all selected programs by clicking on DeSelectAll. Choose Compiler Options Compiler options are located in the Option pane, as shown in Figure 17-5. Figure 17-5. Compile Options Pane. To choose compiler options 1.
• To use defines that are currently stored with the program, click on Storeddefines. The compiler will ignore any of the defines in the current Guardian context. 5. Choose one of the recompile options by clicking on the appropriate button: • To have only the statements that are actually executed recompiled, click on Recompileondemand. Otherwise, all statements are recompiled if the program is invalid. • To have all statements in the selected programs recompiled, click on Recompileall. 6.
Figure 17-6. Compile New Program Window Choose New Programs To choose new programs, you select a volume and subvolume in which the programs are located and then the programs using the VolumesSubvolumes-Programs pane. 1. In the Volumes field, double-click on the volume that contains the programs. The subvolumes in that volume are displayed in the Subvolumes field. 2. In the Subvolumes field, double-click on the name of the subvolume that contains the programs. The programs in the subvolume are displayed. 3.
The selected programs are displayed in the Selected Programs pane shown in Figure 17-7. Figure 17-7. Selected Programs Pane. Assign Catalog Use the Catalogs pane to assign the catalog for a program. 1. To choose a program, click on the name of the program in the Selected Programs pane. 2. In the Catalogs field, choose a catalog to register the programs in by double-clicking on the name of the catalog. Choose Compiler Options Compiler options are located in the Options pane. To choose compiler options, 1.
• To use defines that are defined in the current Guardian context, click on Currentdefines. To establish a new context of defines, specify a TACL obey file name in the Defines Obey File field. • To use defines that are currently stored with the program, click on Storeddefines. The compiler will ignore any of the defines in the current Guardian context. 5.
Figure 17-8. Copy/Move Window. Choose Programs Use the Catalogs and Programs pane to choose the programs to copy or move. To choose the programs, 1. In the Catalogs field, double-click on the name of the catalog that contains the program. The programs registered in the catalog are displayed in the Programs field. 2. Use the following methods to choose the program to copy or move. The selected programs are displayed in the Program Source and Target fields.
Define the Target Location Use the Program Source and Target fields, to define the target of the copy or move operation. Type the target location in the Program Name Target field. Choose the Copy or Move Operation Use one of the following methods to choose the copy or move operation: • To switch between copy and move options for one program at a time, double-click on the Copy/Move field for each program. • To move all selected programs, click on MoveAll. • To copy all programs, click on CopyAll.
• To use defines that are currently stored with the program, click on Storeddefines. The compiler will ignore any of the defines in the current Guardian context. 5. Choose one of the recompile options by clicking on the appropriate button: • To have only the statements that are actually executed recompiled, click on Recompileondemand. • To have all statements in the selected programs recompiled, click on Recompileall. Compiler options are located in the Compile Options pane.
Figure 17-9. Rename Program Window. Choose the Program Use the Catalogs and Programs pane to choose the program to rename. To choose a program, 1. In the Catalogs field, double-click on the name of the catalog that contains the program. The programs in the catalog are displayed in the Programs field. 2. In the Programs field, double-click on the name of the program you want to rename. The attributes of the selected program are displayed.
• Show. Displays the SQL commands created by dba/m Database Manager without executing them. • Queue. Queues the command to be submitted in a batch later.
16 Managing Programs
