HP Neoview Database Administrator's Guide HP Part Number: 544558-001 Published: August 2007 Edition: 2.
© Copyright 2007 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..........................................................................................................11 Who Should Use This Manual..............................................................................................................11 New and Changed Information in This Edition...................................................................................11 Related Documentation...............................................................................
Guidelines for Database Administrators..............................................................................................33 Neoview SQL Table Options.................................................................................................................33 5 Managing Database Objects.....................................................................................37 Schemas.................................................................................................................
Displaying Scheduled Operations’ Status............................................................................................82 Modifying Scheduled Operations...................................................................................................83 Deleting Scheduled Operations.......................................................................................................83 Data Backups...................................................................................................
Sample Recovery Scenario.............................................................................................................117 Reconfiguring a Recovery-Enabled Task.......................................................................................119 Duplicate Records in .bad Files.....................................................................................................119 Extracting Data................................................................................................
List of Tables 4-1 6-1 8-1 8-2 8-3 8-4 8-5 8-6 8-7 B-1 Notes for Creating Tables..............................................................................................................34 Neoview VTS System Status Field Information ...........................................................................85 Required CONFIG_TASK_LOAD Arguments............................................................................108 Optional CONFIG_TASK_LOAD Arguments..............................................
List of Examples 3-1 3-2 3-3 3-4 7-1 7-2 8-1 8-2 8-3 8-4 Freezing an Account......................................................................................................................30 Thawing an Account.....................................................................................................................31 Creating an Account......................................................................................................................31 Removing an Account.................
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 Client Guide for Database Administrators Information on using the Dashboard Client, including how to install the Client, start and configure the Client Server Gateway (CSG), use the Client windows and property sheets, interpret entity screen information, and use Command and Control to manage queries from the Client. Neoview Owner’s Manual Site-planning information and basic hardware information.
Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: MGR lowercase italic letters Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name Change Bar Notation Change bars are used to indicate substantive differences between this manual and its preceding version.
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.
For Additional Help • Problems Use the management tools to try to fix problems. (See “Management Tools” (page 19).) If a problem persists, or the management tools are unable to perform the needed task, call your HP representative. See also the HP Neoview Owner’s Manual. • 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 and high-speed data loaders are 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 87). About The Services ID Your system is preconfigured with a services ID. It is intended to be used by HP Support.
a Windows platform and Internet Explorer cannot find the plug-in, a prompt gives you the option to install the plug-in automatically. To check the product version of the Java runtime being used, open your browser's Java console to display the product version information. For systems that are not connected to the Internet, you must manually install the plug-in on each workstation. You should connect to the Internet during installation to find and install the most current version of the plug-in.
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 21). 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: 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 User IDs To... See...
3. 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 23) “Creating a New Role” (page 25) “Changing the Password and Expiration Date of a Role” (page 25) “Deleting a Role” (page 26) “Creating a New User ID” (page 27) “Changing the Password and Expiration Date of a User” (page 27) “Deleting a User ID” (page 28) “Viewing User Status” (page 29) “Managing Security Policies” (page 29) “Managing Linux Accounts” (page 30) Understanding
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 8 characters long and may not contain double quotes (“). For additional information, see DB Admin online help NOTE: Neoview Release 2.2 offers 64-bit encryption of passwords. If you upgrade from Release 2.x to Release 2.2 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.
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. You can assign a user ID to only one role. 5. Enter the required information.
4. Enter the new information and click OK. Passwords must be 1 to 8 characters long and may not contain double quotes (“). For additional information, see DB Admin online help. NOTE: Neoview Release 2.2 offers 64-bit encryption of passwords. If you upgrade from Release 2.x to Release 2.2 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 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. Click the Users tab and then click the Info tab: 3.
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. DB Admin displays the current policies. To change them, enter the required information and click OK: For additional information, see DB Admin online help.
Example 3-2 (page 31) shows an example of thawing an account: Example 3-2 Thawing an Account Please enter your option now: 2 Enter the user ID you wish to Thaw (or Q/q to quit): pay25 You entered pay25. Is this correct (Y/N) [y]: Unlocking password for user payl25. passwd: Success The system displays the menu for your next task.
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. To create a non-partitioned table using Neoview Script, specify the NO PARTITION option with the CREATE TABLE command.
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, HP Neoview Script, 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 Script from a Perl or Python command line or from a Perl or Python program. For more information, see the HP Neoview Script Guide.
• “Materialized View Groups” (page 69) — “Creating Materialized View Groups” (page 69) — “Displaying Materialized View Groups and their Properties” (page 70) — “Changing Materialized View Groups” (page 71) — “Dropping (Deleting) Materialized View Groups” (page 71) • “Synonyms” (page 72) — “Creating Synonyms” (page 72) — “Displaying Synonyms and Their Properties” (page 73) — “Changing Synonyms” (page 73) — “Dropping (Deleting) Synonyms” (page 74) • “Stored Procedures in Java (SPJs)” (page 74) Schemas Y
3. Click the name of a schema to see a list of objects that belong to that schema: If you have privileges on that schema, you will see links to create various objects. 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.
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.
3. 4. 5. Right-click the Schema name and select Create Table Wizard, or click Create Table Wizard to start the Create Table Wizard. Or, you can click Create Table in the right pane. Enter a name for the new table and click Next: 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.
6. DB Admin prompts you to define columns for the table. Enter information for the first column and click OK: For more information about data types, see Appendix A (page 139). 42 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. Using the Neoview loader to load data into a table with an identity column is not supported. 11. When you have selected the key, click Next. 12.
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 47) For additional information, see DB Admin online help.
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. 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.
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.
Granting and Revoking Access (Privileges) to a Table 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 privileges you want to change. Right-click the table 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.
Managing Database Objects
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. 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. To display DDL information for all tables in the schema, right-click the Tables folder and select DDL Tool: 3. To display DDL information for one table, click the Database tab, then navigate to the schema and right-click the table name.
4. 5. 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. DB Admin attempts to alter the table and either displays an error or displays its new information. For additional information, see DB Admin online help. Changing Table Attributes 1. 2. 3.
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.
3. Right-click the table for which you want to create a trigger and select Create Trigger to start the Create Trigger Wizard. In this example, suppose we have a database to record prescriptions. There are two tables: prescriptions lists prescriptions for each patient, and generic_drugs lists generic equivalents for brand name drugs. Before a prescription record is inserted, this trigger looks up the drug in generic_drugs before the prescription record is inserted.
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. 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.
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.
2. 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. Under the DDL tab, the DDL Tool button displays DDL information. For additional information, see DB Admin online help.
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.
2. 3. 4. 5. Click the Database tab, then navigate to the schema and table to display its views in the left pane. Expand the Views folder in the left pane. Right-click the view name and then select Drop. DB Admin prompts you to confirm the drop. When you click Yes, it drops the view. For additional information, see DB Admin online help. Materialized Views Creating Materialized Views A materialized view is like a cache: a copy of data that can be accessed quickly.
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.
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. DB Admin allows you to indicate if the materialized view should ignore changes on the file. Clear or select the box and click Next: 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.
2. Click the Database tab, then navigate to the schema and click the materialized view name in the right pane, or open the Materialized Views folder. DB Admin displays the materialized views in this schema. Click the materialized view name in the lists in either pane. DB Admin displays the materialized view’s information in the right pane: Use the arrows on the right to view information under the Attributes, DDL, Space, and Privileges tabs. Under the DDL tab, the DDL Tool button displays DDL information.
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.
3. 4. 5. DB Admin prompts you to confirm that you want to create this group. When you click Yes, it creates the materialized view group. It does not yet contain any materialized views. DB Admin prompts you to add materialized views to the group. If you select No, you can add them later. See “Changing Materialized View Groups” (page 71). If you select Yes, it displays the Alter Materialized View Group Wizard: Select the materialized views you want to add, then click Finish.
2. Click the Database tab, then navigate to the schema and table and click the materialized view name in the right pane, or open the Materialized Views Groups folder. DB Admin displays the materialized view groups for this table. Click the materialized view group name in the lists in either pane. DB Admin displays the materialized view group’s information in the right pane: For additional information, see DB Admin online help. Changing Materialized View Groups 1. 2. 3. 4.
2. 3. Click the Database tab, then navigate to a materialized view group in the left pane. Right-click the materialized view group name and then select Drop. DB Admin prompts you to confirm the drop. When you click Yes, it drops the materialized view group. For additional information, see DB Admin online help. Synonyms A synonym is an alternate name that can replace multi-part or fully-qualified names for SQL objects. You can refer to an object by its name or by the synonym.
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.
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. Start DB Admin and log on using the user ID that created the synonym or log on as a user authorized to change that synonym.
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 77) “Schedule Table Row Deletion” (page 80) “Displaying Scheduled Operations’ Status” (page 82) “Modifying Scheduled Operations” (page 83) “Deleting Scheduled Operations” (page 83) “Data Backups” (page 84) “Neoview VTS System Status Page” (page 84) “Displaying the System Status Page” (page 84) “Interpreting the System Status Page” (page 85) “Data Recovery” (page 85) Schedule Maintain Wizard You ca
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: 78 Operation Description All Perform all maintenance operations for this table. Reorganize Table Defragment this table. Update Statistics Run update statistics on this table.
Operation Description Refresh Materialized View Group Update all the Materialized Views in a group with the latest contents from the base table or tables. Reorganize Materialized Views Defragment Materialized Views for this table. Reorganize Materialized View Defragment the indexes on Materialized Views for this table. Indexes If you select Continue on Error, maintenance operations continue if they encounter an error. You can view messages logged by an operation that encounters an error.
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.
4. Click Next to display the Schedule page or Finish to schedule the delete operation to begin immediately. If you select Next, DB Admin displays the Schedule Age Table Wizard: 5. Select the Start, Recurrence, and End criteria, and then click Finish. DB Admin prompts you to confirm that you want to schedule this operation. If predicate validation was successful, DB Admin displays the operation UID for the scheduled operation. Click OK to confirm the operation: 6. 7.
8. To view the status of the operation, see “Displaying Scheduled Operations’ Status” (page 82). For additional information, see DB Admin online help. Displaying Scheduled Operations’ Status 1. 2. Start DB Admin and log on using any user ID. You can view only the operations scheduled by the user ID you used to log on. Click the Operations tab to view operations for the logged-on user ID: You can sort each column by clicking on its header. Click the Refresh button to refresh the screen.
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 re-schedule it. Deleting Scheduled Operations 1. 2. 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.
3. 4. 5. Find the operation you want to delete. Right-click the operation name and select Delete. DB Admin prompts you to confirm that you want to delete the operation. For additional information, see DB Admin online help. Data Backups If your Neoview platform is configured for recovery, it will perform backups automatically, using the HP Neoview Virtual Tape Server (VTS). For information on commands for dumping the database, see the Neoview Script Guide.
Interpreting the 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. Version The version of software installed on the Neoview VTS. Created The date on which the installed Neoview VTS software was created.
7 Other Operations This chapter describes: • • • • “Managing the Super ID” (page 87) “Viewing System Events” (page 88) “Monitoring Queries” (page 92) “Performing Linux System Administration Tasks” (page 92) 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.
Viewing System Events You can view dial-out and database events on the Neoview platform by using the OSM Event viewer. 1. 2. 3. 4. Start DB Admin using “https” rather than “http”. Append the system URL with “:9991” and click Enter. To start the OSM Event Viewer, log on using the DBA role. The OSM configuration screen is displayed: From the Log Files list, select Collectors. The system displays a list of collectors.
Messages in red are critical. Messages in green are informational. 5.
3. 4. From Log Positioning, select By time. Click Show Events. A new window displays the last 1000 events. 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.
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.
Monitoring Queries For information about monitoring queries, see the Neoview Management Dashboard Client User Guide. Performing Linux System Administration Tasks About the Sysadmin Account Use the sysadmin user account to perform system maintenance tasks and to maintain Neoview Loader logs. When you log in to the Linux system as sysadmin, the system displays a menu-driven screen: You have logged on to linux-platform as sysadmin. The following are your options: 1. 2. 3. 0.
8 Using the Neoview Loader The Neoview Loader provides processes and commands for loading data into your Neoview platform or extracting data from it. gcmd is the user interface for managing the Neoview Loader. Using gcmd commands, you can load data from any data source running on a Linux platform. You will probably need to do some data preparation before the data is suitable for loading.
Loading (Importing) Data To load or extract data, log on to the Linux system with an account you created with the useradmin account. See “Linux Accounts” (page 18) for a description of accounts that are provided with your system. Submit commands to the Neoview loader from your home directory. This path is set automatically, so you do not need to qualify the name.
Step 3: Confirm Completion of the Data-Load Task To verify that the load completed successfully: 1. Check the log file for each load task. The gelserver data-load process for each load task creates a log file named task-name.task-instance-name.log in the log directory specified at the time of installation (typically /opt/tandem/genus/gel/log). This log file contains messages that indicate the success or failure of the load task.
Writers : --------1. 16.107.156.158:1032 2. 16.107.156.158:5025 3. 16.107.156.158:9025 4. 16.107.156.158:13025 5. 16.107.156.158:17025 6. 16.107.156.158:21024 7. 16.107.156.158:25025 8. 16.107.156.158:29024 9. 16.107.156.158:33024 10. 16.107.156.158:37024 11. 16.107.156.158:41025 12. 16.107.156.158:45024 13. 16.107.156.158:49024 14. 16.107.156.158:53024 15. 16.107.156.158:57025 16. 16.107.156.
---------------------------------------------------------------PNo. Requested Applied Rejected Affected 0 52 52 7 45 1 52 52 5 47 2 52 52 4 48 3 58 58 3 55 4 67 67 9 58 5 60 60 5 55 6 49 49 5 44 7 59 59 5 54 8 60 60 8 52 9 47 47 3 44 10 62 62 12 50 11 63 63 7 56 12 67 67 9 58 13 46 46 4 42 14 39 39 4 35 15 67 67 10 57 Tot. 900 900 100 800 CTot.
STOP request until they time out. You will not be able to start the same task again until at least one of the servers has processed the STOP request and changed the task status to either STOPPED or ABORTED. When a data-load task is stopped abruptly, the status of the task instance is set to ABORTED. The status of each data-load process is set to STOPPED. To determine how many records were loaded before the task was stopped, check the log files (see “Logs” (page 115)).
When you rerun the task, if job recovery was enabled, the system uses the built-in recovery feature. For information on recovery, see “Recovery” (page 116). Transaction Size Limit When you configure the transaction size you can improve performance by setting the size to 2500 or higher. If you are not running multiple load tasks concurrently you can set the transaction size close to the limit, described later in this chapter.
Examples • Suppose that your load task has this configuration: Input files 4 Table partitions 128 Transaction size 1,000 Internal record size 500 Use this formula to calculate memory allocation: Memory allocation = ((2 * 4) + (3 * 128)) * 1000 * 500 = 196 MB • Suppose that your load task has this configuration: Input files 8 Table partitions 256 Transaction size 5,000 Internal record size 500 Use this formula to collate memory allocation: Memory allocation = ((2 * 8) + (3 * 256)) * 5000 * 5
the result will be a NULL input value. Use the rightTrim(-tm)argument to prevent the trimming of trailing blanks. Setting this argument to N means that all blank characters are retained. If the input field contains double quote characters you must enclose the input for that field in double quotes, and any embedded double quote characters must be preceded by another double quote character.
century changes from 19 to 20. For example, if crossoverYear is set to 50, all years whose value is greater than or equal to 50 will have the century set to 19, and all years whose value is less than 50 will be set to 20. For example, if you specify these formats: dateFormat = MMDDYY dateSeparator = crossoverYear = 50 then input date: 12-15-50 is stored as: 12-15-1950 and input date: 12-15-06 is stored as 12-15-2006 Times Times use this fixed format: hh:mm:ss.ffff Time fields cannot be enclosed in quotes.
set defined for the target column. The cSetConversion (-cc) argument allows you to specify that no character set conversion should be applied for this load task. This feature is useful when you want to load data, such as SHIFT-JIS, into a table, but do not want to convert the SHIFT-JIS data to UTF-8 format before loading. When you use this feature with delimited input where the input data is multibyte (e.g.
For example, if you specify these formats: dateFormat = MMDDYY dateSeparator = crossoverYear = 50 Input date: 12-15-50 is stored as: 12-15-1950 Input date: 12-15-06 is stored as 12-15-2006 Times Times use this fixed format: hh:mm:ss.ffff Timestamp The date portion of a timestamp field follows the same rules as dates. The time portion follows the same rule as times. Suppose you specify the date format as MMDDYYYY and the dateSeparator as the slash ("/") character.
Using a Format File You can use the formatFile (-ff) argument to point to a format file that defines the format of the input data.
NOTE: “+”.
Identity Column Support The Neoview Loader supports the loading of data into tables that have been created with an identity column. You can have the system automatically generate values for the identity column as part of the load task, or you can provide the values for the identity column. In that case, the identity column will be treated as for any other column.
Configuring a Data-Task To Load a Table With An Identity Column If the table you are loading contains an identity column, the Neoview Loader checks the value of the -ic (identityColumn) option. Specify USER or SYSTEM (the default is SYSTEM, so you need to set this option only if the default was changed or the option is to be set to USER).
Table 8-1 Required CONFIG_TASK_LOAD Arguments (continued) Short Long Default Description Name Name Value -rt requestType N.A. Task configuration and definition request. Must be set to CONFIG_TASK_LOAD. -tk taskName N.A. Task name; maximum 32 characters -tm rightTrim Y Specifies whether trailing blanks should be removed from input data for character fields.
Optional Arguments for Configuring a Data-Load Task Table 8-2 Optional CONFIG_TASK_LOAD Arguments Short Long Default Description Name Name Value -bs bufferSize 32768 Not used at this time. -cc cSetConversion Y Specifies whether character set conversion is performed. Valid values are: Y (perform character set conversion) N (do not perform character set conversion) -cp compression N Compression is not applicable to a Neoview database appliance. Do not change the value from N.
Table 8-2 Optional CONFIG_TASK_LOAD Arguments (continued) Short Long Default Name Name Value -es errorLoggingSeverity INFO Description Error logging severity. Valid values are: ERROR WARNING INFO TRACE -fd fieldDelimiter | Field delimiter. Specify a single character enclosed in single quotes. For example: ',' To specify delimiters that are nonprinting characters, use the associated hexadecimal value encoding for the nonprinting character.
Table 8-2 Optional CONFIG_TASK_LOAD Arguments (continued) Short Long Default Name Name Value -it inputType FILE Description Input type for the task. Valid values are: FILE TABLE PIPE QUEUE -ml msgCountLimit 500000 Limit on the number of messages that can be received on the JMS queue. When this limit is reached, the loader stops reading from the queue. Valid range is 1 to 1000000000. -of overwriteFlag N Specifies whether an existing task’s arguments can be changed.
Table 8-2 Optional CONFIG_TASK_LOAD Arguments (continued) Short Long Default Name Name Value -rf recoveryFlag Y (in current release) Description Specifies whether recovery is enabled. Valid values are: Y (yes, enable recovery) N (no, do not enable recovery) If you are upgrading from a prior release, any tasks created under that release will be run with recovery disabled, which was the previous default.
Table 8-3 START_TASK Arguments (continued) Short Long Default Name Name Value -un username N.A. Description Neoview user name. For example: user1 -pw N.A. password Neoview password. For example: User9999 Arguments for Stopping a Task Table 8-4 STOP_TASK Arguments Short Long Default Description Name Name Value -rt requestType N.A. Stop task request. Must be set to STOP_TASK -tk taskName N.A. Task name -un username N.A. Neoview user name. For example: user1 -pw N.A.
User Name and Password You can specify environmental variables for the user name and password so that you do not have to store the user name and password as part of any command files that you use. For example, if you use the environment variables USER and PSW to pass in the user name and password, any loader tasks could refer to these environment variables as -un $USER and -pw $PSW.
Rejected Data Records If a data-load task encounters invalid data, it writes the data to a .bad file. There are two sources of rejected records: • • The gelserver data-load process’ input validation The target system’s write process, where records can be rejected by SQL Records that fail input validation are written to a file named task-name.task-instance-name.input.bad located in the log directory specified at the time of installation (typically /opt/tandem/genus/gel/log).
-gs server1 -tn NEO-CMSERVER \ -it FILE \ -ii /opt/testdata/WISC-INSERT \ -ot TABLE -oi CAT.SCH.WISC \ -un user1 -pw mypassword \ -of N Running a Recovery-Enabled Task To start a recovery-enabled task, use the START_TASK request, the same request you use to start a task with recovery disabled.
gcmd -rt CONFIG_TASK_LOAD \ -tk TASK_RECOVERY_ENABLED -rf Y \ -gs server1 -tn NEO-CMSERVER \ -it FILE \ -ii /opt/testdata/WISC-INSERT \ -ot TABLE -oi CAT.SCH.
Readers : --------1. /opt/testdata/WISC-INSERT Writers : --------1. 16.107.129.58:1138 2. 16.107.129.58:5063 3. 16.107.129.58:9052 4. 16.107.129.58:13053 5. 16.107.129.58:17038 Cumulative statistics from previous runs: Requested Applied Rejected Affected 30200 30200 278 29522 ---------------------------------------------------------------[16:29:20] MASTER> MASTER Execution Started. [16:29:22] MASTER> INFO : WRT_TARGET ODBC/MX FastPath has been enabled.
If this happens, when the task is recovered the rejected record is processed again and is written for a second time to the input.bad file for that task. If records are being rejected on the Neoview platform, these records are sent to the gelserver data-load process to be written to the sql.bad file for that task. After writing the rejected records to the sql.bad file, the gelserver data-load process sends an acknowledgement to the Neoview gelserver process.
11 132874 132874 1195 131679 12 132850 132850 1196 131654 13 132755 132755 1158 131597 14 132748 132748 1145 131603 15 132580 132580 1219 131361 16 132972 132972 1167 131805 17 132393 132393 1228 131165 18 133074 133074 1199 131875 19 132348 132348 1193 131155 20 133315 133315 1167 132148 21 133236 133236 1152 132084 22 133555 133555 1221 132334 23 133244 133244 1196 132048 24 133081 133081 1189 131892 25 132706 132706 1181 131525 26 133354 133354 1292 132062 27 133274 133274 1211 132063 28 132564 132564 11
/home/roledba/data/daily-extract-OCT01-1 … /home/roledba/data/extract-OCT01-5 • This task configuration is the same as the example above, except that by specifying the -cf option, the output data will be concatenated into a single file named /home/roledba/data/daily-extract-OCT01.out: gcmd -rt CONFIG_TASK_EXTRACT -tk DAILY-EXTRACT-1 \ -ss neo0001 -sn NEO-CMSERVER \ -ii "SELECT col1, col2, col3 FROM NEO.DB.T1 BROWSE ACCESS" \ -pt NEO.DB.
Example 8-4 (page 131)shows the task instance log file for a extract task that completed without errors: Stopping an Extract Task To abruptly stop an extract task while it is running, enter a gcmd command on the Linux platform with requestType STOP_TASK: gcmd -rt STOP_TASK argument-list For example, to stop the task started in Step 2: gcmd -rt START_TASK -tk DAILY-EXTRACT -ti OCT01 -un role.dba -pw roledba For descriptions of each command argument, see “Arguments for Stopping a Task ” (page 114).
query can be parallelized and return the correct results. If you are not sure, run the query in serial mode. A common scenario in which you cannot use parallel mode is a query with an ORDER BY. It cannot be parallelized because the data will only be ordered for each partition and not at the table level. The order of the data will also be lost on the Linux system, which will be receive and write data from multiple Extractor processes without regard to the order of the data.
Output Considerations for Extract Tasks You can control these aspects of extract output: • Number of output files or pipes Use the numberOfOutputFiles (-nf) argument to specify the number of output files or pipes to be used for the extract task. Increasing the number of output files or pipes can improve overall performance. Each Reader thread in the Linux Extractor process will process the data blocks to each output file in round-robin fashion as each block is received from the Neoview platform.
NUMERIC (x,y) The width of the field will be equal to x+2 to account for the sign and decimal point, regardless of whether the source column is signed or unsigned. The field will be padded with leading blanks as needed. DECIMAL (x,y The width of the field will be equal to x+2 to account for the sign and decimal point, regardless of whether the source column is signed or unsigned. The field will be padded with leading blanks as needed.
Command-Line Arguments for Extract Tasks Required Arguments for Configuring an Extract Task Required arguments for an extract task are described in Table 8-6: Table 8-6 Required CONFIG_TASK_EXTRACT Arguments Short Long Default Name Name Value -ii inputItem N.A. Description The text of the SELECT query to be used to extract the data. The total length of the query must be less than 32K. It must be inclosed within double quotes.
Table 8-6 Required CONFIG_TASK_EXTRACT Arguments (continued) Short Long Default Name Name Value -tk taskName N.A. Description Task name. For example: MYTASK1 -un username N.A. Neoview user name.
Table 8-7 Optional CONFIG_TASK_EXTRACT Arguments (continued) Short Long Default Name Name Value -hd header N Description Specifies whether a header record is to be written to each output file or pipe. The header record contains the column names from the SELECT query. Valid values are: Y (yes, write a header record) N (no, do not write a header record) -it inputType QUERY Input type for the task. The only valid value is QUERY. -nc nullChar N.A.
Table 8-7 Optional CONFIG_TASK_EXTRACT Arguments (continued) Short Long Default Name Name Value -pt partitioningTable N.A. Description Specifies the partitioning table to be used for a parallel mode extract. The partitioning table must be one of the tables specified in the query you provided for the input Item. For details, see “Partitioning Table Restrictions” (page 124). -rd recordDelimiter LF Record delimiter.
Example 8-4 Extract Task Instance Log File Example [15:07:10] MASTER> INFO : SESSION ---------------------------------------------------------------SESSION PARAMETERS -------------------- Task Name : EXT-II-1 Task Instance Name : EXT-II-1 Query : SELECT * FROM NEO.GENUS_TEST_DB.
Neoview Loader FAQs 1. How do I change the arguments of an existing task? For example, I want to change the name of the input file. To update the arguments of an existing task, reconfigure the task (CONFIG_TASK_LOAD) with the overwrite flag set to Y. (-of Y) 2. How does the update operation work? Can I update only some columns? The Neoview Loader updates all columns except clustering key columns.
9. Is the task name.task-instance-name.log file written over or appended if I later initiate another task on the same table? If you specify the same task instance name, and the task instance log file already exists, the loader will rename the existing task instance log file by appending the modification timestamp to the file name, and will then create a new task instance log file.
9 Managing Client Data Sources and Connectivity This chapter describes: • • • • • “Reconfiguring ODBC Client Data Sources” (page 135) “Configuring ODBC Client Data Sources for Update Operations” (page 135) “Removing ODBC Client Data Sources” (page 135) “Performing Database Connectivity Tasks” (page 135) “NDCS Services” (page 136) 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.
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 available to accept a connection Initial Number of servers started when the data source was started The NDCS Server Status tab displays this screen: The NDCS Server Status tab displays this information: 138
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 JMS Info Reading Data From a JMS Queue In addition to reading input from flat files and named pipes, the Gelserver on the Linux platform has been enhanced to read input data from a Java Message System (JMS) queue and load this data into target tables on the Neoview platform. This capability is integrated into the client application for the Gelserver, and the Gelserver application is the consumer of each JMS message.
• • Messages are removed automatically from the JMS Queue as they are read by the GEL JMS Consumer (auto-acknowledge). The GEL JMS Consumer terminates when one of these two events is triggered: — The Idle Time Limit is reached, the user-configurable time limit for the GEL JMS Consumer to idle and wait for queue entries before terminating. — Message Count Limit is reached, the user-configurable limit for the number of queue entries to be processed by the GEL JMS Consumer.
The Gelserver installer for the Linux platform contains these entries to support JMS: j2ee_home=/opt/jboss-4.0.0 The directory where the JBOSS J2EE Appserver is installed java_home=/usr/lib/jvm/java-1.4.2-bea-1.4.2.08 The directory where BEA JROCKIT JVM is installed jms_msgcount_limit=50000 The limit on the number of entries to be processed from the JMS Queue jms_idletime_limit=10 The idle time in minutes NOTE: When setting up Java-compliant and J2EE 1.
This is a sample JMS start task request: ./gcmd -rt -tk -ti -un START_TASK \ GENUS_JMS_REAL \ RUN_19JUN \ user1 -pw mypasswd This is a sample JMS stop task request: ./gcmd -rt -tk -ti -un STOP_TASK \ GENUS_JMS_REAL \ RUN_19JUN \ user1 -pw mypasswd There are no JMS-specific changes to either the START_TASK or STOP_TASK request. JMS Logs An additional JMS log is used by the GEL JMS Consumers. The JMS log file is in addition to the task instance log file maintained by the Gelserver on the Linux platform.
---------------------------------------------------------------------SESSION PARAMETERS ----------Task Name : GENUS_JMS_REAL DB Object Name : USRCAT.USRSCH.T1 Compress Data : false Data Format : DELIM Field Delimiter : , RowsToSkip : 0 Rowset Size : 1 Operation Type : INSERT Transaction Size : 1 ExecutionParallelism : PARTITIONWISE LogSeverityLevel : INFO Recovery Flag : true Readers : --------1. ConnectionFactory:queue/A 2. ConnectionFactory:queue/B Writers : --------1. 204.160.19.
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 Symbols .bad file, 116 .
Displaying columns in a table, 47 DDL for indexes, 60 DDL for materialized view groups, 71 DDL for materialized views, 68 DDL for schemas, 39 DDL for synonyms, 73 DDL for tables, 51 DDL for views, 62 indexes, 59 materialized view groups, 70 materialized views, 67 scheduled operations, 82 schemas, 38 stored procedures, 74 synonyms, 73 views, 61 Documents, related information, 11 Dropping indexes, 60 materialized view groups, 71 materialized views, 69 schemas, 40 tables, 56 views, 62 E Events viewing databas
named pipes, extract output, 127 named pipes, transaction size limit, 99 overview, 93 recovery, 116 stopping a data load task, 97 stopping an extract task, 123 Loading data commands for, 108 configuring task, 94 confirming completion, 95 performance, 93, 99 starting task, 94 stopping, 97 Logon help link, 21 Logs extract task, 130 input.bad, 116 jms, 116, 146 loader, 115 recovery, 117 rejected data, 116 sql.
S Schemas creating, 39 DB creating tables, 23, 24 description of, 20, 38 displaying, 38 displaying DDL, 39, 40 dropping (deleting), 40 preconfigured, 20 USR creating tables, 23, 24 description of, 20, 38 Screen shots adding columns to a table, 42 adding roles, 25 adding user IDs, 27 age table operation failure, 81 predicate, 80 schedule, 81 Alter Synonym Wizard, 73 changing a role’s password, 25 a user ID’s password, 27 Create Index Wizard defining columns, 58 entering name, 57 unique, 57 Create Materialize
NDCS data source status, 137 NDCS server status, 138 NDCS status, 137 objects, displaying, 39 operations age table, 80 manage super ID, 87 Rename Table Wizard, 53 Rename View Wizard, 62 renaming tables, 53 views, 62 roles adding, 25 changing password, 25 deleting, 26 Schedule Age Table Wizard failure, 81 predicate, 80 schedule, 81 UID, 81 Schedule Maintain Wizard defining schedule, 80 select operation, 77 Schedule Populate Index Wizard, 59 schema objects, 39 schemas creating, 39 displaying, 38 displaying DD
creating using existing tables, 44 creating views for, 60 defaults, 33 defragmenting, 78 deleting (dropping) indexes, 60 deleting (dropping) synonyms, 74 deleting (dropping) views, 62 deleting rows, 80 displaying DDL, 51 displaying indexes for, 59 displaying materialized view groups for, 70 displaying materialized views for, 67 displaying properties, 47 displaying synonyms for, 73 displaying views for, 61 dropping (deleting), 56 dropping (deleting) indexes, 60 dropping (deleting) synonyms, 74 dropping (dele
