5 Replies Latest reply on Apr 2, 2021 4:14 PM by Arya Nymeria

    Read and write to the same table? Can we?

    Arya Nymeria Active Member

      Hello friends, I have a requirement in such a way that I have to use the target table as source to get the key value and update the same table. The reason being is i need to pull the file date from stage table. Apparently i have to identify a record that exists in history table but not in stage table. so I am using the below sql.

       

      SELECT

      KEY,

      (SELECT FILE_DT FROM STAGE where rownum=1) AS FILE_DT

      FROM

      HISTORY H

      WHERE NOT EXISTS

      (SELECT HPC_NUM, NAME FROM STAGE S

        WHERE

      H.HPC_NUM =S.HPC_CODE

         AND

         H.NAME=S.NAME

         );

      So to avoid any locks because the data gets read and write to the same table, how to avoid table or db lock?

        • 1. Re: Read and write to the same table? Can we?
          Nico Heinze Guru

          In order to avoid this kind of conflict completely, you will need some "intermediate storage" (such as another DB table or a flat file) to store the key attributes plus a flag (to indicate whether this record exists in the staging table); then in the next step you source this "intermediate storage" and can write to the target table without incurring any locks or whatever.

           

          Yes, that's not the only approach, but in my experience the safest one.

           

          Regards,

          Nico

          • 2. Re: Read and write to the same table? Can we?
            Arya Nymeria Active Member

            Thank you for your reply. Yeah, i tried writing the file date to a flat file on unix and get the date from the file in the mapping to avoid using the same table as source and target. However, I wanted to know if there is still a possibility.

            • 3. Re: Read and write to the same table? Can we?
              Nico Heinze Guru

              It is possible, but the penalty in terms of performance (UPDATE vs. INSERT statements) is usually heavy.

              Not to forget that depending on the DBMS you may encounter deadlocks of the worst kind. Such approaches can even lead to the whole DB coming to a grinding halt; that happened to me app. ten years ago on DB2 on AIX, and that was the PowerCenter repository of the production environment... not funny, in particular not if the admins (which was the case here) had to restart the whole AIX box. That's a sacrilege for them.

              That's why I firmly suggest to *never* use the same table as source and target within the same target load path in a mapping.

               

              Regards,

              Nico

              • 4. Re: Read and write to the same table? Can we?
                Syed Aziz Guru

                Hello Arya,

                Is it not recommended to have the same source and target table to read and update.  This is because database transactions intended to guarantee atomicity, consistency, isolation, and durability.  A sequence of database transaction operations should smoothly satisfy the ACID properties.

                Best regards,

                Syed

                • 5. Re: Read and write to the same table? Can we?
                  Arya Nymeria Active Member

                  Thank you. I changed my design and proceeded without using the source and target for reading and writing data. Thank you everyone.