Myrmex can consume SQL files which can then be executed by agents to produce SQL based reports or metrics.

Location in Git

SQL files can be placed under any directory structure under your [myrmex assets root]/sql directory. Myrmex will scan and index this directory tree so that you can search for SQL files using any part of their file path. Organize these files under sensible paths to make it easier/intuitive for users to locate what they are looking for:

myrmex/
└── assets
    └── sql
        └── oracle
            └── utilization
                └── disk
                    ├── tables.sql        # by table disk utilization
                    └── tablespaces.sql   # by tablespace disk utilization 

Given the above directory structure, you can search using terms like:

• tables which will return
  • sql/oracle/utilization/disk/tables.sql
• disk which will return
  • sql/oracle/utilization/disk/tables.sql
  • sql/oracle/utilization/disk/tablespaces.sql
• oracle/utilization/disk which will return
  • sql/oracle/utilization/disk/tables.sql
  • sql/oracle/utilization/disk/tablespaces.sql

Supported SQL queries

• select statements only

• Single statements only

• Must alias calculated columns

• Does not support bind variables

• Oracle dialect statements must escape any bind variable placeholder (:) character literals with ::.
  For example, convert

  TO_CHAR(sysdate, 'MM/DD/YYYY HH12:MI:SS AM')
  

  to

  TO_CHAR(sysdate, 'MM/DD/YYYY HH12::MI::SS AM')
  

Best practices

• No need to terminate statements with ;, /, go etc
• No need to use order by clauses in your statements. The SQL Reports system supports explicit and persistent ordering for sql results
• You should not convert date or timestamp columns to strings. The system automatically formats such columns before rendering to UI. Additionally, if converted to strings then they might loose their natural sort order depending on the applied format.

Documentation

The last comment block above an SQL statement will be the documentation for the SQL file. The first paragraph of this block will be the short documentation for the SQL file which will appear in the UI when searching for files.

/* copyright 2019 Arisant LLC */

/*
Returns the KB size for the tablespaces in a database.

Also returns the allocation block size for a tablespace along with the a boolean flag to indicate that the tablespace
uses a 'big file'
*/
select tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE/1024 as BLOCK_SIZE, tbs.BIGFILE, sum(nvl(df.BYTES, 0))/1024 as SIZE_KB
from DBA_TABLESPACES tbs 
left outer join DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME
group by tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, tbs.BIGFILE

Alternatively, this will also have the same effect

-- copyright 2019 Arisant LLC

---
--- Returns the KB size for the tablespaces in a database.
---
--- Also returns the allocation block size for a tablespace along with the a boolean flag to indicate that the tablespace
--- uses a 'big file'
select tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE/1024 as BLOCK_SIZE, tbs.BIGFILE, sum(nvl(df.BYTES, 0))/1024 as SIZE_KB
from DBA_TABLESPACES tbs 
left outer join DBA_DATA_FILES df on tbs.TABLESPACE_NAME = df.TABLESPACE_NAME
group by tbs.TABLESPACE_NAME, tbs.BLOCK_SIZE, tbs.BIGFILE

From either one of the above SQL files the short documentation will be

Returns the KB size for the tablespaces in a database

While the full documentation will be

Returns the KB size for the tablespaces in a database. Also returns the allocation block size for a tablespace along with the a boolean flag to indicate that the tablespace uses a 'big file'