Hi,
The parallel job consists of a DRS connector stage and the o/p link fom this stage goes to a copy stage. The DRS stage executes a before SQL and a dummy main SQL (Select 'Dummy' as zzz).
Noramally this job executes with in 30 minutes. But sometimes it executes long time (around 6 Hrs) even with very low data volume . We are not able to see any issue with the SQL.
When checked the log we could see the job is stuck after setting the configuration file (log entry "main_program: APT configuration file:" ) .
The next entry after 6 hours is "main_program: This step has 1 dataset:"
Any idea why this issue appears?.
The DS version : 11.3
DB : Vertica 7
SQL execution job executing long
Moderators: chulett, rschirm, roy
I don't know about Vertica, but I have seen the same problem with Oracle time and time again over the years. Its optimizer gets easily confused and randomly adds 6 hour delays for no reason, unless you analyze the statistics on the table ahead of time. For what it may be worth, maybe Vertica has the same "feature" as Oracle.
Choose a job you love, and you will never have to work a day in your life. - Confucius
how long does the same sql take if you run it in a sql tool?
"Randomly" taking much longer is usually an indication of one of just a few things... need to run stats, need to index something in the table, database was doing something else (backup? commits? who knows?), network issue (traffic, router gone haywire, again, who knows?) ... things like this. It does not sound like a datastage issue, so eliminate datastage from the variables and just run the sql in a tool to diagnose it.
"Randomly" taking much longer is usually an indication of one of just a few things... need to run stats, need to index something in the table, database was doing something else (backup? commits? who knows?), network issue (traffic, router gone haywire, again, who knows?) ... things like this. It does not sound like a datastage issue, so eliminate datastage from the variables and just run the sql in a tool to diagnose it.