ODS Transfer

[vian_abduljalil]

hello,

the ODSTransfer scrip is running every houre but no data inserted in the table ODS_PRODUCTION_LOG and when I checked the File ODS Log I found this message:

Thu Aug 14 09:55:39 CEST 2014 ODSShopOrderExpansion: Started
Thu Aug 14 09:55:46 CEST 2014 ODSShopOrderExpansion: [SQL][BaseDataSource].....The following fatal SQL exception has occurred:
UPDATE ODS_SHOP_ORDER SET ODS_MODIFIED_DATE_TIME = '2014-08-14T07:50:37.000Z' ,PLANNED_ROUTER_REVISION = '#' ,ITEM_REVISION = 'D/6' ,QTY_RELEASED = '10.000000' ,QTY_DONE = '10.000000' ,SHOP_ORDER = '96998767' ,STATUS = '503' ,ROUTER_REVISION = 'D' ,PLANNED_ITEM = '3BSE018290R1' ,ODS_ACTUAL_START_DATE = '2014-07-31T08:31:00.000Z' ,PLANNED_ROUTER = '3BSE018290R1' ,PRIORITY = '500' ,PLANNED_ROUTER_TYPE = 'U' ,ODS_ACTUAL_COMP_DATE = '2014-08-07T13:27:01.000Z' ,PLANNED_START_DATE = '2014-07-18T00:00:00.000Z' ,CUSTOMER = '10461' ,BOM_TYPE = 'U' ,PLANNED_BOM = '3BSE018290R1' ,SITE = 'SEVA1' ,PLANNED_COMP_DATE = '2014-07-25T00:00:00.000Z' ,ROUTER = '3BSE018290R1' ,ORDER_TYPE = 'PRODUCTION' ,ODS_PLANNED_START_DATE = '2014-07-18T00:00:00.000Z' ,BOM_REVISION = 'D/6' ,ODS_RELEASED_DATE = '2014-07-31T08:31:00.000Z' ,PLANNED_BOM_TYPE = 'D/6' ,QTY_ORDERED = '0.000000' ,ACTUAL_COMP_DATE = '2014-08-07T13:27:01.000Z' ,BOM = '3BSE018290R1' ,QTY_SCRAPPED = '0.000000' ,ERP_ORDER = 'false' ,ROUTER_TYPE = 'U' ,RELEASED_DATE = '2014-07-31T08:31:00.000Z' ,ODS_PLANNED_COMP_DATE = '2014-07-25T00:00:00.000Z' ,ACTUAL_START_DATE = '2014-07-31T08:31:00.000Z' ,ITEM = '3BSE018290R1' ,QTY_TO_BUILD = '10.000000' ,HANDLE = 'ShopOrderBO:SEVA1,96998767' ,PLANNED_ITEM_REVISION = 'D/4'  WHERE SITE = 'SEVA1'  AND SHOP_ORDER = '96998767'
com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
at com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeUpdate(ODSBaseDataSource.java:1112)
at com.sap.me.frame.jdbc.DynamicQuery.executeUpdate(DynamicQuery.java:859)
at com.sap.me.frame.AbstractSqlImplementation.executeUpdate(AbstractSqlImplementation.java:71)
at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:301)
at com.sap.me.ods.olap.ODSBase.ODSInsertOrUpdate(ODSBase.java:919)
at com.sap.me.ods.olap.ODSShopOrderRollupProcess.ODSExpansion(ODSShopOrderRollupProcess.java:183)
at com.sap.me.ods.ODSShopOrderExpansion.main(ODSShopOrderExpansion.java:40)

Thu Aug 14 09:55:46 CEST 2014 ODSShopOrderExpansion: Exception Occurred.
javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1454)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:388)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:338)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:185)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:160)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:306)
at com.sap.me.ods.database.ODSBaseDataSource$StatementWrapper.executeUpdate(ODSBaseDataSource.java:1112)
at com.sap.me.frame.jdbc.DynamicQuery.executeUpdate(DynamicQuery.java:859)
at com.sap.me.frame.AbstractSqlImplementation.executeUpdate(AbstractSqlImplementation.java:71)
at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:301)
at com.sap.me.ods.olap.ODSBase.ODSInsertOrUpdate(ODSBase.java:919)
at com.sap.me.ods.olap.ODSShopOrderRollupProcess.ODSExpansion(ODSShopOrderRollupProcess.java:183)
at com.sap.me.ods.ODSShopOrderExpansion.main(ODSShopOrderExpansion.java:40)
javax.ejb.EJBException: nested exception is: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
at com.sap.me.frame.AbstractDataSource.executeUpdate(AbstractDataSource.java:307)
at com.sap.me.ods.olap.ODSBase.ODSInsertOrUpdate(ODSBase.java:919)
at com.sap.me.ods.olap.ODSShopOrderRollupProcess.ODSExpansion(ODSShopOrderRollupProcess.java:183)
at com.sap.me.ods.ODSShopOrderExpansion.main(ODSShopOrderExpansion.java:40)
Thu Aug 14 09:55:46 CEST 2014 ODSShopOrderExpansion: Completed
Thu Aug 14 09:55:48 CEST 2014 ODSProductionLogExpansion: Started
Thu Aug 14 09:55:50 CEST 2014 ODSProductionLogExpansion: Inserted 0 records in to ODS_PRODUCTION_LOG with 0 errors

what is the reason? how can I resolve the issue??

Regards,

Vian

[sergiy_katerinich]

Hi Vian,

Looks like a bug. Please submit a support ticket. For this investigation, you will be asked for WIP and ODS database dumps.

Regards,

Sergiy

[vian_abduljalil]

Hi Sergiy,

I found that the shop order 96998767 , had wrong value in the table SHOP_ORDER in the field PLANNED_BOM_BO, if you update this value in shop_order table, what happend in the table ODS_TEMP_SHOP_ORDER? did this table update with new value from shop order table or it keep the old information all the time?

the first row in ODS_TEMP_SHOP_ORDER contains information about shop order 96998767 and it seems to preventing ODSproductionETL to execute.

how the ODSproductionETL script actually working?

thank you

Regards,

/Vian

[sergiy_katerinich]

Hi Vian,

ODS_TEMP_SHOP_ORDER should have records only during execution of the script. At the end of execution, all ODS_TEMP_* tables used by the script should be cleared out. So, if you see any record in this table before you start the script (and you are sure that the previous round has been completed), then it is likely there has been a problem during a previous round.

Then you need to rectify the data in ODS tables manually. Or delete the existing data and re-process from scratch (if Archiving is not used).

So, to make a long story short, you need to compare info about this Shop Order from WIP.SHOP_ORDER, ODS.ODS_TEMP_SHOP_ORDER and ODS.ODS_SHOP_ORDER tables. Depending on where and what info exists, further steps should be taken. But this is a really good load of work, not trivial for sure. You'd rather created a support ticket.

Forum thread is not the best place for describing all tech details of ODS data processing. But some info is available here.

Regards,

Sergiy

[vian_abduljalil]

hi Sergiy,

we have already created a support ticket and waiting for answer.

thank you for your response

Regards,

/Vian