OpenText Core Analytics Database Settings Overview

• 1: Connection Data Settings
• 2: CIDR
• 3: Connection Data Settings
• 4: Alerts
• 4.1: Monitoring alerts
• 4.2: Alert configuration
• 5: Resource pools

1 - Connection Data Settings

You can configure the default connection data settings in OTCAD. You can manage IP addresses with Classless Inter-Domain Routing (CIDR) and scale your plan to suit your requirements.

2 - CIDR

CIDR

Cloud infrastructure engineers use CIDR notation to manage IP address ranges for cloud-based networks. IP addresses enable resources in your Virtual Private Cloud (VPC) to communicate with each other and with resources over the Internet. CIDR notation is a way to represent an IP address and its network mask.

OpenText supports ranges of IP addresses using Classless Inter-Domain Routing (CIDR) notation. For example, 192.168.1.0/24 represents all IPv4 addresses in the range of 192.168.1.0 to 192.168.1.255.

The format of these addresses is as follows:

• An individual IPv4 address is 32 bits, with 4 groups of up to 3 decimal digits. For example, 10.0.1.0.
• An individual IPv6 address is 128 bits, with 8 groups of 4 hexadecimal digits. For example, 2001:0db8:85a3:0000:0000:8a2e:0370:7334.

Public IPv4 and IPv6 addresses

All subnets have an attribute that determines whether a network interface created in the subnet automatically receives a public IPv4 address. Therefore, when you launch an instance into a subnet that has this attribute enabled, a public IP address is assigned to the primary network interface that's created for the instance. You can control whether your instance receives a public IP address by doing the following:

• Modifying the public IP addressing attribute of your subnet.
• Enabling or disabling the public IP addressing feature during instance launch, which overrides the subnet's public IP addressing attribute.
• Unassigning a public IP address from your instance after launch by managing the IP addresses associated with a network interface.

Public IPv6 addresses are IPv6 addresses that can be configured to remain private or configured to be reachable over the Internet.

Private IPv4 and IPv6 addresses

Private IPv4 addresses are not reachable over the Internet and can be used for communication between the instances in your VPC. When you launch an instance into a VPC, a primary private IP address from the IPv4 address range of the subnet is assigned to the primary network interface (for example, eth0) of the instance. Each instance is also given a private (internal) DNS hostname that resolves to the private IP address of the instance.

You cannot share private IPv6 addresses on the Internet. You can use a private IPv6 address if you want your private networks to support IPv6 and you have no intention of routing traffic from these addresses to the Internet. You can connect to the internet from a resource that has a private IPv6 address. But you must route traffic through a resource in another subnet with a public IPv6 address.

Configure CIDR settings

To configure CIDR settings:

1. In the Settings Overview page, select View CIDR. The CIDR page displays.
2. Select Private.
3. To add an IP address, select Add IP.
4. From the Value drop-down list, select either IPv4 or IPv6 depending on the IP address you want to add. Do one of the following: a. Enter a valid IPv4 or IPv6 address. b. Copy a valid IPv4 or IPv6 address from another source and select Paste.
5. Select √.

3 - Connection Data Settings

Scaling

Workload scaling enhances application performance and resource efficiency in dynamic environments. Scaling ensures that applications can handle varying workloads without performance degradation. OpenText provides the ability to automatically scale resources up or down based on real-time metrics, allowing organizations to respond quickly to changes in traffic. This elasticity not only improves user experience but also optimizes resource utilization, helping to minimize costs associated with underused or overprovisioned resources.

You cam manage compute to your database to improve the performance of complex analytic queries that have been running for a long time. Adding compute helps these queries run faster.

Configure the compute of your database to suit your query requirements and workload performance needs.

To manage compute for your database:

1. In the Settings Overview page, select Scaling.

2. Select View Scaling. The horizontal database scaling options are displayed. You can scale the database either manually or automatically. Do one of the following:

   a. To manually scale the database, select Manual. b. Slide across the slider horizontally to scale the database depending on the size - small, small-medium, medium, medium-large, or large. c. Select Apply changes. d. In the View scaling log area, click the Audit log and Scaling log tabs to view the log details.

   <-Or->

   a. To automatically scale the database, select Auto. Select this option only if you need to automatically scale the database based on defined parameters. b. From the Scale up to drop-down list, select either Large or Medium depending on the size of your database.

   Note

      Define the maximum compute beyond which the system cannot add anything and balance the loads automatically.
   

   c. Use the CPU usage toggle button to scale out the database when the CPU usage exceeds a value in the range 1% to 100%. d. Use the Memory usage toggle button to scale out the database when the memory usage exceeeds a value in the range 1% to 100%.

   Note

     When you apply both CPU usage and Memory usage, the system adds additional compute when either of the thresholds are reached.
   

   e. Select Apply changes. f. In the View scaling log area, click the Audit log and Scaling log tabs to view the log details. The Audit log provides information about the following:
   • Date & time
   • Event
   • Performed by The Scaling log provides information about the following:
   • Date & time
   • Scale event
   • Compute (units)
   • Triggered
   • Performed by

4 - Alerts

You can configure the default alert settings in OpenText™ Core Analytics Database (OTCAD).

Monitoring alerts

OTCAD periodically checks system health and performance. When an important event occurs or system performance fluctuates beyond user or system-defined limits, OTCAD generates a message to alert you about the state of your system. You can view and manage these alerts.

OTCAD sends alerts to you to ensure that you are immediately aware of any changes to your database that might require attention. You receive notifications by email and can also view them from the Bell icon in the upper‑right corner of the application.

Alert categories

Select Alerts in the Settings Overview page.

• The All categories tab displays all alerts in the system by default.
• The Queries tab displays pre-configured alerts to monitor queries.
• The Resource pool tab displays pre-configured alerts to monitor resource pools.
• The Custom tab displays custom alerts in the system.

  Note

  Turn the alert on to activate the corresponding pre-defined alert rule in the database. The system evaluates and sends notifications when alerts are turned on. Turning the alert off deactivates the corresponding alert rule and stops further evaluation and notifications.

4.1 - Monitoring alerts

Pre-configured alerts

The system provides pre-configured alerts to provide system monitoring capabilities without manual setup. Each alert has a pre-configured threshold that defines the acceptable performance limit. The system then sends a message notification when the database exceeds that threshold.

You can monitor the system using preconfigured alerts without setting them up manually.

Queries

Query performance alerts notify you when queries exceed execution time thresholds. The system provides the following pre-configured alerts to monitor queries:

• Failed query number - The total number of queries that failed during execution due to errors.
• Query running time - A query that is neither in the queue nor completed is a running query. The Query Running Time reflects the elapsed time since the query started executing.
• Queued query number - The number of queries currently waiting in the queue for resources before execution begins.
• Retried query number - The number of queries that are placed on hold for different reasons, such as limited resources.
• Spilled query number - The number of queries that were written to disk when the system did not have enough memory.

Resource pool

The system sends alerts when an individual resource pool reaches a specified state or usage level. For details about resource pool configuration parameters, see Built-in resource pools configuration. You can configure the following resource pool alerts:

• Ended query with queue time exceeding limit - The number of queries that were not executed because their queue time exceeded the allowed limit.
• Ended query with runtime exceeding limit - The number of queries that were stopped because they ran longer than the time limit set by the resource pool.
• Maximum allowed resource value - The maximum resource limits (such as memory, CPU) allowed for a query defined in the resource pool.
• Minimum starting resource value - The minimum resources required for a query to start running.
• Queries with resource rejections - The number of queries that were rejected due to insufficient resources.
• Queries reaching the max allowed execution time - Set a time threshold to see how many queries reached that limit during execution.

Custom alerts

Create custom alerts to measure system performance metrics that are not monitored by the pre-configured alerts. Create a dynamic SQL query that triggers an alert when it returns any results. You can configure how often an alert is generated, the alert criticality level, and who receives email alerts. For example, you can create custom alerts that monitor the following:

• Failed logins within a configurable time period
• Idle database user sessions using a configurable time limit

  Note

  You must have administrator privilege to create, edit, clone, and delete custom alerts.

Create a custom alert

Create custom alerts to track abnormalities and performance fluctuations for queries and resource pools using your own database schemas and tables. When the database triggers an active alert, you receive notifications according to the alert priority.

1. In the Settings Overview page, select Alerts. The Alerts page appears.

2. Choose Custom alert.

3. In the Alert name field, enter a unqiue name for the custom alert.

4. In the Description field, enter an optional description for the custom alert.

5. In the Trigger condition query field, enter the following SQL query and choose Run:

   SELECT
     login_timestamp,
     user_name,
     node_name,
     client_hostname,
     reason
   FROM
     login_failures
   WHERE
     reason in ('INVALID USER', 'FAILED', 'INVALID DATABASE')
     AND login_timestamp > sysdate - INTERVAL '{{Time_Interval}}'
   

   The AND clause of the preceding query encloses the variable of type String named Time_Interval in double curly braces to represent a valid SQL syntax element.

   A variable is a dynamic value that is evaluated at runtime that you can configure after you save the alert. You can add up to 6 variables per custom alert. Variable names may consist of a maximum of 14 alpha-numeric characters, including underscores. Verify that the variable uses the correct data type and format. Variables of type String require single quotes around the curly braces.

   A SQL query triggers an alert if it returns one or more rows.

6. From the Interval list, choose the time interval at which the SQL query is executed.

7. In the Criticality level area, choose the alerty priority. By default, the alert criticality is Low.

8. Choose the option Send an email copy. In the Add new user field, enter the email address of the recipient. For multiple recipients, enter the email addresses separated by a comma.

9. Choose Create.

10. Turn the alert on or off using the switch at the right of the alert row.

Edit a custom alert

After creating a custom alert, you can edit the details to suit your requirements.

1. In the Alerts page, select a custom alert.
2. Hover over ⋮ and select Edit. All the details of the custom alert are populated. You cannot edit the name of the custom alert. For more information about editing a custom alert, see Create a custom alert

Clone a custom alert

You can create a clone or replica of an existing custom alert. The settings of the existing custom alert are copied to the cloned custom alert. You can edit these settings in the cloned custom alert.

1. In the Alerts page, select a custom alert.
2. Hover over ⋮ and select Clone. Information in all fields is pre-populated. You can edit this information. For more information about editing a custom alert, see Create a custom alert

Delete a custom alert

You can delete a custom alert that is no longer in use or required.

1. In the Alerts page, select a custom alert.

2. Hover over ⋮ and select Delete.

3. In the Confirmation dialog, select Delete.

   The custom alert is deleted.

4.2 - Alert configuration

Alert configuration

Enable and customize settings for pre-configured or custom alerts for each database. Pre-configured and custom alerts generate messages when the component they measure exceeds the specified threshold.

Configurable settings

Select Alerts in the Settings Overview page. To configure an alert, you must turn the switch on the right of the row to the on position. By default, custom alerts are turned off. Pre-configued and custom alerts have the following settings:

• Trigger condition - Add lower and upper limits on acceptable operating values for the component. Move the arrow up or down to either increase or decrease the query count.
• Interval - Choose how often the system executes the underlying SQL for the alert.
• Resource pool - Choose the resource pool to which you need to add this alert.
• Criticality level - Choose the alerty priority. By default, the alert criticality is Low.
• Send an email copy - Choose this option to enter the email address of the recipient. For multiple recipients, enter the email addresses separated by a comma.

Configuring resource pool alerts

Resource pool alerts use the same configurable settings as the other alerts, but you can set alerts per resource pool.

1. Login to OTCAD.
2. In the Settings Overview page, select Alerts. The Alerts page appears.
3. In the Resource pools tab, select the resource pool to which you need to add this alert.
4. Edit the trigger condition, interval, resource pool, criticality level, and recipient user email address. For more information, see Configurable settings.

Configuring query alerts

You can configure alerts that monitor queries.

1. Login to OTCAD.
2. In the Settings Overview page, select Alerts. The Alerts page appears.
3. In the Queries tab, select the query to which you need to add this alert.
4. Edit the trigger condition, interval, criticality level, and recipient user email address. For more information, see Configurable settings.

View alerts

The Alerts page organizes alerts to help you effectively monitor the state of your database. Pre-configured and custom alerts generate messages when the component they measure exceeds the specified threshold.

1. Log in to OTCAD.
2. Click the Bell icon in the upper‑right corner of the application. The Alerts page appears.
3. From the Date range list, choose the period to view the alert. Do one of the following:
   • Choose Last 15 min to view only those alerts that were created in the last 15 minutes.
   • Choose Last 1 hour to view only those alerts that were created in the last one hour.
   • Choose Last 7 days to view only those alerts that were created in the last 7 days.
   • Choose Last 14 days to view only those alerts that were created in the last 14 days.
4. From the Notification type list, choose the type of alert. Do one of the following:
   • Choose All to view both resource pool and query alerts.
   • Choose Queries to view only query alerts.
   • Choose Resource pool to view only resource pool alerts.
5. In the Severity area, choose the option to view the alerts by severity. Do one of the following:
   • Choose Low to view alerts with low priority.
   • Choose High to view alerts with high priority.
6. Select Mark as read after you view an alert and do not wish to view the same alert again.

   Note

   The system does not display notifications that are marked as read.
   

5 - Resource pools

Resource pools

A resource pool comprises a pre-allocated subset of the system resources with an associated queue. Super users can view information about resource pool parameters and make changes to existing parameters through the Resource pools page.

View resource pool details

You can view information about the resource pools available in the system. To view resource pool details:

1. In the Settings page, select Resource pools. The Resource pool page lists the following information:
   • Name - Name of the resource pool.
   • Memory size - Total per-node memory available to the database resource manager that is allocated to this resource pool.
   • Runtime cap (seconds) - Maximum time (in seconds) for which a query in the resource pool can execute. If a query exceeds this setting, it cascades to a secondary pool.
   • Priority - Priority of queries in this resource pool when they compete for resources in the GENERAL pool.
   • Runtime priority - Specifies how the resource manager prioritizes run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool.
   • Queue timeout - The interval that the request waits for resources to become available before being rejected.
   • Max concurrency - Maximum number of concurrent execution slots available to the resource pool across the cluster.
2. In the Actions column, select ⋮ and View details. The Pool details page lists pool-specific information.
3. Select Close.

Create a resource pool

Only super users can create and configure resource pools.

1. In the Settings page, select Resource pools > Create. The Create resource pools page is displayed.

2. In the Pool name field, enter a unique name for the resource pool. If you specify a resource pool name with uppercase letters, OTCAD converts them to lowercase letters.

   The following naming requirements apply:

   • Built-in pool names cannot be used for user-defined pools.
   • All user-defined global resource pools must have unique names.
   • Names of global resource pools cannot be the same.

3. From the Cascade to list, select an existing resource pool that is either a user-defined pool or the GENERAL pool.

4. From the User list, assign existing users to a resource pool. This resource pool is used to process all queries from its assigned users, allocating resources as set by resource pool parameters. The selected user is added to the resource pool by default.

5. In the Minimum memory field, enter the total per-node memory available to the database resource manager that is allocated to this resource pool. The default is 0%. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

6. In the Maximum memory field, enter the maximum size per node the resource pool can grow by using memory from the GENERAL pool. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

7. In the Maximum query memory field, enter the maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, OTCAD stops and returns an error. From the list, select the percentage of maximum memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes upto the value of maximum memory.

8. The CPU affinity mode specifies whether the resource pool has exclusive or shared use of the CPUs specified in the Affinity set field below. Select one of these values:

   • Shared - Queries that run in this resource pool share its Affinity set CPUs with other resource pools.
   • Exclusive - Dedicates Affinity set CPUs to this resource pool only and excludes other resource pools. If Affinity set is set as a percentage, then that percentage of CPU resources available only are assigned to this resource pool.
   • Any - Queries in this resource pool can run on any CPU.

9. Affinity set - CPUs available to this resource pool. All nodes must have the same number of CPUs. The CPU resources assigned to this set are not available to GENERAL resource pools. From the list, do one of the following:

   • Select Range to dedicate a range of contiguous CPU indexes in the From and To fields to this resource pool.
   • Select Single to dedicate one or more comma-delimited CPUs to this resource pool.
   • Select Percentage to dedicate the percentage of all available CPUs to use for this resource pool. The database rounds this percentage down to include whole CPU units.

10. In the Minimum concurrency field, enter the preferred number of queries to execute concurrently in the resource pool. Select the Auto option to calculate the value automatically at query runtime. The system sets this parameter to the lower of these two calculations, but never less than 4:

    • Number of logical cores
    • Memory divided by 2GB

    Change this parameter only after evaluating performance over a period of time.

    Note

       If the number of logical cores on each node is different, **Auto** is calculated differently for each node. Distributed queries run like the minimal effective planned concurrency. Single node queries run with the planned concurrency of the initiator.
    

11. In the Maximum concurrency field, enter the maximum number of concurrent execution slots available to the resource pool.

12. In the Execution parallelism field, enter the number of threads used to process any single query issued in this resource pool. Select the Auto option to calculate the setting from the number of cores, available memory, and amount of data in the system. Unless memory is limited, or the amount of data is very small, the system sets this parameter to the number of cores on the node.

13. In the Pool priority field, enter the priority of queries in this resource pool when they compete for resources in the GENERAL pool. Enter either negative or positive integer values, where higher numbers denote higher priority.

    • For user-defined resource pools, enter a value in the range -100 to 100.
    • For built-in resource pools such as SYSQUERY, RECOVERY, and TM, enter a value in the range of -110 to 110.

14. From the Runtime priority list, select how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool. Select one of Low, Medium, or High. The default is Medium.

15. In the Runtime priority threshold field, enter the maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries run with a priority of HIGH. The default value is 2.

16. In the Queue timeout (seconds) field, enter the maximum time (not more than an year) a request can wait (in seconds) for pool resources before it is rejected. There is no maximum wait time. Requests can be queued indefinitely up to one year.

17. In the Runtime cap (seconds) field, enter the maximum run time allowed (not more than an year) to queries in this resource pool. If a query exceeds this setting, it cascades to a secondary pool.

Edit a resource pool

After creating a resource pool, you can edit the details to suit your requirements. Only database administrators can edit resource pools.

1. In the Resource pools page, under the Actions column, choose ⋮ and Edit. All the details about the resource pool are populated. You cannot edit the following fields:

   • Pool name
   • Affinity set
   • Minimum currency
   • Execution parallelism

2. From the Cascade to list, choose the secondary resource pool for executing queries that exceed the RUNTIMECAP setting of their assigned resource pool.

3. From the User list, assign existing users to a resource pool. This resource pool is used to process all queries from its assigned users, allocating resources as set by resource pool parameters. The selected user is added to the resource pool by default.

4. In the Minimum memory field, enter the total per-node memory available to the database resource manager that is allocated to this resource pool. The default is 0%. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

5. In the Maximum memory field, edit the maximum size per node the resource pool can grow by using memory from the GENERAL pool. From the list, select the percentage of total memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes.

6. In the Maximum query memory field, edit the maximum amount of memory this resource pool can allocate at runtime to process a query. If the query requires more memory than this setting, OTCAD stops and returns an error. From the list, select the percentage of maximum memory, or amount of memory in kilobytes, megabytes, gigabytes, or terabytes upto the value of maximum memory.

7. The CPU affinity mode specifies whether the resource pool has exclusive or shared use of the CPUs specified in the Affinity set field below. Select one of these values:

   • Shared - Queries that run in this resource pool share its Affinity set CPUs with other resource pools.
   • Exclusive - Dedicates Affinity set CPUs to this resource pool only and excludes other resource pools. If Affinity set is set as a percentage, then that percentage of CPU resources available only are assigned to this resource pool.

8. Affinity set - Edit the CPUs available to this resource pool. All nodes must have the same number of CPUs. The CPU resources assigned to this set are not available to GENERAL resource pools. From the list, do one of the following:

   • Select Range to dedicate a range of contiguous CPU indexes in the From and To fields to this resource pool.
   • Select Single to dedicate one or more comma-delimited CPUs to this resource pool.
   • Select Percentage to dedicate the percentage of all available CPUs to use for this resource pool. The database rounds this percentage down to include whole CPU units.

9. In the Maximum concurrency field, edit the maximum number of concurrent execution slots available to the resource pool.

10. In the Pool priority field, edit the priority of queries in this resource pool when they compete for resources in the GENERAL pool. Edit either negative or positive integer values, where higher numbers denote higher priority.

    • For user-defined resource pools, enter a value in the range -100 to 100.
    • For built-in resource pools such as SYSQUERY, RECOVERY, and TM, enter a value in the range of -110 to 110.

11. From the Runtime priority list, select how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool. Select one of Low, Medium, or High. The default is Medium.

12. In the Runtime priority threshold field, edit the maximum time (in seconds) in which query processing must complete before the resource manager assigns to it the resource pool's RUNTIMEPRIORITY. All queries run with a priority of HIGH. The default value is 2.

13. In the Queue timeout (seconds) field, edit the maximum time (not more than an year) a request can wait (in seconds) for pool resources before it is rejected. There is no maximum wait time. Requests can be queued indefinitely up to one year.

14. In the Runtime cap (seconds) field, edit the maximum run time allowed (not more than an year) to queries in this resource pool. If a query exceeds this setting, it cascades to a secondary pool.

    For more information about editing a resource pool, see Create a resource pool.

Delete a resource pool

You can delete a resource pool that is no longer in use or required.

1. In the Resource pools page, under the Actions column, choose ⋮ and Delete.
2. In the Confirmation dialog, select Delete. Requests that are queued against the deleted pool are transferred to the GENERAL pool according to the priority of the pool compared to the GENERAL pool. If the pool’s priority is higher than the GENERAL pool, the requests are placed at the head of the queue; otherwise, transferred requests are placed at the end of the queue.