Page tree
Skip to end of metadata
Go to start of metadata

 

Overview

This page provides some simple queries against the available repository tables and provides suggestions for writing repository queries.

Using SELECT (*) in a programmatic query (embedded in a program) is not guaranteed to return columns in the same order all the time. The following list applies to cases when the result of the query is parsed programmatically or used in an automated calculation. Although a few of the sample queries use the asterisk wildcard (*) to select all columns in the table for simplicity, generally using the asterisk (*) wildcard is not desirable because:

  • It is easier to extract meaningful information by selecting individual fields relevant to the question being asked.
  • The number and definitions of columns in a table might change in future versions.
  • Column name changes can cause problems in programs.

If you are not parsing the result data, using select(*) is acceptable.

  • To increase database concurrency, tables use the “FOR READ UNCOMMITTED ACCESS” option. If you are creating your own views, please include this option in the view creation statements.

Get the Number of Rows in a Table

Each of these queries gets the number of rows in a specific table:

select count(*) from TRAFODION.”_REPOS_”.METRIC_QUERY_TABLE;
select count(*) from TRAFODION.”_REPOS_”.METRIC_SESSION_TABLE;

Retrieve a Specified Number of Rows from a Table

This query retrieves the ten rows most recently added to a table.

SELECT [first 10] (EXEC_START_UTC_TS) AS EXEC_START_UTC_TS
,(QUERY_ELAPSED_TIME) AS QUERY_ELAPSED_TIME
,(CLIENT_NAME) AS CLIENT_NAME
from TRAFODION.”_REPOS_”.METRIC_QUERY_TABLE
ORDER BY EXEC_START_UTC_TS DESC FOR READ UNCOMMITTED ACCESS;

Results from this query:

EXEC_START_UTC_TS                          QUERY_ELAPSED_TIME    CLIENT_NAME                                    
---------------------------------------    --------------------- -------------- 
2015-01-23 20:13:52.168465                             596928513 jsmith                                           
2015-01-23 19:54:59.781856                             596062217 jsmith                                           
2015-01-23 18:56:29.161112                             596850565 jsmith                                           
2015-01-23 05:00:53.195720                              25763720 jsmith                                           
2015-01-23 05:00:53.183254                              25654012 jsmith                                           
2015-01-23 05:00:53.159172                              26889044 jsmith                                           
2015-01-23 05:00:53.145655                              26130489 jsmith                                           
2015-01-23 05:00:53.131733                              29286381 jsmith                                           
2015-01-23 05:00:53.121666                              29719566 jsmith                                           
2015-01-23 05:00:53.110709                              28609887 jsmith                                           

--- 10 row(s) selected.

Retrieve Statistics for Completed Queries

This query retrieves information for the ten most recent queries that completed normally. You could enhance this query to filter for time ranges, user IDs, or any other attribute.

The data following the query shows values that might be returned in a single record.

SELECT [first 10] (EXEC_START_UTC_TS) AS EXEC_START_UTC_TS
,(EXEC_END_UTC_TS) AS EXEC_END_UTC_TS
,(QUERY_ELAPSED_TIME) AS QUERY_ELAPSED_TIME
,(CLIENT_NAME) AS CLIENT_NAME
from TRAFODION.”_REPOS_”.METRIC_QUERY_TABLE
where EXEC_END_UTC_TS IS NOT NULL
ORDER BY EXEC_START_UTC_TS DESC FOR READ UNCOMMITTED ACCESS;

Results from this query:

EXEC_START_UTC_TS          EXEC_END_UTC_TS             QUERY_ELAPSED_TIME      CLIENT_NAME                                                                                                        
-------------------------- --------------------------  ----------------------- ------------------ 
2015-01-23 20:13:52.168465 2015-01-23 20:23:49.096973                596928513 jsmith                                                                                                               
2015-01-23 19:54:59.781856 2015-01-23 20:04:55.844068                596062217 jsmith                                                                                                               
2015-01-23 18:56:29.161112 2015-01-23 19:06:26.011672                596850565 jsmith                                                                                                               
2015-01-23 02:50:20.864777 2015-01-23 02:50:40.992508                 20127734 jsmith                                                                                                              
2015-01-23 02:50:20.765243 2015-01-23 02:50:41.572032                 20806793 jsmith                                                                                                              
2015-01-23 02:50:20.700952 2015-01-23 02:50:41.265200                 20564251 jsmith                                                                                                               
2015-01-23 02:50:20.669214 2015-01-23 02:50:41.565020                 20895809 jsmith                                                                                                               
2015-01-23 02:50:20.637598 2015-01-23 02:50:41.313594                 20675999 jsmith                                                                                                              
2015-01-23 02:50:20.530423 2015-01-23 02:50:40.805979                 20275561 jsmith                                                                                                              
2015-01-23 02:50:20.502155 2015-01-23 02:50:40.519456                 20017303 jsmith                                                                                                               

--- 10 row(s) selected.

Count Completed Queries, According to Specified Criteria

The following queries all return the number of completed queries, but they break down the number in different ways.

Completed Queries Executed on a System in the Last 24 Hours

SELECT DISTINCT (CAST(EXEC_START_UTC_TS AS DATE)) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MAX_START_TIME,
MIN(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MIN_START_TIME
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;

Results from this query:

START_DATE NUM_OF_QUERIES       MAX_START_TIME  MIN_START_TIME
---------- -------------------- --------------- ---------------
2015-01-23                  123 20:13:52.168465 18:56:29.161112

--- 1 row(s) selected.

Completed Queries Executed on a System in the Previous Two Days

To count queries that executed from one midnight to the next, instead of within a 24 hour period relative to the current time, the following query uses the DATE_TRUNC function in the SQL WHERE clause.

SELECT DISTINCT (CAST(EXEC_START_UTC_TS AS DATE)) AS START_DATE,
COUNT(*) AS NUM_OF_QUERIES,
MAX(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MAX_START_TIME,
MIN(CAST(EXEC_START_UTC_TS AS TIME(6))) AS MIN_START_TIME
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= DATE_TRUNC('DAY', CURRENT - INTERVAL '1' DAY)
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY 1
FOR READ UNCOMMITTED ACCESS;

Results from this query:

START_DATE NUM_OF_QUERIES       MAX_START_TIME  MIN_START_TIME
---------- -------------------- --------------- ---------------
2015-01-23                  242 20:13:52.168465 00:00:27.139447

--- 1 row(s) selected.

Queries within the Past 24 Hours, Grouped by Client Name

This example indicates how many queries different clients ran within a 24-hour period.

SELECT client_name, COUNT(*) AS NUM_OF_QUERIES
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY client_name
ORDER BY client_name
FOR READ UNCOMMITTED ACCESS;

Results from this query:

CLIENT_NAME   NUM_OF_QUERIES
------------  --------------
MCHAN                     88
jsmith                    14
--- 2 row(s) selected.

Queries within the Past 24 Hours, Grouped by Client Name and Including Average and Maximum Elapsed Time

SELECT client_name,
COUNT(*) AS num_of_queries,
AVG(query_elapsed_time)/1000 AS avg_qry_elapsed_msec,
MAX(query_elapsed_time)/1000 AS max_qry_elapsed_msec
FROM TRAFODION."_REPOS_".METRIC_QUERY_TABLE
WHERE EXEC_START_UTC_TS <= CURRENT
AND EXEC_START_UTC_TS >= CURRENT - INTERVAL '1' DAY
AND EXEC_END_UTC_TS IS NOT NULL
GROUP BY client_name
ORDER BY client_name
FOR READ UNCOMMITTED ACCESS;

Results from this query:

CLIENT_NAME NUM_OF_QUERIES AVG_QRY_ELAPSED_MSEC MAX_QRY_ELAPSED_MSEC
----------- -------------- -------------------- --------------------------
MCHAN                   88                36095 73486
jsmith                  14                  306 1200
--- 2 row(s) selected.

Formatting Techniques to Enhance Readability of Query Output

Often the typical data in a column is much narrower than the maximum defined column size. For example, the SESSION_ID column is defined as CHARACTER(108), actual values in those columns are usually much shorter. Many query tools support automatic adjustment of column size for viewing results, but in some cases, it is necessary to adjust the size of the columns returned from the query to improve readability of the results. Trafodion SQL supports several string manipulation functions. Among these, the LEFT function is useful for simple column size reduction. In addition to string functions, use of CAST is sometimes helpful for modifying the appearance or size of numeric fields.

For example, the following query run from TrafCI produces results that can be difficult to read:

select [first 10]
client_name,
count(*) as query_count
from TRAFODION."_REPOS_".METRIC_QUERY_TABLE
where EXEC_END_UTC_TS IS NOT NULL
group by client_name
order by client_name;

CLIENT_NAME
QUERY_COUNT
------------------------------------------------------------------------------------------------------------
FMASTROPOL
3
JHANSEN13
127
SCOX1
687
THE0101
79
THE0102
2
THE0103

Minor formatting of the columns can yield a more readable result:

select [first 10]
left (client_name, 25) as client_name1,
count(*) as query_count
from TRAFODION."_REPOS_".METRIC_QUERY_TABLE
where EXEC_END_UTC_TS IS NOT NULL
group by 1
order by 1;
CLIENT_NAME              QUERY_COUNT
------------------------ ------------
FMASTROPOL                          3
JHANSEN13                         127
SCOX1                             688
THE0101                            79
THE0102                             2
THE0103                             2

Return to the Trafodion Manageability page.

  • No labels