batch insert - update tables (related by foreign keys) simultaneously

[Former Member]

Hi,

I have two tables that are connected by a foreign key.

I am using two batch transaction like:

  PERFORM bdc_transaction USING 'SM30'
                          CHANGING p_subrc.

With this I first update one table and then i do a refresh to the bdcdata restart the filling process of the bdcdata and then perform a new transaction.

The issue is that i want to prevent that if the second table is somehow being used by someone how do I assure that there is rollback and that no data is inserted in both the tables.

Remember that the bdc_transaction allways performs a commit work.

If there is a better way to do this than recorring to transactions please mention it.

Best regards,

Luis

[Former Member]

Hello,

Hope, you want to update either u2018both the tableu2019 or nothing. Not like if the second table is updated, then only update the first one.

In this case, you can use the LUW u2013 Logical unit of work concept.

Hope, you are familiar with the Update function module. Create a new one & update the table directly using the ABAP SQL statements.

So that, you can update u2018both the tableu2019 or rollback if any issues.

Please let me know if any issue in implementing LUW.

Regards,

Selva K.

[Former Member]

Yes I want to update both tables or nothing that was what I meant to say tks

Ok I think that will do the trick.

I am not familiar with the LUW concept but I'll do some research, if I have any troubles I'll tell you about it.

I forgot to mention my first idea to solve the problem it was by recorring to block using queue and unqueue functions but there still was a minor chance for an error to occur, so I thought it wasn't a nice work arround...

Tks allot for the reply.

Cumpliments,

Luis

[Former Member]

Another thing I forgot to mention.

I need the transport order code because the tables must be manually transported from development to production prior to its aproval by a manager.

That was why I used the batch transaction, to associate the tables update to the same transport order, so this way I think it won't work with LUW...

Another hint for this?

[Former Member]

Hello,

By LUW, I meant to create an update function module to implement the required logic. Once you go through the LUW topic, you will be familiar with how it can be implemented.

If you create any workbench object, it will ask for a transport order & you donu2019t have any issue in transporting the same.

Regards,

Selva K.

[che_eky]

If you use Batch Input then I cannot see anyway to rollback updates to the first table. As you say you get a commit work after the first batch input call.

What do you mean "if the second table is somehow being used by someone"? You can control access to tables using the SAP lock mechanism. Using this you will be able to see if anyone is locking the first or second table before you start any updates.

[Former Member]

This is how i am processing this:<br />

<p />

<pre class="jive-pre"><code class="jive-code jive-java">* lock second table

PERFORM lock_table USING <font color="navy">'E'</font>

<font color="navy">'table2'</font>

CHANGING ls_subrc.

&nbsp;

&nbsp;

PERFORM batch_insert_first_table USING ...

CHANGING ls_subrc.

• unlock second table

PERFORM lock_table USING <font color="navy">'D'</font>

<font color="navy">'table2'</font>

CHANGING ls_subrc.

&nbsp;

• lock first table

PERFORM lock_table USING <font color="navy">'E'</font>

<font color="navy">'table1'</font>

CHANGING ls_subrc.

&nbsp;

• BATCH ZHRGT_T705V

PERFORM batch_insert_second_table USING ...

CHANGING ls_subrc.

&nbsp;

• unlock first table

PERFORM lock_table USING <font color="navy">'D'</font>

<font color="navy">'Vtable1'</font>

CHANGING ls_subrc.

&nbsp;

</code></pre><br />

<br />

now the lock table is like this:<br />

<br />

<pre class="jive-pre"><code class="jive-code jive-java">FORM lock_table USING p_action

p_table

CHANGING p_subrc.

&nbsp;

CALL FUNCTION <font color="navy">'VIEW_ENQUEUE'</font>

EXPORTING

action = p_action

enqueue_mode = <font color="navy">'E'</font>

view_name = p_table

• ENQUEUE_RANGE = <font color="navy">' '</font>

• TABLES

• SELLIST =

EXCEPTIONS

client_reference = 1

foreign_lock = 2

invalid_action = 3

invalid_enqueue_mode = 4

system_failure = 5

table_not_found = 6

OTHERS = 7.

IF sy-subrc 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

&nbsp;

ENDFORM. <font color="red">&quot; LOCK_TABLE</font>

&nbsp;

</code></pre><br />

<br />

each of those * PERFORM batch* as this structure<br />

<br />

<pre class="jive-pre"><code class="jive-code jive-java">

PERFORM bdc_dynpro USING arg arg.

PERFORM bdc_field USING arg arg.

&nbsp;

• ...

• ...

• ...

&nbsp;

PERFORM bdc_dynpro USING arg arg.

PERFORM bdc_field USING arg arg.

PERFORM bdc_transaction USING <font color="navy">'SM30'</font>

CHANGING p_subrc.

</code></pre>

the only problem that can happen, it is if someone locks the second table, while the first one is being updated.

now the main porpuse of the batch it is because one of the table it's SAP standard, and i has been told to dont use direct SQL over them.

the other as a said it was because I need to associate a transport order to the tables updates.

I realize it is possible using LUW to do this, but as there is a standard table I can't use direct inserts so I implemented it this way.

Cumpliments and tks.

Edited by: LuisMesquita on May 19, 2010 6:39 PM