Skip to Content
author's profile photo Former Member
Former Member

JDBC adapter - Select statement

Hi,

I have XI scenario with JDBC adapter which selects rows in some Oracle DB. Problem is that table is huge, which means that one select with cca. 1000 rows, produces XML message with cca. 100MB, and this sometimes is not good and causes me problems. I tried to solve this with select like this one:

select * from db_table where status = '-1' and rownum <= 100

update db_table set status = '0' where status = '-1' and rownum <= 100

So my question is, is this correct and can I be sure that update statement will update exactly selected rows and nothig else? This means that I will not get correct result. Do I have somethig to affraid off or this will work correctly?

br

mario

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Dec 22, 2008 at 08:50 AM

    One of the best practice in such cases would be to use a staging table. i.e have a copy of the table you are accessing which will have all the contents of the original table.

    While selecting always select the first 100 (or any number which you find is suitable in terms of performance) rows and after that have a delete statement to remove those rows.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 22, 2008 at 09:12 AM

    Hi,

    Maybe you could cumulate the data and sent it. In one of my scenarios I had the same problem. But after cumulating the data it went down around %70.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      According to SAP Note Number: 831162:

      8. Transaction Handling (Sender)

      Q: If I have the following configured in a JDBC Sender:

      Select Query:

      SELECT column FROM TABLENAME WHERE FLAG = "TRUE"

      Update Query:

      UPDATE TABLENAME SET FLAG = "FALSE" WHERE FLAG = "TRUE"

      How do I know that the JDBC adapter will not update newly added rows (rows that were added between the time that the SELECT and UPDATE queries were executed) that were not read in the initial SELECT query?

      A: The SELECT and the UPDATE are run in the same DB transaction, i.e. both statements have the same view on the database.

      Make sure that both statements use the same WHERE clause. An additional requirement for the correct operation of this scenario is the configuration of an appropriate transaction isolation level on the database (i.e., repeatable_read or serializable). You might also consider using a "SELECT FOR UPDATE" statement instead of a plain SELECT statement to ensure proper locking on the database.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.