the problems of ofbiz using DB2

[guo weizhan]

Hi All,

I try to use the ofbiz with DB2, the table can create successfully, but It
thrown the SQLCODE=-803 and SQLCODE=-530 errors when insert the data,  it
look like there are something wrong about the primary key, is any body can
explain this for me and give me some clue how to fix it?

I have read
http://docs.ofbiz.org/display/OFBIZ/Entity+Engine+DB2+Express-C+integration,
there are two problems in this two article, are they fixed? And are they
same problems with my situation?

Many thanks..

I search the error code from DB2 site, here it's:
-----------------------DB2 error code info---------------------
 User Response:

Examine the insert or update value of the foreign key first, and then
compare it with each of the parent key values of the parent table to
determine and correct the problem.

sqlcode : -530


User Response:

Examine the definition for the index identified by index-id .

For an UPDATE statement, ensure that the specified operation is not itself
inconsistent with the uniqueness constraint. If this does not show the
error, examine the object table content to determine the cause of the
problem.

For an INSERT statement, examine the object table content to determine which
of the values in the specified value list violates the uniqueness
constraint. Alternatively, if the INSERT statement contains a subquery, the
object table contents addressed by that subquery must be matched against the
object table contents to determine the cause of the problem.

For a DELETE statement, examine the identified dependent table for unique
constraints on foreign keys that are defined with the rule ON DELETE SET
NULL. This table has a foreign key column included in the identified unique
index that cannot be set to null since there is already a null in the column
for that table.

Federated system users: isolate the problem to the data source failing the
request (refer to the Troubleshooting Guide to determine which data source
is failing to process the SQL statement) and examine the index definitions
and data for the conditions listed previously.

sqlcode : -803
---------------------------------end DB2 error code
info-------------------------------








*below is some trace:*
---- exception report
----------------------------------------------------------
Failure in create operation for entity [InventoryItem]:

org.ofbiz.entity.GenericEntityException: Error while inserting:
[GenericEntity:InventoryItem][createdStamp,2007-12-06 15:33:08.579(
java.sql.Timestamp)]

[createdTxStamp,2007-12-06 15:33:06.375(java.sql.Timestamp
)][currencyUomId,USD(java.lang.String)][facilityId,WebStoreWarehouse(
java.lang.String)]

[inventoryItemId,9001(java.lang.String
)][inventoryItemTypeId,NON_SERIAL_INV_ITEM(java.lang.String)][lastUpdatedStamp,2007-12-06
15:33:08.579

(java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:06.375(
java.sql.Timestamp)][locationSeqId,TLTLTLLL01(java.lang.String
)][ownerPartyId,Company

(java.lang.String)][productId,GZ-2644(java.lang.String)][unitCost,0.0(
java.lang.Double)] (SQL Exception while executing the following:INSERT INTO

OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID, PRODUCT_ID,
PARTY_ID, OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED,
DATETIME_MANUFACTURED,

EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID, UOM_ID, BIN_NUMBER,
LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,
AVAILABLE_TO_PROMISE_TOTAL,

QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE, SERIAL_NUMBER, SOFT_IDENTIFIER,
ACTIVATION_NUMBER, ACTIVATION_VALID_THRU, UNIT_COST, CURRENCY_UOM_ID,

LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?,

?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505,
SQLERRMC=15;OFBIZ.INVENTORY_ITEM, DRIVER=3.50.152)). Rolling back
transaction.
Exception:

org.ofbiz.entity.GenericEntityException
Message: Error while inserting:
[GenericEntity:InventoryItem][createdStamp,2007-12-06 15:33:08.579

(java.sql.Timestamp)][createdTxStamp,2007-12-06 15:33:06.375(
java.sql.Timestamp)][currencyUomId,USD(java.lang.String
)][facilityId,WebStoreWarehouse

(java.lang.String)][inventoryItemId,9001(java.lang.String
)][inventoryItemTypeId,NON_SERIAL_INV_ITEM(java.lang.String)][lastUpdatedStamp,2007-12-06


15:33:08.579(java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:06.375
(java.sql.Timestamp)][locationSeqId,TLTLTLLL01(java.lang.String)]

[ownerPartyId,Company(java.lang.String)][productId,GZ-2644(java.lang.String
)][unitCost,0.0(java.lang.Double)] (SQL Exception while executing the

following:INSERT INTO OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID,
INVENTORY_ITEM_TYPE_ID, PRODUCT_ID, PARTY_ID, OWNER_PARTY_ID, STATUS_ID,
DATETIME_RECEIVED,

DATETIME_MANUFACTURED, EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID,
UOM_ID, BIN_NUMBER, LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,

AVAILABLE_TO_PROMISE_TOTAL, QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE,
SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER, ACTIVATION_VALID_THRU,
UNIT_COST,

CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?,

?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-803,
SQLSTATE=23505, SQLERRMC=15;OFBIZ.INVENTORY_ITEM, DRIVER=3.50.152))
---- stack trace ---------







---- exception report
----------------------------------------------------------
Failure in create operation for entity [UserLogin]:

org.ofbiz.entity.GenericEntityException: Error while inserting:
[GenericEntity:UserLogin][createdStamp,2007-12-06 15:33:16.285(
java.sql.Timestamp)]

[createdTxStamp,2007-12-06 15:33:16.265(java.sql.Timestamp
)][currentPassword,47ca69ebb4bdc9ae0adec130880165d2cc05db1a(java.lang.String
)]

[lastUpdatedStamp,2007-12-06
15:33:16.285(java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06
15:33:16.265(java.sql.Timestamp)][partyId,hds

(java.lang.String)][userLoginId,demoshop(java.lang.String)] (SQL Exception
while executing the following:INSERT INTO OFBIZ.USER_LOGIN (USER_LOGIN_ID,

CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, HAS_LOGGED_OUT,
REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE, LAST_TIME_ZONE,

DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID) VALUES (?,
?, ?, ?, ?, ?,

?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-530,
SQLSTATE=23503, SQLERRMC=OFBIZ.USER_LOGIN.USER_PARTY, DRIVER=3.50.152)).
Rolling back

transaction.
Exception: org.ofbiz.entity.GenericEntityException
Message: Error while inserting:
[GenericEntity:UserLogin][createdStamp,2007-12-06 15:33:16.285

(java.sql.Timestamp)][createdTxStamp,2007-12-06 15:33:16.265(
java.sql.Timestamp
)][currentPassword,47ca69ebb4bdc9ae0adec130880165d2cc05db1a

(java.lang.String)][lastUpdatedStamp,2007-12-06 15:33:16.285(
java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:16.265(
java.sql.Timestamp)]

[partyId,hds(java.lang.String)][userLoginId,demoshop(java.lang.String)] (SQL
Exception while executing the following:INSERT INTO OFBIZ.USER_LOGIN

(USER_LOGIN_ID, CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED,
HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE,
LAST_TIME_ZONE,

DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, LAST_UPDATED_STAMP,
LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID) VALUES (?,
?, ?, ?, ?, ?,

?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-530,
SQLSTATE=23503, SQLERRMC=OFBIZ.USER_LOGIN.USER_PARTY, DRIVER=3.50.152))
---- stack trace -----

[BJ Freeman]

803 AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX IN
INDEX
SPACE indexspace-name CONSTRAINS COLUMNS OF THE TABLE SO NO
TWO
ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF
EXISTING ROW IS Xrid

Explanation: The table that is the object of the INSERT or UPDATE
operation is constrained (by UNIQUE INDEX in the INDEX SPACE
indexspace-name to have unique values in certain columns. Completion of
the requested INSERT or UPDATE would result in duplicate values
occurring in row rid.


I am guessing from this that the length of the index is limited.
so it is seeing the new index as a duplicate.

it could be. what is written, but I don't have first hand experience.

guo weizhan sent the following on 12/6/2007 12:14 AM:

> Hi All,
>
> I try to use the ofbiz with DB2, the table can create successfully, but It
> thrown the SQLCODE=-803 and SQLCODE=-530 errors when insert the data,  it
> look like there are something wrong about the primary key, is any body can
> explain this for me and give me some clue how to fix it?
>
> I have read
> http://docs.ofbiz.org/display/OFBIZ/Entity+Engine+DB2+Express-C+integration,
> there are two problems in this two article, are they fixed? And are they
> same problems with my situation?
>
> Many thanks..
>
> I search the error code from DB2 site, here it's:
> -----------------------DB2 error code info---------------------
>  User Response:
>
> Examine the insert or update value of the foreign key first, and then
> compare it with each of the parent key values of the parent table to
> determine and correct the problem.
>
> sqlcode : -530
>
>
> User Response:
>
> Examine the definition for the index identified by index-id .
>
> For an UPDATE statement, ensure that the specified operation is not itself
> inconsistent with the uniqueness constraint. If this does not show the
> error, examine the object table content to determine the cause of the
> problem.
>
> For an INSERT statement, examine the object table content to determine which
> of the values in the specified value list violates the uniqueness
> constraint. Alternatively, if the INSERT statement contains a subquery, the
> object table contents addressed by that subquery must be matched against the
> object table contents to determine the cause of the problem.
>
> For a DELETE statement, examine the identified dependent table for unique
> constraints on foreign keys that are defined with the rule ON DELETE SET
> NULL. This table has a foreign key column included in the identified unique
> index that cannot be set to null since there is already a null in the column
> for that table.
>
> Federated system users: isolate the problem to the data source failing the
> request (refer to the Troubleshooting Guide to determine which data source
> is failing to process the SQL statement) and examine the index definitions
> and data for the conditions listed previously.
>
> sqlcode : -803
> ---------------------------------end DB2 error code
> info-------------------------------
>
>
>
>
>
>
>
>
> *below is some trace:*
> ---- exception report
> ----------------------------------------------------------
> Failure in create operation for entity [InventoryItem]:
>
> org.ofbiz.entity.GenericEntityException: Error while inserting:
> [GenericEntity:InventoryItem][createdStamp,2007-12-06 15:33:08.579(
> java.sql.Timestamp)]
>
> [createdTxStamp,2007-12-06 15:33:06.375(java.sql.Timestamp
> )][currencyUomId,USD(java.lang.String)][facilityId,WebStoreWarehouse(
> java.lang.String)]
>
> [inventoryItemId,9001(java.lang.String
> )][inventoryItemTypeId,NON_SERIAL_INV_ITEM(java.lang.String)][lastUpdatedStamp,2007-12-06
> 15:33:08.579
>
> (java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:06.375(
> java.sql.Timestamp)][locationSeqId,TLTLTLLL01(java.lang.String
> )][ownerPartyId,Company
>
> (java.lang.String)][productId,GZ-2644(java.lang.String)][unitCost,0.0(
> java.lang.Double)] (SQL Exception while executing the following:INSERT INTO
>
> OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID, INVENTORY_ITEM_TYPE_ID, PRODUCT_ID,
> PARTY_ID, OWNER_PARTY_ID, STATUS_ID, DATETIME_RECEIVED,
> DATETIME_MANUFACTURED,
>
> EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID, UOM_ID, BIN_NUMBER,
> LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,
> AVAILABLE_TO_PROMISE_TOTAL,
>
> QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE, SERIAL_NUMBER, SOFT_IDENTIFIER,
> ACTIVATION_NUMBER, ACTIVATION_VALID_THRU, UNIT_COST, CURRENCY_UOM_ID,
>
> LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP)
> VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?,
>
> ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505,
> SQLERRMC=15;OFBIZ.INVENTORY_ITEM, DRIVER=3.50.152)). Rolling back
> transaction.
> Exception:
>
> org.ofbiz.entity.GenericEntityException
> Message: Error while inserting:
> [GenericEntity:InventoryItem][createdStamp,2007-12-06 15:33:08.579
>
> (java.sql.Timestamp)][createdTxStamp,2007-12-06 15:33:06.375(
> java.sql.Timestamp)][currencyUomId,USD(java.lang.String
> )][facilityId,WebStoreWarehouse
>
> (java.lang.String)][inventoryItemId,9001(java.lang.String
> )][inventoryItemTypeId,NON_SERIAL_INV_ITEM(java.lang.String)][lastUpdatedStamp,2007-12-06
>
>
> 15:33:08.579(java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:06.375
> (java.sql.Timestamp)][locationSeqId,TLTLTLLL01(java.lang.String)]
>
> [ownerPartyId,Company(java.lang.String)][productId,GZ-2644(java.lang.String
> )][unitCost,0.0(java.lang.Double)] (SQL Exception while executing the
>
> following:INSERT INTO OFBIZ.INVENTORY_ITEM (INVENTORY_ITEM_ID,
> INVENTORY_ITEM_TYPE_ID, PRODUCT_ID, PARTY_ID, OWNER_PARTY_ID, STATUS_ID,
> DATETIME_RECEIVED,
>
> DATETIME_MANUFACTURED, EXPIRE_DATE, FACILITY_ID, CONTAINER_ID, LOT_ID,
> UOM_ID, BIN_NUMBER, LOCATION_SEQ_ID, COMMENTS, QUANTITY_ON_HAND_TOTAL,
>
> AVAILABLE_TO_PROMISE_TOTAL, QUANTITY_ON_HAND, AVAILABLE_TO_PROMISE,
> SERIAL_NUMBER, SOFT_IDENTIFIER, ACTIVATION_NUMBER, ACTIVATION_VALID_THRU,
> UNIT_COST,
>
> CURRENCY_UOM_ID, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
> CREATED_TX_STAMP) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-803,
> SQLSTATE=23505, SQLERRMC=15;OFBIZ.INVENTORY_ITEM, DRIVER=3.50.152))
> ---- stack trace ---------
>
>
>
>
>
>
>
> ---- exception report
> ----------------------------------------------------------
> Failure in create operation for entity [UserLogin]:
>
> org.ofbiz.entity.GenericEntityException: Error while inserting:
> [GenericEntity:UserLogin][createdStamp,2007-12-06 15:33:16.285(
> java.sql.Timestamp)]
>
> [createdTxStamp,2007-12-06 15:33:16.265(java.sql.Timestamp
> )][currentPassword,47ca69ebb4bdc9ae0adec130880165d2cc05db1a(java.lang.String
> )]
>
> [lastUpdatedStamp,2007-12-06
> 15:33:16.285(java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06
> 15:33:16.265(java.sql.Timestamp)][partyId,hds
>
> (java.lang.String)][userLoginId,demoshop(java.lang.String)] (SQL Exception
> while executing the following:INSERT INTO OFBIZ.USER_LOGIN (USER_LOGIN_ID,
>
> CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED, HAS_LOGGED_OUT,
> REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE, LAST_TIME_ZONE,
>
> DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID) VALUES (?,
> ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-530,
> SQLSTATE=23503, SQLERRMC=OFBIZ.USER_LOGIN.USER_PARTY, DRIVER=3.50.152)).
> Rolling back
>
> transaction.
> Exception: org.ofbiz.entity.GenericEntityException
> Message: Error while inserting:
> [GenericEntity:UserLogin][createdStamp,2007-12-06 15:33:16.285
>
> (java.sql.Timestamp)][createdTxStamp,2007-12-06 15:33:16.265(
> java.sql.Timestamp
> )][currentPassword,47ca69ebb4bdc9ae0adec130880165d2cc05db1a
>
> (java.lang.String)][lastUpdatedStamp,2007-12-06 15:33:16.285(
> java.sql.Timestamp)][lastUpdatedTxStamp,2007-12-06 15:33:16.265(
> java.sql.Timestamp)]
>
> [partyId,hds(java.lang.String)][userLoginId,demoshop(java.lang.String)] (SQL
> Exception while executing the following:INSERT INTO OFBIZ.USER_LOGIN
>
> (USER_LOGIN_ID, CURRENT_PASSWORD, PASSWORD_HINT, IS_SYSTEM, ENABLED,
> HAS_LOGGED_OUT, REQUIRE_PASSWORD_CHANGE, LAST_CURRENCY_UOM, LAST_LOCALE,
> LAST_TIME_ZONE,
>
> DISABLED_DATE_TIME, SUCCESSIVE_FAILED_LOGINS, LAST_UPDATED_STAMP,
> LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, PARTY_ID) VALUES (?,
> ?, ?, ?, ?, ?,
>
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (DB2 SQL Error: SQLCODE=-530,
> SQLSTATE=23503, SQLERRMC=OFBIZ.USER_LOGIN.USER_PARTY, DRIVER=3.50.152))
> ---- stack trace -----
>