Abnormal termination of stage, CLOBs, ODBC

MrBlack · Post by **MrBlack** » Wed Jul 15, 2015 12:32 pm

I'm trying to move a CLOB from MS SQL 2000 connecting through ODBC and if I place a NULL in the transformer everything works. When I map the clob, then i get "Abnormal termination of stage my_job..Transformer_1 detected" and that's it. Nothing else to go on that I can see to google on.

The datatype in stages are a LongVarChar, which is what metadata table import wizard defined it as.

I've set my array sizes to 1

I've try using LOB References

I'm using the newer ODBC connector stages

I've tried two different ODBC drivers

I've tried changing the performance settings from "In Process" to "Interprocess"

I've tried not using a transformer but just linking straight from the ODBC to Oracle.

Specifying key columns or not

I'm running out of ideas of what to try to change to either get a more meaningful error message or getting an actual resolution how to get this CLOB data moved over.

priyadarshikunal · Post by **priyadarshikunal** » Wed Jul 15, 2015 3:30 pm

Well, there should be some other error messages or warnings?

Also try splitting the job operations to find out where it actually breaks. There is nothing in the message posted, which suggests any issue in particular.

ray.wurlod · Post by **ray.wurlod** » Wed Jul 15, 2015 4:00 pm

If you reset the job, do you get further diagnostic information logged?

MrBlack · Post by **MrBlack** » Fri Jul 17, 2015 3:07 pm

Upon resetting the job, this is the only additional info that is given:

Code: Select all

From previous run 
DataStage Job 1482 Phantom 30504
terminate called after throwing an instance of 'std::bad_alloc'
  what():  std::bad_alloc
Abnormal termination of DataStage.
Fault type is 6.  Layer type is BASIC run machine.
Fault occurred in BASIC program JOB.648475904.DT.1736552834.TRANS1 at address 274.

I know it's definitely this clob issue because if in the transformer if I don't map the column and just use a @NULL as a place holder for the insert, everything runs fine.

I also just tried using the older ODBC stage that was used before these newer connector stages and still the same results.

ray.wurlod · Post by **ray.wurlod** » Fri Jul 17, 2015 7:39 pm

Fault type is the signal number. 6 is SIGABRT (abort with core dump), which doesn't really add to your knowledge.

Are you trying to load a null into a not nullable column?

ShaneMuir · Post by **ShaneMuir** » Mon Jul 20, 2015 3:16 am

There was a post earlier this month dealing with CLOBs, and the issue was something about the positioning of the the CLOB field. The issue suggested that the CLOB field had to be last field in the schema for the stage to work. Admittedly this was a DB2 issue on parallel, but when looking at the issue, I do remember coming across a similar issue on the IBM website regarding ODBC stages.

Where is the CLOB field in your schema design? If its not the last field, is it possible to try moving it there?

MrBlack · Post by **MrBlack** » Wed Jul 22, 2015 1:53 pm

ShaneMuir - I tried your suggestion to move the clob to be the last column in the table and still no luck.

As an experiment I tried casting the clob to varchar knowing that the data would be truncated and i get this error when I do:

Code: Select all

PL_class_info..ODBC_Connector_1: Exception thrown from CC_AccessorFactory::createAccessor, file CC_AccessorFactory.cpp, line 60: Accessor not allowed at root of metadata hierarchy.  This must be a cursor.

After much research and trying various settings, I've opened an IBM Support ticket today to see what they have to say. My guess is that it's either something with the driver, or I'm still on 11.3 FP1 and I haven't yet upgraded to 11.3 FP 2 and all the other intermediate patches since FP1.

MrBlack · Post by **MrBlack** » Wed Aug 05, 2015 9:09 am

IBM Support finally got back after a couple of exchanges of emails and job designs. Here's the final email from them that has the solution to the problem as well as an additional link to a tech article that others may be interested in: http://www-01.ibm.com/support/docview.w ... wg21683476

Code: Select all

IBM Update8/5/15 8:59 AM
Action taken: Received requested details from customer.                 
                                                                        
============                                                            
                                                                        
Job design had LongVarChar with length of 2147483647. Modifying th      
length to 2147483645 resolved the issue.                                
                                                                        
Sent email to customer.                                                 
                                                                        
=============                                                           
                                                                        
Hello Michael,                                                          
                                                                        
I apologize for the delay in getting back to you.                       
                                                                        
First I'd like to mention that I see that you are using Oracle 12C and  
I want to ensure that you have reviewed the following technote:         
http://www-01.ibm.com/support/docview.wss?uid=swg21683476               
                                                                        
As for your job design, in order for the job to run successfully I just 
had to modify the length of note column for both tables and set it to   
2147483645.                                                             
                                                                        
Please open up the transformer stage and modify the length for both and 
let me know if that helps resolve your issue.                           
                                                                        
Best regards,                                                           
                                                                        
                                                 
                                                                        
=============                                                           
                                                                        
Action plan: Await feedback from customer.

Even though I had used the metadata table import wizard which produced the LongVarChar(2147483647) column, this was incorrect and changing the length that they recommended fixes the error.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 05, 2015 4:39 pm

There is a technical reason underlying all of this.

2147483647 is the largest integer (int32) that can be represented in a twos-complement environment.

However, to store a variable length string, the length has to be stored, so you can not use all 2147483647 positions for characters.

On that basis, to be honest, I would have thought 2147483643 would have been a safer call (since a 32-bit integer requires four bytes).

chulett · Post by **chulett** » Wed Aug 05, 2015 6:18 pm

I was wondering how they came up with that particular value...