DataStage to Oracle connection establishment taking moretime

amarnaath.ds · Post by **amarnaath.ds** » Mon Jan 27, 2014 7:57 am

Hi Team

simple datarule in Information Analyzer is taking more time when comapared to oracle query executed in oracle query window.
for ex: If i ran oracle query in query window it takes 1 min time to retrive results.Same query i executed through IA it is taking 21 min to finshed the job.

I did some analysis on datastage director log and found that

To establish a connection from oracle to Datastage is taking more time around 25min for small amount of data.If there is huge amount of data connection establishment taking hours of time and job get aborts.

Is there any property to reduse connection establishment from Datastage to oracle.Below is the .odbc.ini file entry.

Driver=/opt/IBM/InformationServer/Server/branded_odbc/lib/VMora25.so
Description=Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000
CachedCursorLimit=32
CachedDescLimit=0
CatalogIncludesSynonyms=1
CatalogOptions=0
DefaultLongDataBuffLen=1024
DescribeAtPrepare=0
EnableDescribeParam=0
EnableNcharSupport=0
EnableScrollableCursors=1
EnableStaticCursorsForLongData=0
EnableTimestampWithTimeZone=0
HostName=plgmtiw2.na.gmacfs.com
LocalTimeZoneOffset=
LockTimeOut=-1
LogonID=UID
password=PWD
PortNumber=1521
ProcedureRetResults=0
SID=tiw2
UseCurrentSchema=1

Please help me to getrid of this issue.
Thanks in Advance

chulett · Post by **chulett** » Mon Jan 27, 2014 9:03 am

Is it taking 25 minutes to "establish a connection" or 25 minutes to start returning records? They are not the same thing.

amarnaath.ds · Post by **amarnaath.ds** » Tue Jan 28, 2014 6:34 am

Craig

It is taking time to establish connection.Please Find below sample of log in IA.

5.35.46: pxbridge(2),0: Connected to Oracle, version 11.02.0000 Oracle 11.2.0.2.0 through driver VMora25.so.

5.35.47: pxbridge(2),0: Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed.

5:56:53 :pxbridge(0),0: Connected to Oracle, version 10.02.0000 Oracle 10.2.0.5.0 through driver VMora25.so

5:56:53: pxbridge(0),0: Output 0 produced 4297403 records

In tha above Log pxbridge(2) is completed at 5.35am and it connected to Pxbridge(0) at 5.56Am.So here the difference is 21 mins
and whole job finnished with in 23 mins.

chulett · Post by **chulett** » Tue Jan 28, 2014 8:52 am

I don't think so. I doubt it connected at 5:56:53 and before another second ticked by it output 4.3 million records. But I'll let others that know IA continue the conversation, I was just trying to get some clarifications on the issue.

stuartjvnorton · Post by **stuartjvnorton** » Tue Jan 28, 2014 5:26 pm

As Craig said, that just doesn't sound right.
It's not going to take that long to connect, yet grab 4M records in under a second. ie don't blindly believe everything you read.

A better way to test the connection time would probably be to run a rule against 1 or 2 records.

Have you tried it at a different time of day? What else is going on at the time (DB, InfoSphere, network)?

amarnaath.ds · Post by **amarnaath.ds** » Wed Jan 29, 2014 2:08 am

Thanks for reply.

I have checked the ColumnAnalysis/DataRules with different amount of data today and found the analysis below.

Oracle version 11.02.0000 -- Source DB
Oracle version 10.02.0000 -- IADB

test for 100000(1lac) records with 5 columns
---------------
IA job finished with in minute.
Not taking any time to eastablish connection with IADB.

test for more than 10lac records with 5 columns
------------
IA job finished in 20 min
connection establishment taking 19 min(to connect IADB) and whole job completes in minute

test for more than 1cr records with 5 columns
-----------
IA job is taking hours of time and job getting aborted with below error message
pxbridge: ODBC function "SQLEndTran(SQL_ROLLBACK)" reported: SQLSTATE = 08S01: Native Error Code = 2396:
Msg = [IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]ORA-02396: exceeded maximum idle time, please connect again
ORA-02063: preceding line from DB (CC_OdbcConnection::rollback, file CC_OdbcConnection.cpp, line 1171)

stuartjvnorton · Post by **stuartjvnorton** » Wed Jan 29, 2014 3:53 am

I'm guessing 1cr is a lot?

Again, it doesn't make sense that it's your connection.
You might be running out of memory and going to scratch disk?
Try testing against the 10lac set and watch your scratch directory.

You also haven't told us what the rule logic is. Some rule logic, like unique, max, min, occurs, etc are much more expensive than scalar logic eg =, > etc.
If you're using unique over 1 million rows and it's going to scratch disk at the same time, you'd expect it to run like a dog.

chulett · Post by **chulett** » Wed Jan 29, 2014 8:02 am

1 Crore is 10M. You'll also see Lakh mentioned here which is 100,000.

chulett · Post by **chulett** » Wed Jan 29, 2014 8:04 am

We saw these same "ORA-02396: exceeded maximum idle time" errors when our connection user had a timeout and the query didn't start returning records within that window. Solution? Remove the timeout.