Inconsistent JDBC Adapter

[Former Member]

Hi Guys,

Took me quite a while to find this page haha I prefer the old sdn.. Anyway.. I've got some issues on my JDBC adapter. With the default parameters I'mselecting records from a SQL database:

My Select and Update Queries:

Reason why I'm selecting 500 at a time is cause our ECC system goes up to about 5000 IDOCS then undoes the process cause off some buffer size I suppose which basis does not want to change. So I'm selecting batches of 500 updating their selectindicators to 1 so it should not select the same set twice. This is exactly what it does 😕 If I run this on SQL server itself it works 100% every time. Selecting the top 500 transactions for a specific day. Oh and there's no primary key on the table.. After the select it updates the exact same set.

With PI though, it's works but every other set it duplicates a set Why on earth would it do that? I've tried increasing the poll interval to 30 but it still happens..

Thanks, Jan

[Former Member]

Hi Jan,

What we have done in the similar scenario is to create a stored Proc on the DB side. This can be exactly the same as your current SELECT. With the stored Proc you can then specify the amount of records you want to SELECT at a time and then within the Stored Proc you also do a UPDATE of the records selected. See the stored Proc Example below.

(We have 3 extra columns in our DB table, UpdateStatus, RefNumber and Interface (Date column). This could be a problem because your table already exist however with a skilled DB admin it would be easy to migrate the data to a temp table and then recreate the table with the extra columns and then bring the data in from the temp table.

Look at the update statement in the JDBC Adapter. I hope this will help you.

CREATE PROCEDURE [dbo].[StoredProcName]

-- Add the parameters for the stored procedure here

DECLARE @param1 number

AS

BEGIN

            DECLARE @RefNumber int, @UpDateStatus nvarchar(50)

            Declare @RowCnt int

DECLARE c5 CURSOR

FOR

SELECT RefNumber, UpDateStatus

FROM    dbo.tableName    

where (Interface is null and (UpDateStatus <> 'U' or UpDateStatus is null ))

order by ColumnName desc

FOR UPDATE OF UpDateStatus

OPEN c5

Set @RowCnt = 1

FETCH NEXT FROM c5

INTO @RefNumber,@UpDateStatus

WHILE @@FETCH_STATUS = 0

BEGIN

                        print @RowCnt

                        if @RowCnt < Param1

                        Begin

                                    UPDATE [TabelName]

                                    SET [UpDateStatus] = 'U'

                                    WHERE CURRENT OF c5         

                                    Set @RowCnt = @RowCnt + 1

                                    FETCH NEXT FROM c5

                                    INTO @RefNumber,@UpDateStatus

                        end

                        else

                        Begin

                                    break

                        End

END

close c5

DEALLOCATE c5

SELECT   *

FROM         tableName

where (Interface is null and UpDateStatus = 'U')

END

GO

[baskar_gopalakrishnan2]

>With PI though, it's works but every other set it duplicates a set Why on earth would it do that? I've tried increasing the poll interval to 30 but it still happens..

As Raja said you can create function in SQL and call that function from PI. But that requires change in you target structure and getting help from database team. This happens mainly due to lack of  indexing issue at the database level. You can read even more than 1000 records in a single message every time. We are reading about 2500 records for even every message with complex high volume data.  This should not be a problem. If the database backend has no primary keys and issues, you might see slow response and moderate performance only.

Reading some record and update the colums for the read records to avoid reread next time is perfect design. Duplicates occurs due to some data issues.

[former_member212767]

Set the Transction Isolation Level to serializable in the Advanced settings --> Advanced mode.

[rajasekhar_reddy14]

I worked extensively with JDBC adapter but i never faced this kind of issue(never used Top command).

One alternative is request your DB team to write a function in SQL (they have to write Select and Update statement logic) and call the function in JDBC channel and give some dummy statement in Update statement column and see how it works.

Regards,

Raj

[Former Member]

Will I need to change my data structure for this? This is live at the moment and changing the structure would require a change control which takes time.. These guys don't want to wait so long as it's urgent to get it into our production system. If it's just the select statement I can change it would be cool.. But for now I've asked the SQL guy to create a view I can call which contains just the data I need so I can eliminate the complexed queries. Hoping this would work...