QuerySurge works with Micro Focus Vertica just like it works with other database products - through a standard JDBC connection, using the Vertica JDBC driver. In fact, the QuerySurge installer bundles the Vertica JDBC driver, so you can install the driver when you install your QuerySurge Agents.
Note: Instructions for setting up a Vertica connection using the QuerySurge Connection Wizard are available in this article.
However, Vertica has some specific features and options that may impact how you use it with QuerySurge. This article details those items so that you can adjust your QuerySurge configuration where necessary, or modify your testing strategy with Vertica, so that you get the most from your QuerySurge data tests.
Vertica Data Sizes
Vertica supports large data objects (e.g. VARCHAR, 65KB; LONG VARCHAR 32MB). By default, QuerySurge supports a maximum insert message size of 50 MB. This is the largest message size that QuerySurge Agents can send, and it is the largest size that QuerySurge can accept. Therefore, no single row of data can exceed this size. It is possible that tables containing large Vertica objects may exceed QuerySurge's default 50 MB maximum insert size per row. If this issue is encountered, it can be resolved by raising QuerySurge's maximum insert message size.
You can change the maximum message size via the following steps:
- Stop all QuerySurge services. Use the recommended order for stopping (and starting) services, which you can find in this article.
- Locate the QuerySurge MySQL config file. In the default install path, the file is found at:
[Windows] C:\Program Files\QuerySurge\mysql\my.ini
- Make a backup copy of your QuerySurge MySQL config file.
- Edit the QuerySurge MySQL config file (carefully!) using a text editor. Find the max_allowed_packet key (which defaults to 50M) and set it to a value that reflects the maximum size that your data requires.
Note: Setting this value to extremely large sizes may have performance impacts on your network and on QuerySurge, so judicious handling is advised.
- Re-start your QuerySurge services in the proper sequence.
Vertica Numeric Types
Vertica supports some custom values for specific numeric data types. These include the NaN value, +inf and -inf. Unfortunately, the database that QuerySurge uses (MySQL) does not support these custom types, so we are forced to map these values to NULL in QuerySurge.
If your data contains these values, one option for a workaround is to cast the numeric columns to a string type (e.g. CHAR or VARCHAR) in your SQL query, with appropriate handling on both Source and Target of the QueryPair. Another option is to include logic in your SQL to convert these values to a conventional numeric data value that can be used to support your test.