Improving performance of DB2 insert

[Gazelle]

Requirement:
We are trying to improve the performance of a simple load job:
Sequential File -> Transformer (adding some metadata columns) -> Db2Connector
The job was taking ~15 minutes to load 4,117,660 rows (the average rowsize is 244 bytes).
The job was run using 2 nodes, which looks like it runs 2 separate Insert statements.
In production, we expect about 10,000,000 rows, so we are concerned about the runtime.

The environment is:
- DataStage v8.5
- Unix AIX v6.1
- DB2 v9.7
- DataStage and DB2 are running on separate LPARs.
- The DB2 LPAR has 12 CPU's and 49 GB of memory.
- The DataStage LPAR has 4 CPU's and 8 GB of memory.
- The DataStage LPAR has a local DB2 instance that has the remote db cataloged.
- A SAN is used for storage: at the moment, only the DB2 LPAR uses high-performance disk.

What's been tried:
- Replacing the DB2Connector with a Copy stage: the job runs in 35 seconds.
- Replace the SequentialFile with a RowGenerator (4117660 rows): the job ran in 15 minutes.
- Messing with DB2 bufferpools, tablespaces, containers, heap sizes: the job ran in 14 minutes.
- Upping the arraysize and recordcount made little difference (but a size of 20000 resulted in a timeout error for 4 nodes but no error with 2 nodes).
- Upping the number of nodes made little difference.
- The Unix sysadmin reports no bottlenecks with CPU, memory, disk or network.
- The job only uses 2 of the 4 CPU's but I don't know why.
- Removing the indexes: the job ran in 9 minutes.

Current problem:
What we are now trying is the "Bulk Load" option of the DB2Connector.
However, the job aborts with the error:
Parallel job reports failure (code 262276)
No other errors or warning appear in the log.

Has anyone come across this error, or can anyone suggest a solution?

Also, feel free to suggest other ways we can improve performance.

[MT]

Hi Gazelle

some thoughs from my side:

DB2 Load is the right way to go on if the number of rows is that big.
You have the option to specify a message file - use it and double check it after the load - you will find all relevant DB2 messages there and maybe a hint to the real problem causing the error.

Indexes have to be rebuilt / restructured when new data comes in so they will always take a considerable amount of time. The more indexes the more time will be consumed but dropping is not a real solution because they are needed for select performance and ensuring uniqueness where required.

We have excellent experiences with DB2 Load performance in DS 8.5.

If you are - for whatever reason - decide NOT to use load (which I would not recommend) you have the option altering the table using the APPEND ON option.
This will avoid a free space search when adding rows and the rows will be added to the "bottom" of the table.

- The job only uses 2 of the 4 CPU's but I don't know why.

What does your DataStage Config File look like?
How many nodes are configured there?


regards
Michael

[Gazelle]

Thanks Michael. It's good to know we're on the right track. Now we just need to get the bulk load working! [Edit: the job fails with the 262276 message before writing the message file]
Since we saved so much time by dropping indexes, we plan to use the "do not update table indexes" option, and wear the cost of the index rebuild in the next query statement.

- The job only uses 2 of the 4 CPU's but I don't know why.

What does your DataStage Config File look like?
How many nodes are configured there?

The Config File is unchanged from the IBM install:

Code: Select all

{
	node "node1"
	{
		fastname "dstagelpar"
		pools ""
		resource disk "/apps/InformationServer/dsadm/InformationServer/Server/Datasets" {pools ""}
		resource scratchdisk "/apps/InformationServer/dsadm/InformationServer/Server/Scratch" {pools ""}
	}
	node "node2"
	{
		fastname "dstagelpar"
		pools ""
		resource disk "/apps/InformationServer/dsadm/InformationServer/Server/Datasets" {pools ""}
		resource scratchdisk "/apps/InformationServer/dsadm/InformationServer/Server/Scratch" {pools ""}
	}
	node "node3"
	{
		fastname "dstagelpar"
		pools ""
		resource disk "/apps/InformationServer/dsadm/InformationServer/Server/Datasets" {pools ""}
		resource scratchdisk "/apps/InformationServer/dsadm/InformationServer/Server/Scratch" {pools ""}
	}
	node "node4"
	{
		fastname "dstagelpar"
		pools ""
		resource disk "/apps/InformationServer/dsadm/InformationServer/Server/Datasets" {pools ""}
		resource scratchdisk "/apps/InformationServer/dsadm/InformationServer/Server/Scratch" {pools ""}
	}
}

We also have a 1-node, 2-node and 8-node config file, all using the same resource and scratch, and the default pool.
We have requested for resource and scratch to be on separate filesystems
Future plans are to have each node using separate filesystems, but there is no spare disk capacity at the moment.
Another future plan is to partition some DB2 tables, but there are no DB2 partitions at the moment, .

[Gazelle]

Well, I do not know the cause of the 262276 error, but I recreated the DB2 Connector stage (i.e. deleted and re-added the stage), and now the bulk load works fine.
So my guess is it was something hanging around from when the stage was defined with a write mode of "Insert".

The job now runs in just over 3 minutes, which is acceptable.
Deferring the index rebuild (for 2 indexes) made little difference.