HP Neoview Workload Management Services Guide HP Part Number: 613603-002 Published: July 2010 Edition: HP Neoview Release 2.
© Copyright 2010 Hewlett-Packard Development Company, L.P. 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. Warranty The information contained herein is subject to change without notice.
Table of Contents About This Document.......................................................................................................13 Intended Audience................................................................................................................................13 New and Changed Information in This Edition...................................................................................13 Document Organization.......................................................................
System Service.................................................................................................................................47 Predefined HPS Services and Connection Rules.............................................................................47 Default Service.................................................................................................................................48 User-Defined Services..........................................................................
Add an Execution Rule..................................................................................................................101 Set the Execution Rule Attributes..................................................................................................106 Alter an Execution Rule.................................................................................................................108 Delete an Execution Rule..........................................................................
Cancel a Query...............................................................................................................................145 How to Cancel a Query............................................................................................................145 Monitor WMS Services........................................................................................................................146 Understand the NWMS Services Counters.....................................................
Examples........................................................................................................................................187 ALTER RULE COMP Command........................................................................................................189 Syntax.............................................................................................................................................189 Considerations....................................................................
Syntax.............................................................................................................................................214 Considerations...............................................................................................................................214 Examples........................................................................................................................................215 INFO RULE COMP Command..............................................
Total Number of Executing Queries in Various States.............................................................236 Examples........................................................................................................................................237 STATUS QUERY Command................................................................................................................238 Syntax..........................................................................................................
Syntax.............................................................................................................................................256 Considerations...............................................................................................................................256 Examples........................................................................................................................................257 STATUS WMS VERSION Command..........................................
List of Figures 1-1 1-2 1-3 1-4 1-5 1-6 1-7 1-8 5-1 5-2 5-3 5-4 5-5 5-6 How a Connection Rule Is Applied..............................................................................................22 How the NDCS Server Assigns a Client Session to a Service.......................................................23 How Compilation Rules Are Applied...........................................................................................24 How WMS Handles an Incoming Query...................................
List of Tables 1 3-1 3-2 3-3 4-1 4-2 4-3 4-4 5-1 6-1 6-2 6-3 A-1 A-2 A-3 A-4 A-5 A-6 A-7 A-8 A-9 A-10 A-11 A-12 A-13 A-14 A-15 A-16 A-17 A-18 A-19 A-20 A-21 A-22 A-23 A-24 A-25 A-26 A-27 A-28 A-29 A-30 A-31 A-32 B-1 C-1 12 Cancellation of Queries in Neoview Release 2.4 and 2.5..............................................................14 Typical Query Workloads in an Enterprise Data Warehouse.......................................................43 Criteria for Defining Query Workloads..................
About This Document This manual describes how to use the Neoview Workload Management Services (WMS) to perform workload and resource management on a Neoview data warehousing platform. WMS commands enable you to define services and resource thresholds, monitor system resources, and manage queries. Intended Audience This manual is intended for customers who are using WMS to manage workloads on a Neoview data warehousing platform.
Chapter or Appendix New or Changed Information Appendix A: WMS Commands Updated these WMS commands: • “ALTER WMS Command” (page 204) • “ALTER WMS CONN Command” (page 206) • “DELETE RULE Command” (page 210) • “INFO RULE Command” (page 214) • “INFO RULE COMP Command” (page 216) • “INFO RULE CONN Command” (page 217) • “INFO RULE EXEC Command” (page 218) • “INFO SERVICE Command” (page 219) • “INFO WMS Command” (page 223) • “STATUS QUERIES Command” (page 229) • “STATUS RULE Command” (page 240) • “STATUS RULE
Chapter or Appendix Description Appendix B: Query Types Managed Describes the types of queries that run on the Neoview platform and whether by WMS they are managed by WMS. Appendix C: Query States and Substates Describes the states and substates that a query might have at different times while being managed by WMS. Notation Conventions General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual.
A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines.
[ESCAPE esc-char-expression] Related Documentation This manual is part of the HP Neoview customer library. Neoview Customer Library The manuals in the Neoview customer library are listed here for your convenience.
Neoview Workload Management Information about using Neoview Workload Management Services (WMS) to Services Guide manage workload and resources on a Neoview data warehousing platform.
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 This chapter introduces the Neoview Workload Management Services (WMS) and describes its purpose and benefits, how it works, and how to start using it.
If query statistics aggregation is enabled, WMS manages both non-unique and unique queries. Non-unique queries access multiple rows of data whereas unique queries access only one row of data. If query statistics aggregation is disabled, WMS manages only non-unique queries, which access multiple rows of data, and does not manage unique queries, which access only one row of data. When query statistics aggregation is disabled, unique queries run directly against the database without being managed by WMS.
1. 2. 3. 4. 5. A client application requests a connection to the Neoview platform and passes its connection attributes to the NDCS server. The NDCS server requests connection rules from WMS. WMS sends the connection rules to the NDCS server. The NDCS server compares the connection attributes of the client application with the connection rules from WMS.
For information on connection rules, see “Connection Rules” (page 70). After the client application connects to the Neoview platform and is assigned to a service, WMS proceeds with the “Compilation Phase” (page 24). Compilation Phase After a query is prepared, the NDCS server passes the execution decision making to WMS. WMS compares the compiler estimates of the query against the compilation rules set for the service and, along with other criteria, determines whether to permit the query to start executing.
Figure 1-4 How WMS Handles an Incoming Query Incoming query to WMS No Is WMS in the ACTIVE state? Yes Are the number of queries on this segment < max allowed*? No Is the compilation rule action = REJECT? Yes WMS rejects the request to execute the query and returns an error message to the client application. No Yes Yes No Is the compilation rule action = HOLD? Is the service ACTIVE? WMS places the query in the HOLDING state.*** Yes WMS sets and saves the warning level.
For information on compilation rules, see “Compilation Rules” (page 84). When the query starts executing, WMS proceeds with the “Execution Phase” (page 26). Execution Phase When a query has been executing for a configured interval (by default, five minutes), WMS starts periodically checking the execution rules set for the service against the query runtime statistics to determine whether the query can continue executing.
6. 7. If the query is allowed to finish executing, Neoview SQL returns the results of the query to the NDCS server. The NDCS server sends the query results back to the client application. If the query is cancelled, WMS terminates the query without stopping the NDCS server, and the NDCS server returns an SQL error message (8007) to the client application that issued the query. The client application remains connected to the Neoview platform. For more information, see “Cancel a Query” (page 145).
Behavior Without Aggregation Without query statistics aggregation, the execution of repetitive, short-running queries can overload system resources and inundate the Repository with unnecessary records. Consider what happens when query statistics aggregation is not enabled for a query that is prepared once and executed repeatedly. Each time the query executes, the NDCS server sends a “would like to execute” request to WMS. See Figure 1-7.
Figure 1-8 Query Statistics Aggregation Enabled for a Repetitive Query Repository Database NDCS Server SQL Compiler or Executor WMS Process Query prepare Would like to execute Begin message Query execution Aggregate statistics REPOS_INTERVAL expires Update the Repository Query execution WMS_INTERVAL expires Update WMS Query finished End message VST021.vsd As Figure 1-8 illustrates, the NDCS server prepares the query once and executes it repeatedly.
During the statistics aggregation, the configured WMS interval (WMS_INTERVAL) determines how frequently the NDCS server updates the WMS process with the query statistics. WMS makes those query statistics available for real-time monitoring in the Neoview Performance Analyzer (or the Neoview Query Viewer). The configured WMS interval must be less than or equal to the Repository interval (REPOS_INTERVAL).
Example of How to Use WMS This example demonstrates Tasks 3 through 5, which are mentioned in “How to Start Using WMS” (page 30). 1. Configure a WMS service with thresholds that limit the system resources that queries allowed to consume and determine when queries are allowed to run: Welcome to the HP Neoview Command Interface 2.5 (c) Copyright 2006-2010 Hewlett-Packard Development Company, LP.
WMS>ADD RULE EXEC EXECRULE3 (USED_ROWS > EST_USED_ROWS, WARN-LOW); --- WMS operation complete. WMS> This step shows these WMS commands: “ADD RULE CONN Command” (page 178), “ADD RULE COMP Command” (page 174), and “ADD RULE EXEC Command” (page 182). You can also perform this step using the Neoview Performance Analyzer or the Neoview Query Viewer (that is, the NPA Tools). For more information, see Chapter 5 (page 69). 3. Associate the rules with the service.
5. Monitor the query in the Live View of the Neoview Performance Analyzer or the Neoview Query Viewer: Query Put on Hold in SERVICE1 To use the Live View, see Chapter 6 (page 121).
2 Getting Started • • • • • “Installing and Starting WMS on the Neoview Platform” (page 35) “Using the Neoview Performance Analysis Tools” (page 35) “Using the Neoview Command Interface (NCI)” (page 38) “Using the HP Database Manager (HPDM)” (page 39) “Assign Administrators” (page 39) NOTE: For an overview of how to start using WMS, see “How to Start Using WMS” (page 30). Installing and Starting WMS on the Neoview Platform If you have Neoview Release 2.
The Neoview Query Viewer provides the same functionality as the Live View tab in the Neoview Performance Analyzer. However, the Neoview Query Viewer is a standalone tool and does not have the other capabilities of the Neoview Performance Analyzer. The Neoview Performance Analyzer has the Triage Space, where you can examine historical data from the Neoview Repository, and the Query Workbench, where you can analyze the query execution plans. The Neoview Query Viewer does not have those features.
Connecting to the Neoview Platform 1. In the Neoview Performance Analyzer or Neoview Query Viewer, use either of these approaches to open the Connect dialog box: • Press the F5 key. • Click the toolbar icon: Connect Icon • Select Workspace > Connect from the menu bar. 2. Set the connection attributes in the Connect dialog box. For more information, see “Set the Connection Attributes” (page 37). 3.
Attribute or Field Description or Action Password Enter your password. Passwords are case-sensitive. Depending on the security policies in force at your site, you might be required to supply two passwords—your own user password and a role password.
At the NS% or WMS> prompt, you can execute a WMS command. Terminate each WMS command that you execute in NCI with a semicolon (;). TIP: Consider putting WMS commands in script files that you can run in NCI. For more information about using script files in NCI, see the Neoview Command Interface (NCI) Guide.
2. In the Configure Workload Management Services dialog box, click the Admin Roles in the tree view. Admin Roles The right pane displays the Neoview roles that are designated as administrators. 3. To retrieve and display the latest Admin Roles Summary, right-click Admin Roles in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command. For more information, see the “STATUS ADMIN Command” (page 228).
• Right-click Admin Roles in the tree view, and then select Add Admin Role from the list: Admin Roles • Click the [Add] button at the bottom of the right pane, or click the the upper right pane. toolbar icon in Add a role Add a role 3. In the Add Admin Role dialog box, enter the name of a role that exists in the Neoview system. 4. Click the [Add] button to add the role, or click [Cancel] to quit the dialog box without adding the role.
Delete an Admin Role NOTE: Only the default administrators, ROLE.DBA, SUPER.SERVICES, and SUPER.SUPER, can delete other administrators. The default administrators are added during installation and cannot be deleted. To delete a Neoview role from the administrators in the WMS configuration: 1. 2. Display the Admin Roles Summary. See “Display the Admin Roles Summary” (page 39).
3 Understanding Your Workload Environment To implement WMS services and rules, you should have a good understanding of your workload environment and the types of query workloads that run in your enterprise data warehouse. This chapter provides guidelines for helping you identify and define the query workloads in your database environment.
Table 3-2 Criteria for Defining Query Workloads Query Workload Characteristic Description Related WMS Settings Types of queries The types of users and applications, and the queries that they issue.
Table 3-3 Tools for Monitoring Query Performance and System Resource Usage (continued) Neoview Management Tool Capabilities Actions That You Can Take Neoview Repository Provides historical data and statistics about these entities on the Neoview platform: • Queries initiated through ODBC and JDBC, including queries managed by WMS • Processing nodes (CPUs) For more information, see the Neoview Repository User Guide.
4 Configuring Services • • • • • “What Are Services?” (page 47) “How Do Services Work?” (page 48) “Configure the WMS System” (page 50) “Configure WMS Services” (page 54) “Export and Save the WMS Configuration” (page 65) NOTE: The tasks in this chapter require you to be an administrator. For more information, see “Assign Administrators” (page 39).
Table 4-1 Predefined HPS Services and Rules (continued) Predefined Services Associated Connection Rules HPS_MANAGEABILITY HPS_MANAGEABILITY9 HPS_MANAGEABILITY10 HPS_MANAGEABILITY11 HPS_TRANSPORTER HPS_TRANSPORTER1 Default Service By default, WMS uses the default service, HP_DEFAULT_SERVICE, to handle incoming queries. As an administrator, you can alter the thresholds of HP_DEFAULT_SERVICE but cannot delete this service.
Setting lower system resource thresholds for lower priority services restricts the number of executing queries associated with lower priority services and enables more queries associated with higher priority services to execute on the Neoview platform. For example, for a lower priority service, suppose you set the CPU busy to 55% and the memory usage to 40%.
Configure the WMS System WMS has a built-in system configuration, the internal SYSTEM service, that defines these thresholds for the Neoview platform: Table 4-2 WMS System Thresholds Threshold Default Value MAX_CPU_BUSY (or Max Processor Utilization) 100% MAX_MEM_USAGE 85% MAX_ROWS_FETCHED 0 (no limit) EXEC_TIMEOUT 0 (no timeout) WAIT_TIMEOUT 0 (no timeout) HOLD_TIMEOUT 0 (no timeout) STATS_INTERVAL 5 seconds RULE INTERVAL 60 seconds RULE_INTERVAL_QUERY_EXEC_TIME (or Rule Start Interval)
3. To retrieve and display the latest system configuration information, use either of these methods: • Click the [Refresh] button at the bottom of the right pane. • Right-click the System name in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command. For more information, see the “STATUS WMS Command” (page 252) (or “INFO WMS Command” (page 223)).
Use the WMS System Configuration Pane This table describes the attributes of the WMS system configuration: Group Box Field Description or Action Status State State of WMS: • INIT (for initialization) • ACTIVE • HOLD • STOPPED • SHUTDOWN This is a read-only field. To change the state of WMS, see “Manage the WMS System” (page 162). Thresholds Max Processor Utilization Maximum percentage of CPU busy allowed. By default, this value is (%) 100%.
Group Box Field Description or Action Wait Timeout (minutes) Maximum time interval a query can be in the waiting state before the query automatically times out. By default, this value is 0, meaning no timeout. This timeout specifies the default value to use when the timeout is not set for the WMS service. Enter a value from 0 to 1440 minutes. Zero means no timeout. Hold Timeout (minutes) Maximum time interval a query can be in the holding state before the query automatically times out.
Configure WMS Services This section describes how to establish user-defined services for managing query workloads in WMS.
3. To retrieve and display the latest services information, right-click Services in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command. For more information, see the “STATUS SERVICE Command” (page 245) (or “INFO SERVICE Command” (page 219)).
Understand the Service Definitions This table describes the attributes of the WMS services that appear in the Services Summary pane: Field Description SERVICE_NAME Name of the service STATE State of the service: • INIT (initialization) • ACTIVE (when the service is within its active period) • HOLD (when the service is put on hold) • STOPPING (when the service has been stopped by a STOP SERVICE command but queries are still executing in the service) • STOPPED (when the service is stopped by a STOP SERVI
Add a Service To add a new service in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon 2.
3. Enter a name for the service, and set the desired attribute values for the service. For more information, see “Set the Service Attributes” (page 59). NOTE: You cannot create a service with the HPS_ prefix. That prefix is reserved for system-generated services. 4. Click the [Add] button to add the service, or click [Cancel] to quit the dialog box without adding the service. NOTE: You can also perform this task using a WMS command. For more information, see the “ADD SERVICE Command” (page 185).
Set the Service Attributes This table describes the attributes that you can set for a WMS service when adding or altering the service: Group Box Attribute or Field Description or Action Service Name If you are adding a service, enter a name for the service. The name can be up to 24 characters long and can include multibyte characters. The name must be unique in WMS. It is not case-sensitive and is always converted to uppercase. If you are altering a service, this is a read-only field.
Group Box Attribute or Field Description or Action Max Rows Fetched Maximum number of rows that can be returned by a query. Enter a number from 0 to 9223372036854775807. Zero means no limit. If you set a Max Rows Fetched (or MAX_ROWS_FETCHED) limit for the service, the NDCS server considers this limit when fetching rows for queries associated with the service.
Group Box Attribute or Field Description or Action Associated Rules Rule Group: Connection Rules Rule Group: Compilation Rules Rule Group: Execution Rules If you are adding a service, this group box does not appear. If you are altering a service, this read-only group box contains a list of the connection, compilation, and execution rules associated with the service. To sort the rules in ascending or descending order, click the Rule Name, Rule Type, Warn Level, or Expression.
Double-click the service NOTE: With a service selected in the Services Summary pane, you can also click the [Alter] button at the bottom of the right pane, or click the right pane. 3. 4. toolbar icon in the upper Set the desired attribute values for the service. For more information, see “Set the Service Attributes” (page 59). Click the [Alter] button at the bottom of the right pane to save your changes to the service. NOTE: You can also perform this task using a WMS command.
Table 4-3 How an Altered Service Attribute Affects Queries Associated With the Service (continued) Altered Service Attribute Do executing queries Do waiting queries in Do holding queries in the service use the the service use the in the service use new attribute? new attribute? the new attribute? Do incoming queries in the service use the new attribute? MAX_MEM_USAGE No Yes Yes, when they are Yes released back into the waiting or executing state MAX_ROWS_FETCHED No Yes Yes, when they are Yes rele
2. In the Configure Workload Management Services dialog box, use either of these methods to select the service that you want to delete: • Under Services in the tree view, right-click the name of the service that you want to delete, and then select Delete from the list. • Click Services in the tree view, and then click the name of the service in the Services Summary pane. Click the [Delete] button at the bottom of the right pane, or click the toolbar icon in the upper right pane.
Display the Service in Effect The GET SERVICE statement shows the service that is in effect for the current session and provides some information about the service and the client session: SQL>get service; CUR_SERVICE PLAN TEXT SESSION_NAME DSN_NAME ROLE_NAME ---------------------------------------------------------------------------------HP_DEFAULT_SERVICE 1 1 TDM_Default_DataSource role.hr CUR_SCHEMA RULE_NAME APPL_NAME ---------------- -------------- ----------- NEO.
Table 4-4 Conversion of the WMS Configuration to WMS Commands (continued) 3. 66 WMS Configuration Equivalent WMS Command User-defined execution rules ADD RULE EXEC System-defined rules (with the HPS_ prefix) ALTER RULE CONN Connection-rule associations with services ALTER WMS CONN Compilation-rule associations with each service ALTER SERVICE COMP Execution-rule associations with each service ALTER SERVICE EXEC User-defined admin roles (The system-created admin roles—SUPER.SUPER, SUPER.
4. In the Save As dialog box, choose the location on your computer where you want to store the WMS command file, and enter a file name that has a .sql or .txt file extension: 5. 6. Click the [Save] button to save the generated commands to a script file on your computer. To close the Export Neoview WMS Configuration dialog box, click the [Done] button at the bottom of the dialog box.
5 Configuring Rules • • • • • • • • • “What Are Rules?” (page 69) “Global Rule Considerations” (page 69) “Connection Rules” (page 70) “Compilation Rules” (page 84) “Execution Rules” (page 98) “Display the Rules Summary” (page 112) “Associate Rules With Services” (page 114) “Display the Services Associated With a Rule” (page 118) “Display the Rules Associated With a Service” (page 119) NOTE: The tasks in this chapter require you to be an administrator.
Connection Rules Connection rules determine which service to assign to the client session. Because connection rules are the mechanism for assigning queries to services, you must create connection rules and associate them with services if you plan to use the services that you created to manage query workloads.
Figure 5-1 Evaluating the First Connection Rule in a Series Client Session Connection Attributes: - Session name - Application name - Login role - Data source name (DSN) = = = = JOB2468 YTDReport ROLE.USR2 DS1 NDCS Server CONNRULE1 = FALSE Neoview WMS CONNRULE1 Attributes: - SESSION - APPL - LOGIN - DSN = = = = The NDCS server checks the next connection rule, CONNRULE2. JOB2468 Not set ROLE.USR1 Not set Rule-to-Service Associations: 1. CONNRULE1 -> SERVICE1 2. CONNRULE2 -> SERVICE1 3.
Connection Attribute Expressions The connection attributes of the client session must match all the connection attribute expressions in the connection rule for the connection rule to be considered true. When the connection attributes of the client session match all the expressions in the connection rule and the connection rule evaluates to true, the NDCS server assigns the client session to the service associated with the connection rule.
connection attribute expressions of more than one connection rule defined in WMS. However, because the NDCS server evaluates each connection rule in the order in which you have associated the connection rules with services, the NDCS server assigns the client session to the service that is associated with the first connection rule that matches the connection attributes of the client session.
The right pane displays a summary of all the connection rules in WMS. For more information, see “Understand the Rule Definitions” (page 113). 4. To retrieve and display the latest connection rules summary, use either of these methods: • Click the [Refresh] button at the bottom of the right pane. • Right-click Rules in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command.
2. In the Configure Workload Management Services dialog box, use either of these methods to launch the Add Rule dialog box: • Right-click Rules or Connection Rules in the tree view, and then select Add Rule from the list: Connection Rules • Click Rules or Connection Rules in the tree view, and then click the [Add] button at the bottom of the right pane, or click the toolbar icon in the upper right pane. Add a rule Add a rule 3.
NOTE: You cannot create a rule with the HPS_ prefix. That prefix is reserved for system-generated rules. 4. 5. Select the Connection (CONN) option if it is not already selected. From the Warn Level list, select a warning level: • WARN-LOW • NO-WARN If you do not select a warning level, the default is NO-WARN, meaning that WMS does not set the warning level or save warning information when the connection rule evaluates to true.
6. If you want SQL commands to execute at the start of the client session (when the rule evaluates to true): a. From the Action list, select SQL_CMD. b. In the SQL String box, enter one or more SQL commands that you want to execute when connecting to the WMS service. NOTE: If an identifier in the SQL command contains multibyte characters, you must delimit the identifier in double quotes, as this example shows: SET SCHEMA " 7.
NOTE: You can also perform this task using a WMS command. For more information, see the “ADD RULE CONN Command” (page 178). After adding a rule, you can view the Rule Details by clicking the name of the rule in the tree view. Set the Connection Rule Attributes This table describes the attributes that you can set for a connection rule when adding or altering the rule: Group Box Attribute or Field Description or Action Rule Name If you are adding a connection rule, enter a name for the rule.
Group Box Attribute or Field Description or Action Aggregate Type Select one or more query types that will have their statistics aggregated: • INSERT (unique or non-unique) • UPDATE (unique or non-unique) • DELETE (unique or non-unique) • SELECT (unique) Click Check all to select all query types. Unique queries access only one row of data. Non-unique queries access multiple rows of data. For more information, see “Query Statistics Aggregation” (page 27).
Group Box Attribute or Field Description or Action Comments Comments Enter optional text to describe the rule. The text cannot exceed 256 characters. Command Preview Command Preview This read-only field displays the WMS command that will be issued when you add the rule. Alter a Connection Rule To alter an existing connection rule in WMS: 1.
Double-click the rule NOTE: With a connection rule selected in the Connection Rules Summary pane, you can also click the [Alter] button at the bottom of the right pane, or click the icon in the upper right pane. toolbar 3. Set the desired attribute values for the connection rule. For more information, see “Set the Connection Rule Attributes” (page 78). 4. Click the [Alter] button at the bottom of the right pane to save your changes to the WMS system configuration.
5. Click [OK] when notified that the connection rule was altered successfully. NOTE: You can also perform this task using a WMS command. For more information, see the “ALTER RULE CONN Command” (page 192). Delete a Connection Rule NOTE: You cannot delete the system-generated rules that start with the HPS_ prefix. If you select any of those system-created rules for deletion, they are ignored, and an informational message is displayed. To delete a connection rule in WMS: 1.
Delete the rule Delete the rule NOTE: In the Connection Rules Summary pane, you can select more than one connection rule to delete. However, you cannot delete the system-generated rules that start with the HPS_ prefix. If you select any of those system-created rules for deletion, they are ignored, and an informational message is displayed. 3. When prompted to confirm the deletion, click [Yes] to continue or [No] to cancel. NOTE: You can also perform this task using a WMS command.
Compilation Rules Compilation rules, which are applied after a query is prepared, determine whether to permit the query to start executing, put the query on hold, or reject the query, based on the compiler estimates for the query.
Figure 5-3 Evaluating the First Compilation Rule Associated With the Service NDCS Server Compiler Estimate for the Query: CMP_DP2_ROWS_USED = 33500 Neoview WMS COMPRULE1 = FALSE SERVICE1 COMPRULE1 Expression: WMS checks the next compilation rule, COMPRULE2. EST_USED_ROWS > 50000 Compilation Rule List: 1. COMPRULE1 2. COMPRULE2 3. COMPRULE3 vst103a.vsd WMS checks the next compilation rule, COMPRULE2, associated with the service.
To implement compilation rules in WMS, see these sections: • • “Add a Compilation Rule” (page 87) “Associate Compilation Rules With a Service” (page 97) Display the Compilation Rules Summary 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon 2. 3.
4. To retrieve and display the latest compilation rules summary, use either of these methods: • Click the [Refresh] button at the bottom of the right pane. • Right-click Rules in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command. For more information, see the “INFO RULE COMP Command” (page 216) (or “STATUS RULE COMP Command” (page 242)). Add a Compilation Rule To add a new compilation rule in WMS: 1.
2. In the Configure Workload Management Services dialog box, use either of these methods to launch the Add Rule dialog box: • Right-click Rules or Compilation Rules in the tree view, and then select Add Rule from the list: Compilation Rules • Click Rules or Compilation Rules in the tree view, and then click the [Add] button at the bottom of the right pane, or click the toolbar icon in the upper right pane.
3. In the Add Rule dialog box, enter a name for the compilation rule, and set the desired attribute values for the rule. The next steps explain how to set the attributes. For a summary of the attributes, see “Set the Compilation Rule Attributes” (page 92). NOTE: You cannot create a rule with the HPS_ prefix. That prefix is reserved for system-generated rules. 4. 5. Select the Compilation (COMP) option if it is not already selected.
6. If you want WMS to take an action when the rule evaluates to true: a. From the Action list, select one of these actions: Action Description REJECT Causes the query to be rejected HOLD Puts the query in the holding state SQL_CMD Causes the NDCS server to execute one or more SQL commands before executing the query You must specify one of those actions if you specify NO-WARN for the warning level. b. 7. If you selected SQL_CMD, enter one or more SQL commands in the SQL String box.
d. In the Value box, enter a value to be compared with the compiler estimate: Compiler Estimate Acceptable Comparison Value EST_TOTAL_MEMORY The rule expression compares the estimated memory for the query, in kilobytes, with this numeric value. Enter a numeric value in kilobytes (KB). EST_TOTAL_TIME The rule expression compares the estimated time to execute the query, in minutes, with this numeric value. Enter a numeric value in minutes.
NOTE: You can also perform this task using a WMS command. For more information, see the “ADD RULE COMP Command” (page 174). After adding a rule, you can view the Rule Details by clicking the name of the rule in the tree view. Set the Compilation Rule Attributes This table describes the attributes that you can set for a compilation rule when adding or altering the rule: Group Box Attribute or Field Description or Action Rule Name If you are adding a compilation rule, enter a name for the rule.
Group Box Attribute or Field Description or Action Aggregate Type Disabled for compilation rules WMS Interval Disabled for compilation rules Repository Interval Disabled for compilation rules Execute Interval Disabled for compilation rules Logical Select one of these operators: • AND, which requires that all expressions in the rule evaluate to true for the rule to be considered true and trigger a warning level or action • OR, which requires that only one of the expressions in the rule evaluate
Group Box Attribute or Field Description or Action Comments Comments Enter optional text to describe the rule. The text cannot exceed 256 characters. Command Preview Command Preview This read-only field displays the WMS command that will be issued when you add the rule. Alter a Compilation Rule To alter an existing compilation rule in WMS: 1.
Double-click the rule NOTE: With a compilation rule selected in the Compilation Rules Summary pane, you can also click the [Alter] button at the bottom of the right pane, or click the toolbar icon in the upper right pane. 3. Set the desired attribute values for the compilation rule. For more information, see “Set the Compilation Rule Attributes” (page 92). 4. Click the [Alter] button at the bottom of the right pane to save your changes to the WMS system configuration.
5. Click [OK] when notified that the compilation rule was altered successfully. NOTE: You can also perform this task using a WMS command. For more information, see the “ALTER RULE COMP Command” (page 189). Delete a Compilation Rule NOTE: You cannot delete the system-generated rules that start with the HPS_ prefix. If you select any of those system-created rules for deletion, they are ignored, and an informational message is displayed. To delete a compilation rule in WMS: 1.
Delete the rule Delete the rule 3. When prompted to confirm the deletion, click [Yes] to continue or [No] to cancel. NOTE: You can also perform this task using a WMS command. For more information, see the “DELETE RULE Command” (page 210). Associate Compilation Rules With a Service To associate one or more compilation rules with a service, see “Associate Rules With Services” (page 114). NOTE: You can also perform this task using a WMS command.
Execution Rules Execution rules, which are applied after a query has been executing for a configured interval (by default, five minutes), determine whether to permit the query to continue executing based on the query runtime statistics.
Figure 5-5 Evaluating the First Execution Rule Associated With the Service Runtime Manageability Services (RMS) Query Runtime Statistic: USED_ROWS = 1567 Neoview WMS EXECRULE1 = FALSE SERVICE1 EXECRULE1 Expression: WMS checks the next execution rule, EXECRULE2. USED_ROWS >= 10000 Execution Rule List: 1. EXECRULE1 2. EXECRULE2 3. EXECRULE3 vst104a.vsd WMS checks the next execution rule, EXECRULE2, associated with the service.
To implement execution rules in WMS, see these sections: • • “Add an Execution Rule” (page 101) “Associate Execution Rules With a Service” (page 111) Display the Execution Rules Summary 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon 2. 3.
4. To retrieve and display the latest execution rules summary, use either of these methods: • Click the [Refresh] button at the bottom of the right pane. • Right-click Rules in the tree view, and then select Refresh from the list. NOTE: You can also perform this task using a WMS command. For more information, see the “INFO RULE EXEC Command” (page 218) (or “STATUS RULE EXEC Command” (page 244)). Add an Execution Rule To add a new execution rule in WMS: 1.
• Right-click Rules or Execution Rules in the tree view, and then select Add Rule from the list: Execution Rules • Click Rules or Execution Rules in the tree view, and then click the [Add] button at the bottom of the right pane, or click the toolbar icon in the upper right pane.
3. In the Add Rule dialog box, enter a name for the execution rule, and set the desired attribute values for the rule. The next steps explain how to set the attributes. For a summary of the attributes, see “Set the Execution Rule Attributes” (page 106). NOTE: You cannot create a rule with the HPS_ prefix. That prefix is reserved for system-generated rules. 4. 5. Select the Execution (EXEC) option if it is not already selected.
6. If you want WMS to cancel the query when the rule evaluates to true, select CANCEL from the Action list. NOTE: You must specify CANCEL is you specify NO-WARN for the warning level. WMS immediately cancels the query. When the query is cancelled, any uncommitted work is automatically rolled back. 7. Specify one to fifteen execution rule expressions to define the conditions that must be met for the execution rule to evaluate to true and trigger a warning level or action: a.
e. In the Value box, enter a value to be compared with the query runtime statistic: Query Runtime Statistic Acceptable Comparison Value USED_ROWS The rule expression compares the number of rows returned to the query with this numeric value. Enter a numeric value, or select EST_USED_ROWS from the Value list. EST_USED_ROWS is the compiler estimate. ACCESSED_ROWS The rule expression compares the number of rows accessed by the query with this numeric value.
Set the Execution Rule Attributes This table describes the attributes that you can set for an execution rule when adding or altering the rule: Group Box Attribute or Field Description or Action Rule Name If you are adding an execution rule, enter a name for the rule. The name can be up to 24 characters long and can include multibyte characters. The name must be unique in WMS. It is not case-sensitive and is always converted to uppercase. If you are altering a rule, this is a read-only field.
Group Box Attribute or Field Description or Action Property Select one of these query statistics to evaluate: • USED_ROWS, which is the number of rows returned to the query • ACCESSED_ROWS, which is the number of rows accessed by the query • TOTAL_MEM_ALLOC, which is the total memory used to process the query • ELAPSED_TIME, which is the elapsed time of the query execution (that is, the time since the start of the query execution until the current time, including any time that the query spent in the wai
Alter an Execution Rule To alter an existing execution rule in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon 2.
NOTE: With an execution rule selected in the Execution Rules Summary pane, you can also click the [Alter] button at the bottom of the right pane, or click the icon in the upper right pane. toolbar 3. Set the desired attribute values for the execution rule. For more information, see “Set the Execution Rule Attributes” (page 106). 4. Click the [Alter] button at the bottom of the right pane to save your changes to the WMS system configuration.
Delete an Execution Rule NOTE: You cannot delete the system-generated rules that start with the HPS_ prefix. If you select any of those system-created rules for deletion, they are ignored, and an informational message is displayed. To delete an execution rule in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar.
Delete the rule Delete the rule 3. When prompted to confirm the deletion, click [Yes] to continue or [No] to cancel. NOTE: You can also perform this task using a WMS command. For more information, see the “DELETE RULE Command” (page 210). Associate Execution Rules With a Service To associate one or more execution rules with a service, see “Associate Rules With Services” (page 114). NOTE: You can also perform this task using a WMS command.
Display the Rules Summary To view a summary of all the rules in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon 2. In the Configure Workload Management Services dialog box, select Rules in the tree view: Rules The right pane displays a summary of all the existing rules in WMS.
Understand the Rule Definitions This table describes the attributes of the WMS rules that appear in the Rules Summary pane: Field Description NAME Name of the rule TYPE Type of the rule: • CONN for a connection rule • COMP for a compilation rule • EXEC for an execution rule WARN_LEVEL Warning level: • WARN-LOW • WARN-MEDIUM • WARN-HIGH • NO-WARN ACT Action taken when the rule evaluates to true: • HOLD • REJECT • CANCEL • SQL string to execute AGGR_QUERY_TYPES For connection rules, this field cont
Associate Rules With Services To apply the rules that you defined to queries that will run in WMS services, you must associate the rules with the services. IMPORTANT: If you do not associate connection rules with services, WMS does not assign queries to those services but instead assigns queries to the default service, HP_DEFAULT_SERVICE. To associate rules with services: 1.
4. In the Rules box, select the name of the rule that you want to associate with a service. Select a rule To display the services that are already associated with the rule, see “Display the Services Associated With a Rule” (page 118). 5. In the Services box, select the name of the service that you want to associate with the rule. Select a service To display the rules that are already associated with the service, see “Display the Rules Associated With a Service” (page 119).
6. Click the [Associate] button to associate the rule with the service. The rule-and-service association appears in the Associated Rules box. 7. Repeat steps 4 through 6 for each rule that you want to associate with a service. NOTE: You cannot associate the same connection rule with more than one service. You cannot have more than 988 rule-to-service associations. 8.
9. To change the order of the rule-and-service associations in the Associated Rules list: a. Select the name of a rule-and-service association in the Associated Rules list. b. Click the [Move up] or [Move Down] button to move the rule-and-service association up or down in the list. Move the rule up or down NOTE: The NDCS server evaluates each rule in the order in which you have listed them in the rule-and-service associations. 10.
Display the Services Associated With a Rule 1. 2. 3. In the Associate Rules dialog box, select the rule type if it is not already selected. See steps 1 through 3 in “Associate Rules With Services” (page 114). Select the name of the rule in the Rules list. Click the [Associated Services] button. The Info box displays the associated services, if any exist, for the rule. 4. To view the definition of the rule, click the [Show Rule Info] button. The Info box displays the WMS command that defines the rule. 5.
Display the Rules Associated With a Service 1. 2. 3. In the Associate Rules dialog box, select the rule type if it is not already selected. See steps 1 through 3 in “Associate Rules With Services” (page 114). Select the name of the service in the Services list. Click the [Associated Rules] button. The Info box displays the associated rules, if any exist, for the service. 4. To view the definition of the service, click the [Show Service] button.
6 Monitoring and Managing Workloads • • • • • • “Live View in the NPA Tools” (page 121) “Monitor Queries” (page 126) “Manage Queries” (page 142) “Monitor WMS Services” (page 146) “Manage WMS Services” (page 151) “Monitor System Resources” (page 165) Live View in the NPA Tools The Live View in the Neoview Performance Analyzer or the Neoview Query Viewer displays the queries that are currently running in the system (or have completed in the last minute) and are being managed by WMS.
Pause the Live View Display The Live View refreshes its display of the running queries and summaries at regular intervals (every 30 seconds by default).
Customize the Query Columns To customize the list of columns displayed for the queries in the Live View pane: 1. 2. Right-click anywhere in the Live View pane. Select Customize Grid Columns from the list. The Customize Live Query View Grid Columns dialog box appears. 3. 4. 5. 6. Clear or select the column names that you want to appear in the Live View pane. Select the Deselect All option to clear all the column names, or select the Select All option to select all the column names.
Preview the SQL Text Select the Preview SQL check box to display the SQL text underneath each query in the Live View pane. For example, when Preview SQL is enabled, the SQL text appears in gray underneath each query in the Live View pane: To remove the SQL text from underneath each query in the Live View pane, clear the Preview SQL check box. Hide Completed Queries To hide completed queries (that is, queries that completed execution in the last minute) in the Live View: 1. 2. 3.
Configure the Live View Settings To configure the Live View settings, select Tools > Options from the menu bar. The Options dialog box appears: In the Options dialog box, you can set the following options for the Live View: Group Box Attribute or Field Description or Action Connection Settings Connection Timeout Determines the number of seconds to wait when establishing a connection. (in secs) The default is 30 seconds.
NOTE: The Neoview Performance Analyzer displays other fields in the Options dialog box. Those fields do not apply to the Live View and are not described here. For more information, see the NPA Tools online help or the Neoview Performance Analysis Tools User Guide. Click [OK] to save your changes, or click [Cancel] to quit without saving your changes.
2. 3. Select Copy To Clipboard from the list. Paste the contents of the clipboard into a file of your choice. Load Queries From the Live View to the Triage Space The Triage Space in the Neoview Performance Analyzer enables you to examine historical data from the Neoview Repository. To load queries from the Live View into the Triage Space for further analysis: 1. 2. Select the queries in the Live View pane.
To hide the Query Details pane, click the [>> Query Details] button. 3. To display the Monitoring Query window, use either of these approaches: • In the Query Details pane, click the [Monitor Query] button. • In the Live View pane, double-click the query. NOTE: You can launch multiple Monitoring Query windows to monitor several queries simultaneously.
In the Monitoring Query window, the query details are organized into these group boxes: Group Box Description Time-Based Metrics Displays time-based metrics for the query. The set of metrics varies depending on the version of the Neoview platform to which you are connected. See “Time-Based Metrics” (page 130).
Understand the Query Details The tables in the next sections describe the information that is displayed for the query in each group box of the Monitoring Query window in the Neoview Performance Analyzer (or the Neoview Query Viewer). (To open the Monitoring Query window, see “Display the Query Details” (page 127).
Name Description Entry Ts Time in Local Civil Time (LCT) when the query entered the Neoview WMS queue requesting permission to execute (admission control) Entry Jts Time in Julian format when the query entered the Neoview WMS queue requesting permission to execute (admission control) Rate-Based Metrics This table describes the rate-based metrics for the query: Name Description Rows/second Rate of the rows being retrieved from the system Processor Usage/second Amount of processor (CPU) cycles that
Name Description Query Name Internal name of the query containing the client host name concatenated with the application name User Name Name of the user connected to the Neoview platform Datasource Name of the data source for the connection Parent Query Id Identifier of the parent query. If there is no parent query ID associated with the query, NONE is displayed.
Rule Warnings This table describes information about rule warnings that occurred while the query was running: Field Description SERVICE NAME Name of the WMS service WARN TYPE CONN for a connection rule COMP for a compilation rule EXEC for an execution rule WARN LEVEL LOW, MEDIUM, or HIGH RULE NAME Name of the rule that triggered the warning EXPR Expression number in the rule, corresponding to the ordinal position of a rule expression that evaluated to true (if the rule was defined with an OR oper
Load the Workbench To load the query into the Query Workbench for further analysis of its query execution plan, click the [Load Workbench] button. For more information about the Query Workbench, see the NPA Tools online help or the Neoview Performance Analysis Tools User Guide. Colorize the Query Text By default, the SQL text is displayed in color within the Query Text box.
Compile-Time Metrics This table describes the compile-time metrics for the query: Name Description Estimated Cost Estimated cost of the query Cardinality Estimate Estimated number of rows returned by the query Cpu Time Estimate Estimated total amount of processor (CPU) time Io Time Estimate Estimated total amount of I/O time Msg Time Estimate Estimated total amount of messaging time Idle Time Estimate Estimated idle time Estimated Total Time Estimated total time Rows Access Estimate Estimat
Run-Time Metrics This table describes the runtime metrics for the query: 136 Name Description Exec State Internal execution state of the query. For more information, please contact your HP Support representative. Error Code Error code value Sql Error Code Top-level error code returned by the query, indicating whether the query completed successfully or with warnings or errors. A positive number indicates a warning. A negative number indicates an error.
Name Description Eid Space Used Total space used by the executor in the disk process Eid Heap Alloc Total heap allocation by the executor in the disk process Eid Heap Used Total heap used by the executor in the disk process Total Memory Allocated Total memory allocated for the query, which is the sum of the SQL Space Used and SQL Heap Used Per-Table Statistics This table describes the per-table statistics for the query: Field Description TABLE NAME Name of the table EST ACCESSED ROWS Estimate
Understand the Session Statistics This table describes the Session Statistics fields: Name Description Application Id Name of the application that initiated the session Client Id Client host name Client User Name Name of the user who originated the session by logging in to the Neoview platform. If the name is not available from the ODBC driver, this field is NULL.
Name Description Total Executes Total number of SQLExecute calls Total Fetches Total number of SQLFetch calls Total Insert Stmts Executed Total number of SQL INSERT statements sent by the HP ODBC driver to the NDCS server. Does not include internally generated SQL statements. Total Login Elapsed Time Mcsec Time in microseconds spent on login, including processing steps on the Neoview platform and, for database users, the time required for interaction with the external authentication server.
Display the State of the Queries Over Time To display the state of the queries over a period of time: 1. Click the icon in the Statement Counters box. The Statement and Services Counter Details window appears and starts displaying the state of the queries over time in graphical and tabular formats: State of the Queries NOTE: The window also displays a summary and detailed information about the WMS services in the NWMS Services Summary and NWMS Services Details boxes.
Understand the Statement Counters Details This table describes the fields that appear in the Statement Counters Details box: Field Description Time Date and time when the statement counters are checked and reported. The display format of the date is mm/dd/yyyy (month, day, year). The display format of the time is hh:mm:ss (hour, minutes, seconds). The statement counters are checked and displayed every 30 seconds by default.
NOTE: The maximum size of the graph is limited to the size of the Statement and Services Counter Details window. 3. To restore the graph to its original size, drag the corner of the graph until the graph shrinks to its original size in the window. Copy the Query Summary Graph to the Clipboard To copy the graph to the clipboard: 1. 2. 3. Right-click the graph, and select Copy. Click [OK] when notified that the image was copied to the clipboard.
You can put a query that is on hold back into the executing or waiting state by performing a release operation. If you use the hold operation on an executing query, the operation suspends the query, suspending all executor processes in which the query is running.
Release a Query NOTE: You must be an administrator to release queries. You can put a query that was put on hold back into the executing or waiting state by performing a release operation. This table shows how the release operation affects holding queries: Table 6-2 Effect of the Release Operation WMS Operation Action RELEASE when QUERY_STATE = HOLDING The query enters the WAITING state, and WMS checks the system resources. If system resources are unavailable, the query remains in the WAITING state.
Cancel a Query NOTE: You must be an administrator to cancel queries. You can cancel (or kill) a query to free system resources. This table shows how the cancel operation affects waiting, holding, suspended, and executing queries: Table 6-3 Effect of the Cancel Operation WMS Operation Action CANCEL when QUERY_STATE = WAITING WMS rejects the query. CANCEL when QUERY_STATE = HOLDING WMS rejects the query. CANCEL when QUERY_STATE = SUSPENDED WMS cancels the query without stopping the NDCS server.
3. When prompted to confirm the cancellation, click [OK]. Internally, WMS performs a CANCEL command on the selected queries. In the Live View, the cancelled (killed) queries are shown in brown text. NOTE: You can also perform this task using the CANCEL QUERY or CANCEL QUERIES command. For more information, see the “CANCEL Command” (page 208).
Display the Services Status To display the state of the services: 1. Click the icon in the NWMS Services box. The Statement and Services Counter Details window appears and displays a summary and detailed information about the WMS services: Services Status The NWMS Services Summary box displays data from the NWMS Services box in the Live View and includes the CPU Busy and Memory Usage percentages from the last sample. This data is refreshed every 30 seconds by default.
This table describes the attributes of the WMS services that appear in the NWMS Services Details box: Field Description SERVICE NAME Name of the service STATE State of the service: • INIT (initialization) • ACTIVE (when the service is within its active period) • HOLD (when the service is put on hold) • STOPPING (when the service has been stopped by a STOP SERVICE command but queries are still executing in the service) • STOPPED (when the service is stopped by a STOP SERVICE command) • TRANSIENT (when t
Log the Service Details to a File You can log the status of the services, including any threshold violations, and the service definitions to a .log file on your computer. When you start logging, the Neoview Performance Analyzer (or the Neoview Query Viewer) automatically updates the log file at regular intervals (every 30 seconds by default) with the latest services data.
Log File Format At regular intervals (30 seconds by default), the Neoview Performance Analyzer (or the Neoview Query Viewer) writes a new block of services data to the log file and appends this data to the previously written services data. The data blocks are separated from each other by dashed lines.
2.
• Under Services in the tree view, right-click the name of the service that you want to hold, and then select Hold from the list. • Click Services in the tree view, and then click the name of the service in the Services Summary pane. Click the [Hold] button at the bottom of the right pane, or click the toolbar icon in the upper right pane. Hold the service Hold the service NOTE: hold. 3.
4. Click [OK] to acknowledge that the service has been put on hold. NOTE: You can also perform this task using the HOLD SERVICE command. For more information, see the “HOLD Command” (page 212). Hold All Services NOTE: You must be an administrator to hold all services. To hold all active services, including the system-created services (HP_DEFAULT_SERVICE, HPS_MANAGEABILITY, and HPS_TRANSPORTER): 1.
Release a Service NOTE: You must be an administrator to release a service. To release a service, the service must be in the HOLD state. To release one or more services that were put on hold: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • 2.
Release the service Release the service NOTE: In the Services Summary pane, you can select more than one held service to release. 3. Click [OK] to acknowledge that the service was released. NOTE: You can also perform this task using the RELEASE SERVICE command. For more information, see the “RELEASE Command” (page 225).
Release All Services NOTE: You must be an administrator to release all services. To release all services that were put on hold: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • In the Statement and Services Counter Details window, click the [Configure] button. 2.
Stop a Service NOTE: You must be an administrator to stop a service. To stop a service, the service must be in the ACTIVE state and must not be one of the system-created services: HP_DEFAULT_SERVICE, HPS_MANAGEABILITY, or HPS_TRANSPORTER. To stop one or more active services in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar.
Stop the service Stop the service NOTE: stop. 3.
4. a. Select the Stop Immediately check box to immediately cancel all executing queries in the service when the service is stopped, or clear this check box to allow the queries to finish executing when the service is stopped. • If you select Stop Immediately, WMS initially changes the state of the service to STOPPING. After cancelling all the executing queries, WMS changes the state of the service to STOPPED. • If you clear Stop Immediately, WMS initially changes the state of the service to STOPPING.
Start a Service NOTE: You must be an administrator to start a service. To start a service, the service must be in the STOPPED state. To start one or more stopped services in WMS: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • 2. In the Statement and Services Counter Details window, click the [Configure] button.
Start the service Start the service NOTE: In the Services Summary pane, you can select more than one stopped service to start. 3. Click [OK] to acknowledge that the service was started. Releasing a service changes the state of the service from STOPPED to ACTIVE or HOLD, depending on the active period of the service. When the service is started, it can accept new queries if it is in the ACTIVE state. NOTE: You can also perform this task using the START SERVICE command.
Manage the WMS System In the Neoview Performance Analyzer (or the Neoview Query Viewer), you can take these actions on the WMS system: • • • • “Hold the WMS System” (page 162) “Release the WMS System” (page 163) “Stop the WMS System” (page 163) “Start the WMS System” (page 165) Hold the WMS System Putting the WMS system on hold suspends all WMS activities. NOTE: You must be an administrator to hold the WMS system. To hold the WMS system, it must be in the ACTIVE state. To hold the WMS system: 1.
Release the WMS System NOTE: You must be an administrator to release the WMS system. To release the WMS system, it must be in the HOLD state. To release the WMS system: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • 2. In the Statement and Services Counter Details window, click the [Configure] button.
1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • In the Statement and Services Counter Details window, click the [Configure] button. 2. In the tree view of the Configure Workload Management Services dialog box, right-click System, and then select Stop from the list: 3.
Start the WMS System NOTE: You must be an administrator to start the WMS system. To start the WMS system, it must be in the STOPPED state. To start the WMS system: 1. Use either of these methods to open the Configure Workload Management Services dialog box: • Select Tools > Configure Workload Management Services from the menu bar. • Click the toolbar icon: Configure Workload Management Services Icon • 2. In the Statement and Services Counter Details window, click the [Configure] button.
Understand the Platform Status Counters The Live View displays the current state of the system resource usage in the Platform Status Counters box near the bottom of the window: The Platform Status Counters box displays the percentage of node (CPU) busy, percentage of memory usage, total number of disk I/Os, and total number of cache hits in the system. The Live View updates this display of the system resource usage every 30 seconds by default.
2. To examine the tabular data, see these instructions: • “Understand the System Resource Values” (page 167) • “Copy the System Resource Data to a Spreadsheet” (page 167) 3. To examine the graphs, see these instructions: • “Enlarge a Graph” (page 168) • “Zoom In on an Area of the Graph” (page 168) • “Copy the Graph to the Clipboard” (page 169) • “Save the Graph to a File” (page 169) • “Print the Graph” (page 169) NOTE: You can also get a snapshot of the system resource usage by using a WMS command.
Enlarge a Graph To increase the size of one of the graphs: 1. 2. Place the cursor at a corner of the graph until the cursor becomes a double arrow ( ). Drag the corner of the graph until the graph expands to the desired size. For example: NOTE: The maximum size of the graph is limited to the size of the Neoview Platform Counter Details window. 3. To restore the graph to its original size, drag the corner of the graph until the graph shrinks to its original size in the window.
3. After you select the area, the Neoview Platform Counter Details window displays only that area of the graph, as this example shows: 4. To restore the graph to its original display, right-click the graph, and select Un-Zoom. Copy the Graph to the Clipboard To copy the graph to the clipboard: 1. 2. 3. Right-click the graph, and select Copy. Click [OK] when notified that the image was copied to the clipboard. Paste the graph from the clipboard into a document or presentation file of your choice.
A WMS Commands This appendix provides syntax, considerations, and examples for the WMS commands. For the WMS commands that are available only the HP Support, see the Neoview Database Support Guide. Administrators are permitted to run any WMS command. By default, administrators are users associated with ROLE.DBA, SUPER.SERVICES, and SUPER.SUPER.
Table A-1 WMS Commands (continued) To perform these tasks: See these WMS commands: To display the defined attributes of rules: • “STATUS RULE Command” (page 240) (or “INFO RULE Command” (page 214)) • “STATUS RULE CONN Command” (page 243) (or “INFO RULE CONN Command” (page 217)) • “STATUS RULE COMP Command” (page 242) (or “INFO RULE COMP Command” (page 216)) • “STATUS RULE EXEC Command” (page 244) (or “INFO RULE EXEC Command” (page 218)) To associate rules with a service: • “ALTER SERVICE COMP Command”
ADD ADMIN Command The ADD ADMIN command designates a Neoview role as an administrator in WMS. The ROLE.DBA, SUPER.SERVICES, and SUPER.SUPER roles are default administrators added during installation. NOTE: Only the default administrators, ROLE.DBA, SUPER.SERVICES, and SUPER.SUPER, are permitted to run this command. Syntax ADD ADMIN role-name role-name Specifies a string of 1 to 50 characters and may not contain multibyte characters. role-name must be a valid Neoview role.
ADD RULE COMP Command The ADD RULE COMP command defines a compilation rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ADD RULE COMP rule-specifier rule-specifier is: rule-name ( [ rule-specifier-operator, ] rule-specifier-list [, warn-level] [, rule-action ] ) [ rule-comment ] rule-specifier-operator is: { AND | OR } rule-specifier-list is: comp-expression [, comp-expression ] ...
rule-specifier-list specifies one or more comma-separated comp-expression. WMS tests each comp-expression in the rule-specifier-list in the order in which you specify them. A rule-specifier-list can contain a maximum of 15 comp-expression. comp-expression specifies one of these compilation rule expressions: EST_TOTAL_MEMORY rule-operator numeric-value An expression comparing the estimated memory for the query with a numeric value that you specify.
rule-action specifies an action to take if rule-specifier-list evaluates to true. If you specify a NO-WARN warn-level, you must specify a rule-action. For a compilation rule, you can specify one of these actions: REJECT | HOLD | { SQL_CMD "sql-string;" } REJECT causes the query to be rejected. HOLD causes the query to be put in the holding state. SQL_CMD "sql-string;" causes the NDCS server to execute an SQL command (or a series of SQL commands) before executing the query.
ADD RULE COMP COMPRULE5 (OR, EST_TOTAL_MEMORY > 320000, EST_CARDINALITY >= 1000000, EST_ACCESSED_ROWS > 20000, EST_USED_ROWS > 20000, SCAN_SIZE > 10000, CROSS_PRODUCT, NO-WARN, REJECT) COMMENT "Created by DBA on 5/14/2010"; • This command adds a compilation rule that sets the warning level to medium if statistics are missing for a single-column group: ADD RULE COMP COMPRULE6 (UPDATE_STATS_WARNING, WARN-MEDIUM) COMMENT "Created by DBA on 5/14/2010"; ADD RULE COMP Command 177
ADD RULE CONN Command The ADD RULE CONN command defines a connection rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ADD RULE CONN rule-specifier rule-specifier is: rule-name ( rule-specifier-list [, warn-level] [, rule-action ] [, aggregation] ) [ rule-comment ] rule-specifier-list is: conn-expression [, conn-expression ] ...
conn-expression specifies one of these connection rule expressions: SESSION { string-value | ICASE (string-value) } Specifies the session name for the connection rule. By default, the string-value is case-sensitive. The keyword ICASE causes the NDCS server to ignore the case during the comparison of the session name from the client and the connection rule. The maximum character length is 24. Put string-value within double quotes when it contains spaces. SESSION does not support multibyte characters.
AGGREGATE aggregate-parameter-list specifies that the NDCS server aggregates the query statistics of a repeating query ID before writing the statistics to the Repository. For more information, see “Query Statistics Aggregation” (page 27). If you specify more than one aggregate parameter, you must enclose the parameters in parentheses. An aggregate parameter can be one of these parameters: QUERY_TYPE query-type-list specifies the types of SQL queries that will have their statistics aggregated.
ADD RULE CONN CONNRULE1 (SESSION ICASE(JOB2468), LOGIN ICASE(ROLE.USR1)); • This command adds a connection rule that requires the client session to have a session name called JOB2486 and have the user logged in as ROLE.USR2: ADD RULE CONN CONNRULE2 (SESSION ICASE(JOB2468), LOGIN ICASE(ROLE.USR2)); • This command adds a connection rule that requires the client session to originate from the QtrReport.exe application, to have the user logged in as ROLE.
ADD RULE EXEC Command The ADD RULE EXEC command defines an execution rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ADD RULE EXEC rule-specifier rule-specifier is: rule-name ( [ rule-specifier-operator, ] rule-specifier-list [, warn-level] [, rule-action ] ) [ rule-comment ] rule-specifier-operator is: { AND | OR } rule-specifier-list is: exec-expression [, exec-expression ] ...
exec-expression specifies one of these execution rule expressions: USED_ROWS rule-operator EST_USED_ROWS | numeric-value An expression comparing the number of rows returned with either the estimated number of rows used by the query or a numeric value that you specify. ACCESSED_ROWS rule-operator EST_ACCESSED_ROWS | numeric-value An expression comparing the number of rows accessed with either the estimated number of rows accessed or a numeric value that you specify.
Considerations • • Use the ALTER SERVICE EXEC command to associate execution rules with a service. You cannot add a rule that begins with the reserved “HPS_” prefix or that has the same name as one of the predefined rules, HPS_MANAGEABILITY1 through 11 and HPS_TRANSPORTER1.
ADD SERVICE Command The ADD SERVICE command adds a service to the WMS configuration on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ADD SERVICE service-name [ service-parameter-list ] service-parameter-list is: service-parameter[, service-parameter]...
NOTE: The service priority is not a runtime or process priority. The service priority serves as a launch priority, determining when the queries in a service are launched from the waiting queue into execution relative to other services. To set the process priorities, please contact your HP Support representative for assistance. service-threshold specifies one of these limits for the service: MAX_CPU_BUSY percentage Specifies the maximum percentage of CPU busy for the service, averaged across all CPUs.
output-option specifies one of these options for storing information about the queries executing in the service: PLAN Collects the execution plans of queries executing in the service so that client tools can use and display the query plans. If not specified, the default is NO_PLAN, meaning that no plans are collected. Use the STATUS QUERY PLAN command to get the query execution plan of the specified query. NO_PLAN Does not collect the execution plans of queries executing in the service.
SERVICE2 has a medium priority, allows a maximum CPU busy of 100 percent and memory usage of 85 percent, and has an active period of 8:00 a.m. to midnight. It has timeout values for executing queries (four hours) and waiting queries (24 hours) but not holding queries.
ALTER RULE COMP Command The ALTER RULE COMP command alters the attributes of a compilation rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
comp-expression specifies one of these compilation rule expressions: EST_TOTAL_MEMORY rule-operator numeric-value An expression comparing the estimated memory for the query with a numeric value that you specify. The value of the second operand, numeric-value, is in kilobytes. EST_TOTAL_TIME rule-operator numeric-value An expression comparing the estimated time to execute the query with a numeric value that you specify. The value of the second operand, numeric-value, is in minutes.
HOLD causes the query to be put in the holding state. SQL_CMD "sql-string;" causes the NDCS server to execute an SQL command (or a series of SQL commands) before executing the query. “sql-string;” is an SQL command string, which is issued when the rule evaluates to true. You must enclose the SQL string in double quotes. The combined length for the command string must not exceed 1024 bytes. Separate each command with a semicolon (;). The last command must always end with a semicolon.
ALTER RULE CONN Command The ALTER RULE CONN command alters the attributes of a connection rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ALTER RULE CONN rule-specifier rule-specifier is: rule-name ( rule-specifier-list [, warn-level] [, rule-action ] [, aggregation] ) [ rule-comment ] rule-specifier-list is: conn-expression [, conn-expression ] ...
conn-expression specifies one of these connection rule expressions: SESSION { string-value | ICASE (string-value) } Specifies the session name for the connection rule. By default, the string-value is case-sensitive. The keyword ICASE causes the NDCS server to ignore the case during the comparison of the session name from the client and the connection rule. The maximum character length is 24. Put string-value within double quotes when it contains spaces. SESSION does not support multibyte characters.
AGGREGATE aggregate-parameter-list specifies that the NDCS server aggregates the query statistics of a repeating query ID before writing the statistics to the Repository. For more information, see “Query Statistics Aggregation” (page 27). If you specify more than one aggregate parameter, you must enclose the parameters in parentheses. An aggregate parameter can be one of these parameters: QUERY_TYPE query-type-list specifies the types of SQL queries that will have their statistics aggregated.
Examples • This command alters the connection rule, CONNRULE1, so that it requires the client session to have a user logged in as ROLE.USR3 instead of ROLE.USR1: ALTER RULE CONN CONNRULE1 (SESSION ICASE(JOB2468), LOGIN ICASE(ROLE.
ALTER RULE EXEC Command The ALTER RULE EXEC command alters the attributes of an execution rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
exec-expression specifies one of these execution rule expressions: USED_ROWS rule-operator EST_USED_ROWS | numeric-value An expression comparing the number of rows returned with either the estimated number of rows used by the query or a numeric value that you specify. ACCESSED_ROWS rule-operator EST_ACCESSED_ROWS | numeric-value An expression comparing the number of rows accessed with either the estimated number of rows accessed or a numeric value that you specify.
include dashes, periods, underscores, and spaces. You must delimit the comment in double quotes. Considerations CAUTION: When altering an attribute of an execution rule, you must also specify any existing attributes in the ALTER RULE EXEC command. Otherwise, the ALTER RULE EXEC command deletes the existing attributes.
ALTER SERVICE Command The ALTER SERVICE command modifies the attributes of a service in the WMS configuration on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ALTER SERVICE service-name [ service-parameter-list ] service-parameter-list is: service-parameter[, service-parameter]...
service-threshold specifies one of these limits for the service: MAX_CPU_BUSY percentage Specifies the maximum percentage of CPU busy for the service, averaged across all CPUs. When this maximum is reached, all new queries associated with the service go into the WAITING state. The range is 0 to 100 percent. Zero means that this threshold is not checked. If not specified, the service uses the value set for the WMS system configuration. By default, this value is 100%.
TEXT Collects the SQL text of queries executing in the service so that client tools can use and display the SQL text. If not specified, the default is NO_TEXT, meaning that no SQL text is collected. Use the STATUS QUERY TEXT command to get the SQL text of the specified query. NO_TEXT Does not collect the SQL text of queries executing in the service. The default is NO_TEXT, meaning that no SQL text is collected. comment is optional text that you can specify to describe the service that you are altering.
ALTER SERVICE COMP Command The ALTER SERVICE COMP command associates compilation rules with a service. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ALTER SERVICE service-name COMP rule-list rule-list is: ( [ rule-name [ , rule-name ... ] | RESET ] ) service-name is the name of an existing service for managing queries.
ALTER SERVICE EXEC Command The ALTER SERVICE EXEC command associates execution rules with a service. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ALTER SERVICE service-name EXEC rule-list rule-list is: ( [ rule-name [ , rule-name ... ] | RESET ] ) service-name is the name of an existing service for managing queries.
ALTER WMS Command The ALTER WMS command modifies the thresholds of the WMS system configuration on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax ALTER WMS wms-parameter-list wms-parameter-list is: wms-parameter [ , wms-parameter ] ...
STATS_INVERVAL stats-interval Specifies the frequency, in seconds, at which WMS collects runtime statistics for all executing queries being managed by WMS. Range is 5 through 300 seconds. If not specified, the interval is 5 seconds by default. RULE_INVERVAL rule-interval Specifies the frequency, in seconds, at which WMS evaluates the execution rules for a query. Range is 60 through 3600 seconds. If not specified, the interval is 60 seconds by default.
ALTER WMS CONN Command The ALTER WMS CONN associates connection rules with services. CAUTION: Each time you enter an ALTER WMS CONN command, you overwrite existing associations of user-defined connection rules and services. However, the associations of the predefined HPS rules and services are preserved.
ALTER WMS RESET STATS Command The ALTER WMS RESET STATS resets the query counters to zero. WMS starts accumulating new running counts and average times of the queries from the time of the reset. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
CANCEL Command The CANCEL command cancels a query or a set of queries being managed by WMS on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
DELETE ADMIN Command The DELETE ADMIN command removes a Neoview role from the list of administrators in WMS. NOTE: Only the default administrators, ROLE.DBA, SUPER.SERVICES, and SUPER.SUPER, are permitted to run this command. The default administrators are added during installation and cannot be deleted. Syntax DELETE ADMIN role-name role-name Specifies a string of 1 to 50 characters and may not contain multibyte characters. role-name must be a valid Neoview role.
DELETE RULE Command The DELETE RULE command removes a rule from the WMS configuration on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax DELETE RULE rule-name rule-name is the name of an existing, user-defined rule. Considerations • • When you delete a rule, all associations with services using the rule are deleted.
DELETE SERVICE Command The DELETE SERVICE command removes a service from the WMS configuration on the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax DELETE SERVICE service-name service-name is the name of an existing, user-defined service for managing queries. service-name must exist in the WMS configuration and cannot be the SYSTEM service.
HOLD Command The HOLD command places a waiting or executing query or set of queries being managed by WMS on hold. To cancel a query, see the “CANCEL Command” (page 208). NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
HOLD QUERY ALL; • This command holds queries in SERVICE1: HOLD QUERIES SERVICE SERVICE1; HOLD Command 213
INFO RULE Command The INFO RULE command returns information from the WMS metadata about a specific rule or all rules. The metadata is updated less often than shared memory in WMS. See the “STATUS RULE Command” (page 240), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
Table A-2 INFO RULE Output (continued) Column Name Data Type Description AGGR_WMS_INTERVAL INT For connection rules, this field contains the frequency, in minutes, at which the NDCS process updates the WMS process after aggregation has started. This field does not apply to compilation or execution rules and is therefore set to zero for those rules. AGGR_EXEC_INTERVAL INT For connection rules, this field is the expected execution time of the query, in minutes.
INFO RULE COMP Command The INFO RULE COMP command returns information from the WMS metadata about all the compilation rules. The metadata is updated less often than shared memory in WMS. See the “STATUS RULE COMP Command” (page 242), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
INFO RULE CONN Command The INFO RULE CONN command returns information from the WMS metadata about all the connection rules. The metadata is updated less often than shared memory in WMS. See the “STATUS RULE CONN Command” (page 243), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
INFO RULE EXEC Command The INFO RULE EXEC command returns information from the WMS metadata about all the execution rules. The metadata is updated less often than shared memory in WMS. See the “STATUS RULE EXEC Command” (page 244), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
INFO SERVICE Command The INFO SERVICE command returns information from the WMS metadata about the attributes of one or all services in WMS. The metadata is updated less often than shared memory in WMS. See the “STATUS SERVICE Command” (page 245), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
Table A-6 INFO SERVICE Output (continued) Column Name Data Type Description SQL_DEFAULTS VARCHAR(1024) SQL string for setting process priorities or defaults COMMENT VARCHAR(1024) Comment string Examples • This command displays information about SERVICE1: INFO SERVICE SERVICE1; • This command displays information about all the services in WMS: INFO SERVICE ALL; 220 WMS Commands
INFO SERVICE COMP Command The INFO SERVICE COMP command returns the names of the compilation rules associated with the specified service from the WMS metadata. The metadata is updated less often than shared memory in WMS. See the “STATUS SERVICE COMP Command” (page 247), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
INFO SERVICE EXEC Command The INFO SERVICE EXEC command returns the names of the execution rules associated with the specified service from the WMS metadata. The metadata is updated less often than shared memory in WMS. See the “STATUS SERVICE EXEC Command” (page 248), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
INFO WMS Command The INFO WMS command returns information from the WMS metadata about the WMS system configuration. The metadata is updated less often than shared memory in WMS. See the “STATUS WMS Command” (page 252), which returns information from shared memory. Syntax INFO WMS Considerations The INFO WMS command returns this information about the WMS system configuration: Table A-9 INFO WMS Output Column Name Data Type Description MAX_CPU_BUSY INT Maximum percentage of CPU busy allowed.
INFO WMS CONN Command The INFO WMS CONN command returns information from the WMS metadata about connection rules associated with services. The metadata is updated less often than shared memory in WMS. See the “STATUS WMS CONN Command” (page 254), which returns information from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
RELEASE Command The RELEASE command releases a query or a set of queries being managed by WMS from the holding state. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
START SERVICE Command The START SERVICE command starts a service and sets the state of the service to ACTIVE. Use this command after issuing a STOP SERVICE command. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax START SERVICE service-name service-name is the name of a user-defined service for managing queries. service-name can be a maximum of 24 characters and must be a unique name in WMS.
START WMS Command The START WMS command reactivates WMS after it has been stopped so that it will accept new queries in its services. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax START WMS Considerations • • Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
STATUS ADMIN Command The STATUS ADMIN command displays the Neoview roles that serve as administrators. Administrators are permitted to perform any WMS operation or command. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS ADMIN Considerations • • Any administrator is permitted to run this command.
STATUS QUERIES Command The STATUS QUERIES command returns information about the queries currently being managed by WMS. Syntax STATUS QUERIES [option] option is: | | | | | [ [ [ [ [ [ ALL [ STATS ] ] EXECUTING STATS ] query-state ] warn-level ] SERVICE service-name [ query-state ] ] ROLE name [ query-state ] ] query-state is: { EXECUTING | WAITING | HOLDING } warn-level is: { WARN | WARN-LOW | WARN-MEDIUM | WARN-HIGH } ALL returns status information about all the queries in WMS.
in the service name. For guidelines on using multibyte character sets, see the Neoview Character Sets Administrator's Guide. query-state is one of these query states: • EXECUTING • WAITING • HOLDING If you do not specify a query state, the command returns status information for all queries associated with the service. ROLE name [ query-state ] returns status information about the queries that are associated with a Neoview role or user name.
Table A-12 Query Compile-Time Statistics (continued) Column Name Data Type Description EST_MSG_TIME DOUBLE PRECISION Estimated number of seconds it takes for the messaging for the query. The estimate includes the time for the number of local and remote messages and the amount of data sent. EST_IDLE_TIME DOUBLE PRECISION Estimated number of seconds to wait for an event to happen. Events include opening a table or starting an executor server process (ESP).
Table A-12 Query Compile-Time Statistics (continued) Column Name Data Type Description QUERY_NAME VARCHAR(200) Application ID and computer name delimited by the || character ROLE_NAME VARCHAR(50) The client role name WARN_LEVEL VARCHAR(6) LOW, MEDIUM, HIGH, or blank when no warnings exist for the query. If more than one warning level exists for the query, the highest warning level appears here. START_TS VARCHAR(26) Local civil time when the query was registered in WMS. YYYY-DD-MM HH:MM:SS.
Table A-13 Query Warning Information (continued) Column Name Data Type Description WARN_LEVEL VARCHAR(6) LOW, MEDIUM, HIGH RULE_NAME VARCHAR(96) Name of the rule that triggered the warning EXPR INT Expression number in the rule, corresponding to the ordinal position of a rule expression that evaluated to true (if the rule was defined with an OR operator) or the total number of rule expressions (if the rule was defined with an AND operator) REASON VARCHAR(512) Rule expression that triggered the
Table A-14 Query Runtime Statistics (continued) Column Name Data Type Description EXEC_STATE VARCHAR(21) Internal execution state of the query: INITIAL OPEN EOF CLOSE DEALLOCATED FETCH CLOSE_TABLES PREPARE PROCESS_ENDED UNKNOWN For more information, please contact your HP Support representative. 234 ELAPSED_TIME LARGEINT Elapsed time of query execution, in microseconds. ACCESSED_ROWS LARGEINT Number of rows accessed from the tables referenced by the query.
Table A-14 Query Runtime Statistics (continued) Column Name Data Type Description STATS_ERROR_CODE INT Warning number returned to the statistics collector while it is obtaining statistics from runtime statistics. A warning of 8922 means that one or more CPUs did not report the statistics for a given query.
Table A-14 Query Runtime Statistics (continued) Column Name Data Type Description COMP_RULE_NAME VARCHAR(100) Displayed as rule-name:expr-num, where rule-name is the last compilation rule evaluated for the query and expr-num is either the ordinal position of the expression that evaluated to true or ALL for all expressions evaluating to true EXEC_RULE_NAME VARCHAR(100) Displayed as rule-name:expr-num, where rule-name is the last execution rule evaluated for the query and expr-num is either the ordinal
Examples • This command displays information for all the queries in WMS: STATUS QUERIES ALL; • This command displays runtime statistics information about all the queries in WMS: STATUS QUERIES ALL STATS; • This command displays status information for all the waiting queries in WMS: STATUS QUERIES WAITING; • This command displays the total number of executing queries in various states in WMS: STATUS QUERIES EXECUTING STATS; • This command displays warning information for queries at the high warning
STATUS QUERY Command The STATUS QUERY command returns information about the specified query that is currently being managed by WMS. Syntax STATUS QUERY { ALL STATS | query-id [ { STATS | PLAN | TEXT } ] } ALL STATS returns runtime statistics information for all the queries in WMS. For output information, see Table A-14 (page 233). query-id returns status information about the specified query. query-id is the query identifier and can be a maximum of 160 characters.
• This command displays the runtime statistics of the specified query: STATUS QUERY MXID01002031074212110209169193275000000000609ROLE.USR200_73_S1 STATS; • This command displays the query execution plan of the specified query: STATUS QUERY MXID01002031074212110209169193275000000000609ROLE.USR200_73_S1 PLAN; • This command displays the SQL text of the specified query: STATUS QUERY MXID01002031074212110209169193275000000000609ROLE.
STATUS RULE Command The STATUS RULE command returns information from shared memory about a specific rule or all rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS RULE [ { rule-name | ALL } ] rule-name returns status information about the specified rule. rule-name is the name of a user-defined rule. The rule name is not case-sensitive and is always converted to uppercase.
Table A-18 STATUS RULE Output (continued) Column Name Data Type Description AGGR_WMS_INTERVAL INT For connection rules, this field contains the frequency, in minutes, at which the NDCS process updates the WMS process after aggregation has started. This field does not apply to compilation or execution rules and is therefore set to zero for those rules. AGGR_EXEC_INTERVAL INT For connection rules, this field is the expected execution time of the query, in minutes.
STATUS RULE COMP Command The STATUS RULE COMP command returns information from shared memory about all the compilation rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
STATUS RULE CONN Command The STATUS RULE CONN command returns information from shared memory about all the connection rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
STATUS RULE EXEC Command The STATUS RULE EXEC command returns information from shared memory about all the execution rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
STATUS SERVICE Command The STATUS SERVICE command returns information from shared memory about the attributes of one or all services in WMS. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS SERVICE [ { service-name | ALL } ] service-name is the name of a user-defined service for managing queries. service-name can be a maximum of 24 characters and must be a unique name in WMS.
Table A-22 STATUS SERVICE Output (continued) Column Name Data Type Description SQL_PLAN VARCHAR(7) PLAN indicates that the query execution plans are collected for display. NO_PLAN indicates that no plans are collected for display. SQL_TEXT VARCHAR(7) TEXT indicates that the SQL text of executing queries is collected for display. NO_TEXT indicates that no SQL text is collected for display. EXEC_TIMEOUT INT Timeout value, in minutes, for executing queries in the service. Zero means no timeout.
STATUS SERVICE COMP Command The STATUS SERVICE COMP command returns the names of the compilation rules associated with the specified service from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS SERVICE service-name COMP service-name is the name of a user-defined service for managing queries.
STATUS SERVICE EXEC Command The STATUS SERVICE EXEC command returns the names of the execution rules associated with the specified service from shared memory. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS SERVICE service-name EXEC service-name is the name of a user-defined service for managing queries. service-name can be a maximum of 24 characters and must be a unique name in WMS.
STATUS SERVICE RULE Command The STATUS SERVICE RULE command displays the services that are associated with the specified rule. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STATUS SERVICE RULE rule-name rule-name is the name of a user-specified rule.
STATUS SERVICE SEGMENT STATS Command The STATUS SERVICE SEGMENT STATS command returns running counts and average times of the queries in one or all services on one or all segments of the Neoview platform. The command returns the total number of queries in various states (executing, waiting, holding, and suspended) and their average execution, waiting, holding, and suspended times. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.
Table A-26 STATUS SERVICE SEGMENT STATS Output (continued) • Column Name Data Type Description TOTAL_CANCEL INT Cumulative number of queries that were cancelled since the start of statistics collection TOTAL_COMPLETE INT Cumulative number of queries that completed since the start of statistics collection AVG_EXEC_SECS INT Average execution time in seconds AVG_WAIT_SECS INT Average waiting time in seconds AVG_HOLD_SECS INT Average holding time in seconds AVG_SUSPEND_SECS INT Average susp
STATUS WMS Command The STATUS WMS command returns information from shared memory about the WMS system configuration and the status of the system resources. Syntax STATUS WMS Considerations The STATUS WMS command returns these configuration and status values: Table A-27 STATUS WMS Output 252 Column Name Data Type Description STATE VARCHAR(9) State of WMS: • INIT (for initialization) • ACTIVE • HOLD • STOPPED • SHUTDOWN MAX_CPU_BUSY INT Maximum percentage of CPU busy allowed.
Example This command displays status information about WMS: STATUS WMS; STATUS WMS Command 253
STATUS WMS CONN Command The STATUS WMS CONN command returns information from shared memory about connection rules associated with services. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
STATUS WMS DETAIL Command The STATUS WMS DETAIL command displays information about CPU and memory consumption per segment and per node.
STATUS WMS SEGMENT STATS Command The STATUS WMS SEGMENT STATS command returns running counts and average times of the queries on one or all segments of the Neoview platform. The command returns the total number of queries in various states (executing, waiting, holding, and suspended) and their average execution, waiting, holding, and suspended times. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER.
Examples • This command displays query counts and average times for all segments on the Neoview platform: STATUS WMS STATS; • This command displays query counts and average times for segment 1 on the Neoview platform: STATUS WMS SEGMENT 1 STATS; STATUS WMS SEGMENT STATS Command 257
STATUS WMS VERSION Command The STATUS WMS VERSION command returns version information for the internal WMS processes.
STOP SERVICE Command The STOP SERVICE command stops a service. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STOP SERVICE service-name [ IMMEDIATE ] service-name service-name is the name of a user-defined service for managing queries. service-name must exist in the WMS configuration and cannot be the SYSTEM service.
STOP WMS Command The STOP WMS command stops WMS and all its services and rejects all waiting and incoming queries in WMS. NOTE: Only administrators are permitted to run this command. By default, an administrator is a user associated with ROLE.DBA, SUPER.SERVICES, or SUPER.SUPER. Syntax STOP WMS [ IMMEDIATE ] IMMEDIATE causes all waiting queries to be rejected and all executing queries to be cancelled. The state of WMS changes to STOPPING.
B Query Types Managed by WMS This table lists the types of queries that run on the Neoview platform and whether they are managed by WMS. The STATEMENT_TYPE field, which contains a compiler-provided value, appears in the output of the STATUS QUERIES command with the ALL STATS option.
C Query States and Substates This table describes the states and substates that a query might have at different times while being managed by WMS. Table C-1 Query States and Substates Query State Substate Description WAITING WMS_MAX_CPU_BUSY The query has been put in the waiting queue because the CPU busy exceeds the MAX_CPU_BUSY value. WMS_MAX_MEM_USAGE The query has been put in the waiting queue because the memory usage exceeds the MAX_MEM_USAGE value.
Table C-1 Query States and Substates (continued) Query State Substate Description REJECTED WMS_ON_HOLD The waiting or holding query is rejected before executing because the WMS system has been put on hold by an administrator. WMS_MAXIMUM_QUERIES_REACHED The waiting or holding query is rejected before executing because the maximum number of executing and waiting queries on the segment has been reached.
Table C-1 Query States and Substates (continued) Query State Substate COMPLETED when NDCS_DIALOGUE_INIT query statistics aggregation is enabled Description Query statistics aggregation finished, and the NDCS Dialogue Initialization function was called in the connection process. NDCS_CONNECTION_IDLE Query statistics aggregation finished because the Connection Idle timeout expired and NDCS is in the process of disconnecting.
Index A ACCESSED_ROWS, syntax of, 183, 197 Active period, setting start and end times, 49 ACTIVE threshold, syntax of, 186, 200 ACTIVE_TIME description of, 219, 245 setting the value, 49 ADD ADMIN command, 173 ADD RULE COMP command, 174 ADD RULE CONN command, 178 ADD RULE EXEC command, 182 ADD SERVICE command, 185 Admin role (see Administrator role) Administrator role adding, 40 deleting, 42 description of, 39 displaying, 39 Aggregation, query statistics, 27 ALTER RULE COMP command, 189 ALTER RULE CONN comm
specifier operator, 182, 196 start interval, 52 warning level, 183, 197 Explain plan, 134 Expressions, compilation rule CROSS_PRODUCT, 175, 190 EST_ACCESSED_ROWS, 175, 190 EST_CARDINALITY, 175, 190 EST_TOTAL_MEMORY, 175, 190 EST_TOTAL_TIME, 175, 190 EST_USED_ROWS, 175, 190 NUM_JOINS, 175, 190 SCAN_SIZE, 175, 190 UPDATE_STATS_WARNING, 175, 190 Expressions, connection rule APPL, 179, 193 DSN, 179, 193 LOGIN, 179, 193 SESSION, 179, 193 Expressions, execution rule ACCESSED_ROWS, 183, 197 CPU_TIME, 183, 197 ELAP
cancelling, 145 compile-time statistics, 135 execution plan, 134 explain plan, 134 handling incoming, 24 holding all queries in a service, 151 all queries in all services, 153 specific query, 142 managing, 142 miscellaneous facts, 131 monitoring, 126 per-table statistics, 137 rate-based metrics, 131 releasing all sets of queries, 156 specific query, 144 specific set of queries, 154 rule warnings, 133 runtime statistics, 136 SQL text, 133 time-based metrics, 130 types managed by WMS, 261 Query plan, 134 Quer
U UPDATE_STATS_WARNING, syntax of, 175, 190 USED_ROWS, syntax of, 183, 197 W WAIT_TIMEOUT, syntax of service, 186, 200 WMS system, 204 Warnings, rules, 133 WMS configuring, 47 description of, 21 how to use, 30 installing, 35 monitoring, 121 stopping, 163 WMS commands list of, 171 using in NCI, 38 WMS configuration, export and save, 65 WMS mode, 38 Workload types, 43 Workload, definition of, 21 270 Index
