Vertica Blog

Vertica Blog

DBadmin

Restarting a Downed Node: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 6, 2018

I’m a big fan of scripting with admintools which provides us with many database tools. One of those awesome tools, that I just became familiar with, is command_start. It allows us to start a downed node, with one caveat: you have to run the command on the node that is down. Here I’ll use the...

Encode Projection Columns with Zstandard Compression: Quick Tip

James Knicely, Vertica Field Chief Technologist
December 3, 2018

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types! Helpful Link: https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/encoding-type.htm Have fun!

Display Tables Referenced by a View: Quick Tip

Phil Molea, Sr. Information Developer, Vertica
November 29, 2018

Jim Knicely authored this tip. Vertica 9.2 introduces the new VIEW_TABLES system table that shows details about view-related dependencies, including the table that reference a view, its schema, and owner. Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/CATALOG/VIEW_TABLES.htm Have fun!
Three 3D arrows, different colors pointing in different directions

Calculate the Catalog Size in Memory on Each Node: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 28, 2018

The RESOURCE_POOL_STATUS system table provides the current state of built-in and user-defined resource pools on each node, including memory usage. The METADATA built-in pool tracks memory allocated for catalog data and storage data structures. Knowing those two facts, we can calculate catalog memory usage on all nodes by querying the system table RESOURCE_POOL_STATUS for the...

Labeling Queries: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 28, 2018

To quickly identify queries for profiling and debugging purposes, include the LABEL hint. Later you can search the QUERY_REQUESTS and QUERY_PROFILES systems table for the label! Helpful Links: https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Profiling/LabelQueries.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATETABLE.htm Have fun!

Rejected Data Table Row Number: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 15, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause saves rejected data into a table. The rejected data table includes an informative column called ROW_NUMBER where its value indicates the rejected row number from the input file. Be aware that when a COPY encounters an empty line while loading...
Database Server Room

Reload Data from a Rejected Data Table: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 14, 2018

When running a COPY command, using the REJECTED DATA parameter with the AS TABLE clause, will save rejected data into a table. If you realize there is a modification to the COPY command that will allow those rejected records to load successfully, you can re-run the updated COPY command against the rejected data in the...

Display Canceled Queries: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 8, 2018

We can cancel a long running query in vsql by typing CTRL+C. The data collector table DC_CANCELS tracks queries that were stopped in this manner. Now I will run a simple SQL statement and hit CTRL+C after waiting for 2 seconds: Helpful Link: https://www.vertica.com/docs/latest/HTML/index.htm#Authoring/Glossary/DataCollector.htm Have fun!

Calculate Request Queue Length: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 7, 2018

The RESOURCE_ACQUISITIONS system table retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session. From this system table, you can calculate how long a request was queued in a resource pool before acquiring the resources it...
Three 3D arrows, different colors pointing in different directions

Master Blog Series: Vertica Database Administrators

Soniya Shah, Information Developer
November 5, 2018

Are you a database administrator looking for ways to get the most from your Vertica database? If so, this post is for you. You’re already familiar with the technicalities of Vertica – the Tuple Mover, deletes, projections, and more. If you’re looking to get started, check out our master blog for new users, Master Blog...

Re-Compute a Table Column’s Default Value Immediately: Quick Tip

James Knicely, Vertica Field Chief Technologist
November 1, 2018

Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. That DEFAULT expression can be derived from another column in the same table! When you update the value in a base column, you will need to re-compute the value in your derived column (the...

DbVisualizer Free for Vertica Distribution Updates

Phil Molea, Sr. Information Developer, Vertica
October 8, 2018

Stephen Crossman authored this post Recently, there have been some changes in how DbVisualizer Free for Vertica is distributed. Previously, there were standard DbVisualizer Free and Pro Edition distributions available on the DbVisualizer web site, and there was a special DbVisualizer Free for Vertica distribution available on the Vertica Marketplace. Now, in an effort to...