Home » Infrastructure » Unix » problem redirecting output from sqlldr to log file (HP-UX B.11.23 U ia64 )
problem redirecting output from sqlldr to log file [message #459873] Tue, 08 June 2010 11:17 Go to next message
chryss_zz
Messages: 3
Registered: June 2010
Location: Italy
Junior Member
Hello everyone,

I'm not Unix-savvy, but until today I have never had so much problem figuring out a solution.
My problem is the following: I have a Unix shell that launches SQLLDR (normally the files are pretty large).
I use 2 logs: one for the Unix shell and one for SQLLDR.
I need the Unix log file to be updated every time SQLLDR reaches a certain number of loaded lines, so (at a certain point in my script) I'm launching the following command:

#!/usr/bin/sh
#variable init and some controls
echo Some stuff > $LOGF 

LOADER="sqlldr ${SQL_USER}/${SQL_PWD}@${SQL_ISTANCE} ${FILECTL} direct=true rows=100000 data=${FILENAME} log=${FILELOGCTL} discard=${FILEDIS} bad=${FILEBAD} errors=9999"

${LOADER} >> $LOGF 2>&1


I was expecting the $LOGF to be updated each time sqlldr loads 100.000 records, but log file remains empty until the load is finished.

Once the load is finished the log file is will look like:

Some stuff
Save data point reached - logical record count 100000.
Save data point reached - logical record count 200000.
etc.

If I launch the sqlldr command directly from the command prompt the SQLLDR outputs the data point reached correctly...it seems that when I launch the sqlldr from the script the response is somehow buffered until the loading process is finished.
Do you guys have any idea why?
I should probably mention that the previous message are immediately inserted into the log file.

Thanks! Smile
Re: problem redirecting output from sqlldr to log file [message #459878 is a reply to message #459873] Tue, 08 June 2010 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm not a Unix expoert but it seems normal to me as screen (tty) is a character device when file is a block device and so input into file is buffered until a flush is done or buffer is full.

Try to use "tee" command but I'm not sure this will change something:
${LOADER} | tee -a $LOGF 2>&1


Regards
Michel
Re: problem redirecting output from sqlldr to log file [message #459955 is a reply to message #459878] Wed, 09 June 2010 02:52 Go to previous messageGo to next message
chryss_zz
Messages: 3
Registered: June 2010
Location: Italy
Junior Member
Thank you Michel, but the
${LOADER} | tee -a $LOGF 2>&1
doesn't work either.

Quote:
... it seems normal to me as screen (tty) is a character device when file is a block device and so input into file is buffered until a flush is done or buffer is full.

I have to disagree because I'm launching the same command on a different machine with the same Unix and Oracle version and the log file is correctly updated. Also the
2>&1
should redirect the stdout to the log file.
On this machine as long as I run the sqlldr command directly from Putty and I don't redirect the feedback to a log file I can see the updates in real time.
When I launch the same command from a shell the results are displayed/logged when sql loader finishes loading the data.
Is there some system parameter that I should check?
Re: problem redirecting output from sqlldr to log file [message #459969 is a reply to message #459955] Wed, 09 June 2010 03:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said I'm not a Unix expert, it was just an idea, maybe you should post this question in a Unix forum, they may have more clues.

Regards
Michel
Re: problem redirecting output from sqlldr to log file [message #459970 is a reply to message #459969] Wed, 09 June 2010 03:46 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That's the standard way buffering on STDIO works.

There are some tricks like unbuffer that can be used, but they all depend on the specific libc implementation on the machine, and while I know some Unixes I don't know HP-UX.
Re: problem redirecting output from sqlldr to log file [message #459971 is a reply to message #459955] Wed, 09 June 2010 03:52 Go to previous messageGo to next message
kaluscha
Messages: 2
Registered: June 2010
Location: Bad Wurzach
Junior Member
I'm not sure whether sqlldr gives output to stdout or stderr.

Try "sqldr ... >out 2>err" to check.

Strange things may happen if you redirect stdout and stderr to the same file as you mentioned. As the output buffers of stdout and stderr are independent, the sequence of messages will be influenced by buffering, e.g. newer messages to stdout may be flushed from the stdout buffer before older messages to stderr because the stderr buffer isn't full yet.

If you really need the log output, you may consider using a pseudo terminal (I would have posted the wikipedia link if the f* forum software would have accepted the link). However, this means you have to write a programme that acts as a server process for this pty.
Re: problem redirecting output from sqlldr to log file [message #460202 is a reply to message #459971] Thu, 10 June 2010 02:57 Go to previous messageGo to next message
chryss_zz
Messages: 3
Registered: June 2010
Location: Italy
Junior Member
Hi Kaluscha,

I've tried what you suggested, but the log file keeps being updated at the end of the run.
Also sqlldr gives the output to stdout (because the log for stderr is always empty).
Thanks for your suggestions!
Re: problem redirecting output from sqlldr to log file [message #460214 is a reply to message #460202] Thu, 10 June 2010 03:45 Go to previous message
kaluscha
Messages: 2
Registered: June 2010
Location: Bad Wurzach
Junior Member
Usually, if output goes to stdout and stdout is a regular file it will be buffered by libc; and you can't do anything about it (unless you had the source code of sqlldr).
You might consider using a named pipe:
mknod np p
cat np & 
sqlldr ... >np

Pipes have a limited buffer so output may appear quicker (depending on your operating system).
Previous Topic: Migration to ORACLE ASM files on AIX 6.1 and ESS Shark 8700
Next Topic: AIX memory usage
Goto Forum:
  


Current Time: Thu Mar 28 15:51:58 CDT 2024