Vertica Blog

Vertica Blog

DBadmin

Hand with suit cuff flips shiny gold coin

Coin Flip User Defined SQL Function

James Knicely, Vertica Field Chief Technologist
July 2, 2019

In the past when my wife and I couldn’t decide which of two restaurants to go to grab dinner, we opened the Coin Flip app on one of our phones to let it decide. Now I let Vertica choose for us via a simple User Defined SQL Function! dbadmin=> SELECT coin_flip, COUNT(*) FROM (SELECT coin_flip()...
Quick Tip - blue button

Non-DBADMIN Access to System Tables (Table by Table)

James Knicely, Vertica Field Chief Technologist
July 1, 2019

The DBADMIN can assign a delegate the SYSMONITOR role to grant full access to system tables without granting full DBADMIN access. The SYSMONITOR role applies to all tables that have the flag IS_MONITORABLE set to true in the SYSTEM_TABLES system table. What if I want a non-DBADMIN user to have full access to only a...
Compass rose with true north pointing to "Helpful Tips" text

Tracking VBR Invocations

James Knicely, Vertica Field Chief Technologist
June 28, 2019

The Vertica Backup and Recovery tool (vbr) allows you to back up and restore either the full database, or one or more schema and table objects of interest. You can also copy a cluster and list backups you created previously. You can view vbr invocations by querying the DC_VBR_INVOCATIONS data collector table! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/BackupRestore/VBRUtilityReference.htm...
Vintage businessman concept wearing futuristic helmet at office

Track License Usage Over Time

James Knicely, Vertica Field Chief Technologist
June 28, 2019

The LICENSE_AUDITS system table lists the results of Vertica's license automatic compliance audits. You can query it to track your Vertica license usage over time! Looks like I’m going to need add some new Vertica license soon! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/LICENSE_AUDITS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Licensing/CalculatingTheDatabaseSize.htm Have fun!
Tips and Tricks Orange Button

A Quick Way to View the Current and Ancient History Mark Epochs

James Knicely, Vertica Field Chief Technologist
June 20, 2019

The MANAGE_EPOCH function can be called to quickly display the Current Epoch (CE) and Ancient History Mark (AHM) Epochs! Helpful Links: https://www.vertica.com/kb/Understanding-Vertica-Epochs/Content/BestPractices/Understanding-Vertica-Epochs.htm Have fun!
Helpful Tips message on post-it note

Enable All Granted Roles on Login

James Knicely, Vertica Field Chief Technologist
June 20, 2019

You can automatically enable roles for users in two ways: Enable roles for individual users on login (either with the SET ROLE command or default roles). Enable all roles for all users on login (at the database level via the EnableAllRolesOnLogin parameter). The second option is great (and easy) if you want all users to...
Business card that says Expert Tips,

Generate DDL for Changed Configuration Parameters

James Knicely, Vertica Field Chief Technologist
June 13, 2019

The CONFIGURATION_CHANGES system table records the change history of system configuration parameters. If you want to apply all of the changes to a different Vertica database, you can generate the necessary DDL commands to do that! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_PARAMETERS.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/CONFIGURATION_CHANGES.htm Have fun!
Copy data

Generating Recursive COPY Commands

James Knicely, Vertica Field Chief Technologist
June 13, 2019

Recently a friend mentioned she wanted to load all of the files using the COPY command, where all the data files were located in many, many sub-directories. You can load all of the files in a single command by looping through each of the sub-directories. I have the following files in sub-directories under the parent...
Counting

Table Row Counts by Schema

James Knicely, Vertica Field Chief Technologist
June 13, 2019

I had a colleague ask today how she could generate a report showing the total table row counts by schema. This should be easy, right? After all, the PROJECTION_STORAGE system table has a ROW_COUNT column. But it turns out it’s a little complex considering there are segmented and unsegmented projections and delete vectors in Vertica....
Spread Daemon Timeout

Set the Spread Daemon Timeout Back to the Default Value

James Knicely, Vertica Field Chief Technologist
May 24, 2019

The default value for the Spread Daemon timeout is 8 seconds when the IP addresses of all cluster nodes start with the same two bytes. If all the IP addresses do not start with the same two bytes, then the default is 25 seconds. When you know your network or nodes may be unable to...
Spread Daemon Timeout

Monitor the Current Value of Node Spread Daemon Timeouts

James Knicely, Vertica Field Chief Technologist
May 24, 2019

When you know your network or nodes may be unable to respond for a specific amount of time due to network delays or temporary pauses of a VM, you can increase the spread timeout period to longer than this time using the SET_SPREAD_OPTION function (available starting in Vertica 9.2.1-0). You can view the current value...
Spread Daemon Timeout

Min and Max Values for Spread Daemon Timeout

James Knicely, Vertica Field Chief Technologist
May 24, 2019

When you know your network or nodes may be unable to respond for a specific amount of time due to network delays or temporary pauses of a VM, you can increase the spread timeout period to longer than this time using the SET_SPREAD_OPTION function (available starting in Vertica 9.2.1-0). You might be wondering what the...