HP Neoview Workload Management Services Guide HP Part Number: 546151-001 Published: May 2009 Edition: HP Neoview Release 2.
© Copyright 2009 Hewlett-Packard Development Company, L.P. Legal Notice Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor’s standard commercial license. The information contained herein is subject to change without notice.
Table of Contents About This Document.......................................................................................................11 Intended Audience................................................................................................................................11 New and Changed Information in This Edition...................................................................................11 Document Organization.......................................................................
Altering a Service.............................................................................................................................35 How Altering a Service Affects Queries....................................................................................35 Deleting a Service............................................................................................................................36 Displaying Information About the Service Definitions............................................
Deleting an Execution Rule.............................................................................................................64 Associating Execution Rules With a Service...................................................................................64 Displaying the Execution Rules Associated With a Service............................................................64 Displaying the Services Associated With an Execution Rule..........................................................
Examples..........................................................................................................................................98 ALTER RULE EXEC Command............................................................................................................99 Syntax..............................................................................................................................................99 Examples......................................................................
Examples........................................................................................................................................118 INFO SERVICE Command.................................................................................................................119 Syntax.............................................................................................................................................119 Considerations..............................................................
STATUS SERVICE COMP Command.................................................................................................144 Syntax.............................................................................................................................................144 Considerations...............................................................................................................................144 Examples................................................................................
List of Figures 1-1 1-2 1-3 1-4 1-5 1-6 4-1 4-2 4-3 4-4 4-5 4-6 How a Connection Rule Is Applied..............................................................................................18 How the NDCS Server Assigns a Client Session to a Service.......................................................19 How Compilation Rules Are Applied...........................................................................................20 How WMS Handles an Incoming Query...........................................
List of Tables 3-1 3-2 4-1 4-2 4-3 5-1 5-2 5-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 B-1 B-2 B-3 10 WMS System Thresholds...............................................................................................................28 How an Altered Service Attribute Affects Queries Associated With the Service.........................
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 workload on a Neoview data warehousing platform.
Computer Type Computer type letters within text indicate case-sensitive keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.sh Bold Text Bold text in an example indicates user input typed at the terminal. For example: ENTER RUN CODE ?123 CODE RECEIVED: 123.00 The user must press the Return key after typing the input. [ ] Brackets Brackets enclose optional syntax items.
expression-n… Punctuation Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: DAY (datetime-expression) @script-file Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "{" module-name [, module-name]... "}" Item Spacing Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma.
Neoview Transporter User Guide Information about processes and commands for loading data into your Neoview platform or extracting data from it. README files for installing Administration products • — README for the HP Neoview Transporter Java Client Management HP Database Manager (HPDM) Help topics that describe how to use the HP Database Manager Client to Online Help connect and manage a Neoview data warehousing platform.
• ODBC Client Administrator Online Help Context-sensitive help topics that describe how to use the ODBC Data Source Administrator. README files for installing Connectivity products — README for the HP Neoview JDBC Type 4 Driver — README for the HP Neoview ODBC Driver for Windows — README for the HP Neoview ODBC Drivers for UNIX Reference Mapping Tables for Neoview Character Sets A hyperlinked collection of East Asian characters supported by Neoview character set functionality.
1 Introduction The Neoview Workload Management Services (WMS) feature provides the infrastructure to help you manage system resources in a mixed workload environment of a Neoview platform. Using WMS, you can influence when queries run and how many system resources they are allowed to consume by assigning groups of queries (that is, query workloads) to services. You can create your own services in WMS and configure them to have a relative priority and a set of thresholds.
Figure 1-1 shows the sequence of events (designated by the numbers in the diagram) that occur when a connection rule is applied during the connection phase. Figure 1-1 How a Connection Rule Is Applied Neoview Data Warehousing Platform 4 1 Client Application Requesting a Connection NDCS Server 5 Neoview Database 2 3 Neoview WMS Management Tool for Monitoring WMS Runtime Statistics (RMS) vst111.vsd 1. 2. 3. 4. 5.
Figure 1-2 How the NDCS Server Assigns a Client Session to a Service Incoming client session The NDCS server gets the connection attributes of the session. Do the connection attributes match the connection rule of a service? Yes Is WARN-LOW defined for the connection rule? No Are there user-defined connection rules in WMS? Yes No No Yes WMS sets the warning level to low and saves warning information. WMS does not set the warning level or save warning information.
Figure 1-3 How Compilation Rules Are Applied Neoview Data Warehousing Platform 1 NDCS Server Client Application Issuing a Query Neoview Database 2 3 Neoview WMS Management Tool for Monitoring WMS Runtime Statistics (RMS) vst112.vsd 1. 2. 3. The client application submits a query to the NDCS server. The NDCS server sends compiler estimates of the query requesting permission to execute to WMS.
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 executing on this segment < max allowed? No Is the compilation rule action = REJECT? 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? Yes WMS sets the warning level and saves warning information.
the WAITING state. If a query is in the HOLDING state, you must manually release the query to allow it to start executing. For information on compilation rules, see “Compilation Rules” (page 51). When the query starts executing, WMS proceeds with the “Execution Phase” (page 22).
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 stops the NDCS server, and the JDBC or ODBC driver returns an error message to the client application.
Setting Up Services, Thresholds, and Rules in WMS Using WMS commands, you can configure services and rules in WMS for queries that run on the Neoview platform.
2 Getting Started • • “Installing and Starting WMS on the Neoview Platform” (page 25) “Installing the Neoview Command Interface (NCI)” (page 25) Installing and Starting WMS on the Neoview Platform If you have Neoview Release 2.4, WMS should already be installed and started on your Neoview platform. If you are an HP support person, see the Neoview Database Support Guide for information about installing and starting WMS on a Neoview platform.
3 Configuring Services • • • “What Are Services?” (page 27) “Configuring the WMS System Configuration” (page 28) “Configuring User-Defined Services” (page 29) What Are Services? Services are a mechanism in WMS to enable you to prioritize query workloads on a Neoview platform and distribute available workload resources.
The next section explains how WMS determines which waiting queries are allowed to execute when system resources become available. How WMS Determines When to Execute Waiting Queries When the system resource usage is at its limits, executing queries continue to execute but incoming queries go into the waiting queue. As queries in each service finish executing, system resources free up, and WMS starts allowing some of the waiting queries to start executing.
SQL>mode ns NS%alter wms max_cpu_busy 95, max_mem_usage 80, max_rows_fetched 50; --- WMS operation complete. NS% For the syntax, see the “ALTER WMS Command” (page 106). Also see “How to Execute WMS Commands in NCI” (page 25).
• • • • • • • • “Using Default Threshold Values in a Service” (page 30) “Setting the Priority of the Service” (page 31) “Setting the System Resource Thresholds of the Service” (page 31) “Setting the Active Time of the Service” (page 32) “Setting Query Timeouts for the Service” (page 33) “Setting the Maximum Rows to Retrieve” (page 33) “Setting the Output Options of the Service” (page 34) “Adding a Comment for the Service” (page 34) For the syntax, see the “ADD SERVICE Command” (page 91).
Setting the Priority of the Service You can set the priority of the service relative to other services.
WAIT_TIMEOUT 720, HOLD_TIMEOUT 1440, PLAN, TEXT, COMMENT "Created by DBA on 5/14/2009"; ADD SERVICE service2 PRIORITY MEDIUM, MAX_CPU_BUSY 75, MAX_MEM_USAGE 60, EXEC_TIMEOUT 240, WAIT_TIMEOUT 1400, PLAN, TEXT, COMMENT "Created by DBA on 5/14/2009"; ADD SERVICE service3 PRIORITY LOW, MAX_CPU_BUSY 55, MAX_MEM_USAGE 40, MAX_ROWS_FETCHED 2000, PLAN, TEXT, COMMENT "Created by DBA on 5/14/2009"; Setting lower system resource thresholds for lower priority services restricts the number of executing queries associa
PLAN, TEXT, COMMENT "Created by DBA on 5/14/2009"; ADD SERVICE service4 PRIORITY HIGH, MAX_CPU_BUSY 95, MAX_MEM_USAGE 80, ACTIVE 00:00 TO 7:59, EXEC_TIMEOUT 120, WAIT_TIMEOUT 720, HOLD_TIMEOUT 1440, PLAN, TEXT, COMMENT "Created by DBA on 5/14/2009"; Setting Query Timeouts for the Service You can set these timeout values for a service: • EXEC_TIMEOUT, a value from zero to 1440 minutes, which causes WMS to cancel executing queries associated with the service when the timeout value is reached.
For example, this ADD SERVICE command defines a service that sets the maximum number of rows to 2000: ADD SERVICE service3 PRIORITY LOW, MAX_CPU_BUSY 55, MAX_MEM_USAGE 40, MAX_ROWS_FETCHED 2000, PLAN, TEXT, COMMENT "Created by DBA on 5/14/09"; If you set a MAX_ROWS_FETCHED limit for the service, the NDCS server considers this limit when fetching rows for queries associated with the service.
Altering a Service Use the ALTER SERVICE command to alter these settings of a service: • Priority • System resource thresholds • Active time of the service • Query timeouts • Maximum rows to retrieve • Output options for the query plan and SQL text • Comment This ALTER SERVICE command changes the priority and active time of SERVICE13 in the WMS configuration: SQL>mode ns NS%alter service service13 priority medium-high, active 08:00 to 13:00; --- WMS operation complete.
Table 3-2 How an Altered Service Attribute Affects Queries Associated With the Service (continued) Do executing queries in the service use the Altered Service Attribute new attribute? Do waiting queries in Do holding queries in the service use the the service use the new attribute? new attribute? Do incoming queries in the service use the new attribute? EXEC_TIMEOUT No, not if they are already executing Yes, when they start Yes, when they are Yes, when they start executing released back into the execut
----------------------- --------- ---------------- ------------ ------------- -------------------------------------- -------- -------- ------------ ------------ -----------HPS_MANAGEABILITY ACTIVE HIGH 0 0 0 FROM 00:00 TO 24:00 NO_PLAN NO_TEXT 0 0 0 SERVICE1 ACTIVE HIGH 95 80 0 FROM 08:00 TO 24:00 PLAN TEXT 120 720 1440 SERVICE4 HOLD HIGH 95 80 0 FROM 00:00 TO 07:59 PLAN TEXT 120 720 1440 HPS_TRANSPORTER ACTIVE MEDIUM 0 0 0 FROM 00:00 TO 24:00 NO_PLAN NO_TEXT 0 0 0 SERVICE2 ACTIVE MEDIUM 75 60 0 FROM 00:00
4 Configuring Rules • • • • • • “What Are Rules?” (page 39) “Global Rule Considerations” (page 39) “Connection Rules” (page 40) “Compilation Rules” (page 51) “Execution Rules” (page 59) “Displaying the Definitions of All Rules” (page 66) What Are Rules? Rules allow you, the administrator, to specify conditions for WMS to evaluate and trigger actions during certain phases of the client session and query execution: • When a client application connects to an NDCS server, the NDCS server compares the connecti
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 4-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 of more than one connection rule defined in WMS. However, 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 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. Connection Attributes in a Client Application Table 4-1 shows the methods for setting connection attributes in a client application. Table 4-1 Methods for Setting Connection Attributes in a Client Application Connection Attribute JDBC ODBC .
ADD RULE CONN CONNRULE21 (APPL NCI, LOGIN ROLE.USR8, WARN-LOW); When you specify WARN-LOW, WMS sets the warning level to low and saves warning information in its shared memory when the connection rule evaluates to true.
For the syntax, see the “STATUS RULE Command” (page 138). Also see “How to Execute WMS Commands in NCI” (page 25).
CONN CONNRULE22 AND 1 APPL = NCI TRANSACTION ISOLATION LEVEL SERIALIZABLE; CONN CONNRULE22 AND 2 DSN ICASE (DSN1) TRANSACTION ISOLATION LEVEL SERIALIZABLE; CONN CONNRULE23 AND 1 APPL = NCI LOW SET SCHEMA SALES; SET LOW SET SCHEMA SALES; SET NO-WARN --- WMS operation complete. NS% For the syntax, see the “STATUS RULE CONN Command” (page 140). Also see “How to Execute WMS Commands in NCI” (page 25).
Associating a Connection Rule With a Service Use the ALTER WMS CONN command to associate a connection rule with a service. You can associate more than one connection rule with a service, but you cannot associate the same connection rule with more than one service. The NDCS server evaluates each connection rule in the order in which you have listed the connection rule and service associations in the ALTER WMS CONN command.
CONN HPS_MANAGEABILITY3 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY4 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY5 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY6 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY7 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY8 HPS_MANAGEABILITY CONN HPS_MANAGEABILITY9 HPS_MANAGEABILITY --- WMS operation complete. %NS 3. Run this LOG command to quit logging the session: NS%log off %NS 4. 5. Open the text file on your workstation.
HPS_MANAGEABILITY4 HPS_MANAGEABILITY5 HPS_MANAGEABILITY6 HPS_MANAGEABILITY7 HPS_MANAGEABILITY8 HPS_MANAGEABILITY9 (CONNRULE1, CONNRULE2) HPS_MANAGEABILITY, HPS_MANAGEABILITY, HPS_MANAGEABILITY, HPS_MANAGEABILITY, HPS_MANAGEABILITY, HPS_MANAGEABILITY, SERVICE1 ); 8. In an NCI session, run the @ or OBEY command in NS mode to run the ALTER WMS CONN command in the text file: SQL>mode ns NS%@c:\alterwmsconn.
Displaying the Service Associated With a Connection Rule Use the STATUS SERVICE RULE command to display the name of the service that is associated with a specified connection rule. In this example, the STATUS SERVICE RULE command displays the name of the service that is associated with CONNRULE1: NS%status service rule connrule1; TYPE RULE_NAME SERVICE_NAME ---- -------------------------- ------------------CONN CONNRULE1 SERVICE1 --- WMS operation complete.
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 4-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.
• • “Adding a Compilation Rule” (page 53) “Associating Compilation Rules With a Service” (page 57) Adding a Compilation Rule Use the ADD RULE COMP command to define a compilation rule in WMS. For example, this ADD RULE COMP command defines the compilation rule, COMPRULE1, setting the warning level to high and the rule action to REJECT if estimated rows used is greater than 50,000: NS%ADD RULE COMP COMPRULE1 (EST_USED_ROWS > 50000, WARN-HIGH, REJECT); --- WMS operation complete.
ADD RULE COMP COMPRULE4 (AND, EST_TOTAL_TIME >= 10, EST_TOTAL_TIME <= 20, NUM_JOINS > 4, WARN-HIGH, HOLD) COMMENT "Created by DBA on 5/14/2009"; In this example, all three compilation rule expressions must evaluate to true for the compilation rule, COMPRULE4, to be considered true. For the syntax of the compilation rule expressions, see the “ADD RULE COMP Command” (page 83). For more information about the AND operator, see “Specifying the AND or OR Operator” (page 54).
• • • • WARN-HIGH WARN-MEDIUM WARN-LOW NO-WARN For example, this ADD RULE COMP command defines a compilation rule that sets the warning level to medium when the compilation rule evaluates to true: ADD RULE COMP COMPRULE2 (EST_USED_ROWS > 25000, WARN-MEDIUM, HOLD); If you specify NO-WARN, WMS does not set a warning level or save warning information for the query when the compilation rule evaluates to true.
For a compilation rule that specifies a warning level, if you do not specify an action and the compilation rule evaluates to true, WMS saves warning information and then determines if there are enough resources to execute the query. Adding a Comment for the Compilation Rule You can optionally provide a comment to describe the rule that you are creating. You must delimit the comment in double quotes.
NS% CAUTION: When altering an attribute of the compilation rule, you must also specify any existing expressions in the ALTER RULE COMP command. Otherwise, the ALTER RULE COMP command deletes the existing expressions. For the syntax, see the “ALTER RULE COMP Command” (page 94). Also see “How to Execute WMS Commands in NCI” (page 25). Deleting a Compilation Rule Use the DELETE RULE command to delete a rule. After you delete a rule, all associations with services using the rule are deleted.
Displaying the Services Associated With a Compilation Rule Use the STATUS SERVICE RULE command to display the names of services that are associated with the specified compilation rule.
Execution Rules Execution rules, which are applied after a query has been executing for at least five minutes, determine whether to permit the query to continue executing based on the query runtime statistics.
Figure 4-5 Evaluating the First Execution Rule Associated With the Service 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.
Adding an Execution Rule Use the ADD RULE EXEC command to define an execution rule in WMS. For example, this ADD RULE EXEC command defines the execution rule, EXECRULE4, setting the rule action to CANCEL if the number of rows accessed is greater than 500 and the elapsed time is greater than 15 minutes: NS%ADD RULE EXEC EXECRULE4 (AND, ACCESSED_ROWS > 500, ELAPSED_TIME > 15, NO-WARN, CANCEL); --- WMS operation complete.
sets the warning level to high and cancels the query if the USED_ROWS expression, highlighted in boldface, evaluates to true: ADD RULE EXEC EXECRULE1 (USED_ROWS >= 10000, WARN-HIGH, CANCEL); The USED_ROWS expression evaluates to true when the number of rows returned to the query is greater than or equal to 10,000.
ALL EXPRESSIONS = TRUE If the operator is OR or if there is only one rule expression, running a STATUS QUERIES WARN command displays the rule expression as the reason for the warning. For more information, see “Displaying Warning Information” (page 75). If you do not specify a warning level, the default is WARN-LOW for the execution rule. Specifying an Action for the Execution Rule You can specify an action to take if the execution rule evaluates to true.
--- WMS operation complete. NS% For the syntax, see the “STATUS RULE EXEC Command” (page 141). Also see “How to Execute WMS Commands in NCI” (page 25). Altering an Execution Rule IMPORTANT: To remove an action from an execution rule, you must delete and then add the execution rule instead of using the ALTER RULE EXEC command. For more information, see “Deleting an Execution Rule” (page 64) and “Adding an Execution Rule” (page 61).
This STATUS SERVICE EXEC command displays the names of the execution rules that are associated with SERVICE1: NS%status service service1 exec; TYPE ---EXEC EXEC EXEC RULE_NAME -----------------------------------EXECRULE1 EXECRULE2 EXECRULE3 SERVICE_NAME -------------------SERVICE1 SERVICE1 SERVICE1 --- WMS operation complete. NS% For the syntax, see the “STATUS SERVICE EXEC Command” (page 145).
Displaying the Definitions of All Rules Use the STATUS RULE ALL command to display the definitions of all rules in WMS.
CONN HPS_MANAGEABILITY9 AND CONN HPS_MANAGEABILITY9 AND CONN HPS_TRANSPORTER1 AND 2 SESSION ICASE (MANAGEABILITY) Created by 3 LOGIN ICASE (ROLE.DBA) Created by 1 APPL ICASE (TRANSPORTER) Created by 1 EST_USED_ROWS >= 50000 1 SESSION = JOB2468 2 LOGIN = ROLE.USR1 1 SESSION = JOB2468 2 LOGIN = ROLE.USR2 1 SESSION = JOB2510 2 APPL = QtrReport.exe 3 LOGIN = ROLE.USR3 4 DSN ICASE (DSN1) 1 APPL = Quarterly Report 2 LOGIN = ROLE.USR4 3 DSN ICASE (DSN1) 1 APPL = NCI 2 LOGIN = ROLE.
5 Managing Workload • • • • • “Monitoring System Resources” (page 69) “Monitoring Queries” (page 69) “Displaying Warning Information” (page 75) “Managing Services” (page 76) “Managing Queries” (page 78) Monitoring System Resources Use the STATUS WMS command to display information about the state of WMS and the system resources: SQL>mode ns NS%status wms; STATE MAX_CPU_BUSY MAX_MEM_USAGE MAX_ROWS_FETCHED RULE_INTERVAL DISK_IO DISK_CACHE EXEC_TIMEOUT WAIT_TIMEOUT HOLD_TIMEOUT STATS_INTERVAL CPU_BUSY MEM_US
0 ALL 219 SERVICE2 0 0 ALL 0 0 2009-04-12 10:36:20.838495 0 0 0 0 0 0 0 2009-04-12 10:36:20.838495 0 0 0 6 0 0 0 6 0 2009-04-12 10:36:20.838495 0 0 0 0 0 0 0 0 0 2009-04-12 10:36:20.838495 0 0 0 0 0 0 0 0 0 2009-04-12 10:36:20.838495 0 0 0 0 0 0 0 0 0 2009-04-12 10:36:20.838495 0 0 0 0 0 0 0 0 0 2009-04-12 10:36:20.838495 0 0 0 SERVICE1 6 0 ALL 126 SERVICE25 0 0 ALL 0 SERVICE13 0 0 ALL 0 SERVICE3 0 0 ALL 0 SERVICE4 0 0 0 --- WMS operation complete.
For the syntax, see the “ALTER WMS RESET STATS Command” (page 109). Displaying the Total Number of Executing Queries Use the STATUS QUERIES EXECUTING STATS command to display the total number of executing queries in various states on each segment of the Neoview platform.
ROLE_NAME WARN_LEVEL START_TS ENTRY_TS ENTRY_JTS LAST_UPDATED -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ----------------------------------------------- ------------------------ -------
MXID01002021092212106317746987210000000649109ROLE.USR200_19506_S1 EXECUTING S1 SELECT_NON_UNIQUE 212110212973907100 212110212974336408 212110213756193494 -1 FETCH 36975589 1407 1407 84 2464608 720 0 0 0 7648 4 7862 0 -1 0 11780 11552 16595 16429 178978 384 318 582 6 27981 1876.0 1876.0 8 106636 212110188631210751 MXID01002031074212110209169193275000000000609ROLE.
227993 384 1876.0 318 582 8 6 89344 27981 212110188706360365 1876.0 --- WMS operation complete. NS% For the syntax, see the “STATUS QUERY Command” (page 136). Each of the commands mentioned above displays the same columns of data in its output. For more information, see Table A-13 (page 132). NOTE: By default, WMS collects runtime statistics for all executing queries at an interval of five seconds. For information on how to change the default, see “ALTER WMS Command” (page 106).
SQL>mode ns NS%status query MXID01002020782212110119447606071000000015509ROLE.USR200_498_S1 text; QUERY_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------select * from neo.sales.customers a , neo.sales.orders b , neo.sales.odetail c , neo.sales.parts d --- WMS operation complete. NS% For the syntax, see the “STATUS QUERY Command” (page 136).
COMP SERVICE1 LOW COMPRULE3 1 EST_USED_ROWS > 1000 MXID01002010657212109516853678165000000115709ROLE.USR100_3616_S1 SERVICE1 EXEC MEDIUM EXECRULE2 1 USED_ROWS >= 1000 MXID01003031050212108224567420861000000331309ROLE.USR100_9972_S1 SERVICE1 COMP LOW COMPRULE3 1 EST_USED_ROWS > 1000 MXID01003020793212110131970645532000000013409ROLE.USR100_435_S1 SERVICE1 COMP LOW COMPRULE3 1 EST_USED_ROWS > 1000 MXID01003020793212110131970645532000000013409ROLE.
NS%release service service1; --- WMS operation complete. NS% For the syntax, see the “RELEASE Command” (page 125). This RELEASE SERVICE ALL command releases all holding queries in all services: NS%release service all; --- WMS operation complete. NS% For the syntax, see the “RELEASE Command” (page 125). Stopping a Service To stop a service in the ACTIVE or HOLD state and put the service in the STOPPED state, use the STOP SERVICE command.
Managing Queries You can manage queries in WMS by using the HOLD, RELEASE, and CANCEL commands: • • • “Holding a Query” (page 78) “Releasing a Query” (page 78) “Cancelling a Query” (page 79) Holding a Query Use the HOLD command to put an individual query, all queries, or all queries for a given service on hold.
Table 5-2 Effect of the RELEASE Command WMS Command 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. If system resources are available, the query enters the EXECUTING state. RELEASE when QUERY_STATE = HOLDING and QUERY_SUBSTATE = SUSPENDED_BY_ADMIN The query starts executing.
A WMS Commands NOTE: Administrators are permitted to run any WMS command. By default, administrators include ROLE.DBA and the super IDs, super.services and super.super.
Table A-1 WMS Commands (continued) To perform these tasks: See these WMS commands: To display the associations of rules with services: • “STATUS SERVICE COMP Command” (page 144) (or “INFO SERVICE COMP Command” (page 121)) • “STATUS SERVICE EXEC Command” (page 145) (or “INFO SERVICE EXEC Command” (page 122)) • “STATUS SERVICE RULE Command” (page 146) • “STATUS WMS CONN Command” (page 150) (or “INFO WMS CONN Command” (page 124)) Chapter 5: Managing Workload To display aggregated statistics about the queri
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 ROLE.DBA or the super ID (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 total memory per CPU used by 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 | { EXEC "sql-string;" } REJECT causes the query to be rejected. HOLD causes the query to be put in the holding state. EXEC "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.
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ADD RULE CONN rule-specifier rule-specifier is: rule-name ( rule-specifier-list [, warn-level] [, rule-action ]) [ rule-comment ] rule-specifier-list is: conn-expression [, conn-expression ] ...
APPL { string-value | ICASE (string-value) } Specifies the application name for the connection rule. The keyword ICASE causes the NDCS server to ignore the case during the comparison of the application name from the client and the connection rule. The maximum character length is 30. Put string-value within double quotes when it contains spaces. APPL supports multibyte characters. DSN { string-value | ICASE (string-value) } Specifies the client data source name for the connection rule.
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 ROLE.DBA or the super ID (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.
Examples • These commands define several execution rules: ADD RULE EXEC EXECRULE1 (USED_ROWS >= 10000, WARN-HIGH); ADD RULE EXEC EXECRULE2 (USED_ROWS >= 1000, WARN-MEDIUM); ADD RULE EXEC EXECRULE3 (USED_ROWS > EST_USED_ROWS, WARN-LOW); 90 WMS Commands
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ADD 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, that can be reached before WMS stops allowing queries associated with the service to execute. 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 this threshold is not checked.
TEXT Collects the SQL syntax of queries executing in the service so that client tools can use and display the SQL syntax. 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 syntax of the specified query. NO_TEXT Does not collect the SQL syntax of queries executing in the service. The default is NO_TEXT, meaning that no SQL text is collected.
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 ROLE.DBA or the super ID (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 total memory per CPU used by 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. EXEC "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. 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. The string can contain multibyte characters.
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ALTER RULE CONN rule-specifier rule-specifier is: rule-name ( rule-specifier-list [, warn-level] [, rule-action ]) [ rule-comment ] rule-specifier-list is: conn-expression [, conn-expression ] ...
APPL { string-value | ICASE (string-value) } Specifies the application name for the connection rule. The keyword ICASE causes the NDCS server to ignore the case during the comparison of the application name from the client and the connection rule. The maximum character length is 30. Put string-value within double quotes when it contains spaces. APPL supports multibyte characters. DSN { string-value | ICASE (string-value) } Specifies the client data source name for the connection rule.
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 ROLE.DBA or the super ID (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.
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 ROLE.DBA or the super ID (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, that can be reached before WMS stops allowing queries associated with the service to execute. 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 this threshold is not checked.
TEXT Collects the SQL syntax of queries executing in the service so that client tools can use and display the SQL syntax. If not specified, the default is NO_TEXT, meaning that no SQL text is collected. Use the STATUS QUERY TEXT command to get the query execution plan of the specified query. NO_TEXT Does not collect the SQL syntax of queries executing in the service. The default is NO_TEXT, meaning that no SQL text is collected.
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ALTER SERVICE service-name COMP rule-list rule-list is: ( rule-name [ , rule-name ... ] ) service-name is the name of an existing service for managing queries. service-name must exist in the WMS configuration and cannot be the SYSTEM service.
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ALTER SERVICE service-name EXEC rule-list rule-list is: ( rule-name [ , rule-name ... ] ) service-name is the name of an existing service for managing queries. service-name must exist in the WMS configuration and cannot be the SYSTEM service.
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 ROLE.DBA or the super ID (super.services or super.super). Syntax ALTER WMS wms-parameter-list wms-parameter-list is: wms-parameter [ , wms-parameter ] ...
Examples • This command alters the system configuration to have a statistics refresh interval of 10 seconds: ALTER WMS STATS_INTERVAL 10; • This command alters the system configuration to have a maximum CPU busy of 80% and a maximum memory usage of 90%: ALTER WMS MAX_CPU_BUSY 80, MAX_MEM_USAGE 90; ALTER WMS Command 107
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 connection rules and services, including associations for the predefined “HPS_” rules and services. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
ALTER WMS RESET STATS Command The ALTER WMS RESET STATS resets the aggregated statistics counters to zero. WMS starts accumulating new aggregated statistics from the time of the reset. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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 ROLE.DBA or the super ID (super.services or super.super).
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 ROLE.DBA or the super ID (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 ROLE.DBA or the super ID (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 110). NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
HOLD QUERY ALL; • This command holds queries in SERVICE1: HOLD QUERIES SERVICE SERVICE1; 114 WMS Commands
INFO RULE Command The INFO RULE command returns information about a specific rule or all rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax INFO RULE [ { rule-name | ALL } ] rule-name is the name of a user-defined rule. rule-name must exist in the WMS configuration. The rule name is not case-sensitive and is always converted to uppercase. WMS supports multibyte characters in the rule name.
INFO RULE COMP Command The INFO RULE COMP command returns information about all the compilation rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
INFO RULE CONN Command The INFO RULE CONN command returns information about all the connection rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
INFO RULE EXEC Command The INFO RULE EXEC command returns information about all the execution rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
INFO SERVICE Command The INFO SERVICE command returns information about the attributes of one or all services in WMS. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax INFO 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.
Examples • This command displays information about SERVICE1: INFO SERVICE SERVICE1; • This command displays information about all the services in WMS: INFO SERVICE ALL; 120 WMS Commands
INFO SERVICE COMP Command The INFO SERVICE COMP command returns the names of the compilation rules associated with the specified service. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax INFO SERVICE service-name COMP 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.
INFO SERVICE EXEC Command The INFO SERVICE EXEC command returns the names of the execution rules associated with the specified service. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax INFO 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.
INFO WMS Command The INFO WMS command returns this information about the WMS system configuration: Syntax INFO WMS Considerations • The INFO WMS command returns this information about the WMS system configuration: Table A-9 INFO WMS Output Information Column Name Data Type Description MAX_CPU_BUSY INT Sets the default value to use when an ADD SERVICE command is executed and this parameter is not specified. Range is 0 to 100 percent. Zero (0) means this threshold is not checked.
INFO WMS CONN Command The INFO WMS CONN command returns information about connection rules associated with services. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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 ROLE.DBA or the super ID (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 ROLE.DBA or the super ID (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.
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. 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 role or user name (the alias of a role).
Table A-11 Query Compile-Time Statistics (continued) Column Name Data Type Description QUERY_SUBSTATE VARCHAR(30) When QUERY_STATE = WAITING: — MAX_CPU_BUSY — MAX_MEM_USAGE — RELEASED_BY_ADMIN When QUERY_STATE = EXECUTING: — RELEASED_BY_ADMIN When QUERY_STATE = HOLDING: — LOADING — PREPARING — EXECUTING_SQL_CMD — BY_COMP_RULE — BY_ADMIN — SUSPENDED_BY_ADMIN When QUERY_STATE = COMPLETED: — HOLD_TIMEOUT — EXEC_TIMEOUT — CANCELLED_BY_ADMIN — QUERY_NOT_FOUND — NDCS_PROCESS_FAILED — CPU_FAILED — SEGMENT_FAIL
Table A-11 Query Compile-Time Statistics (continued) 130 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-11 Query Compile-Time Statistics (continued) • Column Name Data Type Description CMP_DP2_ROWS_ACCESSED DOUBLE PRECISION The estimated number of rows that will be accessed by all the scan, insert, update, and delete operators in the query plan. CMP_DP2_ROWS_USED DOUBLE PRECISION The estimated number of rows that will be accessed by scan and insert operators in the query plan.
Table A-13 Query Runtime Statistics 132 Column Name Data Type Description QUERY_ID VARCHAR(160) Query identifier QUERY_STATE VARCHAR(9) State of the query, which is WAITING, EXECUTING, HOLDING, REJECTED or COMPLETED QUERY_SUBSTATE VARCHAR(30) When QUERY_STATE = WAITING: — MAX_CPU_BUSY — MAX_MEM_USAGE — RELEASED_BY_ADMIN When QUERY_STATE = EXECUTING: — RELEASED_BY_ADMIN When QUERY_STATE = HOLDING: — LOADING — PREPARING — EXECUTING_SQL_CMD — BY_COMP_RULE — BY_ADMIN — SUSPENDED_BY_ADMIN When QUERY
Table A-13 Query Runtime Statistics (continued) Column Name Data Type Description STATEMENT_TYPE VARCHAR(21) OTHER UNKNOWN SELECT_UNIQUE SELECT_NON_UNIQUE INSERT_UNIQUE INSERT_NON_UNIQUE UPDATE_UNIQUE UPDATE_NON_UNIQUE DELETE_UNIQUE DELETE_NON_UNIQUE CONTROL SET_TRANSACTION SET_CATALOG SET_SCHEMA CALL_NO_RESULT_SETS CALL_WITH_RESULT_SETS SP_RESULT_SET COMP_START_TIME LARGEINT Compilation start time COMP_END_TIME LARGEINT Compilation end time EXEC_START_TIME LARGEINT Execution start time EXEC_
Table A-13 Query Runtime Statistics (continued) • Column Name Data Type Description OPENS LARGEINT Number of OPEN calls performed by the executor on behalf of this process OPEN_TIME LARGEINT Time the process spent doing OPENs SQL_ERROR_CODE INT Top-level error code returned by the query, indicating whether the query completed successfully or with warnings or errors NUM_ROWS_IUD LARGEINT Number of rows inserted, updated, or deleted by the query STATS_ERROR_CODE INT Warning number returned
Table A-14 Aggregated Statistics of Executing Queries (continued) Column Name Data Type Description TOT_OPEN INT Number of executing queries in the open state for this segment TOT_EOF INT Number of executing queries in the end-of-file state for this segment TOT_CLOSE INT Number of executing queries in the close state for this segment TOT_DEALLOCATED INT Number of executing queries in the deallocate state for this segment TOT_FETCH INT Number of executing queries in the fetch state for this
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 about all the queries in WMS. For output information, see Table A-13 (page 132). query-id returns status information about a query. query-id is the query identifier and can be a maximum of 160 characters. For output information, see Table A-11 (page 128).
STATUS QUERY MXID01002031074212110209169193275000000000609ROLE.USR200_73_S1; • 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.
STATUS RULE Command The STATUS RULE command returns information about a specific rule or all rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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.
STATUS RULE COMP Command The STATUS RULE COMP command returns information about all the compilation rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
STATUS RULE CONN Command The STATUS RULE CONN command returns information about all the connection rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
STATUS RULE EXEC Command The STATUS RULE EXEC command returns information about all the execution rules. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
STATUS SERVICE Command The STATUS SERVICE command returns information about the attributes of one or all services in WMS. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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.
Considerations • The STATUS SERVICE command returns this information about one or all services in WMS: Table A-21 STATUS SERVICE Output Column Name Data Type Description SERVICE_NAME VARCHAR(96) Name of the service STATE VARCHAR(9) 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) — STOPPE
STATUS SERVICE COMP Command The STATUS SERVICE COMP command returns the names of the compilation rules associated with the specified service. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax STATUS SERVICE service-name COMP 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 EXEC Command The STATUS SERVICE EXEC command returns the names of the execution rules associated with the specified service. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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 ROLE.DBA or the super ID (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 aggregated statistics about the queries running in one or all services on one or all segments of the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax STATUS SERVICE [ service-name ] [ SEGMENT segment-num ] STATS [DETAIL] service-name is the name of a user-defined service for managing queries.
Examples • This command displays aggregated statistics for all services on all segments of the Neoview platform: STATUS SERVICE STATS; • This command displays aggregated statistics for SERVICE1 on all segments of the Neoview platform: STATUS SERVICE SERVICE1 STATS; • This command displays statistics for all services on each segment of the Neoview platform: STATUS SERVICE STATS DETAIL; • This command displays statistics for SERVICE1 on each segment of the Neoview platform: STATUS SERVICE SERVICE1 STAT
STATUS WMS Command The STATUS WMS command returns information about the WMS system configuration and the status of the system resources. Syntax STATUS WMS Considerations • The STATUS WMS command returns these configuration values: Table A-26 STATUS WMS Configuration Values • Column Name Data Type Description STATE VARCHAR(9) State of WMS: INIT (for initialization), ACTIVE, HOLD, STOPPING, STOPPED, or STOP MAX_CPU_BUSY INT Maximum CPU busy allowed. Zero (0) means no limit.
STATUS WMS CONN Command The STATUS WMS CONN command returns information about connection rules associated with services. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
STATUS WMS SEGMENT STATS Command The STATUS WMS SEGMENT STATS command returns aggregated statistics about the queries running on one or all segments of the Neoview platform. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super). Syntax STATUS WMS [SEGMENT segment-num ] STATS segment-num is the number of a segment on the Neoview platform.
STATUS WMS VERSION Command The STATUS WMS VERSION command returns version information for the internal WMS processes. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (super.services or super.super).
STOP SERVICE Command The STOP SERVICE command stops a service. NOTE: Only administrators are permitted to run this command. By default, an administrator is ROLE.DBA or the super ID (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. The service name is not case-sensitive and is always converted to uppercase.
B New and Changed Information This appendix describes new WMS functionality for Neoview Release 2.4 and explains the differences in WMS functionality between releases 2.3 and 2.4. • “What Is New for This Release” (page 155) • “What Changed for This Release” (page 156) What Is New for This Release The following functionality is new for this release: • Rules mechanism (CONN for connection mapping, COMP for compilation rules, and EXEC for execution rules). For more information, see Chapter 4 (page 39).
Table B-1 New WMS Commands for Neoview Release 2.
Table B-2 Comparison of WMS Functionality Between Neoview Release 2.3 and 2.4 (continued) WMS Functionality in Neoview Release 2.3 WMS Functionality in Neoview Release 2.4 Pre-execution control: WMS decided how to handle an incoming query—whether to allow the query to start executing, put the query in a waiting queue, or reject the query—by comparing periodic snapshots of available system resources (CPU and memory) to the MAX_CPU_BUSY and MAX_MEM_USAGE thresholds.
Table B-3 WMS Commands With New Output Columns (continued) WMS Command New Output Columns STATUS QUERY and STATUS QUERIES • • • • • • • • • • • • • • QUERY_SUBSTATE EST_TOTAL_MEM CMP_AFFINITY_NUM CMP_DOP CMP_TXN_NEEDED CMP_MANDATORY_X_PROD CMP_MISSING_STATS CMP_NUM_JOINS CMP_FULL_SCAN_ON_TABLE CMP_HIGH_DP2_MAX_BUF_USAGE CMP_ROWS_ACCESSED_FULL_SCAN CMP_DP2_ROWS_ACCESSED CMP_DP2_ROWS_USED WARN_LEVEL For a complete list of returned columns, see Table A-11 (page 128).
Index A ACCESSED_ROWS syntax of, 89, 100 Active period, setting start and end times, 32 ACTIVE threshold syntax of, 92, 102 ACTIVE_TIME setting the value, 32 ADD RULE COMP command syntax of, 83 ADD RULE CONN command syntax of, 86 ADD RULE EXEC command syntax of, 88 ADD SERVICE command example of, 29 syntax of, 91 Administrator's role, 23 ALTER RULE COMP command syntax of, 94 ALTER RULE CONN command syntax of, 97 ALTER RULE EXEC command syntax of, 99 ALTER SERVICE command, syntax of, 101 ALTER SERVICE COMP c
EST_CARDINALITY, 84, 95 EST_TOTAL_MEMORY, 84, 95 EST_TOTAL_TIME, 84, 95 EST_USED_ROWS, 84, 95 NUM_JOINS, 84, 95 SCAN_SIZE, 84, 95 UPDATE_STATS_WARNING, 84, 95 Expressions, connection rule APPL, 87, 98 DSN, 87, 98 LOGIN, 86, 97 SESSION, 86, 97 Expressions, execution rule ACCESSED_ROWS, 89, 100 ELAPSED_TIME, 89, 100 EST_ACCESSED_ROWS, 89, 100 EST_TOTAL_MEMORY, 89, 100 EST_USED_ROWS, 89, 100 TOTAL_MEM_ALLOC, 89, 100 USED_ROWS, 89, 100 G GET SERVICE statement, 37 H HOLD command example of, 78 syntax of, 113 H
Query plan, 74 Query text, 74 R RELEASE command example of, 76, 78 syntax of, 125 RULE_INTERVAL syntax of, 106 Rules compilation, 51 configuring, 39 connection, 40 description of, 39 execution, 59 monitoring, 69 setting up, 24 Runtime statistics, 72 S SCAN_SIZE syntax of, 84, 95 Segments displaying aggregated statistics, 70 Service starting, 77 stopping, 77 Services active period, 32 adding, 29 altering, 35 comment, 92, 93, 102 configuring, 29 deleting, 36 displaying aggregated statistics, 69 holding all
executing queries, 33 holding queries, 33 waiting queries, 33 TOTAL_MEM_ALLOC syntax of, 89, 100 U UPDATE_STATS_WARNING syntax of, 84, 95 USED_ROWS syntax of, 89, 100 W WAIT_TIMEOUT syntax of, 92, 102, 106 WMS configuring, 27 description of, 17 how to use, 23 installing, 25 monitoring, 69 WMS commands list of, 81 using in NCI, 25 Workload, definition of, 17 162 Index
