HP Neoview Database Administrator's Guide HP Part Number: 514114-001 Published: April 2009 Edition: HP Neoview Release 2.
© Copyright 2009 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...........................................................................
Changing the Password and Expiration Date of a Role.......................................................................24 Deleting a Role......................................................................................................................................25 Creating a New User ID........................................................................................................................26 Changing the Password and Expiration Date of a User.......................................
Creating Materialized View Groups...............................................................................................79 Displaying Materialized View Groups and their Properties..........................................................80 Changing Materialized View Groups.............................................................................................81 Dropping (Deleting) Materialized View Groups............................................................................81 Synonyms....
9 Managing Client Data Sources and Connectivity.................................................113 Reconfiguring ODBC Client Data Sources.........................................................................................113 Configuring ODBC Client Data Sources for Update Operations.......................................................113 Removing ODBC Client Data Sources................................................................................................
List of Tables 3-1 4-1 6-1 8-1 8-2 Naming Rules for Roles and UserIDs...........................................................................................22 Notes for Creating Tables..............................................................................................................34 Neoview VTS System Status Field Information ...........................................................................94 USTAT_AUTO_LIST 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 Transporter User Guide Information about processes and commands for loading data into your Neoview platform or extracting data from it. README files for installing Administration products • — README for the HP Neoview Transporter Java Client Management HP Database Manager (HPDM) Help topics that describe how to use the HP Database Manager Client to Online Help connect and manage a Neoview data warehousing platform.
• ODBC Client Administrator Online Help Context-sensitive help topics that describe how to use the ODBC Data Source Administrator. README files for installing Connectivity products — README for the HP Neoview JDBC Type 4 Driver — README for the HP Neoview ODBC Driver for Windows — README for the HP Neoview ODBC Drivers for UNIX Reference Mapping Tables for Neoview Character Sets A hyperlinked collection of East Asian characters supported by Neoview character set functionality.
Publishing History Part Number Product Version Publication Date 543668-001 HP Neoview Release 1.2 October 2006 543758-001 HP Neoview Release 2.0 March 2007 544558–001 HP Neoview Release 2.2 August 2007 544820–001 HP Neoview Release 2.3 April 2008 514114–001 HP Neoview Release 2.4 April 2009 HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs.
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.
Neoview User Roles and IDs Your Neoview system is delivered with three preconfigured user roles and user IDs: Role User ID Default Password MGR ROLE.MGR hp4BAdmn DBA ROLE.DBA hp4Binfo USER ROLE.USER hp4Binfo You should use the ROLE.MGR user ID to set up user IDs on your system as described under “User IDs” (page 21). CAUTION: system.
end on the first Sunday in November. These dates were previously the first Sunday in April and the last Sunday in October. With this change, the JRE stored rules of the DST observance will be outdated. As a result, any date and time display in DB Admin may no longer be accurate unless you install the new JRE stored rules for DST. Follow the directions on Sun's website: http://java.sun.com/developer/technicalArticles/Intl/USDST/ These Java runtime product versions are not supported: • Versions prior to 1.4.
2. Log on to DB Admin with a valid user ID and password. For more information about user IDs, see “Working With User IDs” (page 19). For detailed descriptions of each window in DB Admin and information about how to perform other tasks, see DB Admin online help. Working With Schemas Your Neoview platform is preconfigured with these schemas: 18 This schema Is used for... DB All tables that database administrators create in the database for end users, including dimension and fact tables.
Working With Tables Your Neoview platform is not preconfigured with tables, but you can create them immediately. To... See... Create a table “Creating Tables ” (page 45), “Creating Tables From Existing Tables” (page 49), “Creating Tables Using the Create Table Tool” (page 50) Manage access privileges to tables “Granting and Revoking Access (Privileges) to a Table” (page 52) Add columns to a table “Adding Columns to a Table” (page 57) Working With User IDs 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 You can manage users and roles one at a time with DB Admin. If you want to create or manage many users and roles at one time, there are user management commands available through the Neoview Command Interface (NCI). See Appendix C (page 125) for these user management commands. Introduction to Roles and User IDs Roles What are they? Roles are vehicles for assigning data-access privileges to user IDs.
Thereafter Any user ID assigned to the MGR role... can create and alter roles and user IDs. Any user ID assigned to the DBA role... can create tables in the DB schema. Any user ID assigned to the USER role... can create tables in the USR schema.
You might create one user ID, assigned to the role PAY, to access payroll information, and another user ID, assigned to the role CST, to access customer account information. In this scenario, the role PAY is granted access to tables containing payroll information, and the role CST is granted access to tables containing customer account information. Any user IDs assigned to these roles have the access privilege granted to that role. Example: Create new roles: 1. 2. 3. Log on to DB Admin using the ROLE.
4. Enter the required information and click OK. Role names are not case sensitive and are limited to 8 characters. For additional information, see DB Admin online help. Changing the Password and Expiration Date of a Role 1. 2. 3. 24 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.
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 26 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. 28 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. 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.
Rules for Passwords Passwords can: • • Be up to 64 characters in length. Not contain these special characters: double quote (“), semi-colon (;), or comma(,).
4 Planning Your Database This chapter describes: • • “Guidelines for Database Administrators” (page 33) “Neoview SQL Table Options” (page 33) 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.
• • • • “Displaying Materialized Views and Their Properties” (page 77) “Changing Materialized Views” (page 78) “Dropping (Deleting) Materialized Views” (page 79) “Materialized View Groups” (page 79) — “Creating Materialized View Groups” (page 79) — “Displaying Materialized View Groups and their Properties” (page 80) — “Changing Materialized View Groups” (page 81) — “Dropping (Deleting) Materialized View Groups” (page 81) • “Synonyms” (page 81) — “Creating Synonyms” (page 82) — “Displaying Synonyms and Th
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: 40 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 41
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 119). 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 52) 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. 56 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 53). 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: Changing Table Columns 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 make changes to that table. Click the Database tab, then navigate to the schema and table you want to change.
3. Right-click the table you want to rename and select Rename to start the Rename Table Wizard. Enter the new name and select the Cascade check box if you want dependent objects to reflect the new name, and then click Finish: 4. DB Admin prompts you to confirm that you want to rename the table. When you click Yes, it attempts to rename the table and either displays an error or indicates the rename was successful and displays its information. For additional information, see DB Admin online help.
5. DB Admin prompts you to confirm that you want to drop the table. When you click Yes, it attempts to drop the table and either displays an error or indicates the drop was successful. For additional information, see DB Admin online help. Indexes Since your Neoview platform is not preconfigured with tables, indexes are not configured, but you can create them immediately. An index is an ordered set of pointers to rows of a table. Each index is based on the values in one or more columns.
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.
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 79). 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. 78 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. 80 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. 82 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 87) “Schedule Table Row Deletion” (page 90) “Displaying Scheduled Operations' Status” (page 90) “Modifying Scheduled Operations” (page 93) “Deleting Scheduled Operations” (page 93) “Neoview VTS System Status Page” (page 93) “Displaying the VTS System Status Page” (page 93) “Interpreting the VTS System Status Page” (page 94) “Data Recovery” (page 95) 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. To purge a table, use the Neoview Command Interface (NCI) and issue a DELETE command with the WITH MULTI COMMIT or WITH NO ROLLBACK option.
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 97) “Viewing System Events” (page 98) “Monitoring Queries” (page 103) 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. You can also specify tables that you do not want UPDATE STATISTICS to automatically operate on.
'INSERT' adds the specified table to the list of tables on which automated UPDATE STATISTICS runs. The option name is case-insensitive. 'DELETE' deletes the specified table from the list of tables on which automated UPDATE STATISTICS runs. The option name is case-insensitive. 'EXCLUDE' adds the specified table to the list of tables on which automated UPDATE STATISTICS is prevented from running. The option name is case-insensitive. 'schema' is the name of the database schema in which the table resides.
NOTE: When you delete all tables from the automated list, warnings 6010 and 6011 continue to occur instead of warnings 6007 and 6008. To restore the warning messages to the ones that occurred before automation, contact HP support. • • • The exclude operation is intended for use when the list of automated tables is dynamic — that is, using the procedure CHG_AUTO_LIST('insert','*','*',?).
SQL>call hp_ustat.chg_auto_list('delete', 'sales', 'parts', ?); RESPONSE -------------------------------------------------------------------------------Table name "SALES.PARTS" DELETEd. --- SQL operation complete. Listing the Automated Tables To list the tables on which UPDATE STATISTICS runs automatically and to display when UPDATE STATISTICS was last run on a table and if there were errors, query these views: • “HP_USTAT.USTAT_AUTO_LIST View” (page 108) • “HP_USTAT.
Table 8-2 USTAT_AUTO_LIST_DETAIL View Column Name Data Type Description SCH_NAME CHAR(128) Schema name. For a delimited schema name, the name is stored in internal format, which means that two successive double quotes are stored as only one double quote. TBL_NAME CHAR(128) Table name. For a delimited table name, the name is stored in internal format, which means that two successive double quotes are stored as only one double quote.
Scheduling Automated UPDATE STATISTICS and REORG SCHEDULE Command The SCHEDULE command enables you to display or modify the time during which the MAINTENANCE tasks are run by the scheduler. The SCHEDULE command with the MAINTENANCE task option is used by automated UPDATE STATISTICS to allocate the maintenance time between the UPDATE STATISTICS and MAINTAIN (REORG) operations.
Days : ALL DAYS Last altered at : 30NOV07 14:25:47 --- NS operation complete. • This command alters the time and the runtime limit for the MAINTENANCE task: SQL>schedule maintenance at 22:22:21 maxruntime 214 Next run Max runtime State Days Last altered at : : : : : 30NOV07 18:23:00 214 minutes Enabled ALL DAYS 30NOV07 14:24:21 --- NS operation complete. • This command uses the default percentage of 50% to run the MAINTAIN (REORG) operation.
9 Managing Client Data Sources and Connectivity This chapter describes: • • • • • “Reconfiguring ODBC Client Data Sources” (page 113) “Configuring ODBC Client Data Sources for Update Operations” (page 113) “Removing ODBC Client Data Sources” (page 113) “Performing Database Connectivity Tasks” (page 113) “NDCS Services” (page 114) 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 This information has been moved to the Neoview SQL Reference Manual.
C User Management Commands to Manage Multiple Neoview Users Commands to Manage Multiple Neoview Users You can manage multiple users at a time on the Neoview platform with five Neoview user management commands. These commands are executed using a CALL statement from Neoview Script: • createUser • dropUser • grantRole • revokeRole • changePassword For error messages returned by these commands, see “Messages for User Management Commands” (page 130).
can log on for the number of days specified by the platform grace period, provided the password is changed during the logon. (See the Neoview Database Administrator's Guide for more information about the platform grace period for password changes.) If expirationDate is the current date and the system grace period is greater than zero, then a user using username is required to change the password in order to logon. This is a required input parameter.
username is the user name to be dropped from the platform. username is a case-sensitive single quoted text string, with a maximum of 32 alphanumeric characters and special characters period (.), hyphen (-), and underscore (_). The first character of a user name must be alphabetic or numeric. User names may not begin with the string “role”. This is a required input parameter. error contains the error numbers if an error occurs. If no error occurs, this field is 0.
Considerations for grantRole • • • • The grantRole command is created in the catalog.schema named neo.uaf. You must either precede the command name with this catalog and schema name or set the default catalog.schema to neo.uaf first: SET SCHEMA neo.uaf; The grantRole command must be performed by a user belonging to the MGR role. The grantRole command affects all segments in the Neoview platform.
Syntax Description for changePassword changePassword {(username,| password,| expirationDays,| expirationDate,| error,| ; message)} expirationDate is {month-name|day|year}| username is the name of the user whose password you are changing. username is a case-sensitive single quoted text string, with a maximum of 32 alphanumeric characters and special characters period (.), hyphen (-), and underscore (_). User names may not begin with the string “role”.. This is a required input parameter.
Considerations for changePassword • • • • The changePassword command is created in the catalog.schema named neo.uaf. You must either precede the command name with this catalog and schema name or set the default catalog.schema to neo.uaf first: SET SCHEMA neo.uaf; The changePassword command must be performed by a user belonging to the MGR role. The changePassword command affects all segments in the Neoview platform.
6 6 User does not exist Cause The username specified in the command does not exist. Effect The operation fails. Recovery Reenter the command specifying a user name that currently exists. 7 7 Invalid character in password Cause The password specified contains an invalid character. Effect The operation fails. Recovery Reenter the command specifying a valid password. 8 8 Password must not be longer than 64 characters Cause The password specified contains more than the maximum of 64 characters.
14 14 Date must be in the form of Jan 1 2000 or Jan 01 2000 Cause The format of the date specified for expirationDate is invalid Effect The operation fails. Recovery Reenter the command specifying a valid date. 15 15 Invalid command syntax Cause The command syntax is invalid. This could be caused by missing or invalid parameters. Effect The operation fails. Recovery Check the syntax for this command and reenter the command using the correct syntax.
Effect The operation fails. Recovery Retry the command. If the command continues to fail contact your HP representative.
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 A Access privileges, granting and revoking , 43, 52 Adding columns to a table, 57, 58 indexes, 61 materialized view groups, 79 materialized views, 72 roles, 23 schemas, 38 tables, 45, 49 user IDs, 26 views, 69 Automated REORG description of, 105 scheduling, 110 Automated tables listing, 108 setting, 105 Automated UPDATE STATISTICS description of, 105 listing automated tables, 108 scheduling, 110 starting, 105 stopping, 109 C changePassword command syntax of, 128 Changing columns in a table, 59 materi
columns in a table, 53 DDL for indexes, 64 DDL for materialized view groups, 80 DDL for materialized views, 77, 78 DDL for schemas, 43 DDL for synonyms, 83 DDL for tables, 56 DDL for views, 71 indexes, 64 materialized view groups, 80 materialized views, 77 scheduled operations, 90 schemas, 38 stored procedures, 84 synonyms, 83 triggers, 68 views, 70 Documents, related information, 9 Dropping indexes, 64 materialized view groups, 81 materialized views, 79 schemas, 44 tables, 60 views, 72 dropUser command syn
refresh materialized views, 89 update statistics, 89 P Passwords, changing expiration, 24, 27 roles, 24 security considerations, 16 security policies, 30 super ID, 97 users, 27 Performance log inserts only, 48 Populate index, 63 Privileges, granting/revoking access to schemas, 43 Privileges, granting/revoking access to tables, 52 R Reconfiguring client data sources, 113 Recovery data, 95 Refresh materialized view groups operation, 89 Refresh materialized views operation, 89 Removing client data sources, 1
schema materialized views, 39 schema privileges, 39 schema procedures, 41 schema synonyms, 42 schema tables, 39 schema views, 41 schemas, 38 space of a table, 55 stored procedures, 85 super ID status, 97 synonyms, 83 system name, 19 tables, 53 triggers, 68, 69 views, 71 dropping indexes, 64 schemas, 44 tables, 60 event viewer, 98, 101 event viewer links, 101 grant/revoke privileges, 52 grant/revoke schema privileges, 43 help link, 20 indexes creating, 61 creating unique, 61 defining columns, 62 displaying,
view events, 101 viewing user status, 29 viewing user status by role, 30 views creating, 70 displaying, 71 entering DDL to create, 70 renaming, 71 virtual tape status, 94 VST system status page, 94 Scripting, 19 Security considerations, 16 managing policies, 30 STOP_AUTOMATED_STATS() stored procedure, 109 Stored procedures, 19, 84 Sun Java Plug-in, supported versions, 16 Super ID, managing, 97 Support, contacting, 13, 95 Synonyms changing, 83 creating, 82 displaying, 83 displaying DDL, 83 dropping (deleting
dropping materialized view groups, 81 renaming, 71 Virtual tape server (VTS), 93 W Wizards Alter Column, 59 Alter Materialized View Group, 81 Alter Synonym, 83 Create Index, 61 Create Materialized View, 72, 75 Create Materialized View Group, 79 Create Schema, 42 Create Synonym, 82 Create Table, 45 Create Table Using, 49 Create Trigger, 65 Create View, 69 Rename Table, 60 Schedule Maintain, 87 142 Index
