For Data Insight 5.0 or later, DQL supports the use of SQLite queries to further manipulate the information retrieved by a DQL query. You can use this advanced facility to extend the capabilities of DQL to get more control over the results you are looking for. For example, you may want to further apply filters to narrow down the results of the original DQL query or take advantage of host of SQLite functions to perform advanced information retrieval. Till Data Insight 5.0, such customizations were possible only outside of the Data Insight Management Console, using SQLite scripts. To perform such customizations administrators needed access to the Management Server. With the Advanced Options feature added in 5.0, users can perform all data manipulations directly from within the Data Insight Console.
For example, if you want to compare two fields from a DQL table, you can do that comparison using Advanced Options through an SQLite query. The following example helps you to find out stub files, for example, placeholders for archived files etc., when the on-disk size is lesser than the logical-size.
DQL Query:
FROM path
GET absname,
size AS size_bytes,
odsize
IF odsize=4096 /*Assuming stub-size equal to 4KB. For 64KB, use 65536. Change condition if required.*/
AND isdeleted = 0
and type = "file"
AND
path.msu.name = "<Share Name>"
sortby odsize desc
Advanced SQL Query:
CREATE TABLE Stub_Files(absname TEXT, Size_MB INTEGER, On_Disk_Size_KB);
insert into Stub_Files
select absname, round(size_bytes /1024.0/1024.0, 2), round(odsize/1024.0,2) from path
where size_bytes > odsize
order by size_bytes desc;
Note: If you need the output from a SQL query to be a part of the final output (DB or CSV format), you must create a table and insert the result of your query in that table. This is demonstrated in the example above.
To use advanced DQL option, from the Management Console, navigate to Create DQL Report > Query. After entering your DQL query, click the Advanced Options tab. Click Run SQL commands on generated DQL output database. Enter your SQLite query in the space provided. Click Save and Run.
For more information on Advanced Options for DQL and how to use the options, refer to the Veritas Data Insight User’s Guide.
To explore additional resources for SQLite functions, you can refer the following
SQLite links:
https://www.sqlite.org/lang_corefunc.html
https://www.sqlite.org/lang_aggfunc.html
https://www.sqlite.org/lang_datefunc.html
