Managing licenses

• 1: Obtaining a license key file
• 2: Understanding Vertica licenses
• 3: Installing or upgrading a license key
• 3.1: New Vertica license installations
• 3.2: Vertica license changes
• 4: Viewing your license status
• 5: Viewing license compliance for Hadoop file formats
• 6: Moving a cloud installation from by the hour (BTH) to bring your own license (BYOL)
• 7: Auditing database size
• 8: Monitoring database size for license compliance
• 9: Managing license warnings and limits
• 10: Exporting license audit results to CSV

1 - Obtaining a license key file

Follow these steps to obtain a license key file:

1. Log in to the Software Entitlement Key site using your passport login information. If you do not have a passport login, create one.

2. On the Request Access page, enter your order number and select a role.

3. Enter your request access reasoning.

4. Click Submit.

5. After your request is approved, you will receive a confirmation email. On the site, click the Entitlements tab to see your Vertica software.

6. Under the Action tab, click Activate. You may select more than one product.

7. The License Activation page opens. Enter your Target Name.

8. Select you Vertica version and the quantity you want to activate.

9. Click Next.

10. Confirm your activation details and click Submit.

11. The Activation Results page displays. Follow the instructions in New Vertica license installations or Vertica license changes to complete your installation or upgrade.

Your Vertica Community Edition download package includes the Community Edition license, which allows three nodes and 1TB of data. The Vertica Community Edition license does not expire.

2 - Understanding Vertica licenses

Vertica has flexible licensing terms. It can be licensed on the following bases:

• Term-based (valid until a specific date).

• Size-based (valid to store up to a specified amount of raw data).

• Both term- and size-based.

• Unlimited duration and data storage.

• Node-based with an unlimited number of CPUs and users (one node is a server acting as a single computer system, whether physical or virtual).

• A pay-as-you-go model where you pay for only the number of hours you use. This license is available on your cloud provider's marketplace.

Your license key has your licensing bases encoded into it. If you are unsure of your current license, you can view your license information from within Vertica.

Community edition license

Vertica Community Edition (CE) is free and allows customers to cerate databases with the following limits:

• up to 3 of nodes

• up to 1 terabyte of data

Community Edition licenses cannot be installed co-located in a Hadoop infrastructure and used to query data stored in Hadoop formats.

As part of the CE license, you agree to the collection of some anonymous, non-identifying usage data. This data lets Vertica understand how customers use the product, and helps guide the development of new features. None of your personal data is collected. For details on what is collected, see the Community Edition End User License Agreement.

Vertica for SQL on Apache Hadoop license

Vertica for SQL on Apache Hadoop is a separate product with its own license. This documentation covers both products. Consult your license agreement for details about available features and limitations.

3 - Installing or upgrading a license key

The steps you follow to apply your Vertica license key vary, depending on the type of license you are applying and whether you are upgrading your license.

3.1 - New Vertica license installations

Follow these steps to install a new Vertica license:

1. Copy the license key file you generated from the Software Entitlement Key site to your Administration host.

2. Ensure the license key's file permissions are set to 400 (read permissions).

3. Install Vertica as described in the Installing Vertica if you have not already done so. The interface prompts you for the license key file.

4. To install Community Edition, leave the default path blank and click OK. To apply your evaluation or Premium Edition license, enter the absolute path of the license key file you downloaded to your Administration Host and press OK. The first time you log in as the Database Superuser and run the Administration tools, the interface prompts you to accept the End-User License Agreement (EULA).

   Note

   If you installed Management Console, the MC administrator can point to the location of the license key during Management Console configuration.

5. Choose View EULA.

6. Exit the EULA and choose Accept EULA to officially accept the EULA and continue installing the license, or choose Reject EULA to reject the EULA and return to the Advanced Menu.

3.2 - Vertica license changes

If your license is expiring or you want your database to grow beyond your licensed data size, you must renew or upgrade your license. After you obtain your renewal or upgraded license key file, you can install it using Administration Tools or Management Console.

Upgrading does not require a new license unless you are increasing the capacity of your database. You can add-on capacity to your database using the Software Entitlement Key. You do not need uninstall and reinstall the license to add-on capacity.

Uploading or upgrading a license key using administration tools

1. Copy the license key file you generated from the Software Entitlement Key site to your Administration host.

2. Ensure the license key's file permissions are set to 400 (read permissions).

3. Start your database, if it is not already running.

4. In the Administration Tools, select Advanced > Upgrade License Key and click OK.

5. Enter the absolute path to your new license key file and click OK. The interface prompts you to accept the End-User License Agreement (EULA).

6. Choose View EULA.

7. Exit the EULA and choose Accept EULA to officially accept the EULA and continue installing the license, or choose Reject EULA to reject the EULA and return to the Advanced Menu.

Uploading or upgrading a license key using Management Console

1. From your database's Overview page in Management Console, click the License tab. The License page displays. You can view your installed licenses on this page.

2. Click Install New License at the top of the License page.

3. Browse to the location of the license key from your local computer and upload the file.

4. Click Apply at the top of the page. Management Console prompts you to accept the End-User License Agreement (EULA).

5. Select the check box to officially accept the EULA and continue installing the license, or click Cancel to exit.

Adding capacity

If you are adding capacity to your database, you do not need to uninstall and reinstall the license. Instead, you can install multiple licenses to increase the size of your database. This additive capacity only works for licenses with the same format, such as adding a Premium license capacity to an existing Premium license type. When you add capacity, the size of license will be the total of both licenses; the previous license is not overwritten. You cannot add capacity using two different license formats, such as adding Hadoop license capacity to an existing Premium license.

You can run the AUDIT() function to verify the license capacity was added on. The reflection of add-on capacity to your license will run during the automatic run of the audit function. If you want to see the immediate result of the add-on capacity, run the AUDIT() function to refresh.

4 - Viewing your license status

You can use several functions to display your license terms and current status.

Examining your license key

Use the DISPLAY_LICENSE SQL function to display the license information. This function displays the dates for which your license is valid (or Perpetual if your license does not expire) and any raw data allowance. For example:

=> SELECT DISPLAY_LICENSE();
                  DISPLAY_LICENSE
---------------------------------------------------
 Vertica Systems, Inc.
2007-08-03
Perpetual
500GB

(1 row)

You can also query the LICENSES system table to view information about your installed licenses. This table displays your license types, the dates for which your licenses are valid, and the size and node limits your licenses impose.

Alternatively, use the LICENSES table in Management Console. On your database Overview page, click the License tab to view information about your installed licenses.

Viewing your license compliance

If your license includes a raw data size allowance, Vertica periodically audits your database's size to ensure it remains compliant with the license agreement. If your license has a term limit, Vertica also periodically checks to see if the license has expired. You can see the result of the latest audits using the GET_COMPLIANCE_STATUS function.

=> select GET_COMPLIANCE_STATUS();
                       GET_COMPLIANCE_STATUS
---------------------------------------------------------------------------------
 Raw Data Size: 2.00GB +/- 0.003GB
 License Size : 4.000GB
 Utilization  : 50%
 Audit Time   : 2011-03-09 09:54:09.538704+00
 Compliance Status : The database is in compliance with respect to raw data size.
 License End Date: 04/06/2011
 Days Remaining: 28.59
(1 row)

To see how your ORC/Parquet data is affecting your license compliance, see Viewing license compliance for Hadoop file formats.

Viewing your license status through MC

Information about license usage is on the Settings page. See Monitoring database size for license compliance.

5 - Viewing license compliance for Hadoop file formats

You can use the EXTERNAL_TABLE_DETAILS system table to gather information about all of your tables based on Hadoop file formats. This information can help you understand how much of your license's data allowance is used by ORC and Parquet-based data.

Vertica computes the values in this table at query time, so to avoid performance problems, restrict your queries to filter by table_schema, table_name, or source_format. These three columns are the only columns you can use in a predicate, but you may use all of the usual predicate operators.

=> SELECT * FROM EXTERNAL_TABLE_DETAILS
    WHERE source_format = 'PARQUET' OR source_format = 'ORC';
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schema_oid            | 45035996273704978
table_schema          | public
table_oid             | 45035996273760390
table_name            | ORC_demo
source_format         | ORC
total_file_count      | 5
total_file_size_bytes | 789
source_statement      | COPY FROM 'ORC_demo/*' ORC
file_access_error     |
-[ RECORD 2 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schema_oid            | 45035196277204374
table_schema          | public
table_oid             | 45035996274460352
table_name            | Parquet_demo
source_format         | PARQUET
total_file_count      | 3
total_file_size_bytes | 498
source_statement      | COPY FROM 'Parquet_demo/*' PARQUET
file_access_error     |

When computing the size of an external table, Vertica counts all data found in the location specified by the COPY FROM clause. If you have a directory that contains ORC and delimited files, for example, and you define your external table with "COPY FROM *" instead of "COPY FROM *.orc", this table includes the size of the delimited files. (You would probably also encounter errors when querying that external table.) When you query this table Vertica does not validate your table definition; it just uses the path to find files to report.

You can also use the AUDIT function to find the size of a specific table or schema. When using the AUDIT function on ORC or PARQUET external tables, the error tolerance and confidence level parameters are ignored. Instead, the AUDIT always returns the size of the ORC or Parquet files on disk.

=> select AUDIT('customers_orc');
   AUDIT
-----------
 619080883
(1 row)

6 - Moving a cloud installation from by the hour (BTH) to bring your own license (BYOL)

Vertica offers two licensing options for some of the entries in the Amazon Web Services Marketplace and Google Cloud Marketplace:

• Bring Your Own License (BYOL): a long-term license that you obtain through an online licensing portal. These deployments also work with a free Community Edition license. Vertica uses a community license automatically if you do not install a license that you purchased. (For more about Vertica licenses, see Managing licenses and Understanding Vertica licenses.)
• Vertica by the Hour (BTH): a pay-as-you-go environment where you are charged an hourly fee for both the use of Vertica and the cost of the instances it runs on. The Vertica by the hour deployment offers an alternative to purchasing a term license. If you want to crunch large volumes of data within a short period of time, this option might work better for you. The BTH license is automatically applied to all clusters you create using a BTH MC instance.

If you start out with an hourly license, you can later decide to use a long-term license for your database. The support for an hourly versus a long-term license is built into the instances running your database. To move your database from an hourly license to a long-term license, you must create a new database cluster with a new set of instances.

To move from an hourly to a long-term license, follow these steps:

1. Purchase a BYOL license. Follow the process described in Obtaining a license key file.

2. Apply the new license to your database.

3. Shut down your database.

4. Create a new database cluster using a BYOL marketplace entry.

5. Revive your database onto the new cluster.

The exact steps you must take depend on your database mode and your preferred tool for managing your database:

• Eon Mode databases: Use either the command line or Management Console.
• Enterprise Mode databases: Use the command line to switch licenses.

Moving an Eon Mode database from BTH to BYOL using the command line

Follow these steps to move an Eon Mode database from an hourly to a long-term license.

Obtain a long-term BYOL license from the online licensing portal, described in Obtaining a license key file.Upload the license file to a node in your database. Note the absolute path in the node's filesystem, as you will need this later when installing the license.Connect to the node you uploaded the license file to in the previous step. Connect to your database using vsql and view the licenses table:

=> SELECT * FROM licenses;

Note the name of the hourly license listed in the NAME column, so you can check if it is still present later.

Install the license in the database using the INSTALL_LICENSE function with the absolute path to the license file you uploaded in step 2:

=> SELECT install_license('absolute path to BYOL license');

View the licenses table again:

=> SELECT * FROM licenses;

If only the new BYOL license appears in the table, skip to step 8. If the hourly license whose name you noted in step 4 is still in the table, copy the name and proceed to step 7.

Call the DROP_LICENSE function to drop the hourly license:

=> SELECT drop_license('hourly license name');

1. You will need the path for your cluster's communal storage in a later step. If you do not already know the path, you can find this information by executing this query:

   => SELECT location_path FROM V_CATALOG.STORAGE_LOCATIONS
      WHERE sharing_type = 'COMMUNAL';
   

2. Synchronize your database's metadata. See Synchronizing metadata.

3. Shut down the database by calling the SHUTDOWN function:

   => SELECT SHUTDOWN();
   

4. You now need to create a new BYOL cluster onto which you will revive your database. Deploy a new cluster including a new MC instance using a BYOL entry in the marketplace of your chosen cloud platform. See:

   • Deploy MC and AWS resources with a CloudFormation template
   • Deploy an MC instance in GCP for Eon Mode

   Important

   Your new BYOL cluster must have the same number of primary nodes as your existing hourly license cluster.

5. Revive your database onto the new cluster. For instructions, see Revive with commuanal storage. Because you created the new cluster using a BYOL entry in the marketplace, the database uses the BYOL you applied earlier.

6. After reviving the database on your new BYOL cluster, terminate the instances for your hourly license cluster and MC. For instructions, see your cloud provider's documentation.

Moving an Eon Mode database from BTH to BYOL using the MC

Follow this procedure to move to BYOL and revive your database using MC:

1. Purchase a long-term BYOL license from the online licensing portal, following the steps detailed in Obtaining a license key file. Save the file to a location on your computer.

2. You now need to install the new license on your database. Log into MC and click your database in the Recent Databases list.

3. At the bottom of your database's Overview page, click the License tab.

4. Under the Installed Licenses list, note the name of the BTH license in the License Name column. You will need this later to check whether it is still present after installing the new long-term license.

5. In the ribbon at the top of the License History page, click the Install New License button. The Settings: License page opens.

6. Click the Browse button next to the Upload a new license box.

7. Locate the license file you obtained in step 1, and click Open.

8. Click the Apply button on the top right of the page.

9. Select the checkbox to agree to the EULA terms and click OK.

10. After Vertica installs the license, click the Close button.

11. Click the License tab at the bottom of the page.

12. If only the new long-term license appears in the Installed Licenses list, skip to Step 16. If the by-the-hour license also appears in the list, copy down its name from the License Name column.

13. You must drop the by-the-hour license before you can proceed. At the bottom of the page, click the Query Execution tab.

14. In the query editor, enter the following statement:

    SELECT DROP_LICENSE('hourly license name');
    

15. Click Execute Query. The query should complete indicating that the license has been dropped.

16. You will need the path for your cluster's communal storage in a later step. If you do not already know the path, you can find this information by executing this query in the Query Execution tab:

    SELECT location_path FROM V_CATALOG.STORAGE_LOCATIONS
       WHERE sharing_type = 'COMMUNAL';
    

17. Synchronize your database's metadata. See Synchronizing metadata.

18. You must now stop your by-the-hour database cluster. At the bottom of the page, click the Manage tab.

19. In the banner at the top of the page, click Stop Database and then click OK to confirm.

20. From the Amazon Web Services Marketplace or the Google Cloud Marketplace, deploy a new Vertica Management Console using a BYOL entry. Do not deploy a full cluster. You just need an MC deployment.

21. Log into your new MC instance and revive the database. See Reviving an Eon Mode database on AWS in MC for detailed instructions.

22. After reviving the database on your new environment, terminate the instances for your hourly license environment. To do so, on the AWS CloudFormation Stacks page, select the hourly environment's stack (its collection of AWS resources) and click Actions > Delete Stack.

Moving an Enterprise Mode database from hourly to BYOL using backup and restore

In an Enterprise Mode database, follow this procedure to move to BYOL, and then back up and restore your database:

Obtain a long-term BYOL license from the online licensing portal, described in Obtaining a license key file.Upload the license file to a node in your database. Note the absolute path in the node's filesystem, as you will need this later when installing the license.Connect to the node you uploaded the license file to in the previous step. Connect to your database using vsql and view the licenses table:

=> SELECT * FROM licenses;

Note the name of the hourly license listed in the NAME column, so you can check if it is still present later.

Install the license in the database using the INSTALL_LICENSE function with the absolute path to the license file you uploaded in step 2:

=> SELECT install_license('absolute path to BYOL license');

View the licenses table again:

=> SELECT * FROM licenses;

If only the new BYOL license appears in the table, skip to step 8. If the hourly license whose name you noted in step 4 is still in the table, copy the name and proceed to step 7.

Call the DROP_LICENSE function to drop the hourly license:

=> SELECT drop_license('hourly license name');

1. Back up the database. See Backing up and restoring the database.

2. Deploy a new cluster for your database using one of the BYOL entries in the Amazon Web Services Marketplace.
   

3. Restore the database from the backup you created earlier. See Backing up and restoring the database. When you restore the database, it will use the BYOL you loaded earlier.

4. After restoring the database on your new environment, terminate the instances for your hourly license environment. To do so, on the AWS CloudFormation Stacks page, select the hourly environment's stack (its collection of AWS resources) and click Actions > Delete Stack.

After completing one of these procedures, see Viewing your license status to confirm the license drop and install were successful.

7 - Auditing database size

You can use your Vertica software until columnar data reaches the maximum raw data size that your license agreement allows. Vertica periodically runs an audit of the columnar data size to verify that your database complies with this agreement. You can also run your own audits of database size with two functions:

• AUDIT: Estimates the raw data size of a database, schema, or table.

• AUDIT_FLEX: Estimates the size of one or more flexible tables in a database, schema, or projection.

The following two examples audit the database and one schema:

=> SELECT AUDIT('', 'database');
  AUDIT
----------
 76376696
(1 row)

=> SELECT AUDIT('online_sales', 'schema');
  AUDIT
----------
 35716504
(1 row)

Raw data size

AUDIT and AUDIT_FLEX use statistical sampling to estimate the raw data size of data stored in tables—that is, the uncompressed data that the database stores. For most data types, Vertica evaluates the raw data size as if the data were exported from the database in text format, rather than as compressed data. For details, see Evaluating Data Type Footprint.

By using statistical sampling, the audit minimizes its impact on database performance. The tradeoff between accuracy and performance impact is a small margin of error. Reports on your database size include the margin of error, so you can assess the accuracy of the estimate.

Data in ORC and Parquet-based external tables are also audited whether they are stored locally in the Vertica cluster's file system or remotely in S3 or on a Hadoop cluster. AUDIT always uses the file size of the underlying data files as the amount of data in the table. For example, suppose you have an external table based on 1GB of ORC files stored in HDFS. Then an audit of the table reports it as being 1GB in size.

Unaudited data

Table data that appears in multiple projections is counted only once. An audit also excludes the following data:

• Temporary table data.

• Data in SET USING columns.

• Non-columnar data accessible through external table definitions. Data in columnar formats such as ORC and Parquet count against your totals.

• Data that was deleted but not yet purged.

• Data stored in system and work tables such as monitoring tables, Data collector tables, and Database Designer tables.

• Delimiter characters.

Evaluating data type footprint

Vertica evaluates the footprint of different data types as follows:

• Strings and binary types—CHAR, VARCHAR, BINARY, VARBINARY—are counted as their actual size in bytes using UTF-8 encoding.

• Numeric data types are evaluated as if they were printed. Each digit counts as a byte, as does any decimal point, sign, or scientific notation. For example, -123.456 counts as eight bytes—six digits plus the decimal point and minus sign.

• Date/time data types are evaluated as if they were converted to text, including hyphens, spaces, and colons. For example, vsql prints a timestamp value of 2011-07-04 12:00:00 as 19 characters, or 19 bytes.

• Complex types are evaluated as the sum of the sizes of their component parts. An array is counted as the total size of all elements, and a ROW is counted as the total size of all fields.

Controlling audit accuracy

AUDIT can specify the level of an audit's error tolerance and confidence, by default set to 5 and 99 percent, respectively. For example, you can obtain a high level of audit accuracy by setting error tolerance and confidence level to 0 and 100 percent, respectively. Unlike estimating raw data size with statistical sampling, Vertica dumps all audited data to a raw format to calculate its size.

The following example audits the database with 25% error tolerance:

=> SELECT AUDIT('', 25);
  AUDIT
----------
 75797126
(1 row)

The following example audits the database with 25% level of tolerance and 90% confidence level:

=> SELECT AUDIT('',25,90);
  AUDIT
----------
 76402672
(1 row)

8 - Monitoring database size for license compliance

Your Vertica license can include a data storage allowance. The allowance can consist of data in columnar tables, flex tables, or both types of data. The AUDIT() function estimates the columnar table data size and any flex table materialized columns. The AUDIT_FLEX() function estimates the amount of __raw__ column data in flex or columnar tables. In regards to license data limits, data in __raw__ columns is calculated at 1/10th the size of structured data. Monitoring data sizes for columnar and flex tables lets you plan either to schedule deleting old data to keep your database in compliance with your license, or to consider a license upgrade for additional data storage.

Viewing your license compliance status

Vertica periodically runs an audit of the columnar data size to verify that your database is compliant with your license terms. You can view the results of the most recent audit by calling the GET_COMPLIANCE_STATUS function.

=> select GET_COMPLIANCE_STATUS();
                       GET_COMPLIANCE_STATUS
---------------------------------------------------------------------------------
 Raw Data Size: 2.00GB +/- 0.003GB
 License Size : 4.000GB
 Utilization  : 50%
 Audit Time   : 2011-03-09 09:54:09.538704+00
 Compliance Status : The database is in compliance with respect to raw data size.
 License End Date: 04/06/2011
 Days Remaining: 28.59
(1 row)

Periodically running GET_COMPLIANCE_STATUS to monitor your database's license status is usually enough to ensure that your database remains compliant with your license. If your database begins to near its columnar data allowance, you can use the other auditing functions described below to determine where your database is growing and how recent deletes affect the database size.

Manually auditing columnar data usage

You can manually check license compliance for all columnar data in your database using the AUDIT_LICENSE_SIZE function. This function performs the same audit that Vertica periodically performs automatically. The AUDIT_LICENSE_SIZE check runs in the background, so the function returns immediately. You can then query the results using GET_COMPLIANCE_STATUS.

An alternative to AUDIT_LICENSE_SIZE is to use the AUDIT function to audit the size of the columnar tables in your entire database by passing an empty string to the function. This function operates synchronously, returning when it has estimated the size of the database.

=> SELECT AUDIT('');
  AUDIT
----------
 76376696
(1 row)

The size of the database is reported in bytes. The AUDIT function also allows you to control the accuracy of the estimated database size using additional parameters. See the entry for the AUDIT function for full details. Vertica does not count the AUDIT function results as an official audit. It takes no license compliance actions based on the results.

Manually auditing __raw__ column data

You can use the AUDIT_FLEX function to manually audit data usage for flex or columnar tables with a __raw__ column. The function calculates the encoded, compressed data stored in ROS containers for any __raw__ columns. Materialized columns in flex tables are calculated by the AUDIT function. The AUDIT_FLEX results do not include data in the __raw__ columns of temporary flex tables.

Targeted auditing

If audits determine that the columnar table estimates are unexpectedly large, consider schemas, tables, or partitions that are using the most storage. You can use the AUDIT function to perform targeted audits of schemas, tables, or partitions by supplying the name of the entity whose size you want to find. For example, to find the size of the online_sales schema in the VMart example database, run the following command:

=> SELECT AUDIT('online_sales');
  AUDIT
----------
 35716504
(1 row)

You can also change the granularity of an audit to report the size of each object in a larger entity (for example, each table in a schema) by using the granularity argument of the AUDIT function. See the AUDIT function.

Using Management Console to monitor license compliance

You can also get information about data storage of columnar data (for columnar tables and for materialized columns in flex tables) through the Management Console. This information is available in the database Overview page, which displays a grid view of the database's overall health.

• The needle in the license meter adjusts to reflect the amount used in megabytes.

• The grace period represents the term portion of the license.

• The Audit button returns the same information as the AUDIT() function in a graphical representation.

• The Details link within the License grid (next to the Audit button) provides historical information about license usage. This page also shows a progress meter of percent used toward your license limit.

9 - Managing license warnings and limits

Term license warnings and expiration

The term portion of a Vertica license is easy to manage—you are licensed to use Vertica until a specific date. If the term of your license expires, Vertica alerts you with messages appearing in the Administration tools and vsql. For example:

=> CREATE TABLE T (A INT);
NOTICE 8723: Vertica license 432d8e57-5a13-4266-a60d-759275416eb2 is in its grace period; grace period expires in 28 days
HINT: Renew at https://softwaresupport.softwaregrp.com/
CREATE TABLE

Contact Vertica at https://softwaresupport.softwaregrp.com/ as soon as possible to renew your license, and then install the new license. After the grace period expires, Vertica stops processing DML queries and allows DDL queries with a warning message. If a license expires and one or more valid alternative licenses are installed, Vertica uses the alternative licenses.

Data size license warnings and remedies

If your Vertica columnar license includes a raw data size allowance, Vertica periodically audits the size of your database to ensure it remains compliant with the license agreement. For details of this audit, see Auditing database size. You should also monitor your database size to know when it will approach licensed usage. Monitoring the database size helps you plan to either upgrade your license to allow for continued database growth or delete data from the database so you remain compliant with your license. See Monitoring database size for license compliance for details.

If your database's size approaches your licensed usage allowance (above 75% of license limits), you will see warnings in the Administration tools , vsql, and Management Console. You have two options to eliminate these warnings:

• Upgrade your license to a larger data size allowance.

• Delete data from your database to remain under your licensed raw data size allowance. The warnings disappear after Vertica's next audit of the database size shows that it is no longer close to or over the licensed amount. You can also manually run a database audit (see Monitoring database size for license compliance for details).

If your database continues to grow after you receive warnings that its size is approaching your licensed size allowance, Vertica displays additional warnings in more parts of the system after a grace period passes. Use the GET_COMPLIANCE_STATUS function to check the status of your license.

If your Vertica premium edition database size exceeds your licensed limits

If your Premium Edition database size exceeds your licensed data allowance, all successful queries from ODBC and JDBC clients return with a status of SUCCESS_WITH_INFO instead of the usual SUCCESS. The message sent with the results contains a warning about the database size. Your ODBC and JDBC clients should be prepared to handle these messages instead of assuming that successful requests always return SUCCESS.

If your Vertica community edition database size exceeds 1 terabyte

If your Community Edition database size exceeds the limit of 1 terabyte, Vertica stops processing DML queries and allows DDL queries with a warning message.

To bring your database under compliance, you can choose to:

• Drop database tables. You can also consider truncating a table or dropping a partition. See TRUNCATE TABLE or DROP_PARTITIONS.

• Upgrade to Vertica Premium Edition (or an evaluation license).

10 - Exporting license audit results to CSV

You can use admintools to audit a database for license compliance and export the results in CSV format, as follows:

admintools -t license_audit [--password=password] --database=database] [--file=csv-file] [--quiet]

where:

• database must be a running database. If the database is password protected, you must also supply the password.

• --file csv-file directs output to the specified file. If csv-file already exists, the tool returns an error message. If this option is unspecified, output is directed to stdout.

• --quiet specifies that the tool should run in quiet mode; if unspecified, status messages are sent to stdout.

Running the license_audit tool is equivalent to invoking the following SQL statements:

select audit('');
select audit_flex('');
select * from dc_features_used;
select * from v_catalog.license_audits;
select * from v_catalog.user_audits;

Audit results include the following information:

• Log of used Vertica features

• Estimated database size

• Raw data size allowed by your Vertica license

• Percentage of licensed allowance that the database currently uses

• Audit timestamps

The following truncated example shows the raw CSV output that license_audit generates:

FEATURES_USED
features_used,feature,date,sum
features_used,metafunction::get_compliance_status,2014-08-04,1
features_used,metafunction::bootstrap_license,2014-08-04,1
...

LICENSE_AUDITS
license_audits,database_size_bytes,license_size_bytes,usage_percent,audit_start_timestamp,audit_end_timestamp,confidence_level_percent,error_tolerance_percent,used_sampling,confidence_interval_lower_bound_bytes,confidence_interval_upper_bound_bytes,sample_count,cell_count,license_name
license_audits,808117909,536870912000,0.00150523690320551,2014-08-04 23:59:00.024874-04,2014-08-04 23:59:00.578419-04,99,5,t,785472097,830763721,10000,174754646,vertica
...

USER_AUDITS
user_audits,size_bytes,user_id,user_name,object_id,object_type,object_schema,object_name,audit_start_timestamp,audit_end_timestamp,confidence_level_percent,error_tolerance_percent,used_sampling,confidence_interval_lower_bound_bytes,confidence_interval_upper_bound_bytes,sample_count,cell_count
user_audits,812489249,45035996273704962,dbadmin,45035996273704974,DATABASE,,VMart,2014-10-14 11:50:13.230669-04,2014-10-14 11:50:14.069057-04,99,5,t,789022736,835955762,10000,174755178

AUDIT_SIZE_BYTES
audit_size_bytes,now,audit
audit_size_bytes,2014-10-14 11:52:14.015231-04,810584417

FLEX_SIZE_BYTES
flex_size_bytes,now,audit_flex
flex_size_bytes,2014-10-14 11:52:15.117036-04,11850