Hi,
I have created a job, reading from an Informix ODBC, looking up at a Hash File and write to a Unverse Stage and Informix Stage. It is extremely slow.
Please tell me how to design in order to make the job run faster. I don't want to use sequential stage. What I can think of is to do something to the Isolation Level???? Is that right?
Please advice.
Thanks,
Cai Bing
improve performance in Transaction Handling tab
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 49
- Joined: Wed Jun 11, 2003 12:49 am
Re: improve performance in Transaction Handling tab
I am assuming you are refering to DataStage Server Jobs.zhaicaibing wrote:Hi,
I have created a job, reading from an Informix ODBC, looking up at a Hash File and write to a Unverse Stage and Informix Stage. It is extremely slow.
Please tell me how to design in order to make the job run faster. I don't want to use sequential stage. What I can think of is to do something to the Isolation Level???? Is that right?
I am unsure of the details you are attempting to achieve here. Are you attempting to do something like this:
Informix -> Transform < (Informix and Universe)
Hash - - - ^
Please clarify on this, so I can understand the goal of the job.
You probably are aware that utilizing ODBC stages tends to be far slower than using native stages. I am not familiar with Informix, as I use Oracle exclusively. That being said, check your stage to ensure that you are loading in a sufficient sized batches. Within the Oracle Stage, there is an option to increase the batch read, defaulting to 1. Increasing that number to 5,000 (your size may depends on your system's capabilities) would yield performance boost of approximately 3 to 30x the records being read.
Please let me know what your goals are with the actual data.
-T.J.
-
- Participant
- Posts: 49
- Joined: Wed Jun 11, 2003 12:49 am
Goal of my job
Hi,
I wanted to sagregate the duplicate records into 2 tables. The Unique record table will be used in other job. The duplicate record table should be store in Informix.
One of the problems is that this person table has OLDIC and NEWIC columnh, sometime OLDIC is fill in and sometime not and sometime NEWIC is fill in and sometime not. Therefore, I cannot identify the duplicate IC. What I did was to create a temp table that contain a new column called IC. This IC column is filled in with OLDIC if there is no OLDIC then fill in with NEWIC. I put this temp table in Informix.
Then I create another job that calculate the occurrances of the IC and put the 2 columns table into a hash file.
The 3rd job is the job that I described in my previous discussion. This job read the temp table in Informix and lookup the hash file. The constraint in the transfomer stated that COUNT = 1 will go to unique table which is UNIVERSE because I wanted to reuse it in another job, else the duplicate record will go to the Infomix table.
Thanks,
Cai Bing
I wanted to sagregate the duplicate records into 2 tables. The Unique record table will be used in other job. The duplicate record table should be store in Informix.
One of the problems is that this person table has OLDIC and NEWIC columnh, sometime OLDIC is fill in and sometime not and sometime NEWIC is fill in and sometime not. Therefore, I cannot identify the duplicate IC. What I did was to create a temp table that contain a new column called IC. This IC column is filled in with OLDIC if there is no OLDIC then fill in with NEWIC. I put this temp table in Informix.
Then I create another job that calculate the occurrances of the IC and put the 2 columns table into a hash file.
The 3rd job is the job that I described in my previous discussion. This job read the temp table in Informix and lookup the hash file. The constraint in the transfomer stated that COUNT = 1 will go to unique table which is UNIVERSE because I wanted to reuse it in another job, else the duplicate record will go to the Infomix table.
Thanks,
Cai Bing
-
- Participant
- Posts: 103
- Joined: Wed Jul 06, 2005 12:29 am
Re: Goal of my job
Hi Caig,
I would always suggest that if you want to improve your job performance by speed set a proper configuration file i mean point each node in a config file to different location provided it has good disk space,this would speed up your job performance.
Cheers,
Rajeev.
I would always suggest that if you want to improve your job performance by speed set a proper configuration file i mean point each node in a config file to different location provided it has good disk space,this would speed up your job performance.
Cheers,
Rajeev.
zhaicaibing wrote:Hi,
I wanted to sagregate the duplicate records into 2 tables. The Unique record table will be used in other job. The duplicate record table should be store in Informix.
One of the problems is that this person table has OLDIC and NEWIC columnh, sometime OLDIC is fill in and sometime not and sometime NEWIC is fill in and sometime not. Therefore, I cannot identify the duplicate IC. What I did was to create a temp table that contain a new column called IC. This IC column is filled in with OLDIC if there is no OLDIC then fill in with NEWIC. I put this temp table in Informix.
Then I create another job that calculate the occurrances of the IC and put the 2 columns table into a hash file.
The 3rd job is the job that I described in my previous discussion. This job read the temp table in Informix and lookup the hash file. The constraint in the transfomer stated that COUNT = 1 will go to unique table which is UNIVERSE because I wanted to reuse it in another job, else the duplicate record will go to the Infomix table.
Thanks,
Cai Bing