Polling DB2 table with SELECT SORT & Update RRS feed

  • Question

  • Hi,

    Is it possible sort & select from DB2 table and then update using BizTalk DB2 adapter? 

    If possible can you provide a sample query please.


    Thursday, January 16, 2020 2:56 PM

All replies

  • I would recommend you call a stored procedure from the DB2 adapter that does all of that.
    Thursday, January 16, 2020 5:50 PM
  • Hi Daniel,

    Yes it is. You can use a select statement directly within your receive location config or wrap the call within an sp (as Colin states), which is strongly recommended. I've done this in the past.

    - select the record ids of your data based on your criteria and a process indicator (i.e. 'not started') into a temp table
    - using the results of this temp table, select your records
    - using the results of this temp table, update a process indicator to 'In Progress' so the same records are not selected again.

    To limit results in DB2 add the following to your where clause:

    fetch first 50 rows only

    Order results is the same as SQL:

    order by [col1], [col2]

    Hope this helps.

    Samad Khan


    Thursday, January 16, 2020 7:49 PM