How to increase the performance of the job

pradkumar · Post by **pradkumar** » Tue Mar 25, 2008 11:30 am

Hi Everyone,

I have developed a job, which select records from table and inserts into other table.

Here the source is sqlserver in one location and target is Informix on unix in different location.I have used ODBC stage for both source table and target table. I have transformer stage for mapping the columns. Currently it is taking 20 mins to load 32000 records.

If i do the same test with the source database and target database are in the same location it is taking not more than 25 secs to load the same data.
There are indexes and keys on the target side.

My DBA did the same test by loading the same data in the informix database which is on different location through FTP. It took 25 secs to load the same data. But why is it taking 20 mins if i load the same data through datastage job.

I guess there is lot of difference in the performance.I want to increase the performance of this job. So that it loads data with greater speed.

Please suggest me some tips to increase the performance of this job.

Thanks,

ray.wurlod · Post by **ray.wurlod** » Tue Mar 25, 2008 3:20 pm

I bet your DBA was not using ODBC protocols. So why are you? You have the choice of "native" client interface, or using bulk loader.

pradkumar · Post by **pradkumar** » Wed Mar 26, 2008 12:01 pm

Hi Ray,

Thanks for the reply.

Iam using Informix CLI stage as target . If i run the same job the job is getting aborted and the error message is as follows.

Attempting to Cleanup after ABORT raised in stage CopyOfBostonDatabase2..Informix_CLI_33

DataStage Job 200 Phantom 3848
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage CopyOfBostonDatabase2..Informix_CLI_33
DataStage Phantom Aborting with @ABORT.CODE = 3

Iam importing the table definitions through plugin meta definitons.

Could you tell where iam going wrong.

Thanks

yddert · Post by **yddert** » Wed Mar 26, 2008 10:50 pm

Looks like ODBC connection details (userid and password) are wrong and table name.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 27, 2008 12:32 am

Not using ODBC!

Could it be that one of your data items is larger than the allowed data type, or that you might be trying to send null to a non-nullable column?

Either of these has been known to cause an access violation.

Also check with your support provider that you have the exact version of the Informix CLI software and DataStage stage type needed for your version of Informix Dynamic Server (IDS). IDS is known to be particularly finicky about client software versions.