Cannot view full Log Detail for SQL *Loader

vcsasikala · Post by **vcsasikala** » Mon Dec 22, 2008 7:04 am

Hi,
I'm having a unix script ICF_LAB_SERV_LINE_DETL.sh. This script has been called in Sequencer job. This script is used to load a table with SQL Loader. While running, I got an SQL Loader error.

Checking in Datastage Director I can view

LabStandardProcessingSeq.61.JobControl (ExecSH): Error when executing command: /dso/dsorg1a/apps/scripts/ICF_LAB_SERV_LINE_DETL.sh dsoreg1 dsoreg1 dsosit /dso/dsorg1a/apps/scripts 61 /dso/dsorg1a/clients/upsx/lab/output /dso/dsorg1a/clients/upsx/lab/logs
*** Output from command was: ***
partition already exists,hence dropping

SQL*Loader: Release 9.2.0.6.0 - Production on Mon Dec 22 05:44:50 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Load completed - logical record count 10.
Datafile= /dso/dsorg1a/clients/upsx/lab/output/LabStdProc_61.txt
Datafile Record count=10
Loaded records= 10
Allowed Error record count= 0
Minimum records to load:10
All records were loaded, but load failed due to some Oracle errors
Check log file /dso/dsorg1a/clients/upsx/lab/logs/ICF_LAB_SERV_LINE_DETL_61.log for more details

When trying to get the log details from command line as

dsjob -logdetail dsoreg1 LabStandardProcessingSeq.61 166

I can get only

LabStandardProcessingSeq.61.JobControl (ExecSH): Error when
executing command:
/dso/dsorg1a/apps/scripts/ICF_LAB_SERV_LINE_DETL.sh dsoreg1 dsoreg1
dsosit /dso/dsorg1a/apps/scripts 61
/dso/dsorg1a/clients/upsx/lab/output
/dso/dsorg1a/clients/upsx/lab/logs
*** Output from command was: ***
partition already exists,hence dropping
SQL*Loader: Release 9.2.0.6.0 - Production on Mon Dec 22 05:44:50
2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Why can't I get the whole log details that I can view in Director?

chulett · Post by **chulett** » Mon Dec 22, 2008 8:08 am

I honestly have no idea. Seems like the kind of question you'd need to ask a vendor to get the answer for, however.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 22, 2008 2:00 pm

An Oracle vendor at that. This problem has occurred while executing the sqlldr command which, curious as it may seem, is not a part of DataStage.

You might include an echo command in the script to discover the command line argument values that it received, and another to reflect the actual sqlldr command that it attempted to execute. But the problem does appear to be in the data (and/or in the constraints for the target of the load).

chulett · Post by **chulett** » Mon Dec 22, 2008 2:14 pm

No, they are saying that the entire thing can be seen when they View the log using the Director but a command line "-logdetail" call only pulls out an abbreviated amount, so that's all DataStage.

I'm just not aware on any limitation of what that particular dsjob option may or may not have.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 22, 2008 2:16 pm

OK. Yes, there are limits in -logdetail. Source for dsjob can be found in the manuals.

vcsasikala · Post by **vcsasikala** » Tue Dec 23, 2008 12:53 am

Hi,
Thanks for the reply.

I can able to view the log details with more than 30 lines also using -logdetail command. Why not SQL Loader alone?

ray.wurlod · Post by **ray.wurlod** » Tue Dec 23, 2008 1:30 am

Dunno. Perhaps look for a terminator character (perhaps ctrl-D) in the actual Oracle log message.

It's total bytes, not total lines, that fills the buffer.

chulett · Post by **chulett** » Tue Dec 23, 2008 9:07 am

As I said, you'd need to ask the people that wrote the product and that ain't us. Open a case with your official support provider and let us know what they say.