IBM Support

IT17080: MFT JEE Database Logger throws exception SqlSyntaxErrorExceptionERRORCODE=-4461, SQLSTATE=42815 for DB2

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • When using the MQ Managed File Transfer (MFT) JEE Database
    Logger application in a WebSphere Application Server with an IBM
    DB2 database for persistence, the following exception is thrown:
    
    LocalExceptio E   CNTR0020E: EJB threw an unexpected
    (non-declared) exception during invocation of method "onMessage"
    on bean "BeanId(IBM MQ Managed File Transfer database
    logger#com.ibm.wmqfte.databaselogger.jee.ejb.jar#DatabaseLoggerM
    DB, null)". Exception data: java.lang.IllegalStateException:
    Exception caught during message processing!
    	at
    com.ibm.wmqfte.databaselogger.jee.ejb.DatabaseLoggerMDB.onMessag
    e
    	    (DatabaseLoggerMDB.java:323)
    Caused by:
    com.ibm.wmqfte.databaselogger.exceptions.DataStoreException:
    [jcc][t4][1092][11644][3.69.49] Invalid data conversion: Wrong
    result column type for requested conversion. ERRORCODE=-4461,
    SQLSTATE=42815
    	at
    com.ibm.wmqfte.databaselogger.operation.jpa.AddTransferStatOpera
    tionImpl.execute
    	    (AddTransferStatOperationImpl.java:83)
    	at
    com.ibm.wmqfte.databaselogger.messageTypes.TransferCompletedMsg.
    insertStats
    	    (TransferCompletedMsg.java:111)
    	at
    com.ibm.wmqfte.databaselogger.messageTypes.TransferCompletedMsg.
    insert
    	    (TransferCompletedMsg.java:77)
    	at
    com.ibm.wmqfte.databaselogger.jee.ejb.DatabaseLoggerMDB.onMessag
    e
    	    (DatabaseLoggerMDB.java:296)
    	...
    Caused by: <openjpa-2.2.3-SNAPSHOT-r422266:1737410 fatal general
    error> org.apache.openjpa.persistence.PersistenceException:
    [jcc][t4][1092][11644][3.69.49] Invalid data conversion: Wrong
    result column type for requested conversion. ERRORCODE=-4461,
    SQLSTATE=42815
    FailedObject:
    com.ibm.wmqfte.web.jpa.Transfer-414d51204d465439305f514d47522020
    a40c7a59205bd503 [java.lang.String]
    	at
    org.apache.openjpa.jdbc.sql.DBDictionary.narrow(DBDictionary.jav
    a:4998)
    	at
    org.apache.openjpa.jdbc.sql.DBDictionary.newStoreException(DBDic
    tionary.java:4958)
    	...
    	at
    org.apache.openjpa.kernel.ROPStoreManager.initialize(ROPStoreMan
    ager.java:57)
    	at
    org.apache.openjpa.kernel.BrokerImpl.initialize(BrokerImpl.java:
    1051)
    	at
    org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:1009)
    	at
    org.apache.openjpa.kernel.BrokerImpl.find(BrokerImpl.java:926)
    	at
    org.apache.openjpa.kernel.DelegatingBroker.find(DelegatingBroker
    .java:231)
    	at
    org.apache.openjpa.persistence.EntityManagerImpl.find(EntityMana
    gerImpl.java:487)
    	at
    com.ibm.ws.jpa.management.JPATxEmInvocation.find(JPATxEmInvocati
    on.java:231)
    	at
    com.ibm.ws.jpa.management.JPAEntityManager.find(JPAEntityManager
    .java:230)
    	at
    com.ibm.wmqfte.databaselogger.operation.jpa.AddTransferStatOpera
    tionImpl.execute(AddTransferStatOperationImpl.java:59)
    	...
    Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException:
    [jcc][t4][1092][11644][3.69.49] Invalid data conversion: Wrong
    result column type for requested conversion. ERRORCODE=-4461,
    SQLSTATE=42815
    	at com.ibm.db2.jcc.am.gd.a(Unknown Source)
    	at com.ibm.db2.jcc.am.gd.a(Unknown Source)
    	at com.ibm.db2.jcc.am.gd.a(Unknown Source)
    	at com.ibm.db2.jcc.am.kc.W(Unknown Source)
    	at com.ibm.db2.jcc.am.ResultSet.getClob(Unknown Source)
    	at
    com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getClob(WSJdbcResultSe
    t.java:1038)
    	at
    org.apache.openjpa.lib.jdbc.DelegatingResultSet.getClob(Delegati
    ngResultSet.java:592)
    	at
    org.apache.openjpa.jdbc.sql.DBDictionary.getClob(DBDictionary.ja
    va:787)
    	at
    org.apache.openjpa.jdbc.sql.DBDictionary.getClobString(DBDiction
    ary.java:799)
    	...
    
    Any "completed" transfer log messages are moved to the Database
    Logger's SYSTEM.FTE.LOG.RJCT and the information they contain is
    not persisted to the DB2 database.  The following message is
    logged in the WebSphere Application Server SystemOut.log file
    when this occurs:
    
    00000085               W
    com.ibm.wmqfte.databaselogger.jee.ejb.DatabaseLoggerMDB
    BFGJE0002W: A message has been moved to the reject queue after 1
    failed attempts at processing it. Rejected message:
      JMSMessage class: jms_text
      JMSType:          null
      JMSDeliveryMode:  2
      JMSDeliveryDelay: 0
      JMSDeliveryTime:  1501346633218
      JMSExpiration:    0
      JMSPriority:      4
      JMSMessageID:
    ID:414d51204d465439305f514d47522020a40c7a59205bdd02
      JMSTimestamp:     1501346633218
      JMSCorrelationID:
    ID:414d51204d465439305f514d47522020d723735922920b04
      JMSDestination:
    queue://MFT_QMGR/SYSTEM.FTE.LOG.RJCT.MFT_DB_LOGR
      JMSReplyTo:       queue://MFT_QMGR/SYSTEM.FTE.COMMAND.AGENT1
      JMSRedelivered:   false
        JMSXAppID: bootstrap.WSPreLauncher
        JMSXDeliveryCount: 0
        JMSXUserID: tom
        JMS_IBM_Character_Set: UTF-8
        JMS_IBM_Encoding: 273
        JMS_IBM_Format: MQSTR
        JMS_IBM_MsgType: 8
        JMS_IBM_PutApplType: 28
        JMS_IBM_PutDate: 20170729
        JMS_IBM_PutTime: 16435323
    <?xml version="1.0" encoding="UTF-8"?><transaction
    version="6.00" ID="414d51204d465439305f514d475220 ...
    

Local fix

  • Set the following Java system property on the JEE application
    server where the Managed File Transfer JEE Database Logger is
    running:
    
      -Dopenjpa.jdbc.DBDictionary=useGetStringForClobs=true
    
    For WebSphere Application Server environments, this should be
    set as a Generic JVM Argument.  Using the Administration
    Console, this can be found using the breadcrumb trail:
    
      Application servers > [server name] >
        Server Infrastructure > Java and Process Management >
    Process Definition
          Additional properties > Generic JVM Arguments
    

Problem summary

  • ****************************************************************
    USERS AFFECTED:
    This issue affects users of the Managed File Transfer JEE
    Database Logger application that includes the fix for APAR
    IT07693:
    
    http://www-01.ibm.com/support/docview.wss?uid=swg1IT07693
    
    and who use an IBM DB2 database to persist information published
    as XML log messages by agents to the SYSTEM.FTE topic on the
    coordination queue manager.
    
    
    Platforms affected:
    MultiPlatform
    
    ****************************************************************
    PROBLEM DESCRIPTION:
    APAR IT07693 added a number of "@Lob" Java Persistence
    Architecture (JPA) annotations to classes within the Managed
    File Transfer (MFT) JEE Database Logger that represented tables
    within the database.  Some of the annotations were added to
    fields where the type of the data that was stored in the
    database depended on whether an IBM DB2 or Oracle database was
    being used.  For example, the ftelog_tables_db2.sql script, used
    for IBM DB2 databases, defines the TRANSFER table using the
    following SQL:
    
    CREATE TABLE "FTELOG"."TRANSFER"  (
    &#09;&#09;  "TRANSFER_ID" CHAR(48) NOT NULL ,
    &#09;&#09;  "JOB_NAME" VARCHAR(256) ,
    &#09;&#09;  "SCHEDULE_ID" BIGINT ,
    &#09;&#09;  "START_ID" BIGINT ,
    &#09;&#09;  "COMPLETE_ID" BIGINT ,
    &#09;&#09;  "RESULTCODE" INTEGER ,
    &#09;&#09;  "RESULTTEXT" LONG VARCHAR,
    &#09;&#09;  "BYTES_TRANSFERRED" BIGINT,
    &#09;&#09;  "STATUS" CHAR(20),
    &#09;&#09;  "RELATED_TRANSFER_ID" CHAR(48))
    &#09;&#09; IN "USERSPACE1" ;
    
    Whereas, the ftelog_tables_oracle.sql script, used for Oracle
    databases, defines the same table as follows:
    
    CREATE TABLE "FTELOG"."TRANSFER"  (
    &#09;&#09;  "TRANSFER_ID" CHAR(48) NOT NULL ,
    &#09;&#09;  "JOB_NAME" VARCHAR(256) ,
    &#09;&#09;  "SCHEDULE_ID" INTEGER ,
    &#09;&#09;  "START_ID" INTEGER ,
    &#09;&#09;  "COMPLETE_ID" INTEGER ,
    &#09;&#09;  "RESULTCODE" INTEGER ,
    &#09;&#09;  "RESULTTEXT" NCLOB,
    &#09;&#09;  "BYTES_TRANSFERRED" INTEGER,
    &#09;&#09;  "STATUS" CHAR(20),
    &#09;&#09;  "RELATED_TRANSFER_ID" CHAR(48));
    &#09;&#09;
    Note that the "RESULTTEXT" field is defined as being data type
    "LONG VARCHAR" for IBM DB2 and NCLOB for Oracle.
    
    When the MFT JEE Database Logger received a transfer log
    "completed" publication, it attempted to update the
    TRANSFER_STATS table within the database.  To do this, it first
    attempted to find the record in the TRANSFER table that
    corresponded to the transfer identifier from the log message.
    If a DB2 database was used, this caused the exception:
    
      com.ibm.db2.jcc.am.SqlSyntaxErrorException:
    [jcc][t4][1092][11644][3.69.49] Invalid data conversion: Wrong
    result column type for requested conversion. ERRORCODE=-4461,
    SQLSTATE=42815
    
    to be thrown as the JPA library attempted to read the DB2 "LONG
    VARCHAR" data as CLOB data due to the @Lob annotation.
    

Problem conclusion

  • The persistence.xml file, included within the Managed File
    Transfer JEE Database Logger EAR application for IBM DB2
    databases, has been updated to specify the "DBDictionary"
    property:
    
      UseGetStringForClobs
    
    with the value:
    
      true
    
    such that the Java Persistence Architecture library will attempt
    to read any @Lob annotated fields in the classes representing
    database tables as String data, as opposed to character streams.
    
    ---------------------------------------------------------------
    The fix is targeted for delivery in the following PTFs:
    
    Version    Maintenance Level
    v7.5       7.5.0.9
    v8.0       8.0.0.8
    v9.0 CD    9.0.4
    v9.0 LTS   9.0.0.3
    
    The latest available MQ maintenance can be obtained from
    'WebSphere MQ Recommended Fixes'
    http://www-1.ibm.com/support/docview.wss?rs=171&uid=swg27006037
    
    If the maintenance level is not yet available information on
    its planned availability can be found in 'WebSphere MQ
    Planned Maintenance Release Dates'
    http://www-1.ibm.com/support/docview.wss?rs=171&uid=swg27006309
    ---------------------------------------------------------------
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT17080

  • Reported component name

    WMQ MFT

  • Reported component ID

    5724H7242

  • Reported release

    750

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2016-09-16

  • Closed date

    2017-08-25

  • Last modified date

    2017-08-25

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    WMQ MFT

  • Fixed component ID

    5724H7242

Applicable component levels

  • R750 PSY

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSCUHLE","label":"WMQ Managed File Transfer"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.5","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
25 August 2017