James Knicely, Vertica Field Chief Technologist
March 8, 2019
To switch the sign of a number you can multiply it by -1. Or you can use the Vertica built-in function NUMERIC_UM to do it for you!
Vertica Blog
Vertica Blog
DBadmin
Are These Two String Values Like Each Other?
James Knicely, Vertica Field Chief Technologist
March 7, 2019
The Vertica built-in function LIKE is used to determine if two strings are like one another, while the NLIKE function is used to determine if two strings are not like one another. You can even include wildcard symbols!
Identify a Table’s Primary Key Columns
James Knicely, Vertica Field Chief Technologist
March 7, 2019
The Vertica system table PRIMARY_KEYS lists the columns in a table's primary key. You can use the LISTAGG function to group the columns of a composite key into a single record! Helpful Link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/PRIMARY_KEYS.htm
Revoke Access to Multiple System Tables from Multiple Users with a New Role
James Knicely, Vertica Field Chief Technologist
March 1, 2019
Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system's resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical...
Revoke Access From Just One System Table
James Knicely, Vertica Field Chief Technologist
February 28, 2019
Vertica provides an API (application programming interface) for monitoring various features and functions within a database in the form of system tables. These tables provide a robust, stable set of views that let you monitor information about your system's resources, background processes, workload, and performance, allowing you to more efficiently profile, diagnose, and view historical...
Assign All Users’ Granted Roles as Default Roles
James Knicely, Vertica Field Chief Technologist
February 26, 2019
By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session. You have to assign one or more of a user’s granted roles as default roles so that they are enabled when a user logs on. You do this with the ALTER USER … DEFAULT ROLE command....
View Tuple Mover Operation Run-time Intervals
James Knicely, Vertica Field Chief Technologist
February 26, 2019
Vertica’s Tuple Mover manages WOS and ROS data storage. To do that, the Tuple Mover performs two operations: Moveout: Moves data from WOS to ROS. Mergeout: Combines small ROS containers into larger ones and purges deleted data. The Tuple Mover operations are controlled by a set of configurable parameters. To quickly view the relevant parameter’s...
Reset the ROUNDROBIN Load Balancing Counter: Quick Tip
James Knicely, Vertica Field Chief Technologist
February 12, 2019
The ROUNDROBIN load balancing scheme chooses the next host from a circular list of hosts in the cluster that are up. You can use the RESET_LOAD_BALANCE_POLICY function to reset the counter each host in the cluster maintains to track which host it will refer a client to when the native connection load balancing scheme is...
Random Host Load Balancing: Quick Tip
James Knicely, Vertica Field Chief Technologist
February 11, 2019
Native connection load balancing is a feature built into the Vertica server and client libraries that helps spread the CPU and memory overhead caused by client connections across the hosts in the database. It can prevent unequal distribution of client connections among hosts in the cluster. Typically folks implement the ROUNDROBIN load balancing scheme which...
Finding All Columns Storing Some Value: Quick Tip
James Knicely, Vertica Field Chief Technologist
February 4, 2019
A long time ago a database developer asked me how she could produce a list of all table columns in the database which store a given value. Note that she was in the process of normalizing tables and wanted to find every fact table that referenced a particular string value to replace it with a...
Try Vertica in a Virtual Machine
Kathy Taylor, Information Technology Developer, Vertica
January 24, 2019
Curious about Vertica? Check out the Vertica Community Edition VM! It’s a free, quick, and an easy hands-on introduction. In the VM, you’ll find everything up and running. You won’t have any setup to do. You’ll find a single-node installation of Vertica Community Edition with the VMart example database, the Vertica client tools, and a...
Determining the Current K-Safety: Quick Tip
James Knicely, Vertica Field Chief Technologist
January 15, 2019
K-safety sets the fault tolerance in your Vertica database cluster. The value K represents the number of times the data in the database cluster is replicated. These replicas allow other nodes to take over query processing for any failed nodes. You can view the current K-Safety of a Vertica database in several ways. Helpful Links:...