OCI lookup

lenny · Post by **lenny** » Tue Mar 18, 2008 1:43 pm

Hello Guru's

I would like to know if any one else faced similar problem and if yes please pass the solution.
I did find some posts similar but not exact

coming to the problem
job fails at transformer stage due to abnormal termination at lookup and writes warring to log then aborts with core dump message.

errors from log
Abnormal termination of stage full_MbrBill_Ld..Transformer_27 detected

DataStage Job 533 Phantom 24912
jobnotify: Unknown error
[24937] DSD.StageRun full_MbrBill_Ld. full_MbrBill_Ld.Transformer_27 2 0/0/1 - core dumped.
DataStage Phantom Finished

job design
Oracle_OCI lookup
|

|
v
Source file ------->transformer--------> Oracle_OCI stage

lookup stage query type was set to user defined.

i changed the settings, query type to Generate Select cluse from columns then this works fine. it works fine only with this option

I need to know why is this happening. or is it known problem with OCI or datastage version

Thank you

chulett · Post by **chulett** » Tue Mar 18, 2008 1:57 pm

If the generated query works and the user-defined one fails, I'd suspect the sql itself is the issue. Don'tcha think?

lenny · Post by **lenny** » Tue Mar 18, 2008 2:07 pm

There is no change in select statement. job will select all rows and finish with abort. I tried all the options in there every does the same except generated. i dont know whats trick....

chulett · Post by **chulett** » Tue Mar 18, 2008 3:19 pm

So the SQL itself is identical between the two? If so, then you need to open a case with your official support provider, I vaguely recall there may be a patch available for this...

ak77 · Post by **ak77** » Tue Mar 18, 2008 4:29 pm

I appreciate your help. Thank you!!!

ArndW · Post by **ArndW** » Wed Mar 19, 2008 7:51 am

Even though I'm not a Duke or a Ray I'll put something in here. Do a reset and see if the generated log entry is any help. A core dump in a transform stage usually means yuo didn something incorrectly that the compiler didn't detect. What are you doing apart from column 1:1 mapping?

lenny · Post by **lenny** » Wed Mar 19, 2008 8:09 am

Apology first
I am really sorry I did not mean to say that even though it sounds like it.

I did try reset. I did not get much info from that may be I missed, so I am going to paste
Log reset messages

DataStage Job 533 Phantom 24912
jobnotify: Unknown error
[24937] DSD.StageRun full_MbrBill_Ld. full_MbrBill_Ld.Transformer_27 2 0/0/1 - core dumped.
DataStage Phantom Finished

Job full_MbrBill_Ld aborted.

(sjWeeklyFull) <- full_MbrBill_Ld: Job under control finished.

Resetting Job full_MbrBill_Ld.
Resetting stage full_MbrBill_Ld..Dump_HipBill.
Resetting stage full_MbrBill_Ld..LC_HipVytra.
Finished Resetting stage full_MbrBill_Ld..Dump_HipBill.
Resetting stage full_MbrBill_Ld..Transformer_27.
Finished Resetting stage full_MbrBill_Ld..LC_HipVytra.
Finished Resetting stage full_MbrBill_Ld..Transformer_27.
Job full_MbrBill_Ld has been reset.
(sjWeeklyFull) <- full_MbrBill_Ld: Job under control finished.

What are you doing apart from column 1:1 mapping?

apart from mapping doing lookup and nothing else...

kumar_s · Post by **kumar_s** » Wed Mar 19, 2008 10:08 am

what is the Query that you are using to do a lookup. Paste both the query, user defined and Generated.

lenny · Post by **lenny** » Wed Mar 19, 2008 10:40 am

kumar_s wrote:what is the Query that you are using to do a lookup. Paste both the query, user defined and Generated. ...

custom SQL
SELECT MRE_0003_MBR_ID_SITE,MRE_3001_MBR_ID_1_3,MRE_3001_MBR_ID_4_5,MRE_3001_MBR_ID_6_9,lpad(MRE_3001_MBR_SEQ_N,2,0)
FROM CCI_hipdwstage1.MbrElg MbrElg
WHERE MRE_0003_MBR_ID_SITE=:1
AND MRE_3001_MBR_ID_1_3=:2
AND MRE_3001_MBR_ID_4_5=:3
AND MRE_3001_MBR_ID_6_9=:4
AND lpad(MRE_3001_MBR_SEQ_N,2,0)=:5
group by MbrElg.MRE_0003_MBR_ID_SITE,MbrElg.MRE_3001_MBR_ID_1_3,MRE_3001_MBR_ID_4_5,MbrElg.MRE_3001_MBR_ID_6_9,MbrElg.MRE_3001_MBR_SEQ_N

Generated SQL
SELECT MRE_0003_MBR_ID_SITE,MRE_3001_MBR_ID_1_3,MRE_3001_MBR_ID_4_5,MRE_3001_MBR_ID_6_9,lpad(MRE_3001_MBR_SEQ_N,2,0)
FROM #$EDW_Stage1_Schema#.MbrElg MbrElg
WHERE MRE_0003_MBR_ID_SITE=:1
AND MRE_3001_MBR_ID_1_3=:2
AND MRE_3001_MBR_ID_4_5=:3
AND MRE_3001_MBR_ID_6_9=:4
AND lpad(MRE_3001_MBR_SEQ_N,2,0)=:5
group by MbrElg.MRE_0003_MBR_ID_SITE,MbrElg.MRE_3001_MBR_ID_1_3,MRE_3001_MBR_ID_4_5,MbrElg.MRE_3001_MBR_ID_6_9,MbrElg.MRE_3001_MBR_SEQ_N

chulett · Post by **chulett** » Wed Mar 19, 2008 11:15 am

In other words, as already noted, they are identical. Well, except for the schema name parameter which is not anything that would cause a core dump. Core dumps come from bugs in the Oracle client, from what I've seen.

ArndW · Post by **ArndW** » Wed Mar 19, 2008 11:54 am

If you have Oracle ODBC configured you could try using that stage to see if you get success or a better error message. The "core" for the DataStage engine means it is likely in Oracle, as a bad transform stage would normally give a DS/Basic error location before coring out.

If you have a savvy UNIX person around and have "dbx" on your machine you can check the stack to see in which program/library the execution was when the job died. Actually, I think you can do that yourself if you have access to dbx, just call up "dbx /path/to/your/core" and then the command "corefile" (I think) which might be sufficient.

chulett · Post by **chulett** » Wed Mar 19, 2008 12:07 pm

No, that's not used. I mean the Oracle client on the DataStage server. Forgot to ask - is the Oracle client version on the DataStage server a different version from what is installed on your actual database?

Ultimately, unless we manage to fix this, any kind of answer about core dumps will need to come from your official support provider - not us. Have you opened a case with them yet?

chulett · Post by **chulett** » Wed Mar 19, 2008 1:34 pm

I assume you mean DBA not dbx, and you'll check with your Oracle folks, but whatever works.

lenny · Post by **lenny** » Wed Mar 19, 2008 1:35 pm

Craig - Yes, we have 9i and 10g client on datastage server and database is 10g.
we are using 9i client. how does this effect

I did not open the tickect yet but ill do it soon...
[/quote]

chulett · Post by **chulett** » Wed Mar 19, 2008 1:37 pm

That shouldn't be a problem, we do the same thing here. What's important to find out is the exact version of the 9i client you have installed - it should be four sets of digits, like 9.2.0.8 for example.