Select and update issue occuring when using JDBC sender channel

[Former Member]

All,

We are doing a select statement followed by an update statement.

After the select of Top 500 records, The update statement updates the top 500 that was selected previously with ICC_Date_time.with current dateso that it is not picked by the next select in the next run.

Here are the following statements

SELECT dbo.SAP_Customer_One_2_One.Record_Key as Header_Record_Key, dbo.SAP_Customer_One_2_One.Client as Header_Client, dbo.SAP_Customer_One_2_One.Sales_Org as Header_Sales_Org, dbo.SAP_Customer_One_2_One.Company_Code as Header_Company_Code, dbo.SAP_Customer_One_2_One.SAPID as Header_SAPID, dbo.SAP_Customer_One_2_One.Party_ID as Header_Party_ID, dbo.SAP_Customer_One_2_One.Party_AddressID as Header_Party_AddressID, dbo.SAP_Customer_One_2_One.AddressID as Header_AddressID, dbo.SAP_Customer_One_2_One.Dept_ID as Header_Dept_ID, dbo.SAP_Customer_One_2_One.Org_Name as Header_Org_Name, dbo.SAP_Customer_One_2_One.ADDRESS_LINE_1 as Header_ADDRESS_LINE_1, dbo.SAP_Customer_One_2_One.ADDRESS_LINE_2 as Header_ADDRESS_LINE_2, dbo.SAP_Customer_One_2_One.CITY_NAME as Header_CITY_NAME, dbo.SAP_Customer_One_2_One.STATE_CD as Header_STATE_CD, dbo.SAP_Customer_One_2_One.POSTAL_CD as Header_POSTAL_CD, dbo.SAP_Customer_One_2_One.POSTAL_EXT_CD as Header_POSTAL_EXT_CD, dbo.SAP_Customer_One_2_One.COUNTY_NAME as Header_COUNTY_NAME, dbo.SAP_Customer_One_2_One.COUNTRY_CD as Header_COUNTRY_CD, dbo.SAP_Customer_One_2_One.PO_BOX as Header_PO_BOX, dbo.SAP_Customer_One_2_One.PO_BOX_POSTAL_CD as Header_PO_BOX_POSTAL_CD, dbo.SAP_Customer_One_2_One.GLN as Header_GLN, dbo.SAP_Customer_One_2_One.CUST_LEGAL_NAME as Header_CUST_LEGAL_NAME, dbo.SAP_Customer_One_2_One.DOING_BUSINESS_AS as Header_DOING_BUSINESS_AS, dbo.SAP_Customer_One_2_One.INDUSTRY_CHANNEL_CD as Header_INDUSTRY_CHANNEL_CD, dbo.SAP_Customer_One_2_One.INDUSTRY_SEGMENT_CD as Header_INDUSTRY_SEGMENT_CD, dbo.SAP_Customer_One_2_One.NAICS_PRIMARY as Header_NAICS_PRIMARY, dbo.SAP_Customer_One_2_One.GOVERNMENT_TYPE as Header_GOVERNMENT_TYPE, dbo.SAP_Customer_One_2_One.APPLIED_SCIENCE_CD as Header_APPLIED_SCIENCE_CD, dbo.SAP_Customer_One_2_One.CONSIG_PROGRAM as Header_CONSIG_PROGRAM, dbo.SAP_Customer_One_2_One.CUSTOMER_LAB_CAT as Header_CUSTOMER_LAB_CAT, dbo.SAP_Customer_One_2_One.BUILD_NUM as Header_BUILD_NUM, dbo.SAP_Customer_One_2_One.FLOOR as Header_FLOOR, dbo.SAP_Customer_One_2_One.SUITE as Header_SUITE, dbo.SAP_Customer_One_2_One.MAIL_STOP as Header_MAIL_STOP, dbo.SAP_Customer_One_2_One.Department as Header_Department, dbo.SAP_Customer_One_2_One.Division as Header_Division, dbo.SAP_Customer_One_2_One.Rudid as Header_Rudid, dbo.SAP_Customer_One_2_One.Sug_Acct_Gp as Header_Sug_Acct_Gp, dbo.SAP_Customer_One_2_One.CSR_Prompt as Header_CSR_Prompt, dbo.SAP_Customer_One_2_One.Text_1 as Header_Text_1, dbo.SAP_Customer_One_2_One.Text_2 as Header_Text_2, dbo.SAP_Customer_One_2_One.Cust_Mast_Process_URL as Header_Cust_Mast_Process_URL, dbo.SAP_Customer_One_2_One.Search_Term as Header_Search_Term, dbo.SAP_Org_Alt_ID.ALT_ID, dbo.SAP_Org_Alt_ID.ALT_ID_TYPE, dbo.SAP_Org_Alt_ID.ALT_ID_VALUE, dbo.SAP_Org_Ecom.Ecomm_ID, dbo.SAP_Org_Ecom.Ecomm_type, dbo.SAP_Org_Ecom.Ecomm_Value, dbo.SAP_Org_Phone.Phone_ID, dbo.SAP_Org_Phone.Phone_Type, dbo.SAP_Org_Phone.Phone_Value, dbo.SAP_Org_Phone.PHONE_EXT_NUM FROM (SELECT  TOP 500  Record_Key, ICC_Date_Time, End_Date_Time, Transfer_Type FROM dbo.SAP_Transfer_Batch_Number  with (UPDLOCK, NOWAIT) WHERE Transfer_Type = 'Customer' AND End_Date_Time IS NOT NULL AND ICC_Date_Time IS NULL order  by   dbo.SAP_Transfer_Batch_Number.Record_Key) Batch INNER JOIN dbo.SAP_Customer_One_2_One ON Batch.Record_Key = dbo.SAP_Customer_One_2_One.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Alt_ID ON Batch.Record_Key = dbo.SAP_Org_Alt_ID.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Ecom ON Batch.Record_Key = dbo.SAP_Org_Ecom.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Phone ON Batch.Record_Key = dbo.SAP_Org_Phone.Record_Key ORDER BY Batch.Record_Key

Update:

UPDATE dbo.SAP_Transfer_Batch_Number  SET ICC_Date_Time = GETDATE() WHERE Record_Key IN   (SELECT TOP 500 Record_Key FROM dbo.SAP_Transfer_Batch_Number   WHERE Transfer_Type = 'Customer'  AND End_Date_Time IS NOT NULL AND  ICC_Date_Time IS NULL   ORDER BY dbo.SAP_Transfer_Batch_Number.Record_Key)

Issue

We are missing at random batches of 500 in a day when this is executed in SAP PI. At times update is executed before the select is triggered

1-500 dropped
501-2500 processed
2501-3000 dropped
3001-3500 processed
3501-4000 dropped
4001-4060 processed

Is there any better approach to do this without missing the batches of 500 in SAP PI JDBC sender channel?

[iaki_vila]

HI Taraka,

I suppose you are with SQL Server, please check this Dheeraj Kumar blog

Regards.

[Former Member]

Hi Inaku Vila

We are doing exactly the same method of selecing TOP 500 records and updating the same 500 records.

Also the Transaction Isolation Level is set to Serializable only.