HP Neoview Release 2.4 Software User Manual

HP Database Manager (HPDM) User Guide

HP Part Number: 546008-001 Published: April 2009 Edition: HP Neoview Release 2.4

About This Document.........................................................................................................7

Intended Audience.................................................................................................................................7

Related Documentation..........................................................................................................................7

Neoview Customer Library...............................................................................................................7

HP Encourages Your Comments............................................................................................................8

1 Introduction to the HP Database Manager..................................................................9

HP Database Manager............................................................................................................................9

Installation Requirements ....................................................................................................................10

Install the HP Database Manager.........................................................................................................10

Uninstall the HP Database Manager....................................................................................................10

Start the HP Database Manager............................................................................................................10

Pull-Down Menus.................................................................................................................................10

Customizing HPDM Windows.............................................................................................................11

About Box.............................................................................................................................................11

Exit the HP Database Manager.............................................................................................................11

2 The Systems Tool..........................................................................................................13

Systems Tool..........................................................................................................................................13

Options............................................................................................................................................13

Define System.......................................................................................................................................13

Add System...........................................................................................................................................13

Edit System...........................................................................................................................................15

Add System Like This...........................................................................................................................16

Remove System.....................................................................................................................................16

Clear Password for System...................................................................................................................16

Test System............................................................................................................................................16

3 Navigating HPDM.......................................................................................................17

Navigation Tree Pane............................................................................................................................17

My Active Systems .........................................................................................................................17

My Other Systems ..........................................................................................................................17

Right Pane.............................................................................................................................................18

4 Managing Database Objects.....................................................................................21

Displaying Schemas and Their Objects................................................................................................22

Attributes Tab..................................................................................................................................22

Tables Tab........................................................................................................................................23

Materialized Views Tab...................................................................................................................23

Materialized View Groups Tab........................................................................................................23

Views Tab.........................................................................................................................................23

Procedures Tab................................................................................................................................24

Synonyms Tab..................................................................................................................................24

DDL Tab...........................................................................................................................................24

Privileges Tab...................................................................................................................................24

Displaying Schema Size...................................................................................................................25

Displaying Tables and Their Properties................................................................................................26

Columns Tab....................................................................................................................................27

Table of Contents 3

Primary Key Tab..............................................................................................................................27

Hash Key Tab...................................................................................................................................27

Unique ConstraintsTab....................................................................................................................27

Foreign Keys Tab.............................................................................................................................28

Attributes Tab..................................................................................................................................28

Partitions Tab...................................................................................................................................28

Usage Tab.........................................................................................................................................29

DDL Tab...........................................................................................................................................29

Statistics Tab....................................................................................................................................29

Privileges Tab...................................................................................................................................29

Displaying Tables Summary............................................................................................................30

Displaying Table Indexes and Their Properties....................................................................................31

Columns Tab....................................................................................................................................31

Attributes Tab..................................................................................................................................32

DDL Tab...........................................................................................................................................32

Partitions Tab...................................................................................................................................32

Displaying Table Indexes Summary................................................................................................32

Displaying Views and Their Properties................................................................................................34

Columns Tab....................................................................................................................................34

Usage Tab.........................................................................................................................................35

DDL Tab...........................................................................................................................................35

Privileges Tab...................................................................................................................................35

Displaying Materialized Views and Their Properties..........................................................................37

Attributes Tab..................................................................................................................................37

Partitions Tab...................................................................................................................................38

Usage Tab.........................................................................................................................................39

DDL Tab...........................................................................................................................................39

Privileges Tab...................................................................................................................................39

Displaying Materialized Views Summary......................................................................................40

Displaying Materialized View Indexes and Their Properties..............................................................41

Columns Tab....................................................................................................................................41

Attributes Tab..................................................................................................................................42

DDL Tab...........................................................................................................................................42

Partitions Tab...................................................................................................................................42

Displaying Materialized View Indexes Summary..........................................................................43

Displaying Materialized View Groups and Their Properties...............................................................44

Attributes Tab..................................................................................................................................44

Members Tab ...................................................................................................................................45

DDL Tab...........................................................................................................................................45

Displaying Procedures and Their Properties........................................................................................46

Attributes Tab..................................................................................................................................46

Parameters Tab.................................................................................................................................47

DDL Tab...........................................................................................................................................47

Privileges Tab...................................................................................................................................47

Displaying Synonyms and Their Properties.........................................................................................49

Attributes Tab..................................................................................................................................49

Usage Tab.........................................................................................................................................50

DDL Tab...........................................................................................................................................50

Displaying Triggers and Their Properties............................................................................................51

Attributes Tab..................................................................................................................................51

Usage Tab.........................................................................................................................................52

DDL Tab...........................................................................................................................................52

Displaying Schema DDL and Their Properties....................................................................................53

DDL Tab...........................................................................................................................................53

Show DDL .......................................................................................................................................53

4 Table of Contents

Displaying Table Statistics and Their Properties..................................................................................56

Displaying Table Column Statistics.................................................................................................56

Viewing Sampled Statistics.............................................................................................................56

5 Using the SQL Whiteboard.........................................................................................59

SQL Whiteboard Area...........................................................................................................................59

SQL Whiteboard At a Glance................................................................................................................59

Statement List Pane...............................................................................................................................60

Statement Pane......................................................................................................................................60

Parameter Prompt............................................................................................................................60

Cut, Copy, Paste, Undo, and Redo..................................................................................................61

Execute Highlighted Text................................................................................................................61

Cancelling an SQL Query ...............................................................................................................61

Statement Details Pane..........................................................................................................................61

Reusing a Connection ..........................................................................................................................61

Loading and Saving in SQL..................................................................................................................62

Highlighting Syntax..............................................................................................................................62

Managing the Layout ...........................................................................................................................62

6 Launching the Neoview Command Interface (NCI).................................................63

Locating the NCI Program....................................................................................................................63

Launching NCI......................................................................................................................................63

Auto Logon Options.............................................................................................................................63

7 Performing HPDM Operations....................................................................................65

Exporting Data......................................................................................................................................65

Data to Clipboard............................................................................................................................65

Data to File.......................................................................................................................................65

CSV Format................................................................................................................................65

HTML Format............................................................................................................................65

Data to Spreadsheet.........................................................................................................................65

Data to Browser...............................................................................................................................65

Communicating System Messages.......................................................................................................66

Overview Area......................................................................................................................................66

System Message Tab.............................................................................................................................66

Navigation............................................................................................................................................66

Right Pane.............................................................................................................................................66

Cloning..................................................................................................................................................67

8 Glossary........................................................................................................................69

Table of Contents 5

About This Document

This guide describes how to use the HP Database Manager (HPDM) to connect and manage a Neoview Data Warehousing Platform for Business Intelligence.

Intended Audience

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.

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 Database Manager

HP Database Manager

The HP Database Manager (HPDM) is an integrated graphical client that allows you to connect and manage multiple HP Neoview platforms. HPDM provides database-object browsing capabilities that are superior to the current Neoview DB Admin product.

HP Database Manager requires the HP ODBC 2.0 client driver for Windows to connect to an HP Neoview platform. Download the HP ODBC 2.0 client driver from the HP software download site and install it prior to running the HP Database Manager.

NOTE: HP recommends using the HP Database Manager product, although you can currently

still use the DB Admin product.

The HP Database Manager (HPDM) features are:

• Database-Object Browsing

— Provides database object browsing capabilities (these capabilities also exist in the DB

Admin product) — Displays partition information for individual Neoview SQL objects — Displays a partition summary for groups of objects (for example, all tables in a schema) — Displays schema size — Displays sequence numbers in table columns — Provides a DDL tool to generate and save the DDL script for a group of Neoview SQL

objects — Displays table profile and statistics information — Generates and displays system status messages

• SQL Whiteboard

— Executes DDL/DML statements — Persists queries across sessions for reuse — Exports query results to a spreadsheet — Allows a portion of an SQL statement to be highlighted and executed (syntax

highlighting) — Cancels an executing query — Opens/saves query text to a file

• HPDM Framework

— Provides connection management for widgets — Supports persistence for widget and application data — Includes menu management — Implements area management (hide, show, and deploy) — Exports grid contents to a spreadsheet — Manages options for an HPDM application as a whole and for individual areas and

widgets — Launches the Neoview Command Interface (NCI) — Supports character sets for ISO, SJIS, or Unicode based on a single-client locale (only

one character set can be configured for each install).

HP Database Manager 9

Installation Requirements

DescriptionRequirement

Windows compatible PC workstationComputer

1024 x 768 pixelsMonitor screen resolution

Recommended minimum 512 MBMemory

Minimum 10 MB for installation and 64 MB free spaceDisk space

Operating system

Install the HP Database Manager

The HP Database Manager is installed with a Windows installer. You can find the installer by searching for “HP Database Manager” at the HP Software Depot website:

http://www.software.hp.com/

Uninstall the HP Database Manager

After HP Database Manager is installed, you can uninstall it by using the Uninstall feature in the Start-Programs->HP Database Manager folder or use the Control Panel, Add and Remove Programs feature.

Start the HP Database Manager

To start the HP Database Manager, double-click the HP Database Manager icon on your desktop or select Start->Programs->HP Database Manager from the Windows menu or click the HP Database Manager shortcut on the desktop.

Microsoft Windows XP/Microsoft Windows Vista 32/64 bit

Neoview version 2.0 or laterODBC

Microsoft .NET Framework 2.0 or laterAdditional

Neoview version R2.4HP Neoview

NOTE: You must connect to the Neoview data warehousing platform through the ODBC client

driver to use the HP Database Manager. See the README for the HP Database Manager (HPDM) for more information.

Pull-Down Menus

The pull-down menus are Windows menus. The pull-down menus are: File, Edit, Tools, Windows, and Help.

The persistence file contains state and user preference information (for example, connection definitions, user specified options and layout of the widgets). HPDM automatically saves the information or you can initiate the action and select the File ->Save Persistence menu.

The File menu options are:

• Open Persistence imports persistence data from a file that was saved earlier by HPDM.

• Save Persistence saves the configuration. The current state is saved when the application

closes or when certain events happen within HPDM.

• Save As saves the configuration to any file. The Save As option can be used when exporting

a specific configuration to other users in the organization.

10 Introduction to the HP Database Manager

NOTE: All of the current settings are replaced by the settings in the file being imported.

Customizing HPDM Windows

HPDM allows you to customize and resize areas that are displayed. HPDM scrolls continuously in any direction, when the appropriate scroll arrow is clicked and held. The relative position of the window is changed by selecting and moving the scroll box along the scroll bar.HPDM retains area settings between sessions.

HPDM windows can be resized by dragging the window corner or border. If a border is dragged, the window size changes only on the side of the border. If a corner is dragged, the two adjoining sides to the corner change size at the same time. When a window is resized, graphical objects in a window get resized, if necessary. When a window is resized, vertical and horizontal scroll bars in a window get resized, if necessary. If a window has a minimum size, it cannot be resized smaller than the minimum size.

About Box

To access the About box, select the Help->About menu in HPDM . The About HP Database Manager box displays the current build information about HPDM. The HPDM .exe version

information and the vproc information is displayed, for example:

Build T2860N27_02MAR2009_HPDM_1218

The About dialog box also displays information about the installed components:

• Installed Components displays the components currently installed in this HPDM product

version.

• Description describes the components. Click on the installed component to view a brief

description of the component.

Exit the HP Database Manager

To exit HPDM, close the HP Database Manager window. HPDM confirms that you want to exit the application.

Customizing HPDM Windows 11

2 The Systems Tool

Systems Tool

The Systems Tool contains the components used to define, add, edit, remove, duplicate, test, and open existing systems on the Neoview platform.

Select the Tools->Systems Tool menu in HPDM and you can create a system definition for each system you want to manage, see “Add System” (page 13).

The Systems Tool window displays these settings:

• State — specifies the current state of the system definition as: Password not specified,

Not tested, Test failed, or Test succeeded.

• System Name — specifies an alias name to identify this Neoview system. The system name

can be up to 32 characters in length, and consist of letters, digits, and the underscore character.

• Server Data Source — specifies the name of the data source on the Neoview system. If the

field is left blank, the default data source is used if it one has been configured.

• User Name — Neoview user name.

• Host Name — the DNS name or IP address for the target Neoview platform.

• Port Number — contains the ODBC server port number.

• Default Schema — contains the default entry for the system (optional).

• DriverString — specifies the ODBC driver.

Options

The options for the Systems Tool are:

• [Test] tests a selected system and displays the results in the Connection Test Result window.

[Test] is enabled if one or more systems is selected.

• [Edit] launches the Edit System window with the selected systems’ current settings. [Edit]

is enabled if one or more systems is selected.

• [Add] launches the Add System window with blank settings, except for the port number

(which defaults to 18650). The ODBC driver defaults to 2.0. [Add] is always enabled.

• [Add Like] launches the Add Like System window with selected systems’ current settings.

You can use these settings as a starting point for adding a new system. [Add Like] is enabled if only one system is selected.

• [Remove] launches the Remove Confirmation window. [Remove] is enabled if one or more

systems is selected.

• [Clear Password] launches the Clear Password Confirmation window. [Clear Password]

is enabled if one or more systems is selected.

Define System

If there are no system definitions persisting from a previous session, regardless of whether the currently running version of HPDM is a new, updated, or an existing installation, select the Tools->Systems Tool in HPDM.

For information about completing the fields in the Systems Tool window, see “Add System”

(page 13).

Add System

To create a new system definition for the Neoview system, use [Add] to launch the Add System window.

Systems Tool 13

NOTE: Once a system is added, the system definition persists from session to session unless it

is removed.

In the Add System window, these fields define a Neoview system:

• Your Name for System — enter an alias name to identify this Neoview system. The name

can be up to 32 characters in length, and consist of letters, digits, and the underscore character.

• Server Data Source — enter the name of the data source on the Neoview system. If the

field is left blank, the default data source is used if it one has been configured.

• User Name — Neoview user name.

• Password — Neoview password.

• Host — enter the DNS name or IP address for the target Neoview platform.

• Port Number — contains the ODBC server port number.

• Default Schema — contains the default schema to use if composing a query (optional).

• Driver — enter the installed Neoview ODBC driver to be used with this Neoview system.

In the Add System window, these buttons allow you to test or add a new system, or close the system definition.

[Test] verifies the validity of a new system using the entries specified in the fields of the Add System window. [Test] is enabled if one or more systems is selected. The selected system is tested and the result is displayed in the Connection Test Result window. This action will:

• Cause an attempt to make a connection to the Neoview system specified in the Add System

window.

• Return the validating result in the Connection Test Result window that indicates the

following: — Test succeeded – the settings have been tested and the system is added to the My

Active Systems folder. (Database area only)

— Test failed – the settings have been tested and the test failed. If the test failed, the

reason for the failure in also provided in the Connection Test Result window. The system is added to the My Other Systems folder. (Database area only)

— Password not specified – the password has been specified. If the password is

empty, you must have clicked in this field to explicitly set an empty password. The system is added to the My Other Systems folder. (Database area only)

— Not tested – the entry is possibly empty and has not been tested. The system is added

to the My Other Systems folder. (Database area only)

[OK] attempts to create the new system using the entries specified in the fields of the Add System window. This action will:

• Cause an attempt to make a connection to the Neoview system specified in the Add System

window.

• If the connection is successful, the entry for the system is created and appears in the Systems

Tool window.

• If the connection attempt is not successful, a Connection Test Result window opens and

indicates the test failed for the Neoview system specified in the Add System window. Click [OK] to close the Connection Test Result window.

14 The Systems Tool

Edit System

NOTE: The Systems Tool window shows the active system definitions. Editing an active system

definition causes all previously configured system definitions to be lost.

To edit a system:

• Select a system in the Systems Tool window and click the Edit button. This brings up the

Edit System window. In the Edit System window the title bar displays the system being edited.

In the Edit System window these fields define a Neoview system:

• Your Name for System — enter an alias name to identify this Neoview system. The system

name can be up to 32 characters in length, and consist of letters, digits, and the underscore character.

• Server Data Source — enter the name of the data source on the Neoview system. If the

field is left blank, the default data source is used if it one has been configured.

• User Name — Neoview user name.

• Password — Neoview password.

• Host — enter the DNS name or IP address for the target Neoview system.

• Port Number — contains the NDCS server port number.

• Default Schema — contains the default schema to use if composing a query (optional).

• Driver — enter the installed Neoview ODBC driver to be used with this Neoview system.

In the Edit System window, these buttons allow you to test or add a new system, or close the system definition.

[Test] validates the changes made to the system using the entries specified in the fields of the Edit System window. [Test] is enabled if one or more systems is selected. The selected system is tested and the result is displayed in the Connection Test Result window. This action will:

• Cause an attempt to make a connection to the Neoview system specified in the Edit System

window.

• Return the validation result in the Connection Test Result window that indicates the

following: — Test succeeded – the settings have been tested and the system is added to the My

— Test failed – the settings have been tested and the test failed. If the test failed, the

— Password not specified – the password has been specified. If the password is

— Not tested – the entry is possibly empty and has not been tested. The system is added

Active Systems folder.

reason for the failure in also provided in the Connection Test Result window. The system is added to the My Other Systems folder.

empty, you must have clicked in this field to explicitly set an empty password. The system is added to the My Other Systems folder.

to the My Other Systems folder.

[OK] attempts to create the new system using the entries specified in the fields of the Edit System window. This action will:

• Cause an attempt to make a connection to the Neoview system specified in the Edit System

window.

• If the connection is successful, the entry for the system is created and appears in the Systems

Tool window.

• If the connection attempt is not successful, a Connection Test Result window opens and

indicates the test failed for the Neoview system specified in the Edit System window. Click [OK] to close the Connection Test Result window.

Edit System 15

Add System Like This

To add a system like the selected system’s current settings:

• Select the system definition you want to duplicate in the Systems Tool window.

• Click [Add Like]. This opens the Add Like System window.

• In the Add Like System window:

— A new name has to be specified in the field “Your name for the system.” — Validate the system information specified for the selected system. The title bar displays

the system whose settings are being used as a template for the new system.

— Select [OK]. You can also select [Test] to test the new system.

Remove System

To remove a selected system:

• Select the system you want to remove in the Systems Tool window.

• Click [Remove] in the Systems Tool window. This opens a Remove Confirmation window.

• In the Remove Confirmation window:

— Click [Yes] to remove the system definition, and close the Remove Confirmation

window.

— Click [No] to retain the system definition and close the Remove Confirmation window.

Clear Password for System

To clear a password for a selected system:

• Select the system that you want to clear the password for in the Systems Tool window.

• Click [Clear Password]. This opens a Clear Password Confirmation window.

• In the Clear Password Confirmation window:

— Click [Yes] to clear the password for the selected system, and close the Clear Password

— Click [No] to retain the password for the selected system, and close the Clear Password

Once the password is cleared, the system is moved under “My Other Systems.”

Test System

To test the validity of a selected system:

• Click [Test]. This action will:

— Cause an attempt to make a connection to the Neoview system specified in the selected

— Return the validating result in the Connection Test Result window that indicates the

Confirmation window.

system definition.

following: ◦ Test succeeded – the settings have been tested and the system is added to the

My Active Systems folder.

◦ Test failed – the settings have been tested and the test failed. If the test failed,

the reason for the failure in also provided in the Connection Test Result window. The system is added to the My Other Systems folder.

◦ Password not specified – the password has been specified. If the password

is empty, you must have clicked in this field to explicitly set an empty password. The system is added to the My Other Systems folder.

◦ Not tested – the entry is possibly empty and has not been tested. The system is

added to the My Other Systems folder.

16 The Systems Tool

3 Navigating HPDM

Navigation Tree Pane

The navigation tree pane in HPDM displays a tree of the database objects in hierarchical form. The navigation tree pane is the left pane of HPDM.

The navigation tree pane allows you to select a system and display the associated database objects in the right pane. Through the use of displays, you can switch between different systems and manage multiple Neoview platforms. In the navigation tree pane, you can select the My Active Systems folder or the My Other Systems folder.

My Active Systems

If you select the My Active Systems folder, the right pane displays this message upon successful logon:

In order to use a system you must have added it. You can do so by clicking the Add System. If you add a system and its settings are complete and correct, it will be added to the My Active Systems folder. If the system’s settings are incomplete and/or incorrect, it will be added to the My Other Systems folder. In either case, you will be able to edit the system later.

At this time, a new system can be added by clicking [Add System] displayed at the bottom of the window. If systems have been added previously, the right pane displays the active systems.

To display the My Active Systems folder options, right-click on the My Active Systems folder. The options are:

• Add to Favorites creates a shortcut for the object under “My Favorites.” The New Favorite

dialog box is launched to provide an alias for the object.

• Refresh refreshes the object and all children for the object in the navigation tree.

• Add System creates a new system, see “Add System” (page 13).

To display options for an active system in the My Active Systems folder, right-click a system and the options are:

• Add to Favorites creates a shortcut for the object under “My Favorites.” The New Favorite

dialog box is launched to provide an alias for the object.

• Refresh refreshes the object and all children for the object in the navigation tree.

• Add System Like This adds a system like the selected system’s current settings, see “Add

System Like This” (page 16).

• Edit System edits an active system, see “Edit System” (page 15).

• Remove System removes a selected system, see “Remove System” (page 16).

• Test System tests the validity of a system, see “Test System” (page 16).

• Clear Password for System clears a password for a system, see “Clear Password for System”

(page 16).

My Other Systems

If you select the My Other Systems folder, the right pane displays the systems where the system settings are incomplete or incorrect. If systems have been previously added, the right pane displays these systems.

Navigation Tree Pane 17

To display the My Other Systems folder options, right-click on the My Other Systems folder and the options are:

• Add to Favorites creates a shortcut for the object under “My Favorites.” The New Favorite

dialog box is launched to provide an alias for the object

• Refresh refreshes the object and all children for the object in the navigation tree.

• Add System creates a new system, see “Add System” (page 13).

To display options for a system in the My Other Systems folder, right-click a system and the options are:

• Add to Favorites creates a shortcut for the object under “My Favorites.” The New Favorite

dialog box is launched to provide an alias for the object.

• Refresh refreshes the object and all children for the object in the navigation tree.

• Add System Like This adds a system like the selected system’s current settings, see “Add

System Like This” (page 16).

• Edit System edits an active system, see “Edit System” (page 15).

• Remove System removes a selected system, see “Remove System” (page 16).

• Test System tests the validity of a system, see “Test System” (page 16).

• Clear Password for System clears a password for a system, see “Clear Password for System”

(page 16).

If you select a system from the My Other Systems folder, the right pane displays the Edit System dialog box. You can edit the system configuration and select [Test] or [Apply].

[Edit System] is displayed at the bottom of the window. At this time, you can edit the system if you desire. If the system settings are correct, the system is successfully added and appears in the My Active Systems folder. If the system settings are not correct, the system appears in the My Other Systems folder.

Right Pane

The right pane displays information about the selected objects in the left pane. The navigation buttons displayed in the top right pane are:

• [Parent] displays the schema folder and all folders for that system.

• [Previous] takes you to the previous schema (if any). If no other schemas exist, [Previous]

• [Next] takes you to the next schema (if any). If no other schemas exist, [Next] is disabled.

When a system object is selected, the buttons that are displayed in the bottom right pane are:

• [Test] tests a selected system and displays the results of in the Connection Test Result

• [Edit] launches the Edit System window with the selected systems’ current settings. [Edit]

• [Add] launches the Add System window with blank settings, except for the port number

• [Add Like] launches the Add Like System window with selected systems’ current settings.

• [Remove] launches the Remove Confirmation window. [Remove] is enabled if one or more

• [Clear Password] launches the Clear Password Confirmation window. [Clear Password]

If a table is displayed in the right pane, the buttons available in the bottom right pane are:

• [Data to Clipboard] copies the table data to the Windows clipboard.

• [Data to Browser] opens a browser to display the data in a tabular format.

is disabled.

window. [Test] is enabled if one or more systems is selected.

is enabled if one or more systems is selected.

(which defaults to 18650). The ODBC driver defaults to 2.0. [Add] is always enabled.

You can use these settings as a starting point for adding a new system. [Add Like] is enabled if only one system is selected.

systems is selected.

is enabled if one or more systems is selected.

18 Navigating HPDM

• [Data to Spreadsheet] opens the Excel application and displays the data.

• [Data to File] exports the data to a file in CSV format.

Right Pane 19

4 Managing Database Objects

This section describes how to display schemas, tables, table indexes, views, materialized views, materialized view indexes, materialized view groups, procedures, synonyms, and triggers in HPDM.

+ 49 hidden pages