Oracle Connector is not pulling the records !!

John Daniel · Post by **John Daniel** » Tue Nov 15, 2011 9:34 am

Hi ,

I'm using Oracle connector stage for the first time.

Have totally 19 columns in the Table definiton
Out those 1 column length is 16383 of Data type LongNVarChar

I'm running the jobs in Dev and IT..its running fine. giving the count.

It seems Dev and IT data is not the correct data.

If i run the same job from ST . Its finished without any warnings but Oracle Connector is not pulling any records from the ST database.

query is given below :

SELECT
OE.NAME AS Account_NAME ,
AA.FILE_NAME AS Attachment,
EA.CREATED AS Message_Sent,
EA.NAME AS Subject,
EM.EMAIL_BODY AS Message,
EM.EMAIL_SNDR_ADDR AS EMAIL_SNDR,
EM.EMAIL_TO_LINE AS EMAIL_TO ,
EAX.ATTRIB_45 AS Inbox,
EA.OWNER_LOGIN AS Owned_By,
EA.PREV_ACT_ID AS Previous_Activity_ID,
EAX.X_PRODUCT_LINE AS X_PRODUCT_LINE,
SR.SR_NUM AS SR_NUM,
EAX.X_SERVICE_TYPE AS Service_Type,
EA.EVT_STAT_CD AS Status,
EA.TODO_CD AS EMAIL_Type,
EA.PAR_EVT_ID AS Parent_Communication_Id,
SC.LAST_NAME AS Last_Name,
SC.FST_NAME AS First_Name,
SC.X_PIN AS PIN
FROM
SIEBEL.S_EVT_ACT EA,
SIEBEL.S_ORG_EXT OE,
SIEBEL.S_ACTIVITY_ATT AA,
SIEBEL.S_EVT_MAIL EM,
SIEBEL.S_EVT_ACT_X EAX,
SIEBEL.S_SRV_REQ SR,
SIEBEL.S_CONTACT SC,
EIMUSER.T_SEC17A_DAT TSEC
WHERE
EA.TARGET_OU_ID = OE.ROW_ID(+)
AND EA.ROW_ID = AA.PAR_ROW_ID(+)
AND EA.ROW_ID = EM.PAR_ROW_ID(+)
AND EA.ROW_ID = EAX.PAR_ROW_ID(+)
AND EA.SRA_SR_ID = SR.ROW_ID(+)
AND EA.TARGET_PER_ID=SC.ROW_ID(+)
AND EA.TODO_CD in ('Email - Inbound', 'Email - Outbound')
AND EA.EVT_STAT_CD='Done'
AND SC.CON_CD ='Participant'
AND EA.X_RECORD_TYPE = 'Email'
AND NVL(EA.PAR_EVT_ID, 'Y') <> 'Y'
AND EA.PAR_EVT_ID <> 'No Match Row Id'
AND EA.PAR_EVT_ID = TSEC.PRIMARY_ID AND EA.ROW_ID = TSEC.MID_ID
AND TSEC.TYPE_CD = 'SECDSBL36'
AND ROWNUM <=10

ShaneMuir · Post by **ShaneMuir** » Tue Nov 15, 2011 1:10 pm

Obvious question first.

Are there records in the table which would match your criteria? Is all the expected data in all the expected tables?

John Daniel · Post by **John Daniel** » Tue Nov 15, 2011 3:29 pm

Hi Muir,

Data is there !!

Good Question !!

qt_ky · Post by **qt_ky** » Tue Nov 15, 2011 9:40 pm

I also have to ask the obvious question. Your "ST database" may have data, but just to confirm, does that data match your query? Have you tried running the same SQL outside of DataStage, like with an Oracle client, against your "ST database" and confirming it returns records?

Did you find a workaround?

John Daniel · Post by **John Daniel** » Tue Nov 15, 2011 11:02 pm

Hi ,

Ran the same query from TOAD. Its displaying the records .

Please help me in this....

ray.wurlod · Post by **ray.wurlod** » Wed Nov 16, 2011 12:18 am

Look in the job log to verify that the SQL statement that you think is being execute is, indeed, the one that is being executed. Sometimes they get changed in minor ways.

qt_ky · Post by **qt_ky** » Wed Nov 16, 2011 8:06 am

Along those lines, double check the job log to make sure it's pointed to the correct database. :D

qt_ky · Post by **qt_ky** » Wed Nov 16, 2011 8:07 am

And just to take it one step further, double check the database entry on the DataStage server. If it uses a tnsnames.ora entry then check that it matches the tnsnames.ora entry on your client.

John Daniel · Post by **John Daniel** » Wed Nov 16, 2011 12:23 pm

ray.wurlod wrote:Look in the job log to verify that the SQL statement that you think is being execute is, indeed, the one that is being executed. Sometimes they get changed in minor ways. ...

Checked in the log view ..of that jobs everything is perfect.

Do you want me to change the array size. ??

You want me to change the Buffer - in the Advance Tab of each stage.
Please suggest on this.

Thanks,
John

John Daniel · Post by **John Daniel** » Wed Nov 16, 2011 12:24 pm

qt_ky wrote:And just to take it one step further, double check the database entry on the DataStage server. If it uses a tnsnames.ora entry then check that it matches the tnsnames.ora entry on your client.

Everything is Mathes !!
Please suggest on this.

Thanks,
John

lstsaur · Post by **lstsaur** » Wed Nov 16, 2011 2:48 pm

Since you said that you are able to pull the data using TOAD. What I would like you to do one more test is to login the server and use SQL*PLUS to run the same SQL against ST database. If It's still NOT returning any record, that means your SQL is wrong. If it's returning records that means you need to add TNS_ADMIN variable on your client side. Let us know the result.

John Daniel · Post by **John Daniel** » Wed Nov 16, 2011 3:20 pm

Run the query as per your word !! its giving the result .

In the TNS_ADMIN : I'm able to run the other jobs all jobs are pointed to ST database.
Have 5 more jobs which has oracle connector those are working fine and giving the result...

Concern is that Job is finishing successfully with 'Zero' records pulled out from DB.
Please advice

John

John Daniel · Post by **John Daniel** » Wed Nov 16, 2011 3:26 pm

This is happending because of Length and Data type issue

Long is the Data type and Length is 16383

Thanks in advance

John

ShaneMuir · Post by **ShaneMuir** » Thu Nov 17, 2011 5:28 am

If the job is running without extracting any data and with no warnings, this would suggest that there is nothing wrong with the query - it can obviously run. If a tnsnames entry was missing it would normally abort.

So a couple of other things to check:
1. Is there a message handler which is suppressing warnings at all? It seems strange that if the exact same query can pull data in toad but not in datastage, that for some reason the record must be being dropped. Lines would rarely be dropped without at least one warning.

2. In your original query what is the purpose of the 'AND ROWNUM <=10'. Do you really only want to pull 10 rows?

John Daniel · Post by **John Daniel** » Fri Nov 18, 2011 10:16 am

Hi ,

Tried that option as well ...its pulling 10 records from the DB

If i removed that join condition it is not pulling all the data from DS

Please let me know how to go ahead..

Thanks,
John

DSXchange