Rocket® Vertica

Hi, I have a ticket_createddate and ticket_Resolveddate in a table. I want to know the number of working days between these two days. How can I find it? Eg: ticket_createddate - 01-Sep-2023 (Friday) ticket_Resolveddate - 04-Sep-2023 (Monday) I want the Output as 2 because Only Friday and Monday has to be counted and Sat/Sun has to be excluded.

I have the following info in "Estimated resources for plan:". Scratch Memory MB: 897 File Handles: 1005 Worker Threads: 24 Blocking Threads: 75 Externalizing Ops: 0 Unbounded Mem Ops: 0 Max Threads: 16 what's the meaning of Unbounded Mem Ops and Externalizing Ops? And there are 3 kinds of threads, what's the relationship among them? Worker Threads,Blocking Threads and Max Threads

I know hash join has 2 activity id, one for inner table build, the other for output table probe. But for group by hash, why there are multiple activity id? What's the meaning of them? From below snapshot, there are activity id 1026,1023,1023 and 1016, each of have different start end and end time, they are based on the profile data

The data are originally stored in columns based format, and in the plan we often saw materilize, so after this materilize is done, will vertica process the data in row format afterward?

Hi! I need to cast input value into string and than use it within the interval option. Can I declare a variable inside a function? The use is for: "current_timestamp + interval to_char(input) minute" the input is numeric.

I saw a join plan as below. I know the meaning of resegment of outer/inner, but what's LOCAL ROUND ROBIN). | | +---> JOIN HASH [Cost: 2B, Rows: 2B] (PATH ID: 4) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT)

After ran a complex query profile, we save it to adhoc.profile_backup. Using the following query, we found one operation_id can be used by multiple activiity_id. operation_id is the number of a thread, then what's activity id? And why a single sort has 4 activity id? select distinct activity_id, operator_id from adhoc.profile_backup where path_id=62 and operator_name ilike 'sort' and node_name='v_node0032' and counter_value>0 order by operator_id

Hi, User have JSON: {"modifier": "mod:VAL"} User wants to load string from 4th char. User insist on having column names same as in JSON. Solution is trivial, using transform, with filler and expression on filler: /opt/vertica/bin/vsql -h vdb.vertica.com -U dbadmin -w supersecret -a << +++ drop table public.load_test; create table public.load_test (modifier varchar(5)); copy public.load_test (modifier filler varchar, modifier as substr(modifier, 4)) from stdin parser fjsonparser() abort on error; {"modifier": "mod:VAL"} \. +++ That gives an error: ERROR 2671: Column reference "modifier" is ambiguous DETAIL: Reference "modifier" could refer to either "public.load_test.modifier" or "*FILLER*.modifier" Looking deeper, Vertica is not allowing to declare filler with same name as table column name: copy public.load_test ("modifier" filler varchar) from stdin parser fjsonparser() abort on error; ERROR 2671: Column reference "modifier" is ambiguous DETAIL: Reference "modif

I have a system running with multiple versions of the ODBC driver for windows. The version I have deployed on most hosts is the 9.2 ODBC driver. I need to use the COPY LOCAL command, but for some reason it seems like the clients do not support it (or maybe the problem has to do with my configuration on the client/server). The error is as following:ERROR [HY000] [Vertica][VerticaDSII] (210) Data streaming error: ERROR 5783: Client error: Copy local has been disabled on this connection (in function () at Bulkload.cpp:288) How do I resolve this and allow for COPY LOCAL to be performed? Is it some connection property I am not aware of? Something in the .INI file? Or maybe some configuration on the server.

I am working with an Opentext (Micro Focus) ITOM partner - the ITOM products ship certain patch level versions of vertica on their SLD sites however the partner would like to install a more recent (10.1.1-26) patch level and they do not have access to the sftp site. I also do not have the current password. The recent patch announcements all seem to say this: "Customer SFTP site. If you need location and credentials SFTP site, please contact Vertica Technical support via the customer portal at https://portal.microfocus.com/." however when I go onto https://portal.microfocus.com there is no obvious vertica contact available and when I click on the "Contact Support" button at the bottom right of the home page the link fails with a 504 Gateway Time-out. Two questions: 1) Isn't there some Opentext-internal-only web page we can go to to get the latest sftp patches password? 2) How would an ITOM partner obtain the latest vertica patch for their customers who have the ITOM-vertica entitlemen

An ITOM customer needs to move their Azure IaaS Vertica cluster from old RHEL7.x hosts to newer OS images. Their idea is to bring up another cluster then use replication (presumably vbr) to sync the content, then delete the original VMs and replace them with ones having the same hostnames with the new OS base version, then using replication again to essentially "restore' the original cluster on the new base OS. Does anyone have experience in the field on this kind of use-case? I am sure customers wanting to move off of RHEL 7.x is fairly common. Are there better alternatives?

Our ITOM customers are moving to RHEL 9.x releases but all current Verticas only support 8.x apparently - perhaps only a matter of testing/validation? ...and by the way a coworker of mine unwittingly installed 12.0.3 Vertica on RHEL 9.2 VMs it seems to work fine.

Hello team, I'm looking to establish a connection from my .NET Core app to Vertica. I've experimented with ODBC drivers, and they seem to work well, even on Linux. However, considering that ODBC is considered somewhat dated, I'm curious if there are any modern alternatives that would enable me to achieve the same functionality on Linux. Any insights or recommendations would be greatly appreciated."

Has anyone Successfully used "WITH" clause in a transformation? I got a qry to run in Vertica Qry builder, but when I load it in a transformation i get the error that says error next to ")".

We are facing an issue with complex types . A complex type stored in the db 10.1.x server is getting corrupted while retrieving with client driver version 11.x or above For ex : if we store [ ‘test1’,’test2’ ] , while retrieving we get something like “%nullnullnullnullnullnullnullnullnullnull-test1test2” As per the documentation all versions of client drivers and fully backward compatible . Can you please help on this . Are we missing something . We are trying to retrieve the data with resultSet.getString(columnIndex)

Hi, I expect vertica returns error when converting to date from wrong/incomplete stringselect to_date('1230','yymmdd') returns:2014-06-05 which doesn't make any sense. We expect incorrect date to fail to convert. Same query in oracle correctly gives an error (not a valid month / input value not long enough for dateformat) I found that it actually might be a feature rather than a bug,https://www.vertica.com/blog/convert-a-string-to-a-date-including-unconventional-dates/ Is there a way how to disable this 'lenient '/unpredictable feature?

Hi experts, I have a requirement, need to compare data between two tables where each table contain single record and based on validation need to throw an error. I written a stored procedure and when ran it, omg it is showing errors from line 1. I am new to Vertica. Below is the skeleton of my proc. Could you please advice on where I am going wrong? create or replace procedure proc_name() language PLvSQL AS $$ DECLARE t1_var1 int; t1_var2 int; t2_var1 int; t2_var2 int; begin select col1 into t1_var1,col2 into t1_var2 from tab1; select col1 into t2_var1,col2 into t2_var2 from tab2; if t1_var1!=t2_var1 or t1_var2!=t2_var2 raise exception 'count not matched' end if; end $$; appreciate your help. Thanks, Sri

Hello everyone, I am going to upgrade my Vertica cluster from 11.1.1-1 to 23.3.0-2. Could I follow this path11.1.1-1 to 23.3.0-2? Or what is the recommendation to do so? Thank you in advance.

Hi, I have to handle TimeTZ in UDx c++. Normally I do not allow TimeTZ in tables, but user managed to create new table with TimeTZ datatype and it was too late when I caught it. After few experiments, I found that TimeTZ in Vertica has number of micros since UTC midnight in top 5 bytes of 8-byte value. Out of curiosity, what lower 3 bytes of TimeTZ contains? Looking at Postgress code, it should be number of seconds since epoch, but PgSQL TimeTZ is 12 bytes vs Vertica 8 bytes. Looks like Vertica original developers invented something new for content of lower 3 bytes in TimeTZ. Would you mind to ask around and share? Thanks in advance Sergey

Could someone help me to write the query to get the below results for every row in the tableTable format: Column1 Column2 Column3 Column4 Column5 Column6 a b server1 1 name1 value1 a b server2 2 name2 value2 a b server3 2 name3 value2 a b server4 3 name4 value3 a b server5 3 name5 value4 a b server6 3 name6 value5 Result for 1st row Column1 Column2 Column3 Column4 Column5 Column6 a b server1 1 name1 value1 a b server2 1 name1 Not defined a b server3 1 name1 Not defined a b server4 1 name1 Not defined a b server5 1 name1 Not defined a b server6 1 name1 Not defined **Result for 2nd row ** a b server1 2 name2 Not defined a b server2 2 name2 value2 a b server3 2 name2 Not defined a b server4 2 name2 Not defined a b server5 2 name2 Not defined a b server6 2 name2 Not defined

I am writing a c++ code in linux using ODBC , I have a table which is having GEN_TIME column of timestamp type, i need 8 data to insert at a time, i defined array of SQL_TIMESTAMP_STRUCT lDateAndTime[10]; and filled with data like below const int NUM_ENTRIES = 8; lDateAndTime[0].year = 2023; lDateAndTime[0].month = 8; lDateAndTime[0].day = 10; lDateAndTime[0].hour = 11; lDateAndTime[0].minute = 30; lDateAndTime[0].second = 0; lDateAndTime[1].year = 2023; lDateAndTime[1].month = 8; lDateAndTime[1].day = 10; lDateAndTime[1].hour = 11; lDateAndTime[1].minute = 30; lDateAndTime[1].second = 0; And i set insert query like below ret = SQLPrepare (hdlStmt, (SQLTCHAR*)"INSERT INTO test_evr_details (evr_event, evr_reason, evr_cause, TRANS_ID, GEN_TIME) VALUES (?, ?, ?, ?, ?)" , SQL_NTS) ; and set the date filed like ret = SQLBindParameter(hdlStmt, 5, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TYPE_TIMESTAMP, 0, 0, (SQLPOINTER)lDateAndTime, sizeof(SQL_TIMESTAMP_STRUCT) , NULL);

Hi, in our Windows solution I updated the Vertica ADO.NET driver to version 23.3.0 using the nuget package. Now I get errors running our tests: The type initializer for 'Vertica.Data.VerticaClient.VerticaLogProperties' threw an exception. ---> System.NullReferenceException: Object reference not set to an instance of an object.. Apparently, the driver does not find/load the Vertica.Data.dll.config file. According to documentation, the driver should create the config file when it is first used. However, it doesn't. So I created it myself and located it in the same directory as Vertica.Data.dll, but the error still occurs. Any idea what the problem migt be?

The company wants something that is more up-to-date, with newer packages, a new kernel, and free, rhel flavored for development purposes. After reading: https://www.vertica.com/docs/12.0.x/HTML/Content/Authoring/SupportedPlatforms/MCandServer.htm?zoom_highlight=rhel It seems like all operating systems except RHEL are quite old, reached their End of Standard Support/EOL, or are near End of Standard Support/EOL: RHEL: Amazon Linux 2 -> 30.06.2023 (EOL) RHEL 7 / Centos 7 / Oracle Linux 7 -> quite old, only maintenance (Standard Support already reached, 2 years ago: 2020-08-06, only maintenance updates until: 2024-06-30) openSUSE Leap 42.3 -> Jul 1st 2019 (Reached EOL) Even deb family looks quite bad: Debian 10.x -> 08.2022 Ubuntu 18.x -> 04.2023 (eol is 2028) Will you support Rocky Linux or Alma Linux? It looks like overall you probably won't support any os that is free from RHEL family.

I have 2 texts in 2 different rows. One has \n and the other one has a new line entered. While applying distinct I want to treat them as one row. Could someone help text 1 : details \r\nwhich has Text 2: details which has

Hi, There is slightly awkward behaviour of flex table keys table. Keys table is being created together with flex table, with name < table>_keys When flex table is being moved to other schema, keys table is being moved as well. This is correct. When flex table is being renamed, keys table is not being renamed. That leads to rather awkward behaviour when you will rename flex table and move to other schema. Please ask development to rename keys table together with renaming flex table. Thank you Sergey