HP Neoview Database Administrator's Guide HP Part Number: 618075-001 Published: July 2010 Edition: HP Neoview Release 2.
© Copyright 2010 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.............................................................................................................7 Who Should Use This Manual................................................................................................................7 New and Changed Information in This Edition.....................................................................................7 Related Documentation...........................................................................
5 Automating Update Statistics and Reorganize Operations.....................................25 Setting Tables for Automated UPDATE STATISTICS...........................................................................25 HP_USTAT.CHG_AUTO_LIST() Stored Procedure........................................................................25 Syntax.........................................................................................................................................25 Considerations................
List of Tables 3-1 4-1 5-1 5-2 Managing Database Objects..........................................................................................................20 Neoview VTS System Status Field Information ...........................................................................22 USTAT_AUTO_LIST View............................................................................................................28 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, and it provides basic task information. It is helpful to have knowledge of database and SQL concepts, connectivity and data source concepts, and basic computer security concepts.
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 — README for the HP Neoview Windows Client Package Administration client 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 monitor Online Help and manage a Neoview data warehousing platform.
• Reference Mapping Tables for Neoview Character Sets A hyperlinked collection of East Asian characters supported by Neoview character set functionality. Neoview SQL Reference Manual Reference information about the syntax of SQL statements, functions, and other SQL language elements supported by the Neoview database software. Neoview Messages Manual Cause, effect, and recovery information for error messages. README for HP Neoview Release 2.
HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to: docsfeedback@hp.com Include the document title, part number, and any comment, error found, or suggestion for improvement you have concerning this document.
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. About This Guide This guide is intended for those who perform standard database administration tasks, and it provides basic task information.
For Information About... See... Troubleshooting Use the management tools to try to fix problems. (See “Management Tools” (page 14).) If a problem persists, or the management tools are unable to perform the needed task, call your HP representative. Messages Many messages that the platform returns are intended only for use by HP. If critical events occur, the platform automatically triggers a call to HP. For user-visible messages, see the Neoview Messages Manual.
2 Getting Started Your system is delivered ready to use: • • • The system is preconfigured. Table spaces are available and ready for data. ODBC server software is installed and ready to go. For details about installing ODBC drivers on client systems, see the README for the HP Neoview ODBC Driver for Windows or the README for the HP Neoview ODBC Driver for Linux, depending on your client platform.
Neoview Roles and User IDs Your Neoview system is delivered with a set of preconfigured roles and user IDs with predefined passwords. CAUTION: For your system’s security, change these passwords as soon as you receive your system, using the Neoview Command Interface (NCI). For detailed information about working with Neoview roles and User IDs, see the Neoview User Management and Security Administration Guide.
Installing Management Tools Installation instructions for Neoview management tools are provided in the README document for that client: • • • • README for the HP Database Manager README for the HP Neoview Command Interface (NCI) README for the HP Neoview Performance Analysis Tools README for the HP Neoview Transporter Java Client You can find these READMEs, as well as the rest of the Neoview documentation set, on the HP BSC portal: http://www.hp.com/go/neoviewdocs.
Schemas A schema ia a named collection of database objects (for example, tables, views, and indexes). Each database object is described in only one schema, although an object can refer to objects described in other schemas. A schema cannot contain other schemas. Each schema is described in a catalog. The schema is the unit of ownership; all database objects in a schema are owned by the schema owner. Your Neoview platform is preconfigured with two schemas.
Materialized Views A materialized view is like a cache: a copy of data that can be accessed quickly. It is a named specification of a result table that is stored in a regular table. You can then query this table (materialized view) directly. For complete information about creating and performing other actions on materialized views, see the Neoview SQL Reference Manual and the Neoview Command Interface (NCI) Guide.
3 Guidelines for Database Administrators This chapter describes some guidelines for managing your database. Partitioning For Tables and Indexes • 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. To create a non-partitioned table using the Neoview Command Interface, specify the NO PARTITION option with the CREATE TABLE command.
For SQL syntax, see the Neoview SQL Reference Manual. For descriptions of creating and managing stored procedures in Java (SPJs), see the Neoview Guide to Stored Procedures in Java. Table 3-1 Managing Database Objects Database Object To Perform This Action... Use this tool...
4 Performing Database Operations This chapter describes: • • • • • “Schedule Maintenance Tasks” (page 21) “Schedule Table Row Deletion” (page 21) “Neoview VTS System Status Page” (page 21) “Interpreting the VTS System Status Page” (page 22) “Data Recovery” (page 23) Schedule Maintenance Tasks You can perform a variety of maintenance tasks on tables and materialized views, such as scheduling automated update statistics or reorganize operations. For more information, see Chapter 5 (page 25).
Interpreting the VTS System Status Page Table 4-1 provides more information about the fields shown in the system status page. Table 4-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. Version The version of software installed on the Neoview VTS. Created The date on which the installed Neoview VTS software was created.
Table 4-1 Neoview VTS System Status Field Information (continued) Box Box Title Field Description 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. To view the current status of the cartridge, move your mouse pointer over the icon next to the cartridge name. * Organized by Neoview platform segment. Data Recovery If you need to recover data for any reason, call HP support.
5 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 28) • “HP_USTAT.
Table 5-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. LAST_RUN TIMESTAMP(0) Time of last UPDATE STATISTICS run.
SQL>call hp_ustat.stop_automated_stats(); --- SQL operation complete. Scheduling Automated UPDATE STATISTICS and REORG Using NCI SCHEDULE Command SCHEDULE is an NCI command that 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.
6 Managing Client Data Sources and Connectivity This chapter describes: • • • • • “For More Information About Neoview Connectivity” (page 31) “Reconfiguring ODBC Client Data Sources” (page 31) “Configuring ODBC Client Data Sources for Update Operations” (page 31) “Removing ODBC Client Data Sources” (page 31) “Neoview Database Connectivity Services (NDCS)” (page 32) For More Information About Neoview Connectivity For Information About... See...
2. 3. 4. 5. To display the list of data source names, click either the System DSN tab or the User DSN tab. (The tab on which a data source is listed depends on how the data source was originally assigned.) Select the data source name you want to remove and then click Remove. In the confirmation dialog box, click Yes to remove the data source or click No to cancel. Repeat Steps 3 and 4 for each data source you want to remove. For more information about a window, click Help.
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 H Adding materialized views, 17 Automated REORG description of, 25 scheduling, 30 Automated tables listing, 28 setting, 25 Automated UPDATE STATISTICS description of, 25 listing automated tables, 28 scheduling, 30 starting, 25 stopping, 29 Help, where to find information in this guide, 11 HP_USTAT.CHG_AUTO_LIST() stored procedure, 25 HP_USTAT.STOP_AUTOMATED_STATS() stored procedure, 29 HP_USTAT.USTAT_AUTO_LIST view, 28 HP_USTAT.
ODBC Programmer’s Reference, 11 P partitioning index, 19 table, 19 Passwords, changing security considerations, 14 R Reconfiguring client data sources, 31 Recovery data, 23 Removing client data sources, 31 REORG operation, automating (see Automated REORG) S SCHEDULE command, 30 Schemas DB description of, 16 preconfigured, 16 USR description of, 16 Screen shots virtual tape status, 21 VST system status page, 21 Scripting, 14 Security considerations, 14 SPJ overview, 17 SQL CREATE INDEX, 19 CREATE TABLE, 1
