如何使用Perl运行DB2导入/加载 [英] How to run DB2 import/load using Perl

问题描述

有没有人尝试从Perl程序中使用DB2导入？

我的应用程序正在插入1000万行，并显然使用DBI连接并执行插入行逐行执行。

从命令行执行DB2导入/加载工作非常好，但是有一种更好的方法，而不是从Perl程序调用系统调用来调用：

 使用IPC :: System :: Simple qw（systemx）; 
使用autodie; 
 
 systemx（db2 connect ...）; 
 systemx（db2 import ...）; 
等？ 
  

谢谢！

解决方案

我实际上已经有你似乎正在遇到的问题。看来在某些系统上，您必须先显式地执行DB2 Connect才能导入。

事实上，当我在导入语句之前立即有一个DB2 Connect字符串时，发现我的脚本表现最为一致，但这可能与系统有关。

建议的解决方案：

我开始使用如下所示的连接语句。检查此语句是否也可以确认您的数据库路径是否有效。

db2连接到$ DB_NAME用户$ DB_USER使用$ DB_PASS

我最终将上面的字符串保存为 $ connnection_starter ，因为你将在几个地方使用它。

然后，我进行了一个系统调用，如下所示：

system（$ con_starter。; db2 import from $ temp_file_path of del commitcount 5000 $ insert_update_setting into tablespace。$ table_name）;

在您的情况下， commitcount 值不一定是必需的（虽然是通常非常适合于非常大的导入），但是我建议使用它，因为如果您通过命令行/ shell运行脚本，DB2会在每5000个记录的状态下记录导入状态的消息。

错误检查

您可以拉入 $？以查看导入命令中是否产生错误，因为如果行为正确，它应该返回 0 。

令人烦恼的是，其他返回码并不是很有帮助。我设置了我的导入命令以记录错误的确切语句，以便稍后可以手动检查：

  if（$？ne 0）{
 $ logger-> warn（无法将$ temp_file_path导入表$ table_name \\\
Error：$？）; 
 $ logger-> warn（Exact statement：\t。$ con_starter。; db2 import from $ temp_file_path of del commitcount 5000 $ insert_update_setting into tablespace。$ table_name）; 
  

希望有帮助！

Has anyone ever try to use DB2 import from within a Perl program?

My application is inserting 10 million of rows and apparently connecting using DBI and doing the insert row by row takes forever.

DB2 import/load from the command line works great, however is there a better way rather than to call a system call from the Perl program to invoke:

use IPC::System::Simple qw( systemx );
use autodie;

systemx( "db2 connect ..." );
systemx( "db2 import ..." );
etc?

thanks!

解决方案

I have actually had exactly the problem you seem to be having. It seems that on some systems you have to explicitly perform a DB2 Connect prior to being able to import.

In fact, I found my scripts behaved the most consistently when I had a DB2 Connect string immediately prior to my import statement, but that may be a system dependent issue.

Suggested solution:

I started out with a connection statement like the following. Checking that this statement will work also allows for a confirmation that your DB path is valid.

db2 connect to $DB_NAME user $DB_USER using $DB_PASS

I ended up saving the string above as $connnection_starter since you will be using it in several places.

I then made a system call like the following:

system($con_starter . "; db2 import from $temp_file_path of del commitcount 5000 $insert_update_setting into tablespace.$table_name");

The commitcount value is not necessarily needed in your case, (although it is generally well suited to very large imports) but I would suggest using it since it causes DB2 to log a message on the status of the import every 5000 records if you are running the script via the command-line/shell.

Error Checking

You can pull in the value of $? to see if an error was produced during the import command, since it should have a return of 0 if the behavior was correct.

Annoyingly, the other return codes are not very helpful. I set up my import command to log the exact statement in case of a failure, so that I could manually review that at a later point:

if ($? ne 0){
    $logger->warn("Failed to import $temp_file_path to table $table_name \nError: $?");
    $logger->warn("Exact statement: \t " . $con_starter . "; db2 import from $temp_file_path of del commitcount 5000 $insert_update_setting into tablespace.$table_name");

Hopefully that helps!

这篇关于如何使用Perl运行DB2导入/加载的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！