2.1 - Data pipelines
This topic provides an overview of data pipelines in OTCAD.
Data pipeline
A data pipeline also known as a data loader is a declarative, automated way to process data continuously from external sources like Amazon S3 or Kafka. They allow you to define where, when, and how you need to load data into OTCAD with minimal manual intervention.
Data pipelines UI tour
You can view and manage data pipelines. After logging in to the OTCAD application, you land on the home page. Select the More options button and Data Pipeline. The Data pipeline page appears as shown in this image:
|
Option |
Description |
|
a |
Create a pipeline: Select this option to create a data pipeline. |
|
b |
Filter by: Select this option to filter data by different criteria. |
|
c |
Search pipeline name or schema: Search for a data pipeline based on the pipeline name or schema. |
|
d |
Actions: Select ⋮ in the Actions column to view pipeline details, edit, clone, pause or resume schedule, and delete a data pipeline. |
This page displays the following information in the Overview card:
- Total number of pipelines - The Total Pipelines card displays the total number of pipelines configured in the system.
- Pipelines that failed execution - Select the Failed Execution list and the period for which you need to view the pipelines that failed to execute.
- Active pipelines - The Active Pipelines card displays the total number of pipelines that are in the Active status.
- From the Duration list, select the duration for which you need to view the data pipelines. You can choose from the following:
- Last 24 hours
- Last week
- Last month
- Last 6 months
- Last year
- Anytime
The Pipelines area displays the following information:
Create a data pipeline
You can create a data pipeline using either the AWS object store or Kafka data source.
Note
When creating data pipelines, ensure that mulitple database objects do not have the same name.
Create a data pipeline using AWS object store
AWS object store, primarily Amazon Simple Storage Service (S3) is a highly scalable, durable, and cost-effective cloud storage service for unstructured data. It stores data as objects in flat containers called buckets.
To create a data pipeline using AWS object store, do the following:
-
In the Data pipelines page, select +Create a pipeline.
The Create a pipeline page is displayed.
-
In the Pipeline name field, enter the name of the pipeline.
-
Select AWS object store.
-
In the Access key ID field, enter your AWS account access key id.
-
In the Secret access key field, enter your AWS account secret access key.
Note
Provide valid AWS credentials for the Access key ID and Secret access key. Invalid AWS credentials do not allow you to create a data pipeline.
-
From the Region list, select the region (geography) of the S3 bucket where the files are present.
-
In the S3 Bucket/File/Folder path field, enter the name or the folder path where the files are present.
-
Select the Data is encrypted option to specify the following parameters:
- Select either AWS Key Management Service Key or Customer managed keys if you wish to encrypt and load data into the S3 bucket.
- Select Encryption key ID.
-
Select Next.
-
In the Retry limit field, specify the number of times the system should attempt to retry a failed file load.
-
In the Parameters field, specify the copy parameters. For more information, see Parameters.
-
Select Next.
-
From the Destination table list, select the destination table to which you need to load the data.
-
Select Next.
-
Specify the schedule at which the data pipeline needs to run. Do one of the following:
-
Select Schedule.
- From the date pickers, select the Start date and End date.
- In the Repeat every field, specify the duration at which the data pipeline needs to run.
- From the Unit list, select the minute, hour, day, week, or month at which the data pipeline needs to run.
- Select the On day option and specify the day on which the data pipeline needs to run.
- Select the On option and specify the exact day and month on which the data pipeline needs to run.
- Select the option Trigger when something is added to run the data pipeline when a file is added to the S3 bucket.
- Enter the SQS credentials in the Access key ID, Secret access key, and Resource URL fields.
<--Or-->
-
Select Execute once.
-
Select Finish.
The data pipeline is created and displayed in the Data pipelines page.
Create a data pipeline using Kafka data source
Define data pipelines to ingest real-time data through Apache Kafka, an open-source distributed real-time streaming platform by leveraging Kafka topics for efficient streaming and processing. A Kafka topic is a category or feed name for streams of data, similar to a table in a database. Consumers read data from Kafka topics, with topics being organized into partitions for parallel processing. Each message in a topic is a record with a key, value, and timestamp. Kafka topics are logs where messages are ordered by an offset within each partition.
To create a data pipeline using Kafka data source, do the following:
-
In the Data pipelines page, select +Create a pipeline.
The Create a pipeline page is displayed.
-
In the Pipeline name field, enter the name of the pipeline.
-
Select Kafka.
Note
Create a destination table from the SQL editor before creating the data pipeline.
-
In the Bootstrap servers field, enter the initial list of Kafka broker addresses that a Kafka client uses to connect to the Kafka cluster.
Note
You can use only those bootstrapped servers that are whitelisted by OpenText. For assistance, contact the technical support team.
Note
For multi-cluster configurations, provide the addresses as a comma-separated list. For example, 10.20.41.15:9092,10.20.41.16:9092.
-
Do one of the following:
-
Select SSL if your Kafka broker is configured with SSL.
SSL is short for Secure Sockets Layer. It is a protocol that creates an encrypted link between a web server and a web browser to ensure that all data transmitted between them is confidential.
-
In the Client key field, enter the encrypted client key generated for the SSL certificate.
-
In the Password field, enter the password associated with the encrypted client key.
-
In the Client certificate field, enter the certificate used to authenticate the client with the Kafka broker.
-
In the CA certificate field, enter the certificate authority (CA) certificate used to validate the Kafka broker.
<--Or-->
-
Select SASL if your Kafka broker is authenticated with Simple Authentication and Security Layer (SASL).
SASL is a Kafka framework for authenticating clients and brokers, which can be used with or without TLS/SSL encryption. It allows Kafka to support different authentication mechanisms.
-
In the SASL mechanism list, select one of the following:
- Plain - A simple username/password authentication mechanism used with TLS for encryption to implement secure authentication.
- SCRAM - SHA - 256 - A secure password-based authentication method that uses a challenge-response protocol and the SHA-256 hashing algorithm to verify user credentials without sending the password in plain text over the network.
- SCRAM - SHA - 512 - A secure authentication mechanism that uses the SHA-512 cryptographic hash function to verify a user's credentials in a "challenge-response" format, which prevents the password from being sent directly over the network.
- In the Username field, enter a valid username.
- In the Password field, enter the password for SASL authentication.
-
Select Proceed.
-
In the Define configuration area, specify the configuration settings for the data source.
- In the Topic field, enter the Kafka topic. A Kafka topic is a logical grouping of messages, split into multiple partitions for parallelism.
- In the Partition box, type or select the number of partitions for the Kafka topic. A partition is a sequence of records within a topic, stored on brokers and consumed independently.
- In the Start offset box, type or select the incremental ID for each partition. The offset is a unique identifier for each record within a partition, used to track consumer progress.
- Select +Add topic to add more topics and partitions.
- Select one of the available parser options depending on the message type in the topic:
-
AVRO - An Avro schema registry is a crucial component in systems that utilize Apache Kafka and Avro for data serialization. Its primary purpose is to centralize the management and evolution of Avro schemas, providing a robust mechanism for ensuring data compatibility and governance in streaming data pipelines.
-
In the URL field, enter the schema registry URL.
-
In the Subject field, enter the subject information from the schema registry.
-
In the Version fieled, enter the version of the schema registry.
<--Or-->
-
In the External Schema field, enter the schema of the AVRO message. Ensure that the schema is in JSON format.
-
JSON - JSON schema registry for Kafka provides a mechanism to define, manage, and enforce the structure of JSON data being produced to and consumed from Kafka topics. This ensures data consistency and compatibility, especially in distributed systems where multiple applications interact with the same data streams.
-
Kafka - A Kafka schema registry is an external service that acts as a central repository for managing and validating schemas for data in a Kafka cluster. It allows producers to register schemas and consumers to retrieve them, ensuring data consistency and compatibility as schemas evolve over time.
-
Select Proceed.
-
From the Destination table list, select the destination table to which you need to load the data.
Note
New data is added to the end of the selected table without changing existing records.
-
Select Proceed.
-
Specify the schedule at which the data pipeline needs to run. Do one of the following:
-
Select Schedule.
- From the date pickers, select the Start date and End date.
- In the Repeat every field, specify the duration at which the data pipeline needs to run.
- From the Unit list, select the minute, hour, day, week, or month at which the data pipeline needs to run.
- Select the On day option and specify the day on which the data pipeline needs to run.
- Select the On option and specify the exact day of the week on which the data pipeline needs to run.
<--Or-->
-
Select Execute once.
-
Select Finish.
The data pipeline is created and displayed in the Data pipelines page.
Note
The data pipeline is created successfully only if the SQL query execution is successful. If the SQL query execution fails, the reason for the failure appears in a message. Resolve the SQL query to ensure successful query execution and data pipeline creation.
Filter a data pipeline
You can filter data pipelines based on certain criteria. Filter data pipelines in one of these ways:
-
Schema
- Select the Filter icon in the Data pipelines page.
- Expand the Schema list.
- Select the required schema.
-
Data source
- Select the Filter icon in the Data pipelines page.
- Expand the Data source list.
- Select the data source or data sources for which you wish to view the data pipeline.
Note
Data pipelines created from an AWS object store appear with the prefix **S3**, while data pipelines created from a Kafka data source appear with the prefix **Topic**.
-
Pipeline status
- Select the Filter icon in the Data pipelines page.
- Expand the Pipeline status list.
- Select Active to view the data pipelines that are active.
A data pipeline is in the
Active status when the schedule end date is a future date and there are one or more ingestions that are yet to complete.
- Select Inactive to view the data pipelines that are inactive.
A data pipeline is in the
Inactive status either when the end date is past or there are no ingestions that are yet to complete.
-
Id of the person who created the pipeline
- Select the Filter icon in the Data pipelines page.
- Expand the Created by list.
- Select the user Id of the person who created the data pipeline.
Search a data pipeline
All data pipelines are displayed by default. You can search data pipelines using specific search criteria. To search data pipelines, do the following:
- In the Data pipelines page, select +Search pipeline name or schema.
- Enter either the pipeline name or the schema name.
- You can sort the data pipeline by the pipeline name or the date on which it was last run. To sort the data pipelines in the ascending or descending order, select the Sort icon for the Pipeline name or Last run on column in the Data pipelines page.
View pipeline details
You can view pipeline details in the Data pipelines page. To view the details of a data pipeline, do the following:
-
In the Data pipeline page, hover over the Pipeline name column and select the +View details icon for a data pipeline.
You can view the following details of the data pipeline in this page.
-
Instance started at - Displays the timestamp of the data pipeline execution.
-
Transaction ID - Displays the transaction ID of the data pipeline execution.
-
Total file size - Displays the size of the file processed by the data pipeline.
-
File loaded or Rows loaded - Displays the number of files (for AWS object store) or the total number of rows loaded into the target table (if the data source is Kafka).
-
Status - Indicates the state of the data pipeline when it was last run. The values are:
- Executed - All rows are successfully loaded into the target table and there are no entries in the rejects table.
- Failed - For data pipelines that use AWS object data store, no rows are loaded into the target table. For data pipelines that use Kafka data source, some rows are loaded into the target table and the rejects table contains one or more entries.
- Partially executed - For data pipelines that use AWS object data store, some rows are loaded into the target table and the rejects table contains one or more entries that failed. For data pipelines that use Kafka data source, no rows are loaded into the target table and the rejects table contains one or more entries.
Note
For data pipelines that use Kafka data source and are in Failed state, you can view the rejects table, edit and fix the entries that display an error. The state of such data pipelines then changes to Partially Fixed. However, you cannot edit and fix the entries in the rejects table for data pipelines that are in the Partially Fixed state.
-
For data pipelines that use the AWS object store and are either in Failed or Partially executed status, select the i icon.
The Files tab appears with the following information:
- Transaction ID
- Timestamp of the transaction
- Batch ID
- File name
- File size
- Status
- Rows loaded
- Actions column (⋮) with options to view the details and synchronize the file.
-
Select View details.
The error log appears with the file name, row number, failure reason, and rejected data. Resolve the error and reload the file.
-
Select Sync file to synchronize the data between the source and destination files. A message "File sync in progress" appears at the top of the page.
-
Select Execute pipeline to execute the selected data pipeline.
Executing a data pipeline loads all files that have not already been loaded and that have not reached the retry limit. Executing the data pipeline commits the transaction.
Edit a data pipeline using AWS object store
After creating a data pipeline, you can edit the details to suit your requirements. To edit a data pipeline, do the following:
-
In the Data pipelines page, mouse over the Pipeline name column and select the +Edit pipeline icon for a data pipeline.
-
Select AWS object store.
Information is pre-populated in all fields.
-
In the Access key ID field, enter your AWS account access key id.
-
In the Secret access key field, enter your AWS account secret access key.
All the details about the data pipeline are populated, except the Access key ID and Secret access key.
Note
Provide valid AWS credentials for the **Access key ID** and **Secret access key**. Invalid AWS credentials do not allow you to edit a data pipeline.
For more information about editing a data pipeline using AWS object store, see Create a data pipeline.
Edit a data pipeline using Kafka data source
After creating a data pipeline, you can edit the details to suit your requirements. To edit a data pipeline, do the following:
-
In the Data pipelines page, hover over the Pipeline name column and select the +Edit pipeline icon for a data pipeline.
-
Select Kafka.
Information is pre-populated in all fields.
-
Do one of the following:
For more information about editing a data pipeline using Kafka data source, see Create a data pipeline using Kafka data source.
Clone a data pipeline using AWS object store
You can create a clone or replica of an existing data pipeline. The configurations of the existing data pipeline are copied to the cloned data pipeline. You can edit these configuration settings in the cloned data pipeline.
- In the Data pipelines page, select ⋮ in the Actions column.
- Select Clone.
The Create a pipeline page is displayed.
- Select AWS object store.
- In the Pipeline name field, enter the name of the pipeline.
- In the Access key ID field, enter your AWS account access key id.
- In the Secret access key field, enter your AWS account secret access key.
Note
Provide valid AWS credentials for the **Access key ID** and **Secret access key**. Invalid AWS credentials do not allow you to edit a data pipeline.
Information in all other fields is pre-populated. You can edit this information. For more information about cloning a data pipeline using AWS object store, see Create a data pipeline using AWS object store.
Clone a data pipeline using Kafka data source
You can create a clone or replica of an existing data pipeline. The configurations of the existing data pipeline are copied to the cloned data pipeline. You can edit these configuration settings in the cloned data pipeline.
-
In the Data pipelines page, select ⋮ in the Actions column.
-
Select Clone.
The Create a pipeline page is displayed.
-
Select Kafka.
-
In the Pipeline name field, enter the name of the pipeline.
-
Do one of the following:
<--Or-->
-
Select SASL if your Kafka broker is authenticated with Simple Authentication and Security Layer (SASL).
-
In the SASL mechanism list, select one of the following:
- Plain - A simple username/password authentication mechanism used with TLS for encryption to implement secure authentication.
- SCRAM - SHA - 256 - A secure, password-based authentication method that uses a challenge-response protocol and the SHA-256 hashing algorithm to verify user credentials without sending the password in plain text over the network.
- SCRAM - SHA - 512 - A secure authentication mechanism that uses the SHA-512 cryptographic hash function to verify a user's credentials in a "challenge-response" format, which prevents the password from being sent directly over the network.
- In the Username field, enter a valid username.
- In the Password field, enter the password for SASL authentication.
Information in all other fields is pre-populated. You can edit this information. For more information about cloning a data pipeline using Kafka data source, see Create a data pipeline using Kafka data source.
Pause and resume schedule
You can pause data ingestion anytime to suit your requirement.
-
In the Data pipelines page, select ⋮ in the Actions column.
-
Select Pause schedule.
-
In the Confirmation dialog, select Pause.
This will pause all data ingestions until the state is changed. Data that is already ingested will not be affected. A message "Data ingestion paused successfully" is displayed.
You can resume data ingestion anytime to suit your requirement.
-
In the Data pipelines page, select the data pipeline that is paused and click ⋮ in the Actions column.
-
Select Resume schedule.
-
In the Confirmation dialog, select Resume.
This will resume all data ingestions based on the defined schedule. A message "Data ingestion resumed successfully" is displayed.
Delete a data pipeline
You can delete a data pipeline that is no longer in use or required.
- In the Data pipelines page, select a data pipeline and click ⋮ in the Actions column.
- Select Resume schedule.
- In the Confirmation dialog, select Remove.
Note
Deleting a data pipeline removes all data pipeline configurations permanently. However, data that is already ingested is not deleted.
5.1 - Dashboards
This topic provides an overview of dashboards in OTCAD.
Dashboards
Dashboards enable you to view health metrics for your database.
Dashboards UI tour
You can view and manage dashboards. After signing in to the OTCAD application, the home page appears. Select the More options button and Dashboard. The Dashboard page appears as shown in this image:
The dashboard displays key metrics and events, including details about long-running queries, average query execution time, memory-intensive queries, and scaling activities.
This page displays the following information:
|
Option |
Description |
|
a |
More options: Select this option to view the More menu options. |
|
b |
Dashboard: View the dashboards in OTCAD by different criteria. |
|
c |
Active users: This card displays the total number of users who are active over the last 24 hours. Select > to view the list of active users. For more information about active users, see Active users. |
|
d |
Data stored: This card displays the aggregate data storage in the database from the first day of the current month till date. |
|
e |
Query overview: This card displays the total count of queries that completed and the queries that failed to execute over the last 24 hours. |
|
f |
Cluster performance: This card displays information about the overall cluster performance. It provides information about average CPU usage, average memory usage, and disk I/O usage over the last 24 hours.
- Select Average CPU usage to view the CPU percentage used by the database server across the primary cluster over the last 24 hours.
Select > to view the average CPU usage and maximum CPU usage over a duration of time. For more information about CPU usage, see CPU usage.
- Select Average Memory usage to view the memory percentage usage information over the last 24 hours.
Select > to view the average memory usage and maximum usage over a duration of time. For more information about average memory usage, see Memory usage.
- Select Disk I/O usage to view the percentage of time a disk is actively engaged in processing I/O requests over the last 24 hours.
Select > to view the average disk I/O usage and maximum usage over a duration of time. For more information about average disk I/O usage, see Disk I/O usage.
- Select Scaling activity to view the consumption of compute in units over the last 24 hours. For more information about scaling activity, see Scaling activity.
|
|
g |
Query insights: This card displays information about open sessions, queued queries, memory intensive queries, and long running queries.
- Select Open sessions to view information about the top 5 sessions that are active over the last 1 hour. Select > to view all the sessions that are active over the last 1 hour. For more information about open sessions, see Sessions.
- Select Queued queries to view information about the top 5 queries in the queue over the last 1 hour. Select > to view all the queries in the queue over the last 1 hour. For more information about queued queries, see Queued queries.
- Select Memory intensive queries to view information about the top 5 queries that consume more than 25% memory over the last 24 hours. Select > to view information about all queries that consume more than 25% memory over the last 24 hours. For more information about memory intensive queries, see Memory intensive queries.
- Select Long running queries to view information about the top 5 queries that took more than 10 minutes to execute over the last 24 hours. Select > to view information about all the queries that took more than 10 minutes to execute over the last 24 hours. For more information about long running queries, see Queries by duration.
|
Expand Cluster performance in the Dashboard page. The following dashboards are displayed:
CPU usage
Identify CPU usage and address system bottlenecks proactively.
The Average CPU Usage card displays information about the average cluster CPU usage trends in the system over a time duration. The Maximum Usage card displays information about the maximum CPU usage over a duration of time.
In the Visualization tab, do the following:
- In the Duration list, do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date. Ensure that the start date does not exceed the end date by more than 30 days.
-
In the View list, choose one of the values: Average, Minimum, or Maximum. Choose Average to view the average CPU usage for every minute over the selected duration of time. Choose Minimum to view the minimum CPU usage for every minute over the selected duration of time. Choose Maximum to view the maximum CPU usage for every minute over the selected duration of time.
-
In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data. Every minute has one data point. For example, if you select 1 day, a total of 1440 data points are plotted on the graph with an axis interval of 1 minute.
In the List view tab, information about the minute-wise time, average CPU usage, maximum CPU usage, and minimum CPU usage is displayed over the selected duration. Choose the Download button to export this information in a spreadsheet.
Memory usage
This helps you compare memory that OTCAD uses versus memory in use by the entire system.
The Average Memory Usage card displays information about the average memory usage trends in the system over the selected duration. The Maximum Usage card displays information about the maximum memory usage over the selected duration of time.
In the Visualization tab, do the following:
- In the Duration list, do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the memory usage. Ensure that the start date does not exceed the end date by more than 30 days.
-
In the View list, choose one of the values: Average, Minimum, or Maximum. Choose Average to view the average memory usage for every minute over the selected duration. Choose Minimum to view the minimum memory usage for every minute over the selected duration of time. Choose Maximum to view the maximum memory usage for every minute over the selected duration of time.
-
In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data. Every minute has one data point. For example, if you select 1 day, a total of 1440 data points are plotted on the graph with an axis interval of 1 minute.
In the List view tab, information about the minute-wise time, average memory usage, maximum memory usage, and minimum memory usage is displayed over the selected duration. Choose the Download button to export this information in a spreadsheet.
Disk I/O usage
Identify and address disk I/O bottlenecks for resource optimization, capacity planning, and so on.
The Disk I/O Usage card displays information about the disk I/O usage in the system over the selected duration. The Maximum Usage card displays information about the maximum disk I/O usage over the selected duration.
In the Visualization tab, do the following:
- In the Duration list, do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the disk I/O usage. Ensure that the start date does not exceed the end date by more than 30 days.
- In the View list, choose one of the values: Average, Minimum, or Maximum. Choose Average to view the average disk I/O usage for every minute over the selected duration. Choose Minimum to view the minimum disk I/O usage for every minute over the selected duration of time. Choose Maximum to view the maximum disk I/O usage for every minute over the selected duration.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data. Every minute has one data point. For example, if you select 1 day, a total of 1440 data points are plotted on the graph with an axis interval of 1 minute.
In the List view tab, information about the minute-wise time, average disk I/O usage, maximum disk I/O usage, and minimum disk I/O usage is displayed over the selected duration of time. Choose the Download button to export this information in a spreadsheet.
Scaling activity
Identify and analyze the potential cost impact due to scaling. View the utilization of additional compute units over the last 24 hours.
The Current Scale Instance card displays information about the current compute units in the system over the selected duration of time. The Maximum Scale card displays information about the maximum compute units over the selected duration of time.
In the Visualization tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the scaling activity. You can select a start date that is more than one month prior to the end date.
In the List view tab, information about the date and time, scale event, compute (units), trigger, and user ID of the person who performed the scaling is displayed. Choose Download to export this information in a spreadsheet.
Query insights
Expand Query insights in the Dashboards page. The following dashboards are displayed:
Queries by volume
Identify the query volumes over a duration to understand database performance, identify bottlenecks, and optimize resource utilization. Identify slow or frequently used queries that require optimization. If query volumes increase quickly, optimize the database infrastructure to handle the increased workload.
The Successful queries card displays information about queries that are successfully executed over the last 24 hours. The Queued queries card displays information about SQL queries that are queued for execution over the last 24 hours. The Query status card displays the number of SQL queries that completed successfully and failed over the last 24 hours.
In the Visualization tab, do the following.
- In the Duration list, do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries by volume. Ensure that the start date does not exceed the end date by more than 30 days.
-
In the View list, choose one of the values: Volume, Success rate, or Failure rate. Choose Volume to view the number of SQL queries executed over the selected duration. Choose Success rate to view the percentage of SQL queries that completed over the selected duration. Choose Failure rate to view the percentage of SQL queries that failed over the selected duration.
-
In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data at an interval of 5 minutes. Every minute has one data point. For example, if you select 1 day at an interval of 1 minute, a total of 1440 data points are plotted on the graph.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the SQL queries by volume. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the time, user name, request status, query, query label, query duration, memory acquired, rows processed, user type, client type, session ID, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Filter criteria
You can filter the dashboards in the Query insights folder based on certain criteria. Choose the Filter icon in each dashboard and filter data using the following criteria:
- Request status
- Expand the Request status list.
- Choose Completed to view the SQL queries that executed succesfully.
- Choose Failed to view the SQL queries that failed to execute.
- Duration
- Choose the required duration from the options available to view the SQL queries for the selected duration.
- Choose Custom and enter the minimum and maximum time in seconds or minutes to view the SQL queries for the selected duration.
- Client type - The client type is categorized by the client application. Different client types are displayed depending on the client application. A few examples of client types are given below:
- JDBC driver - Select this option to view SQL queries executed through the client type JDBC driver.
- ODBC driver - Select this option to view SQL queries executed through the client type ODBC driver.
- ADO.NET Driver - Select this option to view SQL queries for the client type ADO.NET driver.
- vsql - Select this option to view SQL queries for the client type vsql.
Note
Duration is the only applicable filter criteria for the Queries in queue by duration dashboard, while Duration and Client type are the only applicable filter criteria for Queries in queue and Requests by query dashboards.
- Rows processed
- Choose the required options to view SQL queries by the number of rows selected.
- Memory acquired
- Choose the required options to view the memory acquired for executing the SQL queries.
- Currently running
- Choose Yes to view the SQL queries that are currently running.
- System v/s user
- Choose System to view the SQL queries executed by the system.
- Choose User to view the SQL queries executed by specific users.
Queries by duration
Analyze the count of queries that executed over the last 24 hours. Slow-running queries hinder overall database efficiency. Analyze the count of queries in the given interval by execution time. This dashboard displays only those queries that took more than 10 minutes to execute over the last 24 hours.
The Average query duration card displays the mean time taken per query for a selected duration.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries by duration. Ensure that the start date does not exceed the end date by more than 30 days.
- In the View list, choose one of the values: Query Id, Query phase, Query duration, or Query type. Choose Query Id to view the SQL queries based on the query ID over the selected duration. Choose Query phase to view the SQL queries based on the query phase over the selected duration. Choose Query duration to view the SQL queries over specific durations of time. Choose Query type to view the SQL queries by type over the selected duration.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries by duration. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the time, user name, request status, query, query label, query duration, memory acquired, rows processed, user type, client type, session ID, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Queries by memory usage
Analyze the count of queries over the last 24 hours by memory consumption. When a query uses a large share of memory, it can hinder the execution of other simultaneous queries by restricting access to shared resources.
The Average memory usage card displays information about the average memory that is used by all the queries over the selected duration. The Average memory acquired card displays information about memory acquired for executing the SQL queries over the selected duration.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries by duration. Ensure that the start date does not exceed the end date by more than 30 days.
- In the View list, choose either Memory usage or Acquired memory. Choose Memory usage to view the average memory that is used by all the queries over the selected duration. Choose Acquired memory to view the memory acquired for executing the SQL queries over the selected duration.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data at an interval of 5 minutes. Every minute has one data point. For example, if you select 1 day at an interval of 1 minute, a total of 1440 data points are plotted on the graph.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries by memory usage. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the time, user name, request status, query, query label, query duration, memory acquired, rows processed, user type, client type, session ID, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Queries by user
Identify users who generate a high volume of queries over the last 24 hours. See how users work with the database and use these insights to plan resources and manage capacity effectively.
The Average query count card displays information about the total number of queries executed by different users over the selected duration.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries by duration. Ensure that the start date does not exceed the end date by more than 30 days.
- In the View list, choose one of the values: All users, Completed v/s failed, or System v/s user. Choose All users to view the total queries executed by each user over the selected duration. Choose Completed v/s failed to view a comparison of total queries completed v/s total queries failed over the selected duration. Choose System v/s user to view a comparison of total queries completed by all users v/s total queries executed by the system over the selected duration.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries by user. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the time, user name, request status, query, query label, query duration, memory acquired, rows processed, user type, client type, session ID, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Queries in queue
Identify queries awaiting allocation of system resources in the last 1 hour. Analyze query queue patterns and trends to plan future capacity needs. This ensures the system scales appropriately to handle anticipated growth in query volume.
The Average query duration card displays the mean time taken per query during the selected time range.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries in queue. Ensure that the start date does not exceed the end date by more than 30 days.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data at an interval of 5 minutes. Every minute has one data point. For example, if you select 1 day at an interval of 1 minute, a total of 1440 data points are plotted on the graph.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the query entry time, user name, query, query label, time in queue, priority, memory requested, pool name, client type, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Queries in queue by duration
Identify the queries that are queued for resource allocation. Use query queue patterns and trends to plan capacity. Scale the system as needed to manage expected growth in query volume.
The Average query duration card displays the mean time taken per query during the selected time range.
In the Visualization tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries in queue by duration. Ensure that the start date does not exceed the end date by more than 30 days.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue by duration. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the query entry time, user name, query, query label, time in queue, priority, memory requested, pool name, client type, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Requests by query type
Analyze request volumes for a specific duration to assess database performance, identify bottlenecks, and optimize resource use. Review request types to manage resources effectively and ensure the database handles varying loads without slowing down.
The Average query duration card displays the mean time taken per query during the selected time range over the last 24 hours.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the requests by query type. Ensure that the start date does not exceed the end date by more than 30 days.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data for 1 minute, 5 minutes, 15 minutes, 1 hour, or 1 day. By default, line charts are plotted for minute-wise data at an interval of 5 minutes. Every minute has one data point. For example, if you select 1 day at an interval of 1 minute, a total of 1440 data points are plotted on the graph.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the time, user name, request status, query, query label, query duration, memory acquired, rows processed, user type, client type, session ID, statement ID, and transaction ID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
System metrics
Expand System metrics in the Dashboards page. The following dashboards are displayed:
Connections
Identify the total database connections over the last 24 hours to understand the database performance, identify bottlenecks, and optimize resource utilization. Find sessions that are stuck or failing to troubleshoot quickly. Detect deadlocked sessions to fix them and enhance database performance.
The Average connection count card displays the average number of active database connections recorded over the selected duration. It provides a quick overview of connection trends, helping users monitor database usage and identify potential performance issues or unusual activity.
In the Visualization tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
-
Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries in the connections. Ensure that the start date does not exceed the end date by more than 30 days.
-
In the View list, choose Choose Open vs closed to view connections that are open vs connections that are closed. Choose Internal vs user to view connections that are internal to the system vs connections specific to users.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue by duration. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the connection time, connection duration, user name, client host name, session status, connection type, request, is currently running, request duration, transaction ID, statement ID, client type, client label, client OS, and client PID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Filter criteria
You can filter the Connections and Connections by duration dashboards in the System metrics folder based on certain criteria. Choose the Filter icon and filter data using the following criteria:
- connectionDuration
- Choose the required duration from the options available to view the SQL queries for the selected duration.
- Choose Custom and enter the minimum and maximum time in seconds or minutes to view the SQL queries for the selected duration.
- Connection status - Choose Open to view the connections that are open. Choose Closed to view the connections that are closed.
- Connection type
- Choose Internal to view the SQL queries executed by the system.
- Choose User to view the SQL queries executed by specific users.
- Client type - The client type is categorized by the client application. Different client types are displayed depending on the client application. A few examples of client types are given below:
- JDBC driver - Select this option to view SQL queries executed through the client type JDBC driver.
- ODBC driver - Select this option to view SQL queries executed through the client type ODBC driver.
- ADO.NET Driver - Select this option to view SQL queries for the client type ADO.NET driver.
- vsql - Select this option to view SQL queries for the client type vsql.
Connections by duration
Find the total number of database connections for the selected duration to assess database performance, locate bottlenecks, and optimize resource use.
The Average connection count card displays the average number of active database connections recorded over the selected duration. It provides a quick overview of connection trends, helping users monitor database usage and identify potential performance issues or unusual activity.
In the Visualization tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the SQL queries in the connections. Ensure that the start date does not exceed the end date by more than 30 days.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue by duration. Ensure that the start date does not exceed the end date by more than 30 days.
Information about the connection time, connection duration, user name, client host name, session status, connection type, request, is currently running, request duration, transaction ID, statement ID, client type, client label, client OS, and client PID is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Data storage
Detect trends in the amount of data stored within the database. Prevent over-provisioning and lower costs from unused storage. Optimize storage allocation to minimize waste and cost.
The Data storage card displays the average data stored for the selected duration over the last month.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the requests by query type. Ensure that the start date does not exceed the end date by more than 30 days.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data by days, months or quarters. By default, line charts are plotted for day-wise data.
In the List view tab, the date of data storage in the database and data storage (in GB and TB) is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Data transfer
Monitor outbound data transfer trends from the database to detect unusual activity. Identify and block unauthorized transfers to prevent data leakage. Timely investigation and mitigation helps you identify and fix irregular transfer patterns that indicate security breaches or unauthorized access.
The Average data transfer card displays the average data that is transferred for the selected duration over the last month.
In the Visualization tab, the Duration list is displayed.
- Do one of the following:
-
Choose the time duration. Choose one of the following:
-
Month to date (MTD)- the period from the beginning of the current calendar month up to but not including the current date. For example, if today is 10 November, MTD covers data from November 1 to November 9.
-
Last month
-
Quarter to date (QTD) - the period from the first day of the calendar quarter up to the current date. For example, if the current date is November 22, QTD covers data from October 1 to November 22.
-
Last quarter
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to visualize the requests by query type. Ensure that the start date does not exceed the end date by more than 30 days.
- In the Axis Interval list, choose the duration for which you need to view the data in the graph. Choose to view the data by days, months or quarters. By default, line charts are plotted for day-wise data.
In the List view tab, the Duration list is displayed. Do one of the following:
- Choose the time duration in minutes, hours, or days.
<--or-->
- Choose Custom. From the date pickers, select the start date and end date for which you need to view the queries in queue. Ensure that the start date does not exceed the end date by more than 30 days.
In the List view tab, the date of outbound data transfer from the database and data storage (in GB and TB) is displayed. Choose Download to export this information in a spreadsheet. Select the Copy icon to copy the query to the clipboard.
Active users
Identify active users logged into the database over the last 24 hours. Keep the system responsive and maintain a smooth user experience during peak loads. Identify abnormal patterns that may signal security threats. Confirm active sessions belong to authorized users to safeguard the database.
In the List view tab, the user name, super user, roles, and last login time is displayed. Choose Download to export this information in a spreadsheet.
