Vertica Blog

Vertica Blog

DBadmin

Hand with suit cuff flips shiny gold coin

Coin Flip User Defined SQL Function

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! Helpful Links: Have fun!
Quick Tip - blue button

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

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

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: Have...
Vintage businessman concept wearing futuristic helmet at office

Track License Usage Over Time

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: Have fun!
Tips and Tricks Orange Button

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

The MANAGE_EPOCH function can be called to quickly display the Current Epoch (CE) and Ancient History Mark (AHM) Epochs! Helpful Links: Have fun!
Helpful Tips message on post-it note

Enable All Granted Roles on Login

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

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: Have fun!
Copy data

Generating Recursive COPY Commands

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

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

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

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

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...