12 Replies Latest reply on Jun 17, 2019 7:30 PM by mxallen-Oracle

    ORA-28528: Hetrogeneous Services datatype conversion error

    Karthikbr1112

      Hi All,

       

      I am trying to fetch DB2 Data using DB2 Connect in a DB Link

       

      However receiving

       

      ORA-28528: Hetrogeneous Services datatype conversion error, hs configuration and last few lines of trace below, any suggestion will be really helpful

       

      # This is a sample agent init file that contains the HS parameters that are

      # needed for the Database Gateway for ODBC

       

      #

      # HS init parameters

      #

      HS_FDS_CONNECT_INFO = DB2DUD1

      HS_FDS_TRACE_LEVEL = ON

      HS_FDS_TRACE_LEVEL = 255

      #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

      HS_FDS_SHAREABLE_NAME=/u01/unixODBC-2.3.7/lib/libodbc.so

      #HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8

      HS_LANGUAGE = AMERICAN_AMERICA.US7ASCII

      #HS_FDS_DATE_MAPPING=CHAR

      HS_FDS_TIMESTAMP_MAPPING=CHAR

      HS_FDS_SQLLEN_INTERPRETATION=32

      #HS_TIMESTAMP_FORMAT=YYYY-MM-DD-HH24.MI.SS.FF6

      HS_NLS_DATE_FORMAT=YYYY-MM-DD

      HS_RPC_FETCH_REBLOCKING=ON

      #HS_FDS_DELAYED_OPEN=TRUE

      HS_RPC_FETCH_SIZE=1000

      HS_FDS_FETCH_ROWS=1

      #HS_FDS_FETCH_ROWS=200

      #HS_FDS_TRACE_LEVEL=user

       

      #

      # ODBC specific environment variables

      #

      #set ODBCINI=/etc/odbc.ini

      set ODBCINI=/u01/unixODBC-2.3.7/etc/odbc.ini

       

      #

      # Environment variables required for the non-Oracle system

      #

      #set <envvar>=<value>

       

       

       

       

      Last few lines from trace file

       

      SQLFetch: row: 1, column 67, bflsz: 15, bflar: 4

      1 rows fetched

      Exiting hgoftch, rc=0 at 2019/06/10-11:20:13

      hostmstr: 0:   HOA After hoaftch

      hostmstr:          0: RPC After Fetch Row

      hostmstr:          0: RPC Before Describe Table

      hostmstr: 0:   HOA Before hoadtab

      Entered hgodtab at 2019/06/10-11:20:35

      count:1

        table: D001S1.AGENT_AWARD

      Entered hgopcda at 2019/06/10-11:20:36

      Column:1(FK_AGT_PSN_PSN_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:2(AWD_GROUP_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:3(AWD_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:4(AWD_EARNED_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:5, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:5(AWD_EVAL_STATUS_CD): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:6(AWD_UPDT_SRC_IND): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:7(AWD_EXCEPTION_TXT): dtype:1 (CHAR), prc/scl:40/0, nullbl:1, octet:40, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:8(AWD_AUTH_NAME): dtype:1 (CHAR), prc/scl:20/0, nullbl:1, octet:20, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:9(UPDATE_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:1, octet:20, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:10(AWD_PROCESS_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:20, sign:1, radix:10

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      The hoada for table D001S1.AGENT_AWARD follows...

      hgodtab, line 651: Printing hoada @ 0xa91c90

      MAX:10, ACTUAL:10, BRC:1, WHT=6 (TABLE_DESCRIBE)

      DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

        3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_EARNED_DATE

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

        1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

        1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

        3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

      Exiting hgodtab, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoadtab

      hostmstr: 0:   HOA Before hoadafr

      Entered hgodafr, cursor id 0 at 2019/06/10-11:20:36

      Exiting hgodafr, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoadafr

      hostmstr:          0: RPC After Describe Table

      hostmstr:          0: RPC Before Get Statistics

      hostmstr: 0:   HOA Before hoxtcis

      Entered hgotcis at 2019/06/10-11:20:36

      Calling SQLStatistics for D001S1.AGENT_AWARD

      New Index:AI59AI11, type=3, ASCENDING, UNIQUE, cardinality=113598

        ordinal position = 1

        ordinal position = 2

        ordinal position = 3

        ordinal position = 4

      New Index:AI59AI12, type=3, ASCENDING, NON-UNIQUE, cardinality=123

        ordinal position = 1

        ordinal position = 2

      New Index:AI59AI13, type=3, ASCENDING, NON-UNIQUE, cardinality=12829

        ordinal position = 1

      IndexType=SQL_TABLE_STAT: cardinality=113598

      Calling SQLColumns for D001S1.AGENT_AWARD

      Column "FK_AGT_PSN_PSN_ID": dtype=3, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=8

      Column "AWD_GROUP_CODE": dtype=1, colsize=5, decdig=0, char_octet_length=5, cumulative avg row len=13

      Column "AWD_CODE": dtype=1, colsize=5, decdig=0, char_octet_length=5, cumulative avg row len=18

      Column "AWD_EARNED_DATE": dtype=91, colsize=10, decdig=0, char_octet_length=5, cumulative avg row len=34

      Column "AWD_EVAL_STATUS_CD": dtype=1, colsize=1, decdig=0, char_octet_length=1, cumulative avg row len=35

      Column "AWD_UPDT_SRC_IND": dtype=1, colsize=1, decdig=0, char_octet_length=1, cumulative avg row len=36

      Column "AWD_EXCEPTION_TXT": dtype=1, colsize=40, decdig=0, char_octet_length=40, cumulative avg row len=76

      Column "AWD_AUTH_NAME": dtype=1, colsize=20, decdig=0, char_octet_length=20, cumulative avg row len=96

      Column "UPDATE_ID": dtype=3, colsize=10, decdig=0, char_octet_length=20, cumulative avg row len=111

      Column "AWD_PROCESS_DATE": dtype=91, colsize=10, decdig=0, char_octet_length=20, cumulative avg row len=127

      Exiting hgotcis, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoxtcis

      hostmstr:          0: RPC After Get Statistics

      hostmstr:          0: RPC Before SQL Bundling

      hostmstr: 0:   HOA Before hoxclse

      Entered hgoclse, cursor id 1 at 2019/06/10-11:20:36

      Exiting hgoclse, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoxclse

      hostmstr: 0:   HOA Before hoadafr

      Entered hgodafr, cursor id 1 at 2019/06/10-11:20:36

      Exiting hgodafr, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoadafr

      hostmstr: 0:   HOA Before hoxpars

      Entered hgopars, cursor id 1 at 2019/06/10-11:20:36

      type:0

      SQL text from hgopars, id=1, len=240 ...

           00: 53454C45 43542041 312E2246 4B5F4147  [SELECT A1."FK_AG]

           10: 545F5053 4E5F5053 4E5F4944 222C4131  [T_PSN_PSN_ID",A1]

           20: 2E224157 445F4752 4F55505F 434F4445  [."AWD_GROUP_CODE]

           30: 222C4131 2E224157 445F434F 4445222C  [",A1."AWD_CODE",]

           40: 41312E22 4157445F 4541524E 45445F44  [A1."AWD_EARNED_D]

           50: 41544522 2C41312E 22415744 5F455641  [ATE",A1."AWD_EVA]

           60: 4C5F5354 41545553 5F434422 2C41312E  [L_STATUS_CD",A1.]

           70: 22415744 5F555044 545F5352 435F494E  ["AWD_UPDT_SRC_IN]

           80: 44222C41 312E2241 57445F45 58434550  [D",A1."AWD_EXCEP]

           90: 54494F4E 5F545854 222C4131 2E224157  [TION_TXT",A1."AW]

           A0: 445F4155 54485F4E 414D4522 2C41312E  [D_AUTH_NAME",A1.]

           B0: 22555044 4154455F 4944222C 41312E22  ["UPDATE_ID",A1."]

           C0: 4157445F 50524F43 4553535F 44415445  [AWD_PROCESS_DATE]

           D0: 22204652 4F4D2022 44303031 5331222E  [" FROM "D001S1".]

           E0: 22414745 4E545F41 57415244 22204131  ["AGENT_AWARD" A1]

      Exiting hgopars, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoxpars

      hostmstr: 0:   HOA Before hoxopen

      Entered hgoopen, cursor id 1 at 2019/06/10-11:20:36

      hgoopen, line 83: NO hoada to print

      Exiting hgoopen, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoxopen

      hostmstr: 0:   HOA Before hoxdscr

      Entered hgodscr, cursor id 1 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:1(FK_AGT_PSN_PSN_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:2(AWD_GROUP_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:3(AWD_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:4(AWD_EARNED_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:5(AWD_EVAL_STATUS_CD): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:6(AWD_UPDT_SRC_IND): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:7(AWD_EXCEPTION_TXT): dtype:1 (CHAR), prc/scl:40/0, nullbl:1, octet:40, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:8(AWD_AUTH_NAME): dtype:1 (CHAR), prc/scl:20/0, nullbl:1, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:9(UPDATE_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:1, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      Entered hgopcda at 2019/06/10-11:20:36

      Column:10(AWD_PROCESS_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:20:36

      hgodscr, line 506: Printing hoada @ 0xa91a48

      MAX:10, ACTUAL:10, BRC:1, WHT=5 (SELECT_LIST)

      DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

        3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_EARNED_DATE

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

        1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

        1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

        3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

      Exiting hgodscr, rc=0 at 2019/06/10-11:20:36

      hostmstr: 0:   HOA After hoxdscr

      hostmstr:          0: RPC After SQL Bundling

      hostmstr:          0: RPC Before Fetch Row

      hostmstr: 0:   HOA Before hoaftch

      Entered hgoftch, cursor id 1 at 2019/06/10-11:20:37

      hgoftch, line 117: Printing hoada @ 0xa91a48

      MAX:10, ACTUAL:10, BRC:1, WHT=5 (SELECT_LIST)

      DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

        3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_EARNED_DATE

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

        1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

        1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

        3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

      SQLBindCol: column 1, cdatatype: 1, bflsz: 12

      SQLBindCol: column 2, cdatatype: 1, bflsz: 6

      SQLBindCol: column 3, cdatatype: 1, bflsz: 6

      SQLBindCol: column 4, cdatatype: 93, bflsz: 16

      SQLBindCol: column 5, cdatatype: 1, bflsz: 2

      SQLBindCol: column 6, cdatatype: 1, bflsz: 2

      SQLBindCol: column 7, cdatatype: 1, bflsz: 41

      SQLBindCol: column 8, cdatatype: 1, bflsz: 21

      SQLBindCol: column 9, cdatatype: 1, bflsz: 12

      SQLBindCol: column 10, cdatatype: 93, bflsz: 16

      SQLFetch: row: 1, column 1, bflsz: 12, bflar: 7

      SQLFetch: row: 1, column 1, bflsz: 12, bflar: 7

      SQLFetch: row: 1, column 2, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 2, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 3, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 3, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 5, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 5, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 6, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 6, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 7, bflsz: 41, bflar: -1

      SQLFetch: row: 1, column 7, bflsz: 41, bflar: -1

      SQLFetch: row: 1, column 8, bflsz: 21, bflar: -1

      SQLFetch: row: 1, column 8, bflsz: 21, bflar: -1

      SQLFetch: row: 1, column 9, bflsz: 12, bflar: -1

      SQLFetch: row: 1, column 9, bflsz: 12, bflar: -1

      SQLFetch: row: 1, column 10, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 10, bflsz: 16, bflar: 16

      1 rows fetched

      Exiting hgoftch, rc=0 at 2019/06/10-11:20:37

      hostmstr: 0:   HOA After hoaftch

      hostmstr:          0: RPC After Fetch Row

      hostmstr:          0: RPC Before SQL Bundling

      hostmstr: 0:   HOA Before hoxclse

      Entered hgoclse, cursor id 1 at 2019/06/10-11:45:13

      Exiting hgoclse, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoxclse

      hostmstr: 0:   HOA Before hoadafr

      Entered hgodafr, cursor id 1 at 2019/06/10-11:45:14

      Exiting hgodafr, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoadafr

      hostmstr: 0:   HOA Before hoxpars

      Entered hgopars, cursor id 1 at 2019/06/10-11:45:14

      type:0

      SQL text from hgopars, id=1, len=207 ...

           00: 53454C45 43542022 464B5F41 47545F50  [SELECT "FK_AGT_P]

           10: 534E5F50 534E5F49 44222C22 4157445F  [SN_PSN_ID","AWD_]

           20: 47524F55 505F434F 4445222C 22415744  [GROUP_CODE","AWD]

           30: 5F434F44 45222C22 4157445F 4541524E  [_CODE","AWD_EARN]

           40: 45445F44 41544522 2C224157 445F4556  [ED_DATE","AWD_EV]

           50: 414C5F53 54415455 535F4344 222C2241  [AL_STATUS_CD","A]

           60: 57445F55 5044545F 5352435F 494E4422  [WD_UPDT_SRC_IND"]

           70: 2C224157 445F4558 43455054 494F4E5F  [,"AWD_EXCEPTION_]

           80: 54585422 2C224157 445F4155 54485F4E  [TXT","AWD_AUTH_N]

           90: 414D4522 2C225550 44415445 5F494422  [AME","UPDATE_ID"]

           A0: 2C224157 445F5052 4F434553 535F4441  [,"AWD_PROCESS_DA]

           B0: 54452220 46524F4D 20224430 30315331  [TE" FROM "D001S1]

           C0: 222E2241 47454E54 5F415741 524422    ["."AGENT_AWARD"]

      Exiting hgopars, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoxpars

      hostmstr: 0:   HOA Before hoxopen

      Entered hgoopen, cursor id 1 at 2019/06/10-11:45:14

      hgoopen, line 83: NO hoada to print

      Exiting hgoopen, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoxopen

      hostmstr: 0:   HOA Before hoxdscr

      Entered hgodscr, cursor id 1 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:1(FK_AGT_PSN_PSN_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:2(AWD_GROUP_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:3(AWD_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:4(AWD_EARNED_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:5, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:5(AWD_EVAL_STATUS_CD): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:6(AWD_UPDT_SRC_IND): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:7(AWD_EXCEPTION_TXT): dtype:1 (CHAR), prc/scl:40/0, nullbl:1, octet:40, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:8(AWD_AUTH_NAME): dtype:1 (CHAR), prc/scl:20/0, nullbl:1, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:9(UPDATE_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:1, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      Entered hgopcda at 2019/06/10-11:45:14

      Column:10(AWD_PROCESS_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:20, sign:1, radix:0

      Exiting hgopcda, rc=0 at 2019/06/10-11:45:14

      hgodscr, line 506: Printing hoada @ 0xaa1ab0

      MAX:10, ACTUAL:10, BRC:1, WHT=5 (SELECT_LIST)

      DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

        3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_EARNED_DATE

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

        1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

        1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

        3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

      Exiting hgodscr, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoxdscr

      hostmstr:          0: RPC After SQL Bundling

      hostmstr:          0: RPC Before Fetch Row

      hostmstr: 0:   HOA Before hoaftch

      Entered hgoftch, cursor id 1 at 2019/06/10-11:45:14

      hgoftch, line 117: Printing hoada @ 0xaa1ab0

      MAX:10, ACTUAL:10, BRC:1, WHT=5 (SELECT_LIST)

      DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

        3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

        1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

      91 DATE    N 16         16   0/  0 0   0   0 AWD_EARNED_DATE

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

        1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

        1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

        1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

        3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

      91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

      SQLBindCol: column 1, cdatatype: 1, bflsz: 12

      SQLBindCol: column 2, cdatatype: 1, bflsz: 6

      SQLBindCol: column 3, cdatatype: 1, bflsz: 6

      SQLBindCol: column 4, cdatatype: 93, bflsz: 16

      SQLBindCol: column 5, cdatatype: 1, bflsz: 2

      SQLBindCol: column 6, cdatatype: 1, bflsz: 2

      SQLBindCol: column 7, cdatatype: 1, bflsz: 41

      SQLBindCol: column 8, cdatatype: 1, bflsz: 21

      SQLBindCol: column 9, cdatatype: 1, bflsz: 12

      SQLBindCol: column 10, cdatatype: 93, bflsz: 16

      SQLFetch: row: 1, column 1, bflsz: 12, bflar: 7

      SQLFetch: row: 1, column 1, bflsz: 12, bflar: 7

      SQLFetch: row: 1, column 2, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 2, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 3, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 3, bflsz: 6, bflar: 5

      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 4, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 5, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 5, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 6, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 6, bflsz: 2, bflar: 1

      SQLFetch: row: 1, column 7, bflsz: 41, bflar: -1

      SQLFetch: row: 1, column 7, bflsz: 41, bflar: -1

      SQLFetch: row: 1, column 8, bflsz: 21, bflar: -1

      SQLFetch: row: 1, column 8, bflsz: 21, bflar: -1

      SQLFetch: row: 1, column 9, bflsz: 12, bflar: -1

      SQLFetch: row: 1, column 9, bflsz: 12, bflar: -1

      SQLFetch: row: 1, column 10, bflsz: 16, bflar: 16

      SQLFetch: row: 1, column 10, bflsz: 16, bflar: 16

      1 rows fetched

      Exiting hgoftch, rc=0 at 2019/06/10-11:45:14

      hostmstr: 0:   HOA After hoaftch

      hostmstr:          0: RPC After Fetch Row

        • 1. Re: ORA-28528: Hetrogeneous Services datatype conversion error
          mxallen-Oracle

          Does the behavior change if you remove HS_FDS_SQLLEN_INTERPRETATION=32 from the gateway init file?

           

          Regards,

          Matt

          • 2. Re: ORA-28528: Hetrogeneous Services datatype conversion error
            Karthikbr1112

            Hi Matt

             

            Thanks so much for suggesting.

             

            I tried to remove HS_FDS_SQLLEN_INTERPRETATION=32, however still got same error.

             

            I tried to debug further by fetching column wise, and understood that issue was happening for only CHAR columns with certain rows with value as spaces ' '

             

            I was able to fetch rows with few columns with null value however not spaces, any suggestions on how i can handle spaces in a CHAR column? via HS parameters?

             

            Regards,

            Karthik

            • 3. Re: ORA-28528: Hetrogeneous Services datatype conversion error
              mxallen-Oracle

              Try changing HS_RPC_FETCH_REBLOCKING=ON to HS_RPC_FETCH_REBLOCKING=OFF

              Regards

              Matt

              • 4. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                Karthikbr1112

                Hi Matt,

                 

                Thanks again for responding, again same error, i even tried commenting both HS_RPC_FETCH_REBLOCKING and HS_FDS_SQLLEN_INTERPRETATION=32, still same error

                 

                I also noticed that no trace is generated when i tried to select the problem column (column with spaces) , i even changed  HS_FDS_TRACE_LEVEL to DEBUG / OFF, still no trace generated

                 

                Regards,

                Karthik

                • 5. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                  mxallen-Oracle

                  So now you are not getting a trace generated?

                  You should only have a single trace parameter in the gateway init file:

                  HS_FDS_TRACE_LEVEL = 255

                   

                  Any other trace parameter should be removed.

                  When you are not trying to get a trace, then set the value to OFF.

                  HS_FDS_TRACE_LEVEL = OFF

                   

                  Matt

                  • 6. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                    Karthikbr1112

                    Hi Matt,

                     

                    Thanks again, i was not trying to turn off trace, however was surprised to see that trace was not generated.

                     

                    I recheck now and see the trace is getting generated

                     

                    Please find the complete trace below, any suggestions ??

                     

                    [root@l52orvmi-21 log]# more hsDB2DUD1_agt_6339.trc

                     

                     

                    Oracle Corporation --- THURSDAY  JUN 13 2019 12:21:28.379

                     

                     

                    Heterogeneous Agent Release

                    1. 11.1.0.7.0

                     

                     

                     

                     

                    Oracle Corporation --- THURSDAY  JUN 13 2019 12:21:28.378

                     

                        Version 11.1.0.7.0

                     

                    Entered hgogprd

                    HOSGIP for "HS_FDS_TRACE_LEVEL" returned "255"

                    Entered hgosdip

                    setting HS_OPEN_CURSORS to default of 50

                    setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"

                    setting HS_FDS_RECOVERY_PWD to default value

                    setting HS_FDS_TRANSACTION_LOG to default of "HS_TRANSACTION_LOG"

                    setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"

                    setting HS_NLS_NCHAR to default of "AL32UTF8"

                    setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"

                    HOSGIP returned value of "OFF" for HS_RPC_FETCH_REBLOCKING

                    HOSGIP returned value of "1" for HS_FDS_FETCH_ROWS

                    setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"

                    setting HS_FDS_PROC_IS_FUNC to default of "FALSE"

                    setting HS_FDS_CHARACTER_SEMANTICS to default of "FALSE"

                    setting HS_FDS_MAP_NCHAR to default of "TRUE"

                    HOSGIP returned value of "YYYY-MM-DD" for HS_NLS_DATE_FORMAT

                    setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"

                    setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"

                    setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"

                    setting HS_FDS_QUERY_DRIVER to default of "TRUE"

                    setting HS_FDS_SUPPORT_STATISTICS to default of "TRUE"

                    Parameter HS_FDS_QUOTE_IDENTIFIER is not set

                    setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"

                    Exiting hgosdip, rc=0

                    ORACLE_SID is "hsDB2DUD1"

                    Product-Info:

                      Port Rls/Upd:7/0 PrdStat:0

                      Agent:Oracle Database Gateway for ODBC

                      Facility:hsa

                      Class:ODBC, ClassVsn:11.1.0.7.0_0006, Instance:hsDB2DUD1

                    Exiting hgogprd, rc=0

                    hostmstr: 0:   HOA After hoagprd

                    hostmstr: 0:   HOA Before hoainit

                    Entered hgoinit

                    HOCXU_COMP_CSET=1

                    HOCXU_DRV_CSET=1

                    HOCXU_DRV_NCHAR=873

                    HOCXU_DB_CSET=31

                    HOCXU_SEM_VER=102000

                    Entered hgolofn at 2019/06/13-12:21:28

                    HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/u01/unixODBC-2.3.7/lib/libodbc.so"

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLAllocHandle

                    symbol_peflctx=0xaaad6110

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLBindCol

                    symbol_peflctx=0xaaad6270

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLBindParameter

                    symbol_peflctx=0xaaad6aa0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLCancel

                    symbol_peflctx=0xaaad7e70

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLDescribeParam

                    symbol_peflctx=0xaaae05a0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLDisconnect

                    symbol_peflctx=0xaaae0a20

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLEndTran

                    symbol_peflctx=0xaaae38b0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLExecute

                    symbol_peflctx=0xaaae4c60

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLFetch

                    symbol_peflctx=0xaaae54b0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLFreeHandle

                    symbol_peflctx=0xaaae6fa0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLFreeStmt

                    symbol_peflctx=0xaaae6fc0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetData

                    symbol_peflctx=0xaaae8780

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetEnvAttr

                    symbol_peflctx=0xaaaeb810

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetFunctions

                    symbol_peflctx=0xaaaebbd0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLMoreResults

                    symbol_peflctx=0xaaaed730

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLNumResultCols

                    symbol_peflctx=0xaaaee320

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLParamData

                    symbol_peflctx=0xaaaee640

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLPutData

                    symbol_peflctx=0xaaaf0530

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLRowCount

                    symbol_peflctx=0xaaaf0970

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLSetEnvAttr

                    symbol_peflctx=0xaaaf2b40

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLSetDescRec

                    symbol_peflctx=0xaaaf28f0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLColAttribute

                    symbol_peflctx=0xaaad8be0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLColumns

                    symbol_peflctx=0xaaada620

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLConnect

                    symbol_peflctx=0xaaade130

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLDescribeCol

                    symbol_peflctx=0xaaadfea0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLDriverConnect

                    symbol_peflctx=0xaaae15f0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLExecDirect

                    symbol_peflctx=0xaaae4640

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLForeignKeys

                    symbol_peflctx=0xaaae5e00

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetConnectAttr

                    symbol_peflctx=0xaaae7300

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetDiagField

                    symbol_peflctx=0xaaae9c40

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetDiagRec

                    symbol_peflctx=0xaaaeae90

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetInfo

                    symbol_peflctx=0xaaaec860

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetStmtAttr

                    symbol_peflctx=0xaaaec8c0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLGetTypeInfo

                    symbol_peflctx=0xaaaed3a0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLPrepare

                    symbol_peflctx=0xaaaeedb0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLPrimaryKeys

                    symbol_peflctx=0xaaaef2c0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLProcedureColumns

                    symbol_peflctx=0xaaaef8b0

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLProcedures

                    symbol_peflctx=0xaaaeff70

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLSetConnectAttr

                    symbol_peflctx=0xaaaf0c40

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLSetStmtAttr

                    symbol_peflctx=0xaaaf4080

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLSetDescField

                    symbol_peflctx=0xaaaf2310

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLStatistics

                    symbol_peflctx=0xaaaf5960

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Entered hgolofns at 2019/06/13-12:21:28

                    libname=/u01/unixODBC-2.3.7/lib/libodbc.so, funcname=SQLTables

                    symbol_peflctx=0xaaaf6670

                    hoaerr:0

                    Exiting hgolofns at 2019/06/13-12:21:28

                    Exiting hgolofn, rc=0 at 2019/06/13-12:21:28

                    HOSGIP for "HS_OPEN_CURSORS" returned "50"

                    HOSGIP for "HS_FDS_FETCH_ROWS" returned "1"

                    HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"

                    HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"

                    Exiting hgoinit, rc=0 at 2019/06/13-12:21:28

                    hostmstr: 0:   HOA After hoainit

                    hostmstr: 0:   HOA Before hoalgon

                    Entered hgolgon at 2019/06/13-12:21:28

                    reco:0, name:db2admin, tflag:0

                    Entered hgosuec at 2019/06/13-12:21:28

                    Exiting hgosuec, rc=0 at 2019/06/13-12:21:28

                    HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"

                    HOSGIP for "HS_FDS_TRANSACTION_LOG" returned ""HS_TRANSACTION_LOG""

                    HOSGIP for "HS_FDS_TIMESTAMP_AS_DATE" returned "TRUE"

                    HOSGIP for "HS_FDS_CHARACTER_SEMANTICS" returned "FALSE"

                    HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"

                    HOSGIP for "HS_FDS_RESULT_SET_SUPPORT" returned "FALSE"

                    HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"

                    HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"

                    using db2admin as default value for "HS_FDS_DEFAULT_OWNER"

                    HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"

                    Entered hgocont at 2019/06/13-12:21:28

                    HS_FDS_CONNECT_INFO = "DB2DUD1"

                    RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"

                    Entered hgogenconstr at 2019/06/13-12:21:28

                    dsn:DB2DUD1, name:db2admin

                    optn:

                    ##>Connect Parameters (len=35)<##

                    ## DSN=DB2DUD1;

                    #! UID=db2admin;

                    #! PWD=*

                    Exiting hgogenconstr, rc=0 at 2019/06/13-12:21:28

                    DriverName:libdb2.a, DriverVer:10.01.0005

                    DBMS Name:DB2, DBMS Version:11.01.0005

                    Exiting hgocont, rc=0 at 2019/06/13-12:21:28

                    SQLGetInfo returns N for SQL_CATALOG_NAME

                    Exiting hgolgon, rc=0 at 2019/06/13-12:21:28

                    hostmstr: 0:   HOA After hoalgon

                    hostmstr:          0: RPC Before Upload Caps

                    hostmstr: 0:   HOA Before hoaulcp

                    Entered hgoulcp at 2019/06/13-12:21:28

                    Entered hgowlst at 2019/06/13-12:21:28

                    Exiting hgowlst, rc=0 at 2019/06/13-12:21:28

                    SQLGetInfo returns " for SQL_IDENTIFIER_QUOTE_CHAR

                    SQLGetInfo returns Y for SQL_COLUMN_ALIAS

                    14 instance capabilities will be uploaded

                      capno:1991, context:0x0001ffff, add-info:        0

                      capno:1992, context:0x0001ffff, add-info:        0

                      capno:3042, context:0x00000000, add-info: 0, translation:"42"

                      capno:3047, context:0x00000000, add-info:        0, translation:"57"

                      capno:3049, context:0x00000000, add-info:        0, translation:"59"

                      capno:3050, context:0x00000000, add-info:        0, translation:"60"

                      capno:3066, context:0x00000000, add-info:        0

                      capno:3067, context:0x00000000, add-info:        0

                      capno:3068, context:0x00000000, add-info:        0

                      capno:3069, context:0x00000000, add-info:        0

                      capno:3500, context:0x00000001, add-info:       91, translation:"42"

                      capno:3501, context:0x00000001, add-info:       93, translation:"57"

                      capno:3502, context:0x00000001, add-info:      107, translation:"59"

                      capno:3503, context:0x00000001, add-info:      110, translation:"60"

                    Exiting hgoulcp, rc=0 at 2019/06/13-12:21:28

                    hostmstr: 0:   HOA After hoaulcp

                    hostmstr:          0: RPC After Upload Caps

                    hostmstr:          0: RPC Before Upload DDTR

                    hostmstr: 0:   HOA Before hoauldt

                    Entered hgouldt at 2019/06/13-12:21:28

                    0 instance DD translations were uploaded

                    Exiting hgouldt, rc=0 at 2019/06/13-12:21:28

                    hostmstr: 0:   HOA After hoauldt

                    hostmstr:          0: RPC After Upload DDTR

                    hostmstr:          0: RPC Before Begin Trans

                    hostmstr: 0:   HOA Before hoabegn

                    Entered hgobegn at 2019/06/13-12:21:28

                    tflag:0 , initial:1

                    hoi:0x59314768, ttid (len 26) is ...

                      00: 53444249 552E6633 32346130 65662E35  [SDBIU.f324a0ef.5]

                      10: 2E34312E 35313634 3330 [.41.516430]

                    tbid (len 10) is ...

                      0: 05002900 4EE10700 0104 [..).N.....]

                    TXN Capable:2, Isolation Option:0xf

                    Exiting hgobegn, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoabegn

                    hostmstr:          0: RPC After Begin Trans

                    hostmstr:          0: RPC Before Describe Table

                    hostmstr: 0:   HOA Before hoadtab

                    Entered hgodtab at 2019/06/13-12:21:29

                    count:1

                      table: D001S1.AGENT_AWARD

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:1(FK_AGT_PSN_PSN_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:0, octet:0, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:2(AWD_GROUP_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:3(AWD_CODE): dtype:1 (CHAR), prc/scl:5/0, nullbl:0, octet:5, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:4(AWD_EARNED_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:5, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:5(AWD_EVAL_STATUS_CD): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:6(AWD_UPDT_SRC_IND): dtype:1 (CHAR), prc/scl:1/0, nullbl:0, octet:1, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:7(AWD_EXCEPTION_TXT): dtype:1 (CHAR), prc/scl:40/0, nullbl:1, octet:40, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:8(AWD_AUTH_NAME): dtype:1 (CHAR), prc/scl:20/0, nullbl:1, octet:20, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:9(UPDATE_ID): dtype:3 (DECIMAL), prc/scl:10/0, nullbl:1, octet:20, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:10(AWD_PROCESS_DATE): dtype:91 (DATE), prc/scl:10/0, nullbl:0, octet:20, sign:1, radix:10

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    The hoada for table D001S1.AGENT_AWARD follows...

                    hgodtab, line 651: Printing hoada @ 0x17403b70

                    MAX:10, ACTUAL:10, BRC:1, WHT=6 (TABLE_DESCRIBE)

                    DTY NULL-OK  LEN  MAXBUFLEN   PR/SC  CST IND MOD NAME

                      3 DECIMAL N 12         12  10/  0 0   0   0 FK_AGT_PSN_PSN_ID

                      1 CHAR    N 5          5   0/ 0    0   0   0 AWD_GROUP_CODE

                      1 CHAR    N 5          5   0/ 0    0   0   0 AWD_CODE

                    91 DATE    N 16         16   0/ 0    0   0   0 AWD_EARNED_DATE

                      1 CHAR    N 1          1   0/ 0    0   0   0 AWD_EVAL_STATUS_CD

                      1 CHAR    N 1          1   0/ 0    0   0   0 AWD_UPDT_SRC_IND

                      1 CHAR    Y 40         40   0/ 0    0   0   0 AWD_EXCEPTION_TXT

                      1 CHAR    Y 20         20   0/ 0    0   0   0 AWD_AUTH_NAME

                      3 DECIMAL Y 12         12  10/  0 0   0   0 UPDATE_ID

                    91 DATE    N 16         16   0/ 0    0   0   0 AWD_PROCESS_DATE

                    Exiting hgodtab, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoadtab

                    hostmstr: 0:   HOA Before hoadafr

                    Entered hgodafr, cursor id 0 at 2019/06/13-12:21:29

                    Exiting hgodafr, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoadafr

                    hostmstr:          0: RPC After Describe Table

                    hostmstr:          0: RPC Before Get Statistics

                    hostmstr: 0:   HOA Before hoxtcis

                    Entered hgotcis at 2019/06/13-12:21:29

                    Calling SQLStatistics for D001S1.AGENT_AWARD

                    New Index:AI59AI11, type=3, ASCENDING, UNIQUE, cardinality=113598

                      ordinal position = 1

                      ordinal position = 2

                      ordinal position = 3

                      ordinal position = 4

                    New Index:AI59AI12, type=3, ASCENDING, NON-UNIQUE, cardinality=123

                      ordinal position = 1

                      ordinal position = 2

                    New Index:AI59AI13, type=3, ASCENDING, NON-UNIQUE, cardinality=12829

                      ordinal position = 1

                    IndexType=SQL_TABLE_STAT: cardinality=113598

                    Calling SQLColumns for D001S1.AGENT_AWARD

                    Column "FK_AGT_PSN_PSN_ID": dtype=3, colsize=10, decdig=0, char_octet_length=0, cumulative avg row len=8

                    Column "AWD_GROUP_CODE": dtype=1, colsize=5, decdig=0, char_octet_length=5, cumulative avg row len=13

                    Column "AWD_CODE": dtype=1, colsize=5, decdig=0, char_octet_length=5, cumulative avg row len=18

                    Column "AWD_EARNED_DATE": dtype=91, colsize=10, decdig=0, char_octet_length=5, cumulative avg row len=34

                    Column "AWD_EVAL_STATUS_CD": dtype=1, colsize=1, decdig=0, char_octet_length=1, cumulative avg row len=35

                    Column "AWD_UPDT_SRC_IND": dtype=1, colsize=1, decdig=0, char_octet_length=1, cumulative avg row len=36

                    Column "AWD_EXCEPTION_TXT": dtype=1, colsize=40, decdig=0, char_octet_length=40, cumulative avg row len=76

                    Column "AWD_AUTH_NAME": dtype=1, colsize=20, decdig=0, char_octet_length=20, cumulative avg row len=96

                    Column "UPDATE_ID": dtype=3, colsize=10, decdig=0, char_octet_length=20, cumulative avg row len=111

                    Column "AWD_PROCESS_DATE": dtype=91, colsize=10, decdig=0, char_octet_length=20, cumulative avg row len=127

                    Exiting hgotcis, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoxtcis

                    hostmstr:          0: RPC After Get Statistics

                    hostmstr:          0: RPC Before SQL Bundling

                    hostmstr: 0:   HOA Before hoxpars

                    Entered hgopars, cursor id 1 at 2019/06/13-12:21:29

                    type:0

                    SQL text from hgopars, id=1, len=54 ...

                         00: 53454C45 43542022 4157445F 45584345  [SELECT "AWD_EXCE]

                         10: 5054494F 4E5F5458 54222046 524F4D20  [PTION_TXT" FROM ]

                         20: 22443030 31533122 2E224147 454E545F  ["D001S1"."AGENT_]

                         30: 41574152 4422 [AWARD"]

                    Exiting hgopars, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoxpars

                    hostmstr: 0:   HOA Before hoxopen

                    Entered hgoopen, cursor id 1 at 2019/06/13-12:21:29

                    hgoopen, line 83: NO hoada to print

                    Exiting hgoopen, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoxopen

                    hostmstr: 0:   HOA Before hoxdscr

                    Entered hgodscr, cursor id 1 at 2019/06/13-12:21:29

                    Entered hgopcda at 2019/06/13-12:21:29

                    Column:1(AWD_EXCEPTION_TXT): dtype:1 (CHAR), prc/scl:40/0, nullbl:1, octet:40, sign:1, radix:0

                    Exiting hgopcda, rc=0 at 2019/06/13-12:21:29

                    hgodscr, line 506: Printing hoada @ 0x17403858

                    MAX:1, ACTUAL:1, BRC:1, WHT=5 (SELECT_LIST)

                    DTY      NULL-OK  LEN MAXBUFLEN   PR/SC  CST IND MOD NAME

                      1 CHAR Y 40         40   0/  0 0   0   0 AWD_EXCEPTION_TXT

                    Exiting hgodscr, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoxdscr

                    hostmstr:          0: RPC After SQL Bundling

                    hostmstr:          0: RPC Before Fetch Row

                    hostmstr: 0:   HOA Before hoaftch

                    Entered hgoftch, cursor id 1 at 2019/06/13-12:21:29

                    hgoftch, line 117: Printing hoada @ 0x17403858

                    MAX:1, ACTUAL:1, BRC:1, WHT=5 (SELECT_LIST)

                    DTY      NULL-OK  LEN MAXBUFLEN   PR/SC  CST IND MOD NAME

                      1 CHAR Y 40         40   0/  0 0   0   0 AWD_EXCEPTION_TXT

                    SQLBindCol: column 1, cdatatype: 1, bflsz: 41

                    SQLFetch: row: 1, column 1, bflsz: 41, bflar: -1

                    SQLFetch: row: 1, column 1, bflsz: 41, bflar: -1

                    1 rows fetched

                    Exiting hgoftch, rc=0 at 2019/06/13-12:21:29

                    hostmstr: 0:   HOA After hoaftch

                    hostmstr:          0: RPC After Fetch Row

                    hostmstr:          0: RPC Before Commit Trans

                    hostmstr: 0:   HOA Before hoxclse

                    Entered hgoclse, cursor id 1 at 2019/06/13-12:21:31

                    Exiting hgoclse, rc=0 at 2019/06/13-12:21:31

                    hostmstr: 0:   HOA After hoxclse

                    hostmstr: 0:   HOA Before hoadafr

                    Entered hgodafr, cursor id 1 at 2019/06/13-12:21:31

                    Exiting hgodafr, rc=0 at 2019/06/13-12:21:31

                    hostmstr: 0:   HOA After hoadafr

                    hostmstr: 0:   HOA Before hoacomm

                    Entered hgocomm at 2019/06/13-12:21:31

                    keepinfo:0, tflag:1

                       00: 53444249 552E6633 32346130 65662E35  [SDBIU.f324a0ef.5]

                       10: 2E34312E 35313634 3330 [.41.516430]

                    tbid (len 10) is ...

                       0: 05002900 4EE10700 0104 [..).N.....]

                    cmt(0):

                    Entered hgocpctx at 2019/06/13-12:21:31

                    Exiting hgocpctx, rc=0 at 2019/06/13-12:21:31

                    Exiting hgocomm, rc=0 at 2019/06/13-12:21:31

                    hostmstr: 0:   HOA After hoacomm

                    hostmstr:          0: RPC After Commit Trans

                    hostmstr:          0: RPC Before Logoff

                    hostmstr: 0:   HOA Before hoalgof

                    Entered hgolgof at 2019/06/13-12:21:31

                    tflag:1

                    Exiting hgolgof, rc=0 at 2019/06/13-12:21:31

                    hostmstr: 0:   HOA After hoalgof

                    hostmstr:          0: RPC After Logoff

                    hostmstr:          0: RPC Before Exit Agent

                    hostmstr: 0:   HOA Before hoaexit

                    Entered hgoexit at 2019/06/13-12:21:31

                    Exiting hgoexit, rc=0 at 2019/06/13-12:21:31

                    hostmstr: 0:   HOA After hoaexit

                    hostmstr:          0: RPC After Exit Agent

                    • 7. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                      mxallen-Oracle

                      What version of the oracle database is in use?

                      Matt

                      • 8. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                        mxallen-Oracle

                        For 11.1.0.7 of the gateway, do wone of the following:

                         

                        To implement the solution, please execute the following steps on Unix or Linux:

                        A. For versions up to and including 11.1.0.7 -

                        1.Use a compatible 64-bit ODBC driver that handles SQLLEN as a 64-bit value.

                        2. If using the MYSQL ODBC driver follow these steps -

                        1. Obtain unixODBC driver manager source code
                        2. Set the additional environment variable:
                          CFLAGS="-DBUILD_REAL_64_BIT_MODE"
                        3. Compile the unixODBC driver manager and follow the steps on the unixODBC website
                        4. Obtain the MySQL ODBC driver source code
                        5. Set the additional environment variable:
                          CFLAGS="-DBUILD_REAL_64_BIT_MODE"
                        6. Compile the MySQL ODBC driver

                        You will need to contact the driver vendors to get the source code.

                        If there are any errors during the compilation then contact the driver vendor to resolve the problem.

                         

                        3. After rebuilding the driver the sqltype.h file should have an entry -

                        ...
                        /*
                        * Hopefully by now it should be safe to assume most drivers know about SQLLEN now
                        * and the defaukt is now sizeof( SQLLEN ) = 8 on 64 bit platforms
                        *
                        */

                         

                        #if (SIZEOF_LONG_INT == 8)
                        ...

                         

                        B. An alternative for DG4ODBC 11.1.0.7 only is to do the following -

                         

                        1. Apply patch 7513884 to the Gateway ORACLE_HOME

                        2. Add the following parameter to the gateway init<sid>.ora file -

                        HS_FDS_SQLLEN_INTERPRETATION=32

                        • 9. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                          Karthikbr1112

                          Hi Matt,

                           

                          Thanks again for responding, we use Oracle 10g 64Bit.

                           

                          I have used unixODBC 2.3.7 downloaded from http://www.unixodbc.org/

                           

                          Followed the steps configure and make, make install on the site to install it.

                           

                          Please share link for Apply patch 7513884 to the Gateway ORACLE_HOME, its not opening

                           

                          Regards,

                          Karthik

                          • 10. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                            mxallen-Oracle

                            You would access that patch via My Oracle Support under the patches and updates tab.

                             

                            Matt

                            • 11. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                              Karthikbr1112

                              Hi Matt,

                               

                              Thanks again so much, will check and keep you informed

                               

                              Regards,

                              Karthik

                              • 12. Re: ORA-28528: Hetrogeneous Services datatype conversion error
                                mxallen-Oracle

                                Karthik,

                                There is really no need to keep me informed.

                                If the answer solves your issue, then simply mark the thread answered.

                                 

                                Matt