I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Interesting, unfortunately this way i couldn't handle regular non-error log messages that would adhere to the atomic unit and thus in case of error I would lost part of the log information. 

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Inserting into the LOG File with WITH NC is exactly the way we handle (errors and other non-error messages we want to keep).

Our software is running on multiple customer systems (but my manager does not want to install a deguggable version - customer could see the source code!!! Haha).

If something happens the customers call ... you may imagine how exactly users can describe what happened. So we can look into the logfiles and see what happened.

Interesting, unfortunately this way i couldn't handle regular non-error log messages that would adhere to the atomic unit and thus in case of error I would lost part of the log information. 

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Thanks, Birgitta, for your response!

It's good to know that there is at least another team that use WITH NC in this way, but I'm curious-do you have any additional insights into how NC behaves? The documentation I found is pretty vague, and it seems almost paradoxical that "NO COMMIT" effectively means an immediate commit.

Also, if you know of any additional resources or examples that could help me understand this better, I'd really appreciate it!

Inserting into the LOG File with WITH NC is exactly the way we handle (errors and other non-error messages we want to keep).

Our software is running on multiple customer systems (but my manager does not want to install a deguggable version - customer could see the source code!!! Haha).

If something happens the customers call ... you may imagine how exactly users can describe what happened. So we can look into the logfiles and see what happened.

Interesting, unfortunately this way i couldn't handle regular non-error log messages that would adhere to the atomic unit and thus in case of error I would lost part of the log information. 

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Thanks, Birgitta, for your response!

It's good to know that there is at least another team that use WITH NC in this way, but I'm curious-do you have any additional insights into how NC behaves? The documentation I found is pretty vague, and it seems almost paradoxical that "NO COMMIT" effectively means an immediate commit.

Also, if you know of any additional resources or examples that could help me understand this better, I'd really appreciate it!

Inserting into the LOG File with WITH NC is exactly the way we handle (errors and other non-error messages we want to keep).

Our software is running on multiple customer systems (but my manager does not want to install a deguggable version - customer could see the source code!!! Haha).

If something happens the customers call ... you may imagine how exactly users can describe what happened. So we can look into the logfiles and see what happened.

Interesting, unfortunately this way i couldn't handle regular non-error log messages that would adhere to the atomic unit and thus in case of error I would lost part of the log information. 

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

Thanks, Birgitta, for your response!

It's good to know that there is at least another team that use WITH NC in this way, but I'm curious-do you have any additional insights into how NC behaves? The documentation I found is pretty vague, and it seems almost paradoxical that "NO COMMIT" effectively means an immediate commit.

Also, if you know of any additional resources or examples that could help me understand this better, I'd really appreciate it!

------------------------------
Agostino Sclauzero

Original Message:
Sent: Thu August 29, 2024 03:08 AM
From: Birgitta Hauser
Subject: Transaction Management in Stored Procedures: Is It Safe to Use 'WITH NC' to Preserve Log Insertions?

Inserting into the LOG File with WITH NC is exactly the way we handle (errors and other non-error messages we want to keep).

Our software is running on multiple customer systems (but my manager does not want to install a deguggable version - customer could see the source code!!! Haha).

If something happens the customers call ... you may imagine how exactly users can describe what happened. So we can look into the logfiles and see what happened.

------------------------------
Birgitta Hauser
Database and Software Engineer
Selfemployed - Modernization-Education-Consulting on IBM i
Kaufering
+49 170 5269964

Original Message:
Sent: Wed August 28, 2024 05:17 PM
From: Agostino Sclauzero
Subject: Transaction Management in Stored Procedures: Is It Safe to Use 'WITH NC' to Preserve Log Insertions?

Interesting, unfortunately this way i couldn't handle regular non-error log messages that would adhere to the atomic unit and thus in case of error I would lost part of the log information. 

------------------------------
Agostino Sclauzero

Original Message:
Sent: Wed August 28, 2024 03:38 PM
From: Marius le Roux
Subject: Transaction Management in Stored Procedures: Is It Safe to Use 'WITH NC' to Preserve Log Insertions?

Agostino, 

The behavior is correct in the way you described on a basic level. 

To Quote from the ItJungle Link (https://www.itjungle.com/2016/09/27/fhg092716-story02/) :

The compound statement begins with BEGIN ATOMIC. This means that the entire compound statement is to be treated as a whole. If there were multiple database changes under commitment control, and one of them failed, all changes would be rolled back. In this case, a non-atomic statement would probably work just as well.

If you were to add an exception handler that will always be called (in the case of a Div by 0) then it will not abide by the Atomic rules. 

Here is an example:

CREATE OR REPLACE PROCEDURE SCRATCH.TESTPROC ()
LANGUAGE SQL

P1: BEGIN
    
    declare sqlstate   char(5)       default '00000';
    declare ErrorMsg   varchar(96);

    DECLARE X INT;
    
    declare exit handler for sqlexception
      errorHndlr:begin

        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);
         set ErrorMsg = 'SQLSTATE=' concat sqlstate;
         signal sqlstate '99001'
            set message_text = ErrorMsg;

      end errorHndlr;
    
    P2: BEGIN ATOMIC

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        SET X = 1/0; -- Simulates an error    

    END P2;
    
END P1;

(Note: one can "nest" statements also to make the code more "streamlined") 

HTH

------------------------------
Marius le Roux
Owner
MLR Consulting

Original Message:
Sent: Wed August 28, 2024 11:03 AM
From: Agostino Sclauzero
Subject: Transaction Management in Stored Procedures: Is It Safe to Use 'WITH NC' to Preserve Log Insertions?

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------

I'm working on a stored procedure that performs a series of `INSERT` operations into various destination tables. Below is a simplified example of one of the tables involved:

    CREATE TABLE MY_DATA_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The stored procedure also logs the actions performed in a dedicated log table:

    CREATE TABLE MY_LOG_TABLE (C1 INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, C2 TIME);

The body of the stored procedure is as follows:

    BEGIN ATOMIC

        DECLARE X INT;

        INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);    
        INSERT INTO MY_LOG_TABLE (C2) VALUES (CURRENT TIME);

        SET X = 1/0; -- Simulates an error    
    END;

The transactional behavior is implemented using the `ATOMIC` clause of the `BEGIN...END` block, which handles the rollback in case of an error.

**Problem:**

With this approach, not only is the insertion into `MY_DATA_TABLE` rolled back in case of an error, but so are the insertions into `MY_LOG_TABLE`. However, I would like to preserve the log insertions since they are particularly useful when an error occurs.

I found the following solution, which seems to work, but I'm not sure if this is a proper solution or if I'm just exploiting a side effect of an isolation clause, `WITH NC`, whose primary purpose is different so I expose myself to the risk of unwanted effects.

BEGIN ATOMIC

    DECLARE X INT;

    INSERT INTO MY_DATA_TABLE (C2) VALUES (CURRENT TIME);
    
    INSERT INTO MY LOG_TABLE (C2) VALUES (CURRENT TIME) WITH NC;

    SET X = 1/0; -- Simulates an error
    
END;

------------------------------
Agostino Sclauzero
------------------------------