none
BizTalk DB2 Adapter fails to insert text into a unicode field RRS feed

  • Question

  • Hi,

    I'm experiencing a problem when using the DB2 Adapter to insert into a (AS/400) DB2 table with unicode text data.  We're using BizTalk 2010 and HIS 2010.  The problem is centred around inserting data into unicode fields.  Inserting into CHARACTER field (CCSID 37) works fine but it fails when I insert into a unicode field (CCSID 13488).  The DB journaling is turned on and I'm using a DB2 Updategram to perform the insert.  I'd really appreciate some assistance or any pointers, it's getting quite urgent that I get this going.  I'm beginning to think this is a bug but I hope it's something I've got wrong.

    I also can't get the Generate Schema Wizard from Visual Studio working when connecting to these tables, I suspect the two issues are related but the Visual Studio problem I can work around.

    I can insert successfully into this table created like this.. (which would store text in EBCDIC (CCSID 37)

    CREATE TABLE DEV_TEMP.ABC ( 
     AAA CHARACTER(1) DEFAULT NULL , 
     BBB NUMERIC(9, 0) DEFAULT NULL ) ;
    

    But for some reason I cannot insert text into a table created like this, I can insert only numeric data into this table.

    CREATE TABLE DEV_TEMP.ABC ( 
     AAA GRAPHIC(1) CCSID 13488 DEFAULT NULL , 
     BBB NUMERIC(9, 0) DEFAULT NULL ) ;
    

    My database driver settings are like this...

    Provider=DB2OLEDB;User ID=XXXX;Password=XXXX;Initial Catalog=XXXXX;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;
    Network Address=XXXXX;Network Port=446;Package Collection=DEV_TEMP;Default Schema=DEV_TEMP;Process Binary as Character=False;Units of Work=RUW;Default Qualifier=DEV_TEMP;DBMS Platform=DB2/AS400;Use Early Metadata=False;Defer Prepare=False;DateTime As Char=False;Rowset Cache Size=0;Datetime As Date=False;AutoCommit=False;Authentication=Server;Persist Security Info=True;Cache Authentication=False;Connection Pooling=False;Derive Parameters=False;

    I have experimented with the Process Binary as Character and the Host CCSID settings but no luck there.

    In the event log I get this...not very descriptive.  The absence of meaningful error messages makes it tough.

    The adapter failed to transmit message going to send port "DB2 SS Send" with URL "DB2://DB2_XXX". 
    It will be retransmitted after the retry interval specified for this Send Port.



    Details:"Exception of type 'System.Exception' was thrown.".

    The BizTalk error message is completely blank.  I've also run the DB2 tracing but only get the following warning in the logs...

    DRDA AR message: Name: ENDUOWRM, Severity: Warning, Database: XXXXX


     

     

     

     

     

     

    Saturday, November 13, 2010 1:52 PM

Answers

  • I've just tested this with the HIS 2009 DBOLEDB2 driver and the above test works properly... looks like something with the DB2OLEDB driver in HIS 2010.
    Thursday, December 2, 2010 12:27 PM

All replies

  • This is being taken care of internally, but passing on what you should have already received from your internal contact:

    Graphic corresponds to a NCHAR. You would normally use graphic when dealing with DBCS (double byte character sets). Since a graphic(1) column can not contain 2 bytes, it will fail. CCSID 13488 is a Unicode character set, and not even EBCDIC if I’m correct, I’d actually verify what the AS400 is even using here. Either way, it’s Unicode, thus requires 2 bytes for 1 character. A Graphic(1) cannot hold 2 bytes, so it will fail. J

     

    For further testing, and get better error messages, don’t use BizTalk, use C# and the managed data provider, or a linked server, as apparently either BizTalk is not displaying the correct messages, or we are not passing BizTalk the correct error message (SQLState & SQLCode).

     

    ENDUWRM = End Unit of work reply message and is normal, and does not indicate an error.

     


    Charles Ezzell - MSFT
    Tuesday, November 16, 2010 4:22 PM
  • Hi Charles,

    Thanks for the suggestion.  I wish this was the case but I think when we specify the size of the GRAPHIC data type that we're indicating the number of double byte characters.  This is from the IBM site on the GRAPHIC data type.

    GRAPHIC(n) Fixed-length graphic strings that contain n double-byte characters.

    However, I did try increasing the size of characters just in case this was a factor and unfortunately it still did not work.

    Interestingly, when I attempt this in C# using the same database driver and same connection string it works correctly.

    Take your point on the ENDUWRM in the DB2 trace as that also appears in the working C# insert too.  The only information in the BizTalk Adapter DB2 trace is the rollback of the transaction (instead of commit for the C# program insert).

    Thursday, November 18, 2010 11:53 PM
  • I do stand corrected. using a linked server (CCSID = 37, codepage = 12520:

    -- create table on AS/400
    
    exec('DROP TABLE TEST.TESTGR') at power6
    
    exec('CREATE TABLE TEST.TESTGR (AAA GRAPHIC(1) CCSID 13488 DEFAULT NULL, BBB GRAPHIC(2) CCSID 13488 DEFAULT NULL)') at power6
    
    --
    
    declare @var1 nchar(1)
    
    set @var1 = N'C'
    
    select @var1, len(@var1), DATALENGTH(@var1)
    
    exec('insert into TEST.TESTGR values (?,?)', @var1, @var1) at power6
    
    exec('select AAA, hex(AAA),BBB,hex(BBB) from TEST.TESTGR') at power6;
    
    

    The  1st select against @var1 returns

    (No column name)	(No column name)	(No column name)
    C	1	2

    And the 2nd query against DB2/400 returns:

    AAA	00002	BBB	00004
    C	0043	C 	00430020

    I'll have to verify this with managed code and the adapter, but using a linked server appears to work fine.


    Charles Ezzell - MSFT
    Tuesday, November 23, 2010 9:30 PM
  • Just an update...

    On guidance from Microsoft support we've recently updated the PTF's on our AS400 which included some fixes to the IBM DRDA. 

          SF99610                        9111      Not installed                              

          SF99609                        68           Installed                                  

          SF99608                        18           Installed                                  

          SF99601                        14           Installed                                  

          SF99540                        9104      Not installed          

    Unfortunately, it didn't resolve our issue.  Still trying to resolve this...

    Tuesday, November 30, 2010 11:27 PM
  • Previously, I think I mentioned that I thought the DB2 driver was in the clear.  After some additional testing through C# I'm not so sure.

    My previous test in a C# application involved inserting (only) into a table with Unicode characters. This works fine. 

    However, I recently tested selecting the data from this table and it fails.

                     using (OleDbCommand command = new OleDbCommand())
                    {
                        command.Connection = conn;
                       
                        command.Connection.Open();
                        command.CommandType = CommandType.Text;
                        command.CommandText = "SELECT * FROM TESTDTA.F47011";

                        OleDbDataReader dr = command.ExecuteReader();
                        while (dr.Read())
                        {
                            Console.WriteLine(dr[0]);
                            Console.WriteLine(dr[2]);
                        }
                    }

    Error:
    The data value could not be converted for reasons other than sign mismatch or data overflow. For example, the data was corrupted in the data store but the row was still retrievable.

     

    The above test works fine with EBCDIC (CCSID 37) based AS/400's. 

    I already noticed that the Adapter Schema Generation Wizard does not work.  I think this is related to this problem selecting data.  Perhaps the DB2 adapter issue I'm experiencing is also affected by this. 

    I'm also pretty sure this was working in BTS 2009 as we were testing in BTS 2009 before we upgraded to BTS 2010.

     

    Are we able to try the BTS 2009 version of the DB2 OLE DB provider (or perhaps the IBM provider) with BTS 2010?

     

    Thursday, December 2, 2010 11:32 AM
  • I've just tested this with the HIS 2009 DBOLEDB2 driver and the above test works properly... looks like something with the DB2OLEDB driver in HIS 2010.
    Thursday, December 2, 2010 12:27 PM
  • Just want to let you that I had a similar issue that involved with Unicode in DB2.  Microsoft released HIS 2010 CU2 in February 2010.  Although the issue was not on the fixed defect list, the problem was resolved by installing CU2.  You can download CU2 from http://support.microsoft.com/kb/2616519

    Thursday, April 19, 2012 10:59 AM
  • HIS 2010 CU3 is here - http://support.microsoft.com/default.aspx?scid=kb;EN-US;2654652

    Charles Ezzell - MSFT

    Thursday, April 19, 2012 11:08 AM