Inserting performance ; how to tune

[blom0344]

Hello,

Every first of the month I perform an insert of roughly 70.000 new records into a table that consists of some 1.5 million records. The table has a primary key consisting of 5 fields (salesrep,customer,item,year, month). The source database is AS400 (DB2), the target is DB2 7.1.
I really just want to insert a new recordset,there is no chance of a primary key violation during insert, because combination of year and month is , of course , a new one.

The problem i am facing lies in the insert speed. It is down to about 20 rows/second, which means about an hour just for inserting the data. Compared to insert speed in truncated tables, this is 20-50 times slower. I run the datawarehouse and the Powermart server on the same machine (NT, 350Mhz, 512Mb, Powermart 5.1.3). Commit is about after every 10.000 records.

Does anyone have suggestions how to improve the insert speed? All I can think about is dropping the index on the table prior to doing the insert session, but this is not something I want to do by hand (session runs at 6.45 in the morning) I can not find a Powermart functionality that does this. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MattOh]

I've never used DB2 on AS 400, but DB2 normally has a bulk loader. Have you tried using that?

 

[blom0344]

Hello Matt,

Yes I know about loader for DB2 (my target is on DB2 7.1, source is AS400). Point is , we spend big money on Powermart to do a bit of data-transfer at hours we are still asleep. I don't want to get up at 5.oo o'clock to race to work to do a load by hand with the load utility. I want to know how to tune Powermart to do a better job. This is after all the Informatica forum, and Powermart is one of their tools.
Any other idea's?? T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MattOh]

Sorry, while I work for an ETL vendor it's not Informatica. (I'm just lurking here.) I don't know if Powermart can call the bulk loade utility. I just assumed it could.

I wish I could help. Anyone out there??

 

[blom0344]

Hello Matt,

While we're at the subject of ETL....

I am looking for something simple and cheap to get ORACLE data into a DB2 environment. I have no ORACLE key for Powermart (costs a fortune) so I can not use Powermart.
I now do an export to flatfile and use the flatfiles as sources for Powermart mappings....

Is there a specific reason you value Cognos over Business Objects (as seen in the rating of your forums)? I am strictly a BO man, so i am interested in the possible benefits of Cognos. T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[djlocke]

Hello Matt!

There are many ways of improving the performance of your load. First of all you can drop the primary key and recreate it after the load. You simply use a script which is called in the pre session command to drop the index and a script in the post session command to recreate it. If you want to use the bulk loader write the data into a flat file and start the loader in the post session command. This all can be done in one session and the session can be scheduled at a desired time. You just have to ensure that the Informatica server is up and running and can go asleep while it is doing the work for you.

Rgds.
Juergen

 

[blom0344]

Hello Juergen,

Dropping the index on the target , then doing the inserts and the recreating the index was my first thought. Informatica does not provide an easyway to accomplish this. I tried to figure out how to do this using DB2 stored procedure builder , but somehow never managed. Do you mean by script a Java or SQL based stored procedure or just some bat file or .sql file you trigger in pre/post session?
Can you give me examples of these scripts?
(I think I'm trying something the hard way, while it could just be some statement of 1 line text)... T. Blom
Information analist
Shimano Europe
tbl@shimano-eu.com

 

[MattOh]

Hi T.
It looks like (from Juergen's reply) that you must do this in pre and post session commands from a script. That would seem to put it outside Powercenter's control. I'm a little surprised by this, but it is workable.

As for my preferences of Cognos over BO, mostly I'm just a Cognos man. But I never liked the way BO attacked Business Intelligence as just an exercise in advanced reporting. Cognos attacks it from a multitude of directions based on needs, and I like that approach. But this isn't a forum for that discussion.

There are lot's of other ETLs out there if Informatica is too rich for your blood, but I'm a techie and am therefore forbidden from knowing any thing about prices. ;-)

Good luck,
Matt

 

[sreenathkm]

Hello Blom,

I dont know if there is a mechanism to divide the total load in As400, if it was in Oracle we divide the load based on the Rowid and insert data..if not I can never dream of completing my incremental load of our world wide data which will be around 5.5million per day.

Thanks
Sreenathkm

 

[vasa79]

The best way is to drop the index

Calling stored procedures before and after sessions.

The preferred method for dropping and re-creating indexes is to call a stored procedure that drops indexes before running a session. Once the session is finished, you can call another stored procedure that recreates the indexes. After you create the stored procedures in the target database, you configure the session so that the Informatica Server calls one of these procedures before running the session, and the other procedure afterward

 

[blom0344]

Yes, I imagined that dropping the index would be a good idea, but i have searched far and wide for examples how to do this in DB2, but so far nothing came up.
Perhaps you can point to site or other where this is discussed.
(I have lots of stuff on stored procedures, but nothing relates to dropping and rebuilding indexes.........) T. Blom
Information analyst
tbl@shimano-eu.com

 

[khobar]

Hi bloom

Informatica session supports pre-session and post-session.

In the Pre-session call a shell script which in turn calls a STORED procedure to drop the Index ( Check with DB2 syntax for dropping the index i am not sure however Oracle supports DBMS_DDL, DBMS_SQL packages to drop the tables and indexes). In the post-session call a script which will recreate the index after loading

khobar