cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC adapter missed data from database

Former Member
0 Kudos

Hello Experts

We have a repeatative issues in JDBC sender adapter .While it is reading data from database sometimes we are facing the problem tat all the data is not read by adapter but PI read date is generated for all the records.

Some times the issue happened like PI processed first three and last three data but missed 2-3 records from the middle portion.

Please help me to know the reason for this error and how to resolve this types of error.

Thanks in advance .

Somenath

Accepted Solutions (1)

Accepted Solutions (1)

stefan_grube
Active Contributor
0 Kudos

In your communication channel, set under "advanced" tab the parameter "Transaction Isolation level" to serializable.

Compare your select and update clauses. The where-clauses should be absosute identical.

Answers (3)

Answers (3)

Former Member
0 Kudos

Hello All,

Thanks for your help .We are now using the advanced configuration transaction Isolation level to serializable.

Till now no more complain received .Let's hope for the best .

Once again thank you very much for your advice .

Regards,

Somenath

justin_santhanam
Active Contributor
0 Kudos

Stefan:

I have quick question on the transaction Isolation level using Serializable. If we use this option, won't the tables be locked and prevent the legacy applications to insert the records??

Raj.

Former Member
0 Kudos

Hello Stefan ,

Thanks a lot for your reply .

We have used the below Select and update statement

I am not expert in select query .Please suggest if the query is okay or it can be the error.

SELECT * FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL ORDER BY [Transaction_ID] ASC

UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Transaction_ID] in ( SELECT [Transaction_ID] FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL) AND [PI_Read_Date] IS NULL)

Please let me know what new features we can get if we set the the advanced parameter serializable.

Hello Navin,

We are using toad at data base side .If the lock occurs in the database side is it possible to generate tHe PI read date as the data is not read by PI?

Thank you once again for you help.

BR.

somenath

Former Member
0 Kudos

By setting the transaction to serializable mode.. what ever the records selected during that period of processing only will get updated ...

I.e records inserted between select and update will not be updated as they have not selected in selection statement for processing..

execute the query in database and see whether the resultset is as per your requirement or not ..and similarly the update statement...

HTH

Rajesh

stefan_grube
Active Contributor
0 Kudos

> SELECT * FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL ORDER BY [Transaction_ID] ASC

>

> UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Transaction_ID] in ( SELECT [Transaction_ID] FROM [database].[dbo].[table] WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL) AND [PI_Read_Date] IS NULL)

select and update is different, so it can happen that the where clause in update hits more or less table entries as select.

Put the same where clause in update as is select:

UPDATE [database].[dbo].[table] SET [PI_Read_Date] = getdate() WHERE [Delivery_Number] = (SELECT TOP 1 [Delivery_Number] FROM [database].[dbo].[table] where [PI_Read_Date] IS NULL ORDER BY [Delivery_Number] ASC) AND [PI_Read_Date] IS NULL

> Please let me know what new features we can get if we set the the advanced parameter serializable.

serializable means that the whole table is locked against concurrent access during the processing of JDBC adapter. So there cannot be any new entries in table between select and update clause.

baskar_gopalakrishnan2
Active Contributor
0 Kudos

Just few cents ... we dont need to check P_Read_date not null two times in the update query.

Since we use Select and Update statement in the jdbc adapter (sender) there is a possibility that data might get changed in between Select and UPdate calls to database. To avoid that we use Transaction Serializable. By doing this, jdbc adapter call is executed in a single thread for both the calls and letting other concurrent calls to wait in the queue at db level.

Former Member
0 Kudos

Hi Somenath,

This occurs due to lock occured on the data which you are trying to read.IF some other is accessing the same data or trying to read the data there there will be a lock on that perticular data and because of that you are unable to access the data.

Especially this happens if you are using TOAD.... if you open TOAD and try to access data through adapter probably you may get such situation.

Cheers!!!!

Naveen.