Oracle Bulkload

somers · Post by **somers** » Thu Mar 15, 2007 7:48 am

I'm using the oracle Bulkload option. When loading a table I can see the next message in the detaillog

Message: InterfaceSRLDwhdf09Data..SRLTINT_DWHDF09.IDENT1: ORA-01653: unable to extend table OWNER_DWA.SRLTINT_DWHDF09 by 12800 in tablespace DWAD_L001

Message type = INFO
I'm looking how I can change this message type in Warning or Error

Is there anybode who can help me?

Thanks

chulett · Post by **chulett** » Thu Mar 15, 2007 7:53 am

Welcome! :D

Which Oracle Bulkload 'option' are you using? Any particular stage or are you using some other mechanism to spawn the bulk loader? And these are literally green INFO messages, eh? That seems strange. What exact version of DataStage? Is your Oracle instance co-resident on your DataStage server or is it remote?

I'm assuming you know this, but to actually resolve the space issue you need to work with your DBA.

somers · Post by **somers** » Thu Mar 15, 2007 8:04 am

[quote="chulett"]Welcome! :D

Which Oracle Bulkload 'option' are you using? Any particular stage or are you using some other mechanism to spawn the bulk loader? And these are literally green INFO messages, eh? That seems strange. What exact version of DataStage? Is your Oracle instance co-resident on your DataStage server or is it remote?

I'm assuming you know this, but to actually resolve the space issue you need to work with your DBA.[/quote]

Thanks for your reply,

We are using Datastage 7 on the same server as the oracle database. For the bulkload we are using ORAOCIBL.
I know that the DBA must solve the space issue, but I want a message in the log so i can tell Datastage to stop processing.
Info messages are green.

chulett · Post by **chulett** » Thu Mar 15, 2007 8:09 am

Just to be sure, what exact 7 version do you have? For example - 7.0.1, 7.5.1A, 7.5.2?

somers · Post by **somers** » Thu Mar 15, 2007 8:11 am

Server version 7.0

chulett · Post by **chulett** » Thu Mar 15, 2007 9:20 am

Ok. I'd suggest contacting your Support provider as that is not how it should be working AFAIK. We're using 7.5.1A and 7.5.2 here and I don't recall that as an issue. So the 'solution' might be to upgrade or IBM may be able to supply a patch for your version.

This is with 'Automatic' mode, I assume, yes?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 15, 2007 8:00 pm

Surely, if the error is "unable to extend tablespace", then the issue is with the DBA to change the auto-growth characteristics (or even the actual size) of the tablespace in question?

chulett · Post by **chulett** » Thu Mar 15, 2007 8:14 pm

Surely, but if the job only logs a greenie, your job control has no idea that an error occured and will motor merrily down the highway...

somers · Post by **somers** » Tue Mar 20, 2007 2:08 am

The problem described also exists when the bulkload load invalid numbers into the table. In the detaillog I see the INFO-Message(green) but job has been set to status FINISHED and proces doesn't stop. But it is an error and the proces must stop.

We using the bulkload with automatic load option
Anyone any suggestion?

ray.wurlod · Post by **ray.wurlod** » Tue Mar 20, 2007 6:16 am

Once sqlldr has started things are out of DataStage's control. There is no way that DataStage can detect (while sqlldr is running), much less intercept, any errors in the load.

DataStage can check the sqlldr log and/or bad files after the load is complete, but that's about all.

chulett · Post by **chulett** » Tue Mar 20, 2007 6:30 am

somers wrote:Anyone any suggestion?

As noted earlier, contact your Support provider. See what they have to say about the issue. Since it's not using true 'command line' sqlldr in Automatic mode but rather a direct hook into the database via the API I am still under the impression that it should be able to handle errors like this properly.

Let us know what you find out.

DSXchange

Oracle Bulkload

Oracle Bulkload

answer

Problem also exists by 'invalid number'

Re: Problem also exists by 'invalid number'