Vertica Blog

Vertica Blog

VSQL

Photo of actual roadrunner running on a road showing yellow stripe in the middle

Have VSQL Beep Upon Command Completion

Jim Knicely, Vertica Principal Solution Architect
September 6, 2019

There are a lot of cool command line options available to VSQL. One of my favorites is the –b option which causes Vertica to “beep” when a command completes. This is very useful if you are running a very long sequence of SQL commands via a script. You can go off and do other things...
hands sweeping into a dustpan

Easy Development Schema Cleanup (i.e., Have Vertica Drop a Bunch of Tables for You)

Jim Knicely, Vertica Principal Solution Architect
September 5, 2019

On a development database, I have a lot of tables in the PUBLIC schema having similar names starting with “test_”. I’d like to drop all of these tables quickly. To do that, I can have Vertica generate the DROP commands and then execute them for me! P.S. Be careful with this command! Make sure when...
Helpful Tips in blue text with magnifying glass

Investigate Data Collector Table Data Produced by Scrutinize

Jim Knicely, Vertica Principal Solution Architect
September 5, 2019

The diagnostics tool scrutinize collects a broad range of information from a Vertica cluster. Part of the output from scrutinize includes data collector table data. Once extracted, we can easily load the data from a data collector table file into a Vertica Flex Table for analysis! Let’s take a look at the data from the...
Cow peering around edge of frame with blue sky and clouds in shape of a comic book thought bubble.

Make a Cow Display Query Results in VSQL

Jim Knicely, Vertica Principal Solution Architect
August 19, 2019

I used to have fun with the amusing old Linux command Cowsay. It inserts any input into a word bubble and draws an ASCII cow to talk to you. You can make the cow display your query results in VSQL! Helpful Links: Have fun!
Business card that says Expert Tips,

Stop a Stubborn Query that Won’t Cancel

Jim Knicely, Vertica Principal Solution Architect
August 19, 2019

Sometimes a running SQL statement hangs and cannot be stopped using the CLOSE_SESSION or INTERRUPT_STATEMENT function. To stop it, first grab the culprit’s SESSION_ID, TRANSACTION_ID, and STATEMENT_ID from the QUERY_REQUESTS system table, then you can move it to a resource pool that has no resources! After the move and subsequent re-plan, the query will “usually”...
Compass rose with true north pointing to "Helpful Tips" text

Change Your User Password in VSQL with a Meta-Command

Jim Knicely, Vertica Principal Solution Architect
August 14, 2019

The \password VSQL meta-command can be used to change your database user’s password! The admin user can change the database password for any user: Helpful Link: Have fun!
Hand holding old-fashioned brass balance scale on a black background

View the History of Connection Load Balance Operations

Jim Knicely, Vertica Principal Solution Architect
August 9, 2019

The Data Collector table DC_LOAD_BALANCE_OPERATIONS tracks the history of connection load balance operations. Helpful Links: Have fun!
Hand holding old-fashioned brass balance scale on a black background

Load Balance Older Clients (That You Can’t Upgrade for Some Weird Reason)

Jim Knicely, Vertica Principal Solution Architect
August 8, 2019

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as . To load balance vsql you can use the - -enable-connection-load-balance or -C command line options. In really old versions of vsql those options did not exist. How do you load balance connections from those...
Hand holding old-fashioned brass balance scale on a black background

Testing the Native Load Balancer

Jim Knicely, Vertica Principal Solution Architect
August 7, 2019

Native connection load balancing is a feature built into the Vertica Analytic Database server and client libraries as well as . The RUN_LOAD_BALANCE_POLICY function returns a string formatted as address:port that results from running the current Native Load Balancer policy. It’s a great tool for testing after enabling a new Load Balance Policy! Because the...
Disk usage, traffic, and other usage concerns

Diving into Disk Usage

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
August 6, 2019

Would you like to know how much disk space Vertica is using as it runs? This could be useful for capacity planning, monitoring trends, or debugging. Here are some ways to follow disk usage trends and also look at temporary events like Tuple Mover and Join Spills: Helpful Link: Have fun!
Vintage businessman concept wearing futuristic helmet at office

Find and Fix Issues from Vertica Query Events

Bryan Herger, Vertica Big Data Solution Architect at Micro Focus
August 5, 2019

Vertica offers tools like the Workload Analyzer in Management Console (MC) to tune up a Vertica Cluster, but there's a simple way to find and fix issues that Vertica observes and records if you aren't using MC. The query_events table captures optimization issues and suggests fixes. Let's take a look at my demo cluster, checking...
Busy traffic jam

Find the Busiest Hour in the Day for I/O

Jim Knicely, Vertica Principal Solution Architect
August 2, 2019

The IO_USAGE system table provides disk I/O bandwidth usage history for the system. You can query it to find out interesting IO statistics like which hour of the day has the most IO usage on your Vertica cluster! Helpful Link: Have fun!