Skip Menu |

This queue is for tickets about the DBD-DB2 CPAN distribution.

Report information
The Basics
Id: 44246
Status: resolved
Priority: 0/
Queue: DBD-DB2
People
Owner: opendev [...] us.ibm.com
Requestors: morten.bjornsvik [...] experian-da.no
Cc: aff [...] cpan.org
AdminCc:
Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)

History Show all quoted text
  Tue Mar 17 11:33:11 2009 morten.bjornsvik [...] experian-da.no - Ticket created
Subject: DBD::DB2 v1.61 and newer does not work well with DB2v9.1.0.2
Date: Tue, 17 Mar 2009 16:21:05 +0100
To: <bug-DBD-DB2 [...] rt.cpan.org>
From: Morten Bjørnsvik <morten.bjornsvik [...] experian-da.no>
Hi With the new DBD::DB2 v1.61 and v1.7 our old DB2 v9.1.0.2 can't be used. V9.5.0 and V9.5.0.2 works fine though. Would be nice if you add a TESTING section to the readme, which describes which DB2 versions the various driver versions have been tested with. Testing the following program on DBD::DB2 v 1.61 on DB2v9.1.0.2 Install Path Level Fix Pack Special Install Number Install Date -------------------------------------------------------------------------------------------- /opt/db2 9.1.0.2 2 1 Thu May 24 09:05:18 2007 CEST If I install DBD::DB2 v1.61 or higher the error is present, If I reinstall DBD::DB2 v1.3 it works again. Back and forth. #!/opt/smm/bin/perl -w # database barebone test use strict; use warnings; use Carp; use DBI; use DBD::DB2; use Data::Dumper; print "\$DBI::VERSION: " . $DBI::VERSION . "\n"; print "\$DBD::DB2::VERSION: " . $DBD::DB2::VERSION . "\n"; # connect to the database my $dbh = DBI->connect( 'dbi:DB2:<database>', '<user>', '<pw>', { RaiseError => 1 } ); my $sql = "select * from sched order by id asc"; my $sth = $dbh->prepare($sql); $sth->execute(); my $ref = $sth->fetchall_arrayref(); $sth->finish(); print "fetchall_hashref() of '$sql' dump:\n" . Dumper($ref); $ref = undef; $sth = $dbh->prepare($sql); $sth->execute(); while( $ref = $sth->fetchrow_hashref()) { print "dump of fetchrow_hashref()" . Dumper($ref); } With DBD::DB2 v1.61 and v1.7 the output is truncated. see the command column ./db-test2.pl $DBI::VERSION: 1.607 $DBD::DB2::VERSION: 1.61 fetchall_hashref() of 'select * from sched order by id asc' dump: $VAR1 = [ [ '1', '*', '* * * * mon-fri', '{"system":"$SEC', '2009-03-17 12:52:51.188049', '2009-03-17 13:02:00.349395', '2009-03-17 13:02:00.342011', '2009-03-17 12:52:51.188049', 'scheduled', 'Execute tx workflow with file data source every minute on each workday' ], [ '2', '*', '* * * * mon-fri', '{"system":"$SEC', '2009-03-17 12:52:51.228358', '2009-03-17 13:02:00.349395', '2009-03-17 13:02:00.347405', '2009-03-17 12:52:51.228358', 'scheduled', 'Execute tx workflow with MQ data source every minute on each workday' ] ]; dump of fetchrow_hashref()$VAR1 = { 'ID' => '1', 'DESC' => 'Execute tx workflow with file data source every minute on each workday', 'TIMEOBJ' => '* * * * mon-fri', 'STATUS' => 'scheduled', 'UPDATED' => '2009-03-17 12:52:51.188049', 'CREATED' => '2009-03-17 12:52:51.188049', 'LASTRUN' => '2009-03-17 13:02:00.342011', 'RUNHOSTS' => '*', 'COMMAND' => '{"system":"$SEC', 'LASTCHECKED' => '2009-03-17 13:02:00.349395' }; dump of fetchrow_hashref()$VAR1 = { 'ID' => '2', 'DESC' => 'Execute tx workflow with MQ data source every minute on each workday', 'TIMEOBJ' => '* * * * mon-fri', 'STATUS' => 'scheduled', 'UPDATED' => '2009-03-17 12:52:51.228358', 'CREATED' => '2009-03-17 12:52:51.228358', 'LASTRUN' => '2009-03-17 13:02:00.347405', 'RUNHOSTS' => '*', 'COMMAND' => '{"system":"$SEC', 'LASTCHECKED' => '2009-03-17 13:02:00.349395' }; With DBD::DB2 v1.3 it works fine: ./db-test2.pl $DBI::VERSION: 1.607 $DBD::DB2::VERSION: 1.3 fetchall_hashref() of 'select * from sched order by id asc' dump: $VAR1 = [ [ '1', '*', '* * * * mon-fri', '{"system":"$SECANAROOT/bin/workflow/start_workflow.pl --ic_auth=\'File\' --auth_file=\\"$SECANAROOT/files/data/test.data.small\\" --auth_map_name=\'fixed\' --auth_map_name=\'WORKFLOW_AUTH_DATA_SOURCE_FILE\' --ic_cb=\'File\' --cb_file=\\"$SECANAROOT/files/data/test.data.small\\" --cb_map_name=\'fixed\' --cb_map_name=\'WORKFLOW_CB_DATA_SOURCE_FILE\' --ic_pres=\'File\' --pres_file=\\"$SECANAROOT/files/data/test.data.small\\" --pres_map_name=\'fixed\' --pres_map_name=\'WORKFLOW_PRES_DATA_SOURCE_FILE\' --ic_rr=\'File\' --rr_file=\\"$SECANAROOT/files/data/test.data.small\\" --rr_map_name=\'fixed\' --rr_map_name=\'WORKFLOW_RR_DATA_SOURCE_FILE\'"}', '2009-03-17 12:52:51.188049', '2009-03-17 13:02:00.349395', '2009-03-17 13:02:00.342011', '2009-03-17 12:52:51.188049', 'scheduled', 'Execute tx workflow with file data source every minute on each workday' ], [ '2', '*', '* * * * mon-fri', '{"system":"$SECANAROOT/bin/workflow/start_workflow.pl --ic_auth=\'MQ\' --auth_mq_svr_name=\'vmclone1\' --auth_mq_svr_port=\'6666\' --auth_mq_svr_qmgr=\'vmclone1\' --auth_mq_svr_queue=\'smm.queue\' --auth_mq_svr_channel=\'smm.channel\' --auth_map_name=\'csv\' --auth_map_name=\'WORKFLOW_AUTH_DATA_SOURCE_MQ\' --auth_mq_svr_max_msg=\'100\' --ic_cb=\'MQ\' --cb_mq_svr_name=\'vmclone1\' --cb_mq_svr_port=\'6666\' --cb_mq_svr_qmgr=\'vmclone1\' --cb_mq_svr_queue=\'smm.queue\' --cb_mq_svr_channel=\'smm.channel\' --cb_map_name=\'fixed\' --cb_map_name=\'WORKFLOW_CB_DATA_SOURCE_MQ\' --cb_mq_svr_max_msg=\'100\' --ic_pres=\'MQ\' --pres_mq_svr_name=\'vmclone1\' --pres_mq_svr_port=\'6666\' --pres_mq_svr_qmgr=\'vmclone1\' --pres_mq_svr_queue=\'smm.queue\' --pres_mq_svr_channel=\'smm.channel\' --pres_map_name=\'fixed\' --pres_map_name=\'WORKFLOW_PRES_DATA_SOURCE_MQ\' --pres_mq_svr_max_msg=\'100\' --ic_rr=\'MQ\' --rr_mq_svr_name=\'vmclone1\' --rr_mq_svr_port=\'6666\' --rr_mq_svr_qmgr=\'vmclone1\' --rr_mq_svr_queue=\'smm.queue\' --rr_mq_svr_channel=\'smm.channel\' --rr_map_name=\'fixed\' --rr_map_name=\'WORKFLOW_RR_DATA_SOURCE_MQ\' --rr_mq_svr_max_msg=\'100\' --output_channel=\'File\' --output_file=\'/tmp/transaction_wf_ds_mq_alerts_file.json\'"}', '2009-03-17 12:52:51.228358', '2009-03-17 13:02:00.349395', '2009-03-17 13:02:00.347405', '2009-03-17 12:52:51.228358', 'scheduled', 'Execute tx workflow with MQ data source every minute on each workday' ] ]; dump of fetchrow_hashref()$VAR1 = { 'ID' => '1', 'DESC' => 'Execute tx workflow with file data source every minute on each workday', 'TIMEOBJ' => '* * * * mon-fri', 'STATUS' => 'scheduled', 'UPDATED' => '2009-03-17 12:52:51.188049', 'CREATED' => '2009-03-17 12:52:51.188049', 'LASTRUN' => '2009-03-17 13:02:00.342011', 'RUNHOSTS' => '*', 'COMMAND' => '{"system":"$SECANAROOT/bin/workflow/start_workflow.pl --ic_auth=\'File\' --auth_file=\\"$SECANAROOT/files/data/test.data.small\\" --auth_map_name=\'fixed\' --auth_map_name=\'WORKFLOW_AUTH_DATA_SOURCE_FILE\' --ic_cb=\'File\' --cb_file=\\"$SECANAROOT/files/data/test.data.small\\" --cb_map_name=\'fixed\' --cb_map_name=\'WORKFLOW_CB_DATA_SOURCE_FILE\' --ic_pres=\'File\' --pres_file=\\"$SECANAROOT/files/data/test.data.small\\" --pres_map_name=\'fixed\' --pres_map_name=\'WORKFLOW_PRES_DATA_SOURCE_FILE\' --ic_rr=\'File\' --rr_file=\\"$SECANAROOT/files/data/test.data.small\\" --rr_map_name=\'fixed\' --rr_map_name=\'WORKFLOW_RR_DATA_SOURCE_FILE\'"}', 'LASTCHECKED' => '2009-03-17 13:02:00.349395' }; dump of fetchrow_hashref()$VAR1 = { 'ID' => '2', 'DESC' => 'Execute tx workflow with MQ data source every minute on each workday', 'TIMEOBJ' => '* * * * mon-fri', 'STATUS' => 'scheduled', 'UPDATED' => '2009-03-17 12:52:51.228358', 'CREATED' => '2009-03-17 12:52:51.228358', 'LASTRUN' => '2009-03-17 13:02:00.347405', 'RUNHOSTS' => '*', 'COMMAND' => '{"system":"$SECANAROOT/bin/workflow/start_workflow.pl --ic_auth=\'MQ\' --auth_mq_svr_name=\'vmclone1\' --auth_mq_svr_port=\'6666\' --auth_mq_svr_qmgr=\'vmclone1\' --auth_mq_svr_queue=\'smm.queue\' --auth_mq_svr_channel=\'smm.channel\' --auth_map_name=\'csv\' --auth_map_name=\'WORKFLOW_AUTH_DATA_SOURCE_MQ\' --auth_mq_svr_max_msg=\'100\' --ic_cb=\'MQ\' --cb_mq_svr_name=\'vmclone1\' --cb_mq_svr_port=\'6666\' --cb_mq_svr_qmgr=\'vmclone1\' --cb_mq_svr_queue=\'smm.queue\' --cb_mq_svr_channel=\'smm.channel\' --cb_map_name=\'fixed\' --cb_map_name=\'WORKFLOW_CB_DATA_SOURCE_MQ\' --cb_mq_svr_max_msg=\'100\' --ic_pres=\'MQ\' --pres_mq_svr_name=\'vmclone1\' --pres_mq_svr_port=\'6666\' --pres_mq_svr_qmgr=\'vmclone1\' --pres_mq_svr_queue=\'smm.queue\' --pres_mq_svr_channel=\'smm.channel\' --pres_map_name=\'fixed\' --pres_map_name=\'WORKFLOW_PRES_DATA_SOURCE_MQ\' --pres_mq_svr_max_msg=\'100\' --ic_rr=\'MQ\' --rr_mq_svr_name=\'vmclone1\' --rr_mq_svr_port=\'6666\' --rr_mq_svr_qmgr=\'vmclone1\' --rr_mq_svr_queue=\'smm.queue\' --rr_mq_svr_channel=\'smm.channel\' --rr_map_name=\'fixed\' --rr_map_name=\'WORKFLOW_RR_DATA_SOURCE_MQ\' --rr_mq_svr_max_msg=\'100\' --output_channel=\'File\' --output_file=\'/tmp/transaction_wf_ds_mq_alerts_file.json\'"}', 'LASTCHECKED' => '2009-03-17 13:02:00.349395' }; -- Morten Bjørnsvik Experian Decision Analytics AS PB 121, 0102 Oslo, Norway Morten.bjornsvik@experian-da.no +47 21 52 12 65 (office) +47 21 52 12 51 (fax) +47 92 44 83 02 (mob)
  Tue Mar 17 12:59:28 2009 opendev [...] us.ibm.com - Status changed from 'new' to 'open'
  Tue Mar 17 12:59:32 2009 opendev [...] us.ibm.com - Taken
  Tue Mar 17 13:03:12 2009 opendev [...] us.ibm.com - Correspondence added
RT-Send-CC: opendev [...] us.ibm.com
Hi Thanks for raising this ticket. I would get back to you with this by end of day tomorrow. Its a good point you brought about Testing section. I would take that into consideration. I would try to reproduce this error and get back to you. There are a lot of things we have added lately and that might be breaking some backward compatibility so I would need to find that out. Thanks again. -- Thanks Tarun Pasrija IBM OpenSource Application Development Team India Software Labs, Bangalore (India)
  Tue Mar 17 16:24:05 2009 aff [...] cpan.org - Correspondence added
I have been able to reproduce this behavior on Linux x86_64. It seems the bug concerns the "clob" datatype only. $ cat 44246.pl #!/opt/perl/bin/perl -sl use strict; use warnings; use DBD::DB2; use Test::More; plan tests => 2; # Prepare a table my $statements =<<EOF; drop table dbddb2; create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ); insert into dbddb2 values (1, 'fubar'); EOF our $P ||= 0; # print flag our $dbh = undef; sub runtest { my ($dbname, $uid, $pass) = @_; $dbh = DBI->connect("dbi:DB2:$dbname", $uid, $pass, {PrintError => 1, RaiseError => 1}); my $sth = $dbh->prepare(q{select VERSIONNUMBER from SYSIBM.SYSVERSIONS}); croak("no sth") unless $sth; $sth->execute() or croak ("exec err"); my $db2version = $sth->fetchrow_hashref->{VERSIONNUMBER} if $P; $sth->finish; print "DBI::VERSION : " . $DBI::VERSION if $P; print "DBD::DB2::VERSION: " . $DBD::DB2::VERSION if $P; print "DB2 VERSION : " . $db2version if $P; sub exec_statement { my $statement = shift; return unless $statement; print $statement if $P; $dbh->do($statement) or croak("statement failed:". $statement); return 1; } map { chomp $_; exec_statement($_) } split(/;/x, $statements); $sth = $dbh->prepare(q{select id,data from dbddb2}); croak("no sth") unless $sth; $sth->execute() or croak ("exec err"); # This is the test is_deeply($sth->fetchall_arrayref(), [[1,'fubar']], q{Expect 1 row (1,'fubar') returned}); $sth->finish or croak("finish"); $dbh->disconnect or croak("disconnect"); return 1; } runtest( qw (<inst1> <user> <pass>) ); # DB2 9.1.1 runtest( qw (<inst2> <user> <pass>) ); # DB2 9.5.0 __END__ $ cat results.txt $ perl -w 44246.pl -P 1..2 DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.3 DB2 VERSION : 9010200 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') ok 1 - Expect 1 row (1,'fubar') returned DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.3 DB2 VERSION : 9050000 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') ok 2 - Expect 1 row (1,'fubar') returned $ perl -w 44246.pl -P 1..2 DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.61 DB2 VERSION : 9010200 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') not ok 1 - Expect 1 row (1,'fubar') returned # Failed test 'Expect 1 row (1,'fubar') returned' # at 44246.pl line 52. # Structures begin differing at: # $got->[0][1] = 'f' # $expected->[0][1] = 'fubar' DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.61 DB2 VERSION : 9050000 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') ok 2 - Expect 1 row (1,'fubar') returned # Looks like you failed 1 test of 2. $ perl -w 44246.pl -P 1..2 DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.7 DB2 VERSION : 9010200 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') not ok 1 - Expect 1 row (1,'fubar') returned # Failed test 'Expect 1 row (1,'fubar') returned' # at 44246.pl line 52. # Structures begin differing at: # $got->[0][1] = 'f' # $expected->[0][1] = 'fubar' DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.7 DB2 VERSION : 9050000 drop table dbddb2 create table dbddb2 ( id integer not null, data clob not null , constraint pk_dbddb2 primary key (id) ) insert into dbddb2 values (1, 'fubar') ok 2 - Expect 1 row (1,'fubar') returned # Looks like you failed 1 test of 2. -- Best regards, Andreas
  Tue Mar 17 17:24:45 2009 aff [...] cpan.org - Cc AFF added
  Fri Mar 20 06:24:57 2009 opendev [...] us.ibm.com - Correspondence added
RT-Send-CC: opendev [...] us.ibm.com
Hi Friends I have found the root cause of the problem. This problem is happening since for Long/LOB types including XML, the implementation is little different in v91 and 95. I am attaching the changed dbdimp.c file. Please let me know if this works in your environment. Steps to follow 1. Replace your existing dbdimp.c with the one attached. 2. perl Makefile.PL 3. make 4. make install Execute the tests that you had written. PS:- Would be waiting for your response for the same. -- Thanks Tarun Pasrija IBM OpenSource Application Development Team India Software Labs, Bangalore (India)

Message body is not shown because it is too large.

  Fri Mar 20 06:53:22 2009 aff [...] cpan.org - Correspondence added
From: aff [...] cpan.org
On Fri Mar 20 06:24:57 2009, IBMTORDB2 wrote: Show quoted text
> 1. Replace your existing dbdimp.c with the one attached. > Execute the tests that you had written.
Thank you very much for investigating this problem. I have applied you new dbdimp.c, and not it works for me (using the simple test above): $ perl 44246.pl -P 1..4 DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.7-patch-for-bug-44246 DB2 VERSION : 9010200 drop table dbddb2_clob drop table dbddb2_varchar create table dbddb2_clob ( id integer not null, data clob not null , constraint pk_dbddb2_clob primary key (id) ) insert into dbddb2_clob values (1, 'fubar_clob') create table dbddb2_varchar ( id integer not null, data varchar(1024) not null , constraint pk_dbddb2_varchar primary key (id) ) insert into dbddb2_varchar values (1, 'fubar_varchar') ok 1 - Expect 1 row (1,'fubar_clob') returned ok 2 - Expect 1 row (1,'fubar_varchar') returned DBI::VERSION : 1.607 DBD::DB2::VERSION: 1.7-patch-for-bug-44246 DB2 VERSION : 9050000 drop table dbddb2_clob drop table dbddb2_varchar create table dbddb2_clob ( id integer not null, data clob not null , constraint pk_dbddb2_clob primary key (id) ) insert into dbddb2_clob values (1, 'fubar_clob') create table dbddb2_varchar ( id integer not null, data varchar(1024) not null , constraint pk_dbddb2_varchar primary key (id) ) insert into dbddb2_varchar values (1, 'fubar_varchar') ok 3 - Expect 1 row (1,'fubar_clob') returned ok 4 - Expect 1 row (1,'fubar_varchar') returned -- Best regards, Andreas
  Fri Mar 20 07:14:09 2009 aff [...] cpan.org - Correspondence added
On Fri Mar 20 06:53:22 2009, AFF wrote: Show quoted text
> [..] not it works for me (using the
That should be "now it works for me". Regards, Andreas --
  Sat Mar 21 02:01:00 2009 opendev [...] us.ibm.com - Correspondence added
RT-Send-CC: opendev [...] us.ibm.com
Thanks Guys. I would release this patch in the next version of the driver. Closing this ticket. -- Thanks Tarun Pasrija IBM OpenSource Application Development Team India Software Labs, Bangalore (India)
  Sat Mar 21 02:01:01 2009 opendev [...] us.ibm.com - Status changed from 'open' to 'resolved'