DB2UDBEnterprise Load against DB2 without DPF?

[lynnliu]

Hello Experts,

I am getting error when loading data in a DB2 table using DB2 UDB enterprise stage via LOAD method. The error as follows.

Code: Select all

DB2_UDB_Enterprise_0,0: DB2 Load Binary Error.
DB2_UDB_Enterprise_0,0: SQLCODE = -27959; SQLSTATE=/opt/datastage/Ascential/DataStage/Scratch/ordb255309099d04d07_
DB2_UDB_Enterprise_0,0: SQL27959N  The partitioned database configuration option "PARTITIONED DB CONFIG" is invalid.  Reason code = "1".
DB2_UDB_Enterprise_0,0: Failed opening fifo /opt/datastage/Ascential/DataStage/Scratch/ordb255309099d04d07_.000 after 90 seconds: No such device or address.
DB2_UDB_Enterprise_0,0: Errors during load (child returned 2); please examine the file /opt/datastage/Ascential/DataStage/Scratch/APT_DB2_LOADMSG_db2inst20.
DB2_UDB_Enterprise_0,0: The runLocally() of the operator failed.
DB2_UDB_Enterprise_0,0: Input 0 consumed 0 records.
DB2_UDB_Enterprise_0,0: Operator terminated abnormally: runLocally did not return APT_StatusOk

I check the sql27959 message.

Code: Select all

..1.Partitioned database configuration options cannot be specified 
in a non-partitioned database environment or when the 
DB2_PARTITIONEDLOAD_DEFAULT registry variable is set to OFF...

checked with DBA, it isn't using DB2 DPF partitioning.

My question is it can't using load method to db2 without DPF?

another is how to improve the performance of loading data to db2 using datastage.

Thanks millions!

Best Regards
Lynn

[clickart]

You could run through the following checks,

1 - Is your target database partitioned? If not, DB2EE stage may not work correctly as it will not be able to load data parallely. You should be using a DB2 API stage
2 - If your target database is partitioned, check if the partitioning type in DB2EE stage is set as DB2?
3 - For improving your load performance, you can try using DB2 Load stage

Hope this helps

[lynnliu]

Thank clickart.

I got almost 100k rows/second loading speed when i using shell

Code: Select all

db2 "load from person.csv of del replace into db2inst1.person" 

.

I got only 2967rows/second when using UDBDB2Load.
(APT_RDBMS_COMMIT_ROW=5000,Load Method=NamedPipe,Load Mode=Insert)

How to improve the loading performance of UDBDB2Load stage.

Thanks advance.

[clickart]

You can check if the data to be loaded is sorted on the index keys of the table.