Administration

Amazing Getter Functions in Vertica – Part 1

1

Ever wondered, If it was easy for you to query database for essential parameters of the database without querying the system tables. Many facts you wanted to know about the database without typing the complete query is actually possible with Vertica. Vertica provides awesome get functions to make your life easy when working with Vertica.

This article will be a 2 part article covering almost 80% of get functions existing in Vertica with their examples.

Today we will go through 1st part of this article.

We will be seeing, how to check some parameters using small functions ( GET ) which can be very handy while working with Vertica

Topics at a Glance

  1. Session characteristics
  2. Compliance Status
  3. Maximum Column size that can be accomodated by Vertica
  4. Current Load balance policy of the database
  5. Cluster State
  6. Preferred k-safety value for your cluster
  7. current k-safety value of your database
  8. EPOCH Management details

1. Checking Session Characteristics

Starting with the most used command and not a function which can be very handy to know the session characteristics like locale, autocommit, datestyle, search_path, timezone, enabled role, available roles, etc.

Either you can see them all together like this:

Or if you want to check or query any characteristic of the session through some programming language you can use it like this:

This way you can know everything about your sessions using a two work command.

2. Checking Compliance Status

Compliance status, you don’t come across using this every now and then, but for the DBA’s this command can be a real life saver when checking the compliance policy.

Compliance checking lets you monitor the compliance of your database with the availed License size for your database.

Use it like this:

3. Checking Maximum Column size that can be accomodated by Vertica columns.

3a. Checking maximum column size for a regular data type column

If you need to check what number of characters can a regular column intake in Vertica use function get_max_attr_size()

Use it like this:

This shows Vertica can intake 65000 characters in a regular column.
3b. Checking maximum column size for a LONG data type column
The numbers are a bit different when it comes to LONG data type columns. This is the maximum number of characters,
thatyou can input to Vertica. Use functions get_max_long_attr_size()
Use it like this:

This means either 32000000 characters or 32 MB of data can be loaded into a single column row in Vertica

4. Checking Current Load balance policy of the database.

If you want to check the current load balance policy of the database quickly without querying the DATABASES table.
use function get_load_balance_policy()
Use it like this:

This shows the current load balance policy of the database.

5. Checking nodes and cluster state

Checking the nodes state and cluster state is very easy with function get_cluster_state_summary().

This comes very handy when you want to know the state of nodes and don’t want to write a query to nodes table.

Use it like this:

6. Checking the Preferred k-safety value for your cluster.
Vertica also recommends you the k-safety value for your cluster using this function get_preferred_ksafe()
Use it like this:

7. Checking the current k-safety value of your database

If you want to view the current k-safety value of your database without wasting time on querying DATABASES table, use this function get_design_ksafe().

This functions shows you the current k-safety value:

Use it like this:

8. Checking EPOCH related values.
For checking
current epoch number of database – get_current_epoch()
ahm epoch number of database – get_ahm_epoch()
time of ahm epoch number – get_ahm_time()
last good epoch number – get_last_good_epoch()
Use it like this:

These are some of the usefull getter functions in Vertica .

Hope you all find it interesting, stay tuned for part 2 of this article to explore more GET Functions.

About the author / 

Navin Chakraborty

Navin is a Senior Software Engineer with 4 years of experience working with Vertica. He author's his own blog at Vertica-howto and contributes regularly on vertica-forums.com.

1 Comment

  1. Rathinakumar March 10, 2015 at 2:14 AM -  Reply

    When is Part 2 coming up ?

Leave a Reply

Upcoming Events

  • No upcoming events
AEC v1.0.4

Subscribe to Blog via Email

Enter your email address to subscribe and receive notifications of new posts by email.

Read more use cases here.

Notice

This site is not affiliated, endorsed or associated with HPE Vertica. This site makes no claims on ownership of trademark rights. The author contributions on this site are licensed under CC BY-SA 3.0 with attribution required.
%d bloggers like this: