none
Internal network library error for Biztalk 2009 DB2 adapter RRS feed

  • Question

  • hi I am using BiZtalk Adapter for host systems 2.0 with biztalk 2009. i want to connect to DB2  which is in UK location. i added all the necessary inputs . it says Connected to data Source but when i try to Sample query  i will get internal network library error or One or more errors occurred during processing of command.

    Out of curiosity I tried to connect using C# code using the DB2OLEDB provider which is the provider used by DB2 adapter. i did not get any errors but the output was in unknown charecter format . But with different provider IBMDA400, i am getting right output

     i am guessing if any settings needs to change in terms of PC code page or CCSID.

     Appreciate you help on this

    I traced this using SNA trace utility..  following are the lines

    Executing SQL Select Statement: SELECT ''  AS PROCEDURE_CAT, RTRIM(ROUTINE_SCHEMA)  AS PROCEDURE_SCHEM, RTRIM(ROUTINE_NAME)  AS PROCEDURE_NAME, IN_PARMS+INOUT_PARMS AS NUM_INPUT_PARAMS, OUT_PARMS+INOUT_PARMS AS NUM_OUTPUT_PARAMS, RESULT_SETS AS NUM_RESULT_SETS, RTRIM(LONG_COMMENT)  AS REMARKS FROM QSYS2.SYSPROCS WHERE ROUTINE_SCHEMA = 'xgstdev' ORDER BY PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME FOR FETCH ONLY
    DDM   -----------------------------------------------------------------------
    DDM   Send: Length = 86
    DDM   005CD0010000
    DDM   0056200C 00442113 D7C8C6D9 C5D7D6D9 E3E24040 40404040 4040E7C7 E2E3C4C5
    DDM   E5D7D2C7 40404040 40404040 D4E2D5C3 F0F0F140 40404040 40404040 4040F5F0
    DDM   F0F0F0F0 F0F10001 00082114 00007FFF 00062132 2417  
    DDM   -----------------------------------------------------------------------
    DDM   Receive: Length = 21
    DDM   001BD0520000
    DDM   00152205 00061149 00000006 21022417 0005211F F1  
    DDM   -----------------------------------------------------------------------
    DDM   Receive: Length = 83
    DDM   0059D0530000
    DDM   0053241A 07780005 0101330C 70809100 00002501 017FFF07 78000502 01D01876
    DDM   D0800001 33008033 00800300 04030004 04000235 07D00778 00050301 E00971E0
    DDM   540001D0 00010778 00050401 F00671F0 E00000   
    DDM   -----------------------------------------------------------------------
    DDM   Receive: Length = 101
    DDM   006BD0030000
    DDM   0065241B 00000000 64F0F2F0 F0F0D8E2 D8C6C5E3 C3C800D7 C8C6D9C5 D7D6D9E3
    DDM   E2404040 40404040 40000000 00000000 00000000 00000000 00000000 00000000
    DDM   00404040 40404040 40404040 00000014 E2D8D3C3 E4D9D5C3 F1404040 40404040
    DDM   4040FFF0 FF      
    SetSqlState: 2 --> 5
    Converting multibyte data with CCSID 0X4F3D to multibyte data with CCSID 0X04E4
    NLS   -----------------------------------------------------------------------
    NLS   Input: Length = 5
    NLS   F0F2F0F0 F0      
    NLS   -----------------------------------------------------------------------
    NLS   Output: Length = 0
    No errors in the message sync! Returning a warning.
    Closing SQL Select Statement: SELECT ''  AS PROCEDURE_CAT, RTRIM(ROUTINE_SCHEMA)  AS PROCEDURE_SCHEM, RTRIM(ROUTINE_NAME)  AS PROCEDURE_NAME, IN_PARMS+INOUT_PARMS AS NUM_INPUT_PARAMS, OUT_PARMS+INOUT_PARMS AS NUM_OUTPUT_PARAMS, RESULT_SETS AS NUM_RESULT_SETS, RTRIM(LONG_COMMENT)  AS REMARKS FROM QSYS2.SYSPROCS WHERE ROUTINE_SCHEMA = 'xgstdev' ORDER BY PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME FOR FETCH ONLY

     


     

    Friday, July 23, 2010 1:56 AM

Answers

  • If this is an AS/400, take a look at the DDL for the table with System Navigator (right click the table, select generate SQL). This will bring up the table DDL similar to the below. I'm interested to know what the CCSID is for the CHAR() columns. With an AS400 they can actually be anything. If they are CCSID 65535, this is CHAR() AS BINARY. Also, please post your connection string.

    --  Generate SQL
    --  Version:                    V6R1M0 080215
    --  Generated on:               07/28/10 02:26:44
    --  Relational Database:        POWER6L1
    --  Standards Option:           DB2 i5/OS
    CREATE TABLE CNENJ.TEST (
     PGM_NAME CHAR(8) CCSID 37 DEFAULT NULL ,
     CREATE_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     EMAIL_FREQ_HOURS FOR COLUMN EMAIL00001 DECIMAL(3, 0) DEFAULT NULL ,
     EMAIL_AFTER_TS FOR COLUMN EMAIL00002 TIMESTAMP DEFAULT NULL ,
     LAST_EMAIL_TS FOR COLUMN LAST_00001 TIMESTAMP DEFAULT NULL ,
     ABORT_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     ABORT_FREQ_HOURS FOR COLUMN ABORT00001 DECIMAL(3, 0) DEFAULT NULL ,
     ABORT_AFTER_TS FOR COLUMN ABORT00002 TIMESTAMP DEFAULT NULL ,
     LAST_ABORT_TS FOR COLUMN LAST_00002 TIMESTAMP DEFAULT NULL ,
     ACK_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_ADDRESS FOR COLUMN EMAIL00003 CHAR(254) CCSID 37 DEFAULT NULL ,
     TRAILER_REC FOR COLUMN TRAIL00001 CHAR(254) CCSID 37 DEFAULT NULL ,
     FILE_DESC CHAR(254) CCSID 37 DEFAULT NULL ) ;


    Charles Ezzell - MSFT
    Wednesday, July 28, 2010 9:31 AM

All replies

  • From the above, you are trying to convert Adobe 1277 to 1252. Most 'English' code pages would be 37 for the host and 1252 for the PC code pages. What platform is DB2 running on, and what code pages are setup for DB2?
    Charles Ezzell - MSFT
    Monday, July 26, 2010 12:40 AM
  • Appreciate you help.  

    In DB2 adapter by default the selected settings are  CCSID as 37 and pc code page a 1252

    For the questions you asked ,By looking in to the Genius  screen the CCSID is 285

    The system wide CCSID is 65535. the system is in UK and i am accessing from US.

    Monday, July 26, 2010 2:29 PM
  • If this is an AS/400, take a look at the DDL for the table with System Navigator (right click the table, select generate SQL). This will bring up the table DDL similar to the below. I'm interested to know what the CCSID is for the CHAR() columns. With an AS400 they can actually be anything. If they are CCSID 65535, this is CHAR() AS BINARY. Also, please post your connection string.

    --  Generate SQL
    --  Version:                    V6R1M0 080215
    --  Generated on:               07/28/10 02:26:44
    --  Relational Database:        POWER6L1
    --  Standards Option:           DB2 i5/OS
    CREATE TABLE CNENJ.TEST (
     PGM_NAME CHAR(8) CCSID 37 DEFAULT NULL ,
     CREATE_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     EMAIL_FREQ_HOURS FOR COLUMN EMAIL00001 DECIMAL(3, 0) DEFAULT NULL ,
     EMAIL_AFTER_TS FOR COLUMN EMAIL00002 TIMESTAMP DEFAULT NULL ,
     LAST_EMAIL_TS FOR COLUMN LAST_00001 TIMESTAMP DEFAULT NULL ,
     ABORT_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     ABORT_FREQ_HOURS FOR COLUMN ABORT00001 DECIMAL(3, 0) DEFAULT NULL ,
     ABORT_AFTER_TS FOR COLUMN ABORT00002 TIMESTAMP DEFAULT NULL ,
     LAST_ABORT_TS FOR COLUMN LAST_00002 TIMESTAMP DEFAULT NULL ,
     ACK_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_ADDRESS FOR COLUMN EMAIL00003 CHAR(254) CCSID 37 DEFAULT NULL ,
     TRAILER_REC FOR COLUMN TRAIL00001 CHAR(254) CCSID 37 DEFAULT NULL ,
     FILE_DESC CHAR(254) CCSID 37 DEFAULT NULL ) ;


    Charles Ezzell - MSFT
    Wednesday, July 28, 2010 9:31 AM
  • If this is an AS/400, take a look at the DDL for the table with System Navigator (right click the table, select generate SQL). This will bring up the table DDL similar to the below. I'm interested to know what the CCSID is for the CHAR() columns. With an AS400 they can actually be anything. If they are CCSID 65535, this is CHAR() AS BINARY. Also, please post your connection string.

    --  Generate SQL
    --  Version:                    V6R1M0 080215
    --  Generated on:               07/28/10 02:26:44
    --  Relational Database:        POWER6L1
    --  Standards Option:           DB2 i5/OS
    CREATE TABLE CNENJ.TEST (
     PGM_NAME CHAR(8) CCSID 37 DEFAULT NULL ,
     CREATE_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     EMAIL_FREQ_HOURS FOR COLUMN EMAIL00001 DECIMAL(3, 0) DEFAULT NULL ,
     EMAIL_AFTER_TS FOR COLUMN EMAIL00002 TIMESTAMP DEFAULT NULL ,
     LAST_EMAIL_TS FOR COLUMN LAST_00001 TIMESTAMP DEFAULT NULL ,
     ABORT_IND CHAR(1) CCSID 37 DEFAULT NULL ,
     ABORT_FREQ_HOURS FOR COLUMN ABORT00001 DECIMAL(3, 0) DEFAULT NULL ,
     ABORT_AFTER_TS FOR COLUMN ABORT00002 TIMESTAMP DEFAULT NULL ,
     LAST_ABORT_TS FOR COLUMN LAST_00002 TIMESTAMP DEFAULT NULL ,
     ACK_TS TIMESTAMP DEFAULT NULL ,
     EMAIL_ADDRESS FOR COLUMN EMAIL00003 CHAR(254) CCSID 37 DEFAULT NULL ,
     TRAILER_REC FOR COLUMN TRAIL00001 CHAR(254) CCSID 37 DEFAULT NULL ,
     FILE_DESC CHAR(254) CCSID 37 DEFAULT NULL ) ;


    Charles Ezzell - MSFT

    g
    Thursday, August 26, 2010 1:15 PM
  • ddf

    Thursday, August 26, 2010 1:16 PM
  • I have same issue. Do we have answer?
    my Connection String:
    Provider=DB2OLEDB;User ID=****;Password=****;Initial Catalog=AS400***;APPC Mode Name=#INTER;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=AS400**.***.COM;Network Port=446;Package Collection=YK;Process Binary as Character=True;Units of Work=RUW;DBMS Platform=DB2/AS400;APPC Security Type=Program;Defer Prepare=True;Rowset Cache Size=0;Authentication=Server;Persist Security Info=True;Connection Pooling=False;Derive Parameters=False;

    Steven Wang
    Thursday, July 21, 2011 3:12 AM
  • Steven,

    It was suggested that you open a support case with MS on the other thread that you opened.

    Thanks...


    Stephen Jackson - MSFT
    Friday, July 22, 2011 5:29 PM