perl dbi和临时桌子

Question

我正在使用Perl DBI和DB2。

当我运行此代码时：

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);
   }
}

问题是theschema.name运行后是空的。

我怀疑DBI在DO（）之后都不会保留临时表的内容。但是DBI不允许我在do（）中提出多个语句。

如何在DBI中获得临时桌子？

Answer 1

在提交删除行上的是声明全局临时表语句。
使用在提交preserve行选项上使用在显式或隐式commit时保留行。

像这样：

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