DataStage to Oracle connection establishment taking moretime
-
- Participant
- Posts: 9
- Joined: Thu Dec 17, 2009 9:36 am
- Location: india
DataStage to Oracle connection establishment taking moretime
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
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
-
- Participant
- Posts: 9
- Joined: Thu Dec 17, 2009 9:36 am
- Location: india
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.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
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)?
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)?
-
- Participant
- Posts: 9
- Joined: Thu Dec 17, 2009 9:36 am
- Location: india
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)
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)
-
- Participant
- Posts: 527
- Joined: Thu Apr 19, 2007 1:25 am
- Location: Melbourne
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.
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.