Logging Sql statements

Dear All, I have a script which calls another file which contains a list of insert statements to be loaded into the database. The file contents are - dbaccess test /export/home/user1/emp 2> emp.log; dbaccess test /export/home/user1/dept 2> dept.log; dbaccess test /export/home/user1/leave 2> leave.log; dbaccess test /export/home/user1/sal 2> sal.log; The output is written to respective log files, which tells me 1 row inserted for each sql statement. If there is an error in a file and if there are 100 records it becomes difficult to track where the error occured. Is there a way i can log the actual insert clause into a file & if there is an error in that statement i can find out. Thanks in advance. Regards, lloyd

Hi, did you try with "set explain" in the test procedure? Mensaje citado por LLOYD SERRAO <lloyd_s@rediffmail.com>: > Dear All, > > I have a script which calls another file which contains a list of insert > statements to be loaded into the database. The file contents are - > > dbaccess test /export/home/user1/emp 2> emp.log; > dbaccess test /export/home/user1/dept 2> dept.log; > dbaccess test /export/home/user1/leave 2> leave.log; > dbaccess test /export/home/user1/sal 2> sal.log; > > The output is written to respective log files, which tells me 1 row inserted > for each sql statement. If there is an error in a file and if there are 100 > records it becomes difficult to track where the error occured. Is there a way > i can log the actual insert clause into a file & if there is an error in that > statement i can find out. Thanks in advance. > > Regards, > > lloyd > > > >

isn't there something about a violations table.... Something you can turn on that during an insert, and violations will be stored in another table.... Then you can fix those records and get them inserted.... I remember a concept like that, but don't remember the specifics... Norma Jean -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of pamadeo@cespi.unlp.edu.ar Sent: Wednesday, September 15, 2004 6:59 AM To: ids@iiug.org Subject: Re: Logging Sql statements [3425] Hi, did you try with "set explain" in the test procedure? Mensaje citado por LLOYD SERRAO <lloyd_s@rediffmail.com>: > Dear All, > > I have a script which calls another file which contains a list of > insert statements to be loaded into the database. The file contents > are - > > dbaccess test /export/home/user1/emp 2> emp.log; dbaccess test > /export/home/user1/dept 2> dept.log; dbaccess test > /export/home/user1/leave 2> leave.log; dbaccess test > /export/home/user1/sal 2> sal.log; > > The output is written to respective log files, which tells me 1 row > inserted for each sql statement. If there is an error in a file and > if there are 100 records it becomes difficult to track where the error > occured. Is there a way i can log the actual insert clause into a file > & if there is an error in that statement i can find out. Thanks in advance. > > Regards, > > lloyd > > > > ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Can you use dbload. If so, there are many more options available. One is you can have an error file of rejected recs. "Sebastian, ...." <NormaJean.Sebastian@tellabs.com> Sent by: forum.subscriber@iiug.org 09/15/2004 08:15 AM To: ids@iiug.org cc: Subject: RE: Logging Sql statements [3426] isn't there something about a violations table.... Something you can turn on that during an insert, and violations will be stored in another table.... Then you can fix those records and get them inserted.... I remember a concept like that, but don't remember the specifics... Norma Jean -----Original Message----- From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf Of pamadeo@cespi.unlp.edu.ar Sent: Wednesday, September 15, 2004 6:59 AM To: ids@iiug.org Subject: Re: Logging Sql statements [3425] Hi, did you try with "set explain" in the test procedure? Mensaje citado por LLOYD SERRAO <lloyd_s@rediffmail.com>: > Dear All, > > I have a script which calls another file which contains a list of > insert statements to be loaded into the database. The file contents > are - > > dbaccess test /export/home/user1/emp 2> emp.log; dbaccess test > /export/home/user1/dept 2> dept.log; dbaccess test > /export/home/user1/leave 2> leave.log; dbaccess test > /export/home/user1/sal 2> sal.log; > > The output is written to respective log files, which tells me 1 row > inserted for each sql statement. If there is an error in a file and > if there are 100 records it becomes difficult to track where the error > occured. Is there a way i can log the actual insert clause into a file > & if there is an error in that statement i can find out. Thanks in advance. > > Regards, > > lloyd > > > > ----------------------------------------- ============================================================ The information contained in this message may be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any reproduction, dissemination or distribution of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. Thank you. Tellabs ============================================================

Hi, one possibility is to use option "-e" for dbaccess. This will make dbaccess write the SQL statement to stdout, additionally to the (normal) output. In case of error, there will also be some error message, which then can be directly associated with the SQL that caused the error. Unfortunately, this will all go to stdout (rather than stderr). You still get the usual messages to stderr, though. Try a command like this to see if/how it can work for you (csh syntax): (dbaccess -e db1 db1.sql > db1.out) >& db1.err Regards, Martin -- Martin Fuerderer IBM Informix Development Munich Data Management Solutions forum.subscriber@iiug.org wrote on 15.09.2004 12:20:17: > Dear All, > > I have a script which calls another file which contains a list of insert statements to be loaded into the database. The file contents are - > > dbaccess test /export/home/user1/emp 2> emp.log; > dbaccess test /export/home/user1/dept 2> dept.log; > dbaccess test /export/home/user1/leave 2> leave.log; > dbaccess test /export/home/user1/sal 2> sal.log; > > The output is written to respective log files, which tells me 1 row inserted for each sql statement. If there is an error in a file and if there are 100 records it becomes difficult to track where the error occured. Is there a way i can log the actual > insert clause into a file & if there is an error in that statement i can find out. Thanks in advance. > > Regards, > > lloyd > >

One thing I haven't seen yet, is to embed the dbacess -e command within a shell script and execute the shell as follows: dbaccess_script.sh 1>dbaccess_output 2>&1 (You probably do not need to use the shell, I do so since I tend to use CRON quite a bit.) Lots of ways to do something like this but I like this one .... :) Take care. Clifton LLOYD SERRAO <lloyd_s@rediffmail.com> wrote: Dear All, I have a script which calls another file which contains a list of insert statements to be loaded into the database. The file contents are - dbaccess test /export/home/user1/emp 2> emp.log; dbaccess test /export/home/user1/dept 2> dept.log; dbaccess test /export/home/user1/leave 2> leave.log; dbaccess test /export/home/user1/sal 2> sal.log; The output is written to respective log files, which tells me 1 row inserted for each sql statement. If there is an error in a file and if there are 100 records it becomes difficult to track where the error occured. Is there a way i can log the actual insert clause into a file & if there is an error in that statement i can find out. Thanks in advance. Regards, lloyd

Dear All, Thanks for the feedback. Regards, Lloyd