writing to AS400

[jlito]

Hi guys,

I need some information from you. I am running Informatica ver 6.1 with different database.

1st stage (Oracle 9)

oracle to oracle

2nd stage (AS400 db2 using IBM DB2 connect)

oracle to AS400

In my environment right now, when I'm running any mappings using the 1st stage it is quiet fast. But when i populate to AS400 it is very slow like it takes 2 hours in AS400 compared to 30 mins in oracle database.

Do you have any idea what is causing the difference between the two stages. Also the AS400 is just a direct move from oracle to AS400.

Thanks in advance!

Rgds,
jlito

 

[blom0344]

The thing that comes to mind is the commit-rate. How often do you commit data to the target? Do you insert data into a empty AS400 physical? Are you attempting an insert/update strategy?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jlito]

Hi Blom,

The commit defined is the default value (10000) in AS400. The same value i'm using in my Oracle tables. Do I need to adjust anything? Also we are doing update else insert strategy for some of the tables in AS400.

Thanks,
jlito

 

[blom0344]

jlito,

A simple update else insert strategy may be quite inefficient. Did you have a look at data-driven write-actions with update strategy transformations within the mapping?


T. Blom
Information analyst
tbl@shimano-eu.com

 

[jlito]

hi Blom,


I have tested it also using the update strategy. The throughput in AS400 is only 100 to 300. There is no significant change on the performance using the update strategy in AS400.


Is there any performance gain when we use update strategy rather than update else insert load strategy? In Oracle or other database platform?


Rgds,
jlito

 

[blom0344]

Yes ,there should be. Using update transformations will allow PM to know beforehand whether it will have to perform an update or an insert. This should (at least in theory) be quicker than just using an update else insert strategy at the session level.

The difference you experience between ORACLE and AS400 may well be due to the systems themselves. DB2 is notoriously more finnicky in it's concurrency model than other systems, so perhaps inserts/updates are performed slower.
How about the CPU speed and memory available on both systems? Are they comparable?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[jlito]

Hi Blom,

Have you already tried loading to AS400? If you have, did you have any problems on the performance?

Even if there is a difference in the CPU and server configuration, the result is almost the same in terms of the throughput. Maybe the problem here is the AS400 configuration.

Can you give us some suggestions on this?


Thanks.


rgds,
jlito