topic Re: Insert without commit (implicit and explicit) stores data. in Application Development and Automation Discussions

Insert without commit (implicit and explicit) stores data.

jordi_escodaruiz — Thu, 29 May 2008 10:30:49 GMT

Hi all:

I have this piece of code:

REPORT  ztest.

DATA: wa_zsic_abonos_chk TYPE zsic_abonos_chk. "Is a transparent table

START-OF-SELECTION.

   wa_zsic_abonos_chk-bukrs = 'MU01'.
   wa_zsic_abonos_chk-belnr = '99999'.
   wa_zsic_abonos_chk-gjahr = '2008'.

  INSERT into zsic_abonos_chk values wa_zsic_abonos_chk.
  IF sy-subrc <> 0.
    WRITE:/ 'register NOT inserted'.
  ELSE
    WRITE:/ 'register inserted'.
  ENDIF.

  SELECT SINGLE *
    INTO   wa_zsic_abonos_chk
  FROM zsic_abonos_chk
  WHERE bukrs = 'MU01'
    AND belnr = '99999'
    AND gjahr = '2008'.

  IF sy-subrc = 0.
    WRITE:/ 'register found!.'.
  ELSE.
    WRITE:/ 'register NOT found.'.
  ENDIF.

When I run this code I get this results:

This is surprising to me, because I did not write the COMMIT WORK after the Insert.

What I expected to have was:

zsic_abonos_chk is a transparent table without buffering, and the register does not exists before the Insert.

The Database system is running below SAP is DB2.

Can someone explain this behavior?

Thanks in advance

Jordi

Re: Insert without commit (implicit and explicit) stores data.

Sm1tje — Thu, 29 May 2008 10:34:36 GMT

When you use BYPASSING BUFFER in your select, I expect that record will not be found.

When inserting the data, records will be 'stored' in the table already, ready to be commited. If anything goes wrong, you will need to do a ROLLBACK. If no errors occur and you do NO commit, data can be implicitely commited by the system.

Edited by: Micky Oestreich on May 29, 2008 12:34 PM

Re: Insert without commit (implicit and explicit) stores data.

ThomasZloch — Thu, 29 May 2008 10:42:02 GMT

I believe it's because the insert and select statements are within the same internal mode i.e. database connection.

Another process looking for the same record would not find it before you commit or your program ends.

Greetings

Thomas

Re: Insert without commit (implicit and explicit) stores data.

jordi_escodaruiz — Thu, 29 May 2008 10:58:05 GMT

Hi Micky:

Thanks for your answer.

The transparent table ZSIC_ABONOS_CHK does not have the Buffering attribute active, i.e.does not have buffering.

I tried adding BYPASSING BUFFER to the select statement, and I still get the register.

I made another check: Adding ROLLBACK WORK between the INSERT and the SELECT, and I still got the register (result register found!).

INSERT into zsic_abonos_chk values wa_zsic_abonos_chk.
  IF sy-subrc <> 0.
    WRITE:/ 'register not inserted'.
  ENDIF.

  ROLLBACK WORK.

  SELECT SINGLE * BYPASSING BUFFER
    INTO   wa_zsic_abonos_chk
  FROM zsic_abonos_chk
  WHERE bukrs = 'MU01'
    AND belnr = '99999'
    AND gjahr = '2008'.

It's really confusing. I think something is going wrong.

Re: Insert without commit (implicit and explicit) stores data.

jordi_escodaruiz — Wed, 30 Jul 2008 08:09:34 GMT

Today I spent some time to close this thread.

I made this test:

REPORT  ZTEST.

DATA: wa_zsic_abonos_chk TYPE zsic_abonos_chk. "Is a transparent table

START-OF-SELECTION.

   wa_zsic_abonos_chk-bukrs = 'MU01'.
   wa_zsic_abonos_chk-belnr = '999949'.
   wa_zsic_abonos_chk-gjahr = '2008'.

  INSERT into zsic_abonos_chk values wa_zsic_abonos_chk.
  IF sy-subrc <> 0.
    WRITE:/ 'register NOT inserted'.
  ELSE.
    WRITE:/ 'register inserted'.
  ENDIF.

  SELECT SINGLE *
    INTO   wa_zsic_abonos_chk
  FROM zsic_abonos_chk
  WHERE bukrs = wa_zsic_abonos_chk-bukrs
    AND belnr = wa_zsic_abonos_chk-belnr
    AND gjahr = wa_zsic_abonos_chk-gjahr.

  IF sy-subrc = 0.
    WRITE:/ 'register found!.'.
  ELSE.
    WRITE:/ 'register NOT found.'.
  ENDIF.

  ROLLBACK WORK.

  SELECT SINGLE *
    INTO   wa_zsic_abonos_chk
  FROM zsic_abonos_chk
  WHERE bukrs = wa_zsic_abonos_chk-bukrs
    AND belnr = wa_zsic_abonos_chk-belnr
    AND gjahr = wa_zsic_abonos_chk-gjahr.

  IF sy-subrc = 0.
    WRITE:/ 'register found!.'.
  ELSE.
    WRITE:/ 'register NOT found.'.
  ENDIF.

The result is:

This clarifies the matter.

Re: Insert without commit (implicit and explicit) stores data.

jordi_escodaruiz — Wed, 30 Jul 2008 08:10:48 GMT

I close the thread. I answered myself.