Insert without commit (implicit and explicit) stores data.

[jordi_escodaruiz]

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:

register inserted

register found!

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

What I expected to have was:

register inserted

register NOT found

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

[Sm1tje]

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

[jordi_escodaruiz]

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.

[ThomasZloch]

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

[jordi_escodaruiz]

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:

register inserted

register found!.

register NOT found.

This clarifies the matter.

[jordi_escodaruiz]

I close the thread. I answered myself.