I am using the Perl DBI and DB2.

When I run this code:

sub MergePolygonNameTable()
{
   my $table = "THESCHEMA.NAME";

   print "Merging into ${table} table. ", scalar localtime, "\n";
   eval
   {
      $DbHandle->do("
      declare global temporary table session.TEMP_NAME
      (POLICY_MASTER_ID INT
      )
      ;
      ");

      $DbHandle->do("
         CREATE UNIQUE INDEX session.TEMP_NAME_IDX1 ON session.TEMP_NAME
         (POLICY_MASTER_ID ASC
         )");
     $DbHandle->do("
      insert into session.TEMP_NAME
      (POLICY_MASTER_ID
      )
      SELECT pm.ID as POLICY_MASTER_ID
        FROM THESCHEMA.POLICY_MASTER pm

      ");
      $DbHandle->do("
      MERGE INTO THESCHEMA.NAME as t
      USING session.TEMP_NAME as s
         ON t.POLICY_MASTER_ID = s.POLICY_MASTER_ID
      WHEN MATCHED
      ) THEN
      UPDATE SET t.UPDATED_DATETIME = CURRENT_TIMESTAMP
      WHEN NOT MATCHED THEN
      INSERT
      (POLICY_MASTER_ID
      ) VALUES
      (s.POLICY_MASTER_ID
      )
        ;
      ");
   };
   if ($@)
   {
      print STDERR "ERROR: $ExeName: Cannot merge into ${table} table.\n$@\n";
      ExitProc(1);
   }
}

The problem is that the THESCHEMA.NAME is empty after the run.

I suspect that DBI does not keep the contents of the temporary table after the do(). But the DBI does not allow me to put more than one statement in a do().

How do I get temporary tables to work in the DBI?

CodePudding user response：

The ON COMMIT DELETE ROWS is the default option of the DECLARE GLOBAL TEMPORARY TABLE statement.
Use the ON COMMIT PRESERVE ROWS option to preserve the rows upon explicit or implicit COMMIT.

Like this:

      declare global temporary table session.TEMP_NAME
      (POLICY_MASTER_ID INT
      )
      ON COMMIT PRESERVE ROWS
      ;