By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Insert performance into DB2 using informatica

P: n/a
I need to speed up the inserts through informatica, and I came across
insert buf option with db2 packages for import statements and how we
can use that option to do the same in java programs. Are there any
packages to be bound for informatica powercenter, so that I can rebind
them with this option?
My informatica person says that Informatica does not bind any packages
to the database. Is he missing something. Any one who has worked in
informatica with db2, please help me guys,,.

Nov 7 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Ian
Arun Srinivasan wrote:
I need to speed up the inserts through informatica, and I came across
insert buf option with db2 packages for import statements and how we
can use that option to do the same in java programs. Are there any
packages to be bound for informatica powercenter, so that I can rebind
them with this option?
My informatica person says that Informatica does not bind any packages
to the database. Is he missing something. Any one who has worked in
informatica with db2, please help me guys,,.
He is correct. The native DB2 driver for Informatica uses DB2 CLI to
talk to the database.

Theoretically you can bind ALL of the CLI packages with buffered
inserts, but I would not recommend this (that would apply to pretty
much every application connecting to the database except those using
the JDBC Type 4 driver). This can create issues if the applications
have not been coded to handle errors that may occur asynchronously from
the actual call to insert the record.

The best options are using the parallel load option in Informatica
or using the bulk mode in Informatica.

However, one other option is just to have Informatica write to a flat
file and use LOAD. If you are doing multiple loads to the same table,
or have to deal with recoverable loads, then this is generally a better
idea than using bulk mode in Informatica idea because you can specify
load options that aren't available with bulk load mode.

One other thing you can do to enhance insert performance is to put your
tables in APPEND mode. Certain things will prevent this (MDC) or make
it a bad idea (if you have a clustering index and care about the tables'
cluster ratio).

Good luck,
Ian Bjorhovde

Nov 9 '07 #2

P: n/a
Ian wrote:
Arun Srinivasan wrote:
>I need to speed up the inserts through informatica, and I came across
insert buf option with db2 packages for import statements and how we
can use that option to do the same in java programs. Are there any
packages to be bound for informatica powercenter, so that I can rebind
them with this option?
My informatica person says that Informatica does not bind any packages
to the database. Is he missing something. Any one who has worked in
informatica with db2, please help me guys,,.

He is correct. The native DB2 driver for Informatica uses DB2 CLI to
talk to the database.

Theoretically you can bind ALL of the CLI packages with buffered
inserts, but I would not recommend this (that would apply to pretty
much every application connecting to the database except those using
the JDBC Type 4 driver). This can create issues if the applications
have not been coded to handle errors that may occur asynchronously from
the actual call to insert the record.

The best options are using the parallel load option in Informatica
or using the bulk mode in Informatica.

However, one other option is just to have Informatica write to a flat
file and use LOAD. If you are doing multiple loads to the same table,
or have to deal with recoverable loads, then this is generally a better
idea than using bulk mode in Informatica idea because you can specify
load options that aren't available with bulk load mode.

One other thing you can do to enhance insert performance is to put your
tables in APPEND mode. Certain things will prevent this (MDC) or make
it a bad idea (if you have a clustering index and care about the tables'
cluster ratio).

Good luck,
Ian Bjorhovde
Beware of load. You need a dedicated load slot as it can conflict with
queries and even backup (I believe that with V8 both Backup and load do
short locks on the history file). I have seen load force take out a backup.
If you do lots (perhapse 100) of loads per day you will regularly purge
the history file. you will also need to manage failed loads which are much
more unpleasant than failed inserts

Parallel inserts are much faster than a standard insert.

Colin Booth
Nov 10 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.