数据仓库与数据挖掘--Windows下使用Perl连接DB2

Windows下使用Perl连接DB2 呵呵，使用Perl语言连接DB2数据库，好玩吧本文是摘抄杂志里的一文，原作者Michael Hoy & Grant Hutchison原文参见http://www.db2mag.com/story/showArticle.jhtml?articleID=593015511. Perl的DB2驱动Perl语言本身就不多做介绍了。1994年发布的DBI是Perl语言连接关系性数据库的标准。可以从dbi.perl.org获得DBI的源代码和文档。IBM在1995年发布了对于Perl的DB2驱动，这个驱动是符合DBI标准的，在Perl里这个驱动称为DBD::DB2 。可以从ibm.com/software/db2/perl获得DBD::DB2驱动的最新信息。注意：最近的DB2驱动需要至少Perl 5.005_03和DBI 1.21或以上的版本。2. 准备环境步骤如下(1)安装Perl语言环境Windows下可以从www.activestate.com获得Perl的安装包。安装后可以使用perl -v看看Perl的版本信息。DBI驱动和DBD::DB2驱动都是作为Perl的附加模块使用ppm工具安装的。安装方法参见(2), (3)，安装时最好先去ibm.com/software/db2/perl上看看ppm后面的参数有没有变化。(2)安装DBI驱动ppm install http://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBI.ppd(3)安装DBD::DB2驱动ppm install http://ftp.esoftmatic.com/outgoing/DBI/5.8.4/DBD-DB2.ppd(4)安装DB2 runtime client3. 使用Perl连接DB2(1)使用DBI函数DBI->data_sources 来扫描DB2数据库目录并返回一个包含了有效数据源名称(DSN)的数组。----------------datasources.pl-------------#!/usr/lib/perl -w## This perl script prints the list of cataloged DB2 data-sources#use DBI;use DBD::DB2;use DBD::DB2::Constants;print "Operating Systems = $^O\n";print "Perl Binary = $^X\n";print "Perl Version = $]\n";print "DBI Version = $DBI::VERSION\n";print "DBD::DB2 Version = $DBD::DB2::VERSION\n\n";my @DB2DataSources = DBI->data_sources("DB2");print "Available DB2 DSNs:\n\n";foreach my $dsn ( @DB2DataSources ){print " $dsn \n";}-------------------END----------------------(2)获取db2数据库的信息----------datasourceInfo.pl--------------------------#!/usr/lib/perl -w## This perl script prints the information DB2 database#use DBI;use DBD::DB2;use DBD::DB2::Constants;my $dsn = 'dbi:DB2:SAMPLE';my $uid = 'henry';my $pwd = 'happyday';my $dbh = DBI->connect( $dsn, $uid, $pwd ) || die "$DBI::errstr";print "Database Connection Information \n\n";printf( "Server Instance : %s\n", $dbh->get_info( SQL_SERVER_NAME ) );printf( "Database Server : %s\n", $dbh->get_info( SQL_DBMS_NAME ) );printf( "Database Version : %s\n", $dbh->get_info( SQL_DBMS_VER ) ); printf( "Database Alias : %s\n", $dbh->get_info( SQL_DATA_SOURCE_NAME ) );printf( "Database Codepage : %s\n", $dbh->get_info( 2519 ) );printf( "Application Codepage: %s\n", $dbh->get_info( 2520 ) );printf( "Authoriztion Id : %s\n", $dbh->get_info( SQL_USER_NAME ) );printf( "Max Idntifier Len : %s\n", $dbh->get_info( SQL_MAX_IDENTIFIER_LEN ) );printf( "Max Table Name Len : %s\n", $dbh->get_info( SQL_MAX_TABLE_NAME_LEN ) );printf( "Max Index Size : %s\n", $dbh->get_info( SQL_MAX_INDEX_SIZE ) );printf( "Max Columns in Table: %s\n", $dbh->get_info( SQL_MAX_COLUMNS_IN_TABLE ) );-------------------END----------------------(3)获取db2数据库的元数据(比如, 表结构)----------tableinfo.pl--------------------------#!/usr/lib/perl -w## This perl script prints the information of cataloged DB2 table#use DBI;use DBD::DB2;use DBD::DB2::Constants;$dsn = 'dbi:DB2:SAMPLE';$uid = 'henry';$pwd = 'happyday';# Connect to the SAMPLE database$dbh = DBI->connect( $dsn, $uid, $pwd ) || die "$DBI::errstr";# Get the tables for schema HENRY$sth = $dbh->table_info( { 'TABLE_CSHEM' => "HENRY" } );$table_counter = 0;while ( @row = $sth->fetchrow_array ){$catalog = $row[0];$schema = $row[1];$table = $row[2];$table_counter++;printf( "Table %d %s\n", $table_counter, $table );# Now get the column information for this table$sth_col = $dbh->column_info( $catalog, $schema, $table, '%' );if( $sth_col ){while( @row_col = $sth_col->fetchrow_array ){# @row_col has a lot more information. I'll just take# these three fields as an example$column_name = $row_col[3];$type_name = $row_col[5];$column_size = $row_col[6];printf( " %-24s%s(%s)\n", $column_name, $type_name, $column_size );}$sth_col->finish();}}$sth->finish();$dbh->disconnect;-------------------END----------------------(4)执行SQL----------executesql.pl--------------------------#!/usr/lib/perl -w## This perl script manipulate DB2 table#use DBI;use DBD::DB2;use DBD::DB2::Constants;$dsn = 'dbi:DB2:SAMPLE';$uid = 'henry';$pwd = 'happyday';# Connect to the SAMPLE database$dbh = DBI->connect( $dsn, $uid, $pwd ) || die "$DBI::errstr";# Prepare our insert statement$sth = $dbh->prepare( "INSERT INTO sales VALUES('2005-06-25', 'Tom', 'Beijing', 15)");$sth->execute();$sth->finish();$dbh->disconnect;-------------------END----------------------

阅读全文(2780) | 回复(0) | 编辑 | 精华

发表评论：

昵称：
密码：
主页：
标题：

验证码： (不区分大小写,请仔细填写,输错需重写评论内容！)

站点首页 | 联系我们 | 博客注册 | 博客登陆

Sponsored By W3CHINA
W3CHINA Blog 0.8 Processed in 0.531 second(s), page refreshed 144803283 times.
《全国人大常委会关于维护互联网安全的决定》《计算机信息网络国际联网安全保护管理办法》
苏ICP备05006046号