Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2010

    Question Unanswered: DBI::DB2 non ascii character issue.


    I have run into an issue where one field of one record in a DB2 table has a non-ascii character 0xA6. I have run across this in the past on other platforms, and just did a basic substitute in perl to remove it before I processed that record of the recordset. But this one will not get past the fetchrow_array call. I cannot even loop through the recordset with an empty loop. It will also fail if I use fetchrow_hash or any others I have tried. I am not sure if this could be an error in DBI, or DB2, or something I am doing wrong.
    DBI 1.615
    DBD::DB2 1.80
    perl v5.8.8 
    Here is my test script & results:
    print "\nStarting Test Script...\n";
    use strict;
    use DBI;
    use DBD::DB2;
    print localtime(time)."Importing info \n";
    my ($dbh_db2,$stht,@row,$sth,$insSql,$sql);
    my $db2User = 'user';
    my $db2Pass = '******';
    $dbh_db2 = DBI->connect( 'dbi:DB2:IMSC5',$db2User,$db2Pass,
                            RaiseError => 1
                         ) || die ("Database connection not made: $DBI::errstr");
    $sql = "select  substr(cl_summary, 36, 1)
            from    DBC5VJD1.VJDB_OTR_MISC_T m
            where = 'PW065846'";
    #The value = 0xA6.  
    $stht = $dbh_db2->prepare($sql) || die ("Database statment not made: $@\n");
    $stht->execute() || die ("Error: $@\n");
    print $stht->rows." rows\n";
        print "found: ".$stht->fetchrow_array()."\n"; 
    if( $@ )
        print "Unknown error: $@\n";
    OUTPUT with trace
        DBI 1.615-ithread default trace level set to 0x0/5 (pid 26996 pi 1ae21010) at line 8
        Note: perl is running without the recommended perl -w option
    Fri Nov 19 16:31:17 2010Importing MOKA Pending Ticket info
        -> DBI->connect(dbi:DB2:IMSC5, user, ****, HASH(0x1ae222a0))
        -> DBI->install_driver(DB2) for linux perl=5.008008 pid=26996 ruid=500 euid=500
           install_driver: DBD::DB2 version 1.80 loaded from /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/
        New 'DBI::dr' (for DBD::DB2::dr, parent='', id=undef)
        dbih_setup_handle(DBI::dr=HASH(0x1af1fe80)=>DBI::dr=HASH(0x1af1fef0), DBD::DB2::dr, 0, Null!)
        dbih_make_com(Null!, 0, DBD::DB2::dr, 168, 0) thr#1ae21010
        dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), Err, Null!) SCALAR(0x1b2c42e0) (already defined)
        dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), State, Null!) SCALAR(0x1af35320) (already defined)
        dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), Errstr, Null!) SCALAR(0x1af35350) (already defined)
        dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), TraceLevel, Null!) 0 (already defined)
        dbih_setup_attrib(DBI::dr=HASH(0x1af1fef0), FetchHashKeyName, Null!) 'NAME' (already defined)
        <- install_driver= DBI::dr=HASH(0x1af1fe80)
        !! warn: 0 CLEARED by call to connect method
        -> connect for DBD::DB2::dr (DBI::dr=HASH(0x1af1fe80)~0x1af1fef0 'IMSC5' 'user' **** HASH(0x1af44e30)) thr#1ae21010
        New 'DBI::db' (for DBD::DB2::db, parent=DBI::dr=HASH(0x1af1fef0), id=undef)
        dbih_setup_handle(DBI::db=HASH(0x1af203c0)=>DBI::db=HASH(0x1af20310), DBD::DB2::db, 1afa88c0, Null!)
        dbih_make_com(DBI::dr=HASH(0x1af1fef0), 1b0bbbb0, DBD::DB2::db, 176, 0) thr#1ae21010
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), Err, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8ad0) (already defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), State, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8b50) (already defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), Errstr, DBI::dr=HASH(0x1af1fef0)) SCALAR(0x1afa8b10) (already defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), TraceLevel, DBI::dr=HASH(0x1af1fef0)) 0 (already defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), FetchHashKeyName, DBI::dr=HASH(0x1af1fef0)) 'NAME' (already defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), HandleSetErr, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), HandleError, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), ReadOnly, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
        dbih_setup_attrib(DBI::db=HASH(0x1af20310), Profile, DBI::dr=HASH(0x1af1fef0)) undef (not defined)
    connect 'IMSC5', 'user', '***'    <- connect= DBI::db=HASH(0x1af203c0) at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 665
        -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'RaiseError' 1) thr#1ae21010
        STORE DBI::db=HASH(0x1af20310) 'RaiseError' => 1
        <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 717
        -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'PrintError' 1) thr#1ae21010
        STORE DBI::db=HASH(0x1af20310) 'PrintError' => 1
        <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 717
        -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'AutoCommit' 1) thr#1ae21010
        <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 717
        -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'Username' 'user') thr#1ae21010
        STORE DBI::db=HASH(0x1af20310) 'Username' => 'user'
        <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 720 via  at ./ line 15
        <> FETCH= 'user' ('Username' from cache) at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 720
        -> connected in DBD::_::db for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310 'dbi:DB2:IMSC5' 'user' ****' HASH(0x1ae222a0)) thr#1ae21010
        <- connected= undef at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 726
        <- connect= DBI::db=HASH(0x1af203c0)
        -> STORE for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER 'dbi_connect_closure' CODE(0x1af1fdb0)) thr#1ae21010
        STORE DBI::db=HASH(0x1af20310) 'dbi_connect_closure' => CODE(0x1af1fdb0)
        <- STORE= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 735 via  at ./ line 15
        -> prepare for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310 'select  substr(cl_summary, 36, 1)
            from    DBC5VJD1.VJDB_OTR_MISC_T m
            where = 'PW065846'') thr#1ae21010
        New 'DBI::st' (for DBD::DB2::st, parent=DBI::db=HASH(0x1af20310), id=undef)
        dbih_setup_handle(DBI::st=HASH(0x1af204e0)=>DBI::st=HASH(0x1af203a0), DBD::DB2::st, 1afa8bc0, Null!)
        dbih_make_com(DBI::db=HASH(0x1af20310), 1af215b0, DBD::DB2::st, 272, 0) thr#1ae21010
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Err, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8ad0) (already defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), State, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8b50) (already defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Errstr, DBI::db=HASH(0x1af20310)) SCALAR(0x1afa8b10) (already defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), TraceLevel, DBI::db=HASH(0x1af20310)) 0 (already defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), FetchHashKeyName, DBI::db=HASH(0x1af20310)) 'NAME' (already defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), HandleSetErr, DBI::db=HASH(0x1af20310)) undef (not defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), HandleError, DBI::db=HASH(0x1af20310)) undef (not defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), ReadOnly, DBI::db=HASH(0x1af20310)) undef (not defined)
        dbih_setup_attrib(DBI::st=HASH(0x1af203a0), Profile, DBI::db=HASH(0x1af20310)) undef (not defined)
        dbd_st_prepare'd sql f65537
            select  substr(cl_summary, 36, 1)
            from    DBC5VJD1.VJDB_OTR_MISC_T m
            where = 'PW065846'
    fbh 0: '1' , type 1,  1, dsize 1, p0 s456039644
       out: ftype 1, indp 0, bufl 2, rlen 2
        <- prepare= DBI::st=HASH(0x1af204e0) at ./ line 28
        -> execute for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
        <- execute= -1 at ./ line 29
        -> rows for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
        <- rows= -1 at ./ line 30
    -1 rows
        -> fetchrow_array for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
        dbih_setup_fbav alloc for 1 fields
        dbd_st_fetch 1 fields
    Unknown error: Error: Data in column 0 has been truncated to 1 bytes.  A maximum of 2 bytes are available at ./ line 33.
        -> finish for DBD::DB2::st (DBI::st=HASH(0x1af204e0)~0x1af203a0) thr#1ae21010
        <- finish= 1 at ./ line 40
        -> disconnect for DBD::DB2::db (DBI::db=HASH(0x1af203c0)~0x1af20310) thr#1ae21010
        <- disconnect= 1 at ./ line 41
        <> DESTROY(DBI::st=HASH(0x1af204e0)) ignored for outer handle (inner DBI::st=HASH(0x1af203a0) has ref cnt 1)
        -> DESTROY for DBD::DB2::st (DBI::st=HASH(0x1af203a0)~INNER) thr#1ae21010
        <- DESTROY= undef
        dbih_clearcom 0x1af203a0 (com 0x1b2ea2d0, type 3) done.
        <> DESTROY(DBI::db=HASH(0x1af203c0)) ignored for outer handle (inner DBI::db=HASH(0x1af20310) has ref cnt 1)
        -> DESTROY for DBD::DB2::db (DBI::db=HASH(0x1af20310)~INNER) thr#1ae21010
        <- DESTROY= undef
        dbih_clearcom 0x1af20310 (com 0x1af215b0, type 2) done.
        -- DBI::END ($@: , $!: )
        -> disconnect_all for DBD::DB2::dr (DBI::dr=HASH(0x1af1fe80)~0x1af1fef0) thr#1ae21010
        <- disconnect_all= 1 at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/ line 744 via  at ./ line 0
    !   <> DESTROY(DBI::dr=HASH(0x1af1fe80)) ignored for outer handle (inner DBI::dr=HASH(0x1af1fef0) has ref cnt 1)
    !   -> DESTROY in DBD::_::common for DBD::DB2::dr (DBI::dr=HASH(0x1af1fef0)~INNER) thr#1ae21010
    !   <- DESTROY= undef during global destruction
        dbih_clearcom 0x1af1fef0 (com 0x1b0bbbb0, type 1) done.
    Thank you in advance for any help you can give, this one has me stumped


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Why not translate it on the select statement:

    select translate(col1,' ','0xA6') as col1 from mytable


  3. #3
    Join Date
    Nov 2010


    Hello, thanks for the reply. I was able to get translate to work, and wanted to post the syntax in case anyone else needs it. The bad thing is i have to translate each invalid character on each field. I don't think DB2 supports regular expressions or translate allows a list of items to "look for" So still searching for a better solution.

    Here is actually what i did get to work:

    select translate(col1,' ',X'A6') as col1 from mytable

    Very small difference, but thought i would share.


  4. #4
    Join Date
    Nov 2010

    Talking Best Solution

    I thought i would post a better solution that i have found to this issues, in case anyone else is having this problem.

    The translate worked, but only for one specific character in one column. Turns out there are other specific characters in multiple columns, so this did not work out.

    I found that if you set an environment variable it will correct this for everything.

    In Linux you need to set : DB2CODEPAGE=819
    in Windows you need to set : DB2CODEPAGE=1252

    This does not get rid of the characters but prevents the error from DBD:B2, and allows you to handle the non-ascii characters in perl as you loop through the record set.

    Basically this changes DBD:B2 to allow 4-byte characters and pass them through, rather than throwing the truncated field error.

    I found this solution at :

    Just thought i would share.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts