Loading data hangs up

Moderator: NorbertKrupa

Post Reply
sushanth
Newbie
Newbie
Posts: 6
Joined: Wed Sep 25, 2013 6:59 am

Loading data hangs up

Post by sushanth » Mon Oct 07, 2013 10:52 am

Dear all,

I am trying to load data to vertica using copy local.

I have created a console application in .net using ado.net for vertica and creating the text file for the records to be copied to vertica for various date ranges.
After creating the text file iam loading the data using COPY LOCAL.

This is an automated process which will work on daily basis as per the scheduled time.
Every thing is/ was working perfect for the last 7 days.

But today all of sudden it stopped working and my program got stuck while copying the data to vertica db.
During my previous iterations things went well , but during a particular iteration the application got stuck at COPY LOCAL.

I could not find any issues like rejected records or any things. i also checked the windows log but i could not trace it.
I stopped the application and run again then every thing went well as usual.

Since this is an automated process i need to find out the issue that had happened while loading the data.

Is there any way to find the copy error from vertica log files. If so please tell me how to find out the error from the log file or what caused the erro.

Thanks in advance
Sushanth.B

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Loading data hangs up

Post by nnani » Mon Oct 07, 2013 11:35 am

Hi Sushanth,

You can check the CopyErrorLogs for looking out any exception or issues while running the COPY commands.
OR
You can use this error catching mechanism in your program
Every time you start a transaction collect the session id and transaction id and use the session id to track for any errors through the error table and you can get the error description or count of errors if any while your process is running.
OR
You can check the Vertica Log file with the timestamp that matches the timestamp of the COPY command which got stuck.

Hope this helps.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

sushanth
Newbie
Newbie
Posts: 6
Joined: Wed Sep 25, 2013 6:59 am

Re: Loading data hangs up

Post by sushanth » Mon Oct 07, 2013 12:58 pm

Dear nnani,
thanks for the reply.

In my copy command i have given reference to the exception file and i have checked that file and didnt find any error.

what's the CopyErrorLogs you are mentioning? is this the same exception file which iam using along with the copy command?

How can i get the transactionid and sessionid from my program?

what is the name of the error table in vertica.

i have checked the vertica log file but there is no hint for the error that occured.

Please help.
Sushanth.b

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Loading data hangs up

Post by nnani » Mon Oct 07, 2013 2:24 pm

#sushanth
what's the CopyErrorLogs you are mentioning? is this the same exception file which iam using along with the copy command?
The COPY statement automatically saves files containing records of exceptions and rejected data events. By default, Vertica saves these files in the database catalog subdirectory, CopyErrorLogs,
How can i get the transactionid and sessionid from my program?
If you are calling your copy command through some scripts then you can call a sql script within the script

Code: Select all

the sql for transaction and session id

\set ON_ERROR_STOP on
SELECT 'SESSION ID:'|| SESSION_ID FROM V_MONITOR.CURRENT_SESSION;
SELECT 'TRANSACTION ID:'|| TRANSACTION_ID FROM V_MONITOR.CURRENT_SESSION;
Then call this snippet in your copy script this way you can get your session id and transaction id.

what is the name of the error table in vertica.
All the errors are recorded by Vertica in error_messages system table in v_monitor schema.

If you did not get any errors in any of these options then try analysing the error cause in your connection to the server from ADO.net
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “Vertica Data Load”