HP Neoview Database Administrator's Guide HP Part Number: 544820-001 Published: April 2008 Edition: HP Neoview Release 2.
© Copyright 2008 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Manual.............................................................................................................9 Who Should Use This Manual................................................................................................................9 New and Changed Information in This Edition.....................................................................................9 Related Documentation...........................................................................
5 Managing Database Objects.....................................................................................35 Schemas.................................................................................................................................................36 Displaying Schemas and Their Objects...........................................................................................36 Creating Schemas................................................................................................
Displaying Scheduled Operations' Status............................................................................................88 Modifying Scheduled Operations...................................................................................................91 Deleting Scheduled Operations.......................................................................................................91 Neoview VTS System Status Page.................................................................................
List of Tables 4-1 6-1 8-1 8-2 Notes for Creating Tables..............................................................................................................32 Neoview VTS System Status Field Information ...........................................................................92 USTAT_AUTO_LIST View..........................................................................................................106 USTAT_AUTO_LIST_DETAIL View...............................................................
About This Manual This guide describes concepts and tasks that users need to perform in order to use the HP Neoview platform to manage very large databases. Who Should Use This Manual This guide is intended for those who perform standard database administration tasks. It provides basic concepts and task information to get you started quickly and easily. You can find additional information about installing and maintaining the hardware in the HP Neoview Owner’s Manual.
Neoview Management Dashboard Information on using the Dashboard Client, including how to install the Client, Client Guide for Database start and configure the Client Server Gateway (CSG), use the Client windows Administrators and property sheets, interpret entity screen information, and use Command and Control to manage queries from the Client. Neoview Management Dashboard Online Help Context-sensitive help topics that describe how to use the Neoview Management Dashboard Client.
Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This task is described under “Changing the Password and Expiration Date of a Role” (page 23). General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS Uppercase letters indicate keywords and reserved words.
1 Introduction to the HP Neoview Platform The HP Neoview platform provides enterprise-class, SQL data warehousing to support your business intelligence needs. HP leverages industry-standard servers and 30 years of experience with highly available, massively parallel processing in this platform, to provide availability, scalability, and performance. Installation and management of the platform is streamlined and simple.
2 Getting Started Your system is delivered ready to use: • • • The system is preconfigured. Table spaces are available and ready for data. ODBC is installed and ready to go. For details on installing ODBC drivers, see the Neoview ODBC Driver Manual. This chapter provides an overview of the Neoview platform and covers the administrative tasks you need to perform when you first receive your system.
About The Super ID Your system is preconfigured with a super user ID. The super ID is configured to be suspended and HP recommends that you leave it suspended during normal operation. CAUTION: For your system’s security, change this password as soon as you receive your system. To change the super ID password, follow the procedures described in “Managing the Super ID” (page 95). About The Services ID Your system is preconfigured with a services ID. It is intended to be used by HP Support.
Management Tools Use these tools to manage your Neoview platform: Use this tool... To... Neoview DB Admin Manage your data tables, user IDs, and roles for data access privileges. HP automatically includes this Web-based interface on your Neoview platform. Use a Web browser to access DB Admin at the IP address provided by HP support. Microsoft ODBC Database Administrator Add, configure, and manage ODBC client data sources. HP automatically installs ODBC components on your Neoview platform.
To... See... Create additional schemas “Displaying Schemas and Their Objects” (page 36) Display lists of schemas “Displaying Schemas and Their Objects” (page 36) Working With Tables Your Neoview platform is not preconfigured with tables, but you can create them immediately. To... See...
Only members of the services ID and role.mgr can change the logon help link. To customize a help link: 1. 2. 3. Start DB Admin and log on using the service ID or role.mgr. Click DB Admin and then click HP Neoview DB Admin Settings. Enter the text you want to appear as the help link in the Link Display Name field and the URL of your webpage, in the form http://IP_address/filename, in the Logon Help Link field. Click Apply: If the Link Display Name is blank, no help link appears on the start page.
3 Managing Roles, User IDs, and Linux Accounts This chapter describes: • • • • • • • • • “Understanding Roles and User IDs” (page 21) “Creating a New Role” (page 23) “Changing the Password and Expiration Date of a Role” (page 23) “Deleting a Role” (page 24) “Creating a New User ID” (page 25) “Changing the Password and Expiration Date of a User” (page 26) “Deleting a User ID” (page 27) “Viewing User Status” (page 28) “Managing Security Policies” (page 29) Understanding Roles and User IDs Roles What are the
ROLE.DBA is assigned to the DBA role. The initial password is hp4Binfo. This user ID is provided as a convenience. HP recommends that you create your own user IDs based on the DBA role and use them on your system. For additional security, change this ID’s password. ROLE.USER is assigned to the USER role. The initial password is hp4Binfo. This user ID is provided as a convenience. HP recommends that you create your own user IDs based on the USER role and use them on your system.
Example: Create user IDs that use these roles: 1. 2. 3. Log on to DB Admin using the ROLE.MGR user ID. Click the Users tab, then the Add/Alter tab, then select Add User. Select the PAY role and enter a unique user name and password information and click OK. You can use any name for the User ID, such as “Payroll1” or “SmithT”. A user can now log on to the system and access payroll tables with this user ID. Repeat this process to add users that use the CST role. Creating a New Role 1. 2. 3.
4. Enter the new information and click OK. Passwords can be 1 to 64 characters long and may not contain double quotes (“). For additional information, see DB Admin online help NOTE: Neoview Releases 2.2 and later offer 64-bit encryption of passwords. If you upgrade from Release 2.x to Release 2.2 or later, you do not get this added security unless you alter an existing password or add a new password. If you recently upgraded to Release 2.
3. From the Delete Role list, select the role to be deleted. NOTE: 4. The list includes user IDs and roles; be sure to select the correct role name to delete. Click OK. For additional information, see DB Admin online help. Creating a New User ID 1. 2. 3. 4. Start DB Admin and log on using any user ID assigned to the MGR role. Click the Users tab and then click the Add/Alter tab. Select Add User. From the User list, select the role to which the user ID will be assigned.
For additional information, see DB Admin online help. Changing the Password and Expiration Date of a User 1. 2. 3. Start DB Admin and log on using any user ID assigned to the MGR role. Click the Users tab and then click the Add/Alter tab. Select Alter User and choose a user name from the list: 4. Enter the new information and click OK. Passwords must be 1 to 64 characters long and may not contain double quotes (“). For additional information, see DB Admin online help. NOTE: Neoview Releases 2.
or by clicking the “Change Password” link at the top of the screen after they have logged on: Deleting a User ID 1. 2. Start DB Admin and log on using any user ID assigned to the MGR role. Click the Users tab and then click the Delete tab.
3. From the Delete User list, select the user ID to be deleted. NOTE: 4. The list includes user IDs and roles; be sure to select the correct user ID to delete. Click OK. For additional information, see DB Admin online help. Viewing User Status 1. 2. 28 Start DB Admin and log on using any user ID assigned to the MGR role.
3. To see status for all users within a role, double-click on the role folder in the left panel: For additional information, see DB Admin online help. Managing Security Policies You can control how DB Admin handles logon attempts and password features such as length, grace period, history level, and how often a password can be changed. 1. 2. Start DB Admin and log on using any user ID assigned to the MGR role. Click the Users tab and then click the Security Policies tab.
4 Planning Your Database This chapter describes: • • “Guidelines for Database Administrators” (page 31) “Neoview SQL Table Options” (page 31) Guidelines for Database Administrators • CREATE TABLE statement — You can create partitioned and nonpartitioned tables. When you create a partitioned table, the table is automatically partitioned across all the disk volumes on the system.
Table 4-1 Notes for Creating Tables Table Option Notes Columns • • • • Primary Key Columns • You can change the sequence of the columns in the primary key. Although not important from a logical primary key perspective, it is important from the clustering key perspective. • You should use DATE columns as leading columns of the primary key. For more information about DATE columns, see the Neoview SQL Reference Manual. • You can specify the order of the column: either ascending or descending.
Table 4-1 Notes for Creating Tables (continued) Table Option Notes Materialized Views • Only ON REQUEST materialized aggregate views are supported • Only INITIALIZATION ON REQUEST type for materialized views is supported • Creation of the materialized view is specified: — Query expression for the materialized view. This includes the SELECT FROM, WHERE, and GROUP BY clauses. — For HASH BY, you must choose from the list of GROUP BY columns. Limits • Column names can be up to 128 characters in length.
5 Managing Database Objects This chapter describes how to manage your database using the DB Admin tool. The Neoview platform also supports a client-based utility, Neoview Command Interface, which enables you to enter SQL statements interactively or from script files in its command-line interface. You can also pass an SQL statement to NCI from a Perl or Python command line or from a Perl or Python program. For more information, see the Neoview Command Interface (NCI) Guide.
• • • “Changing Materialized Views” (page 76) “Dropping (Deleting) Materialized Views” (page 77) “Materialized View Groups” (page 77) — “Creating Materialized View Groups” (page 77) — “Displaying Materialized View Groups and their Properties” (page 78) — “Changing Materialized View Groups” (page 79) — “Dropping (Deleting) Materialized View Groups” (page 79) • “Synonyms” (page 79) — “Creating Synonyms” (page 80) — “Displaying Synonyms and Their Properties” (page 81) — “Changing Synonyms” (page 81) — “Drop
3. Click on the name of a schema to see information about the schema and its objects.
Click the Materialized View Groups tab to see materialized view groups that belong to the schema: 38 Managing Database Objects
Click the Views tab to see views that belong to the schema: Click the Procedures tab to see procedures that belong to the schema: Schemas 39
Click the Synonyms tab to see synonyms that belong to the schema: For additional information, see DB Admin online help. Creating Schemas 1. 2. 3. Start DB Admin and log on using any user ID. The DBA role is intended for this task. Click the Database tab. DB Admin displays a list of existing schemas and their owners. Right-click Schemas and select Create Schema Wizard. Enter a name for the new schema, then click Finish: Schema names must not start with HP_.
Granting and Revoking Access (Privileges) to a Schema 1. 2. 3. 4. Start DB Admin and log on using the user ID that created the schema, or log on as a user authorized to grant privileges to others in that schema. Click the Database tab, then navigate to the schema whose privileges you want to change. Right-click the schema name to which you want to grant privileges and select Grant/Revoke privileges or click the Privileges tab, then the Grant/Revoke button in the right pane.
3. Right-click the schema name and select DDL Tool.... DB Admin displays DDL information for this schema: Dropping (Deleting) Schemas 1. 2. 3. 4. 5. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to drop the table. Click the Database tab, then navigate to the schema you want to delete. Right-click the schema name and then select Drop. DB Admin prompts you to select RESTRICT or CASCADE to indicate if you want to drop dependent objects.
Creating Tables NOTE: Only user IDs that belong to the role that owns a schema can create tables in that schema. In the schema shown here, any user that belongs to the DBA role can create tables in that schema. 1. 2. 3. 4. Start DB Admin and log on using any user ID allowed to create tables. The DBA role is intended for this task. Click the Database tab, then select the schema, owned by your user ID role, in which you want to create the table. DB Admin displays the tables in this schema.
5. Specify a maximum size for the table, which will be used for its extent sizes, and indicate whether it should be partitioned across all qualified volumes of the cluster. Whether or not you specify a size, you can indicate whether the table is partitioned. The default is partitioned. Click Next: 6. DB Admin prompts you to define columns for the table.
For more information about data types, see Appendix A (page 117). 7. DB Admin displays the column you have added: 8. You can now edit or delete the column, or select Add Column to continue adding columns to your table.
9. When you have added all the columns, click Next. 10. Enter information for the table primary key by selecting columns and adding them to the Primary Key Columns or Hash By Columns lists. CAUTION: Do not use the Identity Column field without consulting HP support. 11. When you have selected the key, click Next. 12. DB Admin displays the Attributes screen where you can indicate if inserts should be logged for this table. SQL maintains a log in order to refresh materialized views.
13. Select Finish when you are ready. DB Admin prompts you to confirm that you want to create this table. When you click Yes, it attempts to create the table and either displays an error or indicates that the creation was successful and displays its information. 14. When the wizard completes successfully, grant access privileges to the table. See “Granting and Revoking Access (Privileges) to a Table” (page 50) For additional information, see DB Admin online help.
4. Enter a name for the new table and click Next: 5. DB Admin prompts you to specify the table size, whether it should be partitioned, its columns, its primary key, and whether inserts should be logged, with screens like the ones you use to create a table. When you have provided this information, click Finish. DB Admin prompts you to confirm that you want to create this table.
1. Use a text editor to create a DDL file with definitions for as many tables as you want to create.
5. To create a table or tables, select Import DDL From Local File and navigate to the location of the DDL file on your workstation. After you confirm that you want to use this file, the Create Table Tool displays the imported DDL settings: Make changes to the DDL, if necessary, by clicking the Column Definitions, Primary Key / Hash By, Table Size, and Attributes tabs. When you are finished with the table definition, click Create Table.
4. Select Grant or Revoke at the top of the screen and check All Privileges if appropriate. Select the table from the left column, then the user from the list in the center. Indicate row and column privileges and select the columns, with shift-click, if appropriate. Check With Grant Option to allow this user to grant these privileges to other users. Click Grant to set the privileges.
3. Click a table name in the lists in either pane. DB Admin displays the table’s information in the right pane.
Use the Alter Attributes button to open the Alter Attributes screen. The Space tab: Use the scroll bar to see percent allocated for all partitions.
The DDL tab: The DDL Tool button displays DDL information. The Privileges tab: Use the Grant/Revoke button to start the Grant/Revoke Privileges Tool. For additional information, see DB Admin online help. Displaying Table DDL Information 1. 2. 54 Start DB Admin and log on using any user ID. The DBA role is intended for this task. Click the Database tab, then navigate to the schema.
3. To display DDL information for one table, click the Database tab, then navigate to the schema and right-click the table name. DB Admin displays DDL information for this table: You can also display DDL information by displaying the table’s properties and viewing the DDL tab. See “Displaying Tables and Their Properties” (page 51). Adding Columns to a Table 1. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to make changes to that table.
2. 3. Click the Database tab, then navigate to the schema and table you want to change. Right-click the table name and select Alter, then Add Column(s) or click the Columns tab, then the Add Columns button in the right pane. Provide information for the new column and click OK: 4. DB Admin displays the column you have added. You can now edit or delete the column, or you can click Add Column to continue adding columns to your table. When you have added all the columns, click OK.
3. Right-click the table name and select Alter, then Attributes or click the Attributes tab, then the Alter Attributes button in the right pane. Check or un-check Log Inserts Only and click OK: Renaming Tables 1. 2. 3. 4. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to grant privileges to others on that table. Click the Database tab, then navigate to the schema and table whose name you want to change.
Enabling or Disabling Reorganize and Update Statistics on Tables The owner of a schema can enable and disable Reorganize and Update Statistics for a table. 1. 2. 3. Start DB Admin and log on using the user ID that owns the schema that the table resides in or log on as a user authorized to grant privileges to others on that table. Click the Database tab, then navigate to the schema and table you want to change.
4. Indicate whether this should be a unique index and click Next: If you specify that the index is unique but the data is not unique, a uniqueness violation error is returned when you populate the index. 5. DB Admin displays the columns available for indexing and hash partitioning. Select the columns you want to use by using the buttons to add them to the appropriate area, then click Finish.
DB Admin prompts you to confirm that you want to create this index. When you click Yes, it attempts to create the index and either displays an error or indicates that the creation was successful and displays its information.
6. You must populate the index before the system can use it. After you create an empty index, the Create Index Wizard prompts you to schedule a populate index operation. Indicate whether to populate the index offline or as an online operation with shared access, when the commit phase should occur, and what action to take if there is an error: 7. Schedule a time for the index to be populated. You can also specify an operation that must complete before the populate index starts.
Displaying Indexes and Their Properties 1. 2. Start DB Admin and log on using any user ID. Click the Database tab, then navigate to the schema and table and click the index name in the right pane, or open the Indexes folder. DB Admin displays the indexes for this table. Click the index name in the lists in either pane. DB Admin displays the index’s information in the right pane: You can view more information with the Columns, Attributes, Space, and DDL tabs.
Triggers A trigger is a mechanism that resides in the database and specifies that when a particular action—an insert, delete, or update—occurs on a table, DB Admin should automatically perform one or more additional actions.
5. Define the time, event, and column or columns for the trigger, then click Next: 6.
7. Enter the search condition—the condition that, when true, activates this trigger, then click Next: 8.
9. DB Admin prompts you to confirm that you want to create the trigger. When you click Yes, it attempts to create the trigger and either displays an error or indicates the rename was successful and displays its information. For additional information, see DB Admin online help. Displaying Triggers and Their Properties 1. 2. Start DB Admin and log on using any user ID.
The DDL tab: Enabling or Disabling Triggers on Tables 1. 2. 3. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to grant privileges to others on that table. Click the Database tab, then navigate to the schema and table where you want to create a trigger. Right-click the table for which you want to create a trigger and select Enable all triggers to enable triggers, or Disable all triggers to disable them.
2. Click the Database tab, then select the schema in which you want to create the view. Right-click the Views folder and then select Create view to start the Create View Wizard, or click Create a new view in the right pane. Enter a name for the view and click Next: 3. Enter the DDL statement listing the fields to be included in the view and click Finish: 4. DB Admin prompts you to confirm that you want to create this view.
2. Click the Database tab, then navigate to the schema and click the view name in the right pane, or open the Views folder. DB Admin displays the views in this schema. Click the view name in the lists in either pane. DB Admin displays the view’s information in the right pane: You can view information under the DDL and Privileges tabs. Under the DDL tab, the DDL Tool button displays DDL information. For additional information, see DB Admin online help.
5. DB Admin prompts you to confirm that you want to rename the view. When you click Yes, it attempts to rename the view and either displays an error or indicates that the rename was successful and displays its information. For additional information, see DB Admin online help. Dropping (Deleting) Views 1. 2. 3. 4. 5. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to change that table.
3. DB Admin prompts you to specify the attributes for the materialized view: Initialization Type specifies whether the materialized view should be initialized at creation or refresh time, Refresh Type specifies when it should be refreshed, and Refresh Commit Each specifies the number of rows that the refresh operation processes before committing a transaction and processing more rows. Enter a value and click Next: 4.
5. You can indicate if you want the refresh operation for the materialized view to ignore changes to the table. Select the Ignore box for a base table if you want the refresh operation for the materialized view to ignore changes to the table. The refresh time for a materialized view grows almost exponentially as more tables participate in a join in the materialized view query expression. Therefore, the fewer tables in the join clause that need tracking, the better the refresh performance. Click Next: 6.
Creating a Materialized Aggregate View 1. 2. Start DB Admin and log on using any user ID that is authorized to access the table for which you want to create a materialized view. Click the Database tab, then select the schema in which you want to create a materialized view. Right-click the Materialized Views folder and then select Create materialized view to start the Create Materialized View Wizard. You can also click Create a materialized view in the right pane. Select Aggregate View or Join View.
4. Enter the DDL statements for the materialized view: Select lists the columns to select, From is the file name to select from, and Group By is a key field. Where is an optional field you can use to limit the selection. Click Next. 5. DB Admin allows you to indicate if the materialized view should ignore changes on the file.
6. DB Admin prompts you to confirm that you want to create this materialized view. When you click Yes, it attempts to create the view and either displays an error or indicates it was successful and displays the new view. DB Admin automatically creates a materialized view group that contains this materialized view. For information on creating, changing, and dropping materialized view groups, see “Materialized View Groups” (page 77). For additional information, see DB Admin online help.
For additional information, see DB Admin online help. Display DDL information with the DDL Tool by right-clicking the materialized view name in the tree in the left pane and selecting DDL Tool.... DB Admin displays DDL information for this materialized view. Changing Materialized Views 1. 2. 3. 4. 5. 76 Start DB Admin and log on using the user ID that created the table or log on as a user authorized to change that table. Click the Database tab, then navigate to a materialized view in the left pane.
6. DB Admin prompts you to confirm the alter. When you click Yes, it displays attributes for the materialized view. For additional information, see DB Admin online help. Dropping (Deleting) Materialized Views 1. 2. 3. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to change that table. Click the Database tab, then navigate to a view in the left pane. Right-click the view name and then select Drop. DB Admin prompts you to confirm the drop.
5. Select the materialized views you want to add, then click Finish. DB Admin confirms that you want to alter the materialized view group. When you click Yes it either displays an error or indicates that the creation was successful and displays the group’s DDL information. For additional information, see DB Admin online help. Displaying Materialized View Groups and their Properties 1. 2. 78 Start DB Admin and log on using any user ID.
For additional information, see DB Admin online help. Changing Materialized View Groups 1. 2. 3. 4. Start DB Admin and log on using the user ID that created the table or log on as a user authorized to change that table. Click the Database tab, then navigate to a schema and materialized view group in the left pane. Right-click the group name and then select Alter.
Creating Synonyms 1. 80 2. Start DB Admin and log on using any user ID that is authorized to access the table for which you want to create a synonym. Click the Database tab, then navigate to a schema in the left pane. Right-click the schema name or a table name and then select Create Synonym to start the Create Synonym Wizard. Or, you can click Create a synonym in the right pane. Enter a name for the synonym and click Next: 3. Select the object for which you want to create the synonym: 4.
Considerations for Synonyms • • • • • • The synonym and the referenced object can be in different schemas. Only the owner of the schema, the super ID, or the services ID can create, alter, or drop a synonym. A synonym name must be unique among tables and views in the schema. You can create an unlimited number of synonyms for an object. You can alter a synonym by associating it with a different object. You cannot rename a synonym. Displaying Synonyms and Their Properties 1. 2.
5. Navigate to and select the table with which you want to associate this synonym and click Finish. 6. DB Admin prompts you to confirm that you want to alter the synonym. When you click Yes, it attempts to alter the synonym and either displays an error or indicates that the alter was successful and displays its information. For additional information, see DB Admin online help. Dropping (Deleting) Synonyms 1. 2. 3. 4. 5.
2. Click the Database tab, then navigate to the schema and click the Procedure link in the right pane, or open the Procedures folder. DB Admin displays the procedures in this schema: 3. To display one procedure, click on its name in either pane. DB Admin displays the procedure: For additional information, see DB Admin online help and the Neoview Guide to Stored Procedures in Java.
6 Performing Database Operations This chapter describes: • • • • • • • • • “Schedule Maintain Wizard” (page 85) “Schedule Table Row Deletion” (page 88) “Displaying Scheduled Operations' Status” (page 88) “Modifying Scheduled Operations” (page 91) “Deleting Scheduled Operations” (page 91) “Neoview VTS System Status Page” (page 91) “Displaying the VTS System Status Page” (page 91) “Interpreting the VTS System Status Page” (page 92) “Data Recovery” (page 93) Schedule Maintain Wizard You can perform a variety
For a materialized view group, DB Admin displays this screen: Select the operation or operations and the option you want and click Next. The default is to perform all operations. To select operations from the list, clear All, then you can select individual operations.
You can select these maintain operations: Operation Description All Perform all maintenance operations for this table. Reorganize Table Defragment this table. Update Statistics Run update statistics on this table. Reorganize Table Indexes Defragment the indexes for this table. Refresh Materialized Views Update Materialized Views with the latest contents from the base table.
4. Click Next to display the Schedule page or Finish to schedule the operation to begin immediately. If you select Next, DB Admin displays the Schedule Maintain Wizard: Schedule Table Row Deletion To minimize table size and improve table performance, you should periodically delete older rows from tables. You can use the DB Admin Age Table operation to purge outdated rows that meet a specified delete predicate.
You can sort each column by clicking on its header. Click the Refresh button to refresh the screen. Click Save Table to save the table as a tab delimited file. Click View Table to view the table in a spreadsheet application. 3. To view an operation’s properties, double-click its name, or right-click the name and select Properties.
Performing Database Operations
4. To view or modify an operation’s schedule, right-click it and select Schedule: Modifying Scheduled Operations You can modify an operation’s schedule, but you cannot modify the object it will affect. To modify that, you must delete the operation, then reschedule it. Deleting Scheduled Operations 1. 2. 3. 4. 5. Start DB Admin and log on using the user ID that scheduled the operation you want to modify. Only the operation originator's user ID will see the operation listed in the Operations tab.
These browsers are supported on Windows: Microsoft Internet Explorer 6.0, Mozilla Firefox 1.5, and Netscape 8.1. Here is an example of the system status page: Interpreting the VTS System Status Page Table 6-1 provides more information about the fields shown in the system status page. Table 6-1 Neoview VTS System Status Field Information Box Box Title Field Description [1] System Host The host name of the Neoview VTS. Time The current date and time.
Table 6-1 Neoview VTS System Status Field Information (continued) Box [5] Box Title Connections* Field Description Total Size The total amount of space in the vault. Device The name of the virtual tape device. Kb/sec The transfer rate for the virtual tape device. C/Ratio The compression ratio for data traveling between the Neoview platform and the Neoview VTS. Cartridge The name of the cartridge mounted on the virtual tape device.
7 Other Operations This chapter describes: • • • “Managing the Super ID” (page 95) “Viewing System Events” (page 96) “Monitoring Queries” (page 101) Managing the Super ID The super ID is the user ID that has unlimited access to the platform. For system security, HP recommends that you configure your system for daily business operations with the super ID inactive. However, there might be times when you must activate the super ID; for example, if super ID privileges are needed to debug a problem.
3. Click Suspend Super or Activate Super. If you select Activate Super, you must enter a new password and confirm it. DB Admin prompts you to confirm that you want to suspend or activate the super ID. When you click Yes, it attempts to perform the operation and either displays an error or indicates that the operation was successful and displays the Manage Super screen with the super ID’s new status. Suspension or activation takes effect immediately.
4. $DBA Collector for database-related events: database activity, data loads, queries, and so on $ZLOG Used by HP Support $ZPHI Used by HP Support At the bottom of the page, click Show Events. A new window opens to display events: Cancel Home Generation time Process ID Subsystem ID 00001 2006-08-17 09:03:50 \HPQ0101.$SYSTEM *TANDEM.DISK.H01 $SYSTEM Unstructured Xsum Error Xsum Error 39 00002 2006-08-22 11:25:01 \HPQ0101.$SYSTEM *TANDEM.DISK.
5.
Viewing a summary of events This is a useful way to monitor past activity on your Neoview and to see what errors are occurring frequently. To view a summary of events: 1. 2. 3. 4. 5. 6. From View Options, select Standard. Enter a value in Number of Events. The default is 1000. If you enter a large value, you may need to increase the Timeout value. Select Suppress duplicates. From Log Positioning, select By time. Click Show Events. A new window displays the last 1000 events, with duplicates suppressed.
W:\nskomake\../common\IpcGuardian.cpp Line Number : 2565 Message : Last client aborted or last client connection failed SQL Text : (not available) 00006 2006-10-11 06:17:09 \HPQ0101.$MXOAS TANDEM.ODBCMX.G06 021002 Start SQL/MX server CORE_SRVR failed on port 18896. Event Type: 1 Component Name: ODBC/MX Service Object Reference: TCP:$ZTC0/18650:NonStopODBC 00007 2006-10-11 06:17:09 \HPQ0101.$MXOAS TANDEM.ODBCMX.G06 020007 A NonStop Process Service error 4002 has occurred.
HTTPS://your-system-url:9991 Monitoring Queries For information about monitoring queries, see the Neoview Management Dashboard Client User Guide.
8 Automating Update Statistics and Reorganize Operations You can automate both UPDATE STATISTICS and MAINTAIN (REORG) operations to run during a maintenance window (that is, a specific time interval) every day and to operate against a specific set of tables.
'INSERT' 'DELETE' 'schema' 'table' response adds the specified table to the list of tables on which automated UPDATE STATISTICS runs. The option name is case-insensitive. deletes the specified table from the list of tables on which automated UPDATE STATISTICS runs. The option name is case-insensitive. is the name of the database schema in which the table resides. To specify all existing schemas, use '*'. If you specify '*' for the schema, you must also specify '*' for the table parameter.
SQL>call hp_ustat.chg_auto_list('insert', '*', '*', ?); RESPONSE -------------------------------------------------------------------------------INSERTed 3 table names (all) into list. --- SQL operation complete. • This CALL statement invokes the CHG_AUTO_LIST() procedure, which deletes all table entries in the automated list, and the list becomes static: SQL>call hp_ustat.
NOTE: To query these views, you must be logged in as ROLE.DBA (or super.services or the super ID if you are HP support). HP_USTAT.USTAT_AUTO_LIST View The USTAT_AUTO_LIST view contains all the automated tables. Because the row width is less than 80 characters, long table and schema names might become truncated in the view. For full column data, query the USTAT_AUTO_LIST_DETAIL view instead. See Table 8-2. Table 8-1 USTAT_AUTO_LIST View Column Name Data Type Description SCH_NAME CHAR(25) Schema name.
SALES SALES PARTS ORDERS 0001-01-01 00:00:00 0 0001-01-01 00:00:00 0 --- 2 row(s) selected. For the syntax of the SELECT statement, see the Neoview SQL Reference Manual. Stopping Automated UPDATE STATISTICS HP_USTAT.
The MAINTENANCE option allows you to modify the time at which the task needs to be run by the scheduler on a daily basis. This option also allows you to specify the maximum runtime limit and the maximum REORG slice. AT time is the time in hh:mm:ss, in 24-hour clock notation, where hh represents the hour, mm represents minutes, and ss represents seconds at which the scheduler runs automated UPDATE STATISTICS and REORG tasks on a daily basis. By default, the time is 00:00:00 (midnight).
SQL>schedule maintenance maxruntime 120 Next run Max runtime State Days Last altered at : : : : : 18DEC07 19:14:00 120 minutes Enabled ALL DAYS 18DEC07 16:37:54 --- NS operation complete. • This command uses the REORG percentage of 5% to run the MAINTAIN (REORG) operation.
9 Managing Client Data Sources and Connectivity This chapter describes: • • • • • “Reconfiguring ODBC Client Data Sources” (page 111) “Configuring ODBC Client Data Sources for Update Operations” (page 111) “Removing ODBC Client Data Sources” (page 111) “Performing Database Connectivity Tasks” (page 111) “NDCS Services” (page 112) Reconfiguring ODBC Client Data Sources 1. Start the Microsoft ODBC Administrator: Start> Programs> HP ODBC 1.0> MS ODBC Administrator 2. 3. 4.
NDCS follows a client/server architecture: Client Components NDCS Components ODBC client application Association server Microsoft ODBC 3.51 driver manager Configuration server ODBC driver 3.
3. Click the Status, Data Source Status, or NDCS Server Status tab.
The Data Source Status tab displays this information: Column Description Service Name Internal name of the system in which the NDCS service is running Data Source Name User-created name for the data source State Current state of the data source Count Total number of running service Connected Number of servers connected to clients Maximum Maximum number of servers configured for this data source Start Ahead Number of servers started ahead of when they are needed Available Number of servers a
The NDCS Server Status tab displays this information: Column Description Service Name Internal name of the system in which the NDCS service is running Data Source Name Name of the data source for which the server is running State Current state of the service PID Client Process ID of the client connected to the server (blank if there is no connection) Application Name Name of the client application connected to the server (blank if there is no connection) User Name Name of the user connected to
A Data Types For details about SQL components, see the Neoview SQL Reference Manual.
Type SQL Designation Decimal number DECIMAL (1,scale) to DECIMAL (18,scale) PIC S9V9 DISPLAY to PIC S9(18) DISPLAY Date-Time Description Size or Range (1) Decimal number with optional scale; stored as ASCII characters; signed or unsigned for 1 to 9 digits; signed required for 10 or more digits 1 to 18 digits. Byte length equals the number of digits. Sign is stored as the first bit of the leftmost byte.
Type SQL Designation Description Size or Range (1) Interval INTERVAL Duration of time; value is in YEAR no constraint(6) the YEAR/MONTH range or MONTH 0-11 the DAY/HOUR/MINUTE/ SECOND/FRACTION range DAY no constraint HOUR 0-23 MINUTE 0-59 SECOND 0-59 FRACTION(n) 0-999999 in which n is the number of significant digits (default is 6; minimum is 1; maximum is 6); stored in 2, 4, or 8 bytes depending on number of digits scale is the number of digits to the right of the decimal.
B Special Neoview Features—Enabled Upon Request Mode_Special_2 NOTE: To enable these features, you must request access from your HP account representative. Mode_special_2 is a new read only default that helps you transition from your current platform to the Neoview platform. HP recommends that you use Neoview syntax and semantics on the Neoview platform to avoid getting errors.
DD-MM-YYYY Specify these formats in the SELECT statement while retrieving data. You must specify a supported date format in the SELECT list of DML queries, as this example shows: "SELECT column_a (FORMAT 'MM/DD/YYYY') FROM table" If you do not specify a FORMAT clause, Neoview SQL returns the default format, YYYY-MM-DD. The FORMAT clause is not supported in DDL and is silently ignored. Specifying a particular date format in a column definition does not cause the date values to be returned in that format.
Example 2 12.3450 12.3350 12.3451 12.3360 12.3330 • rounded rounded rounded rounded rounded to to to to to scale scale scale scale scale 2 2 2 2 2 = = = = = 12.34 12.34 12.35 12.34 12.33 Precision and Scale of Arithmetic Operations The maximum precision is limited to 18. If an arithmetic computation exceeds this precision, Neoview SQL returns an overflow error. The scale of the result is computed using the semantics of your previous DBMS.
Glossary business intelligence (BI) A type of enterprise decision-support system (DSS). data source The data a user wants to access and the associated operating system, database management system (DBMS), and network platform used to access the DBMS. To manage ODBC client access to data in a database appliance, you can add, configure, and delete ODBC data sources on the client workstation. data warehouse A large database that stores all data for a company in a structured way.
Index materialized view groups, 77 materialized views, 70, 73 roles, 23 schemas, 40 synonyms, 80 tables, 43 tables from existing tables, 47 user IDs, 25 views, 67 A Access privileges, granting and revoking , 41, 50 Adding columns to a table, 55, 56 indexes, 58 materialized view groups, 77 materialized views, 70 roles, 23 schemas, 36 tables, 43, 47 user IDs, 25 views, 67 Age table operation, 88 Automated REORG description of, 103 scheduling, 107 Automated tables listing, 105 setting, 103 Automated UPDATE ST
DDL for tables, 54 DDL for views, 69 indexes, 62 materialized view groups, 78 materialized views, 75 scheduled operations, 88 schemas, 36 stored procedures, 82 synonyms, 81 triggers, 66 views, 68 Documents, related information, 9 Dropping indexes, 62 materialized view groups, 79 materialized views, 77 schemas, 42 tables, 58 views, 70 E Events viewing database, 99 viewing dial out, 99 viewing history, 98 viewing in real time, 98 viewing summary, 99 viewing with OSM Event Viewer, 96 G Getting started, 15 Gr
security policies, 29 super ID, 95 users, 26 Performance log inserts only, 46 Populate index, 61 Privileges, granting/revoking access to schemas, 41 Privileges, granting/revoking access to tables, 50 R Reconfiguring client data sources, 111 Recovery data, 93 Refresh materialized view groups operation, 87 Refresh materialized views operation, 87 Removing client data sources, 111 Renaming views, 69 REORG operation, automating (see Automated REORG) Roles changing passwords and expiration dates, 23 creating, 2
synonyms, 81 system name, 18 tables, 51 triggers, 66, 67 views, 69 dropping indexes, 62 schemas, 42 tables, 58 event viewer, 96, 99 event viewer links, 99 grant/revoke privileges, 50 grant/revoke schema privileges, 41 help link, 19 indexes creating, 58 creating unique, 59 defining columns, 59 displaying, 62 dropping, 62 schedule populate index, 61 manage super ID, 95 managing security policies, 29 materialized view groups changing, 79 creating, 77 displaying, 78 materialized views changing, 76 displaying, 7
Scripting, 18 Security considerations, 15 managing policies, 29 STOP_AUTOMATED_STATS() stored procedure, 107 Stored procedures, 18, 82 Sun Java Plug-in, supported versions, 16 Super ID, managing, 95 Support, contacting, 13, 93 Synonyms changing, 81 creating, 80 displaying, 81 displaying DDL, 81 dropping (deleting), 82 System messages, viewing, 96 System name display, 18 System status page (Neoview VTS), 91 T Tables adding columns to, 55, 56 ageing operation, 88 changing, 55, 56 adding columns, 55 attribute
Create Synonym, 80 Create Table, 43 Create Table Using, 47 Create Trigger, 63 Create View, 67 Rename Table, 57 Schedule Maintain, 85 132 Index
