Page 1 of 2

OCI lookup

Posted: Tue Mar 18, 2008 1:43 pm
by lenny
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

Posted: Tue Mar 18, 2008 1:57 pm
by chulett
If the generated query works and the user-defined one fails, I'd suspect the sql itself is the issue. Don'tcha think?

Posted: Tue Mar 18, 2008 2:07 pm
by lenny
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....

Posted: Tue Mar 18, 2008 3:19 pm
by chulett
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...

Posted: Tue Mar 18, 2008 4:29 pm
by ak77
I appreciate your help. Thank you!!!

Posted: Wed Mar 19, 2008 7:51 am
by ArndW
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?

Posted: Wed Mar 19, 2008 8:09 am
by lenny
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...

Posted: Wed Mar 19, 2008 10:08 am
by kumar_s
what is the Query that you are using to do a lookup. Paste both the query, user defined and Generated.

Posted: Wed Mar 19, 2008 10:40 am
by lenny
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

Posted: Wed Mar 19, 2008 11:15 am
by chulett
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.

Posted: Wed Mar 19, 2008 11:54 am
by ArndW
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.

Posted: Wed Mar 19, 2008 12:07 pm
by chulett
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?

Posted: Wed Mar 19, 2008 1:34 pm
by chulett
I assume you mean DBA not dbx, and you'll check with your Oracle folks, but whatever works. :wink:

Posted: Wed Mar 19, 2008 1:35 pm
by lenny
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]

Posted: Wed Mar 19, 2008 1:37 pm
by chulett
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.