Integration

Get Involved. Join the Conversation.

Topic

    Christopher Maggiulli
    Select statement with DB2 connector doesn't return...Answered
    Topic posted July 3, 2018 by Christopher MaggiulliSilver Crown: 22,500+ Points, tagged Adapters, Agent, Database, Mapping, Orchestration 
    175 Views, 8 Comments
    Title:
    Select statement with DB2 connector doesn't return anything to map
    Summary:
    Using the DB2 connector to connect to DB2 over port 446, select statement validates and generates and xsd but there is not outbound mapping.
    Content:
    I administer a test and production customer managed OIC environment 18.2.5. We have an on premise connectivity agent that we use for a majority of our integrations. We have an AS400 that uses DB2 as our main ERP. I can connect to it successfully over port 446 (that's the standard port for DB2 on the as400 not 5000, shouldn't matter though). When I created an integration invoking the DB2 adapter using a simple select statement the it validates the query and generates the XSD, but the outbound mapping does not show anything to map to. Has anyone experienced this issue? In our Java applications we connect directly to the database using a jt400 jdbc driver. We had an issue with connecting to MySQL until I found a special driver and added it to the agenthome/third-party/lib folder. I tried to add the jt400 driver to that folder and restart, no fix. This is turning into a huge issue. On the old forum someone told me the DB2 adapter does not allow select statements, but I haven't been able to find that stated in the documentation.i haven't placed a SR yet because we have several open and I'm the only technical person to manage them currently. If I could find my own solution I'd prefer so that I don't clutter Oracle with tickets then become unresponsive.

    Best Comment

    Rajendra More

    DB2 Adapter is not certified for DB2 running on AS/400. Please refer adapter certification matrix - http://www.oracle.com/technetwork/middleware/adapters/documentation/adaptercertificationmatrix0217-3613709.pdf

    Comment

     

    • Ankur Jain

      Hi,

      We experienced the same issue in ICS while using select * from table command. We have specified the column name in the select statement to resolve such issue.

      For example: select col1, col2 from table.

      Try the same, hope it should resolve your issue.

      Regards,

      Ankur

      http://www.techsupper.com/

       

       

    • Christopher Maggiulli
      No luck unfortunately, the query I'm running explicitly states the return columns. There is an error in the log that says to make sure a *Config.xml file exists. I grepped the box but didn't find the file, I'll upload the issue. Not sure if the adapter only supports a certain version of DB2.
      • Ankur Jain

        This might be an issue but not sure. In mean while you can try to create stored procedure and call from db2 adapter.

        Regards,

        Ankur 

    • Christopher Maggiulli
      Tried but unfortunately the issue doesn't appear to be with the actual SQL but rather eoth the inability to map the result set. I ran my integration and verified the SQL ran from the journals on our as400 but I can't map the output. This is ok for inserts but most of our integrations simply select from our ERP
      • Nagireddy Tadi

        Hi,

        Can you run through adapter wizard with select query and share agent and OIC logs?

        Please refer following link for DB2 adapter certification matrix.

        http://www.oracle.com/technetwork/middleware/adapters/documentation/adaptercertificationmatrix0217-3613709.pdf

         

    • Rajendra More

      DB2 Adapter is not certified for DB2 running on AS/400. Please refer adapter certification matrix - http://www.oracle.com/technetwork/middleware/adapters/documentation/adaptercertificationmatrix0217-3613709.pdf

      • Christopher Maggiulli

        Hello, I'm not sure what you are explicitly speaking about in that matrix.  It states in on db2 10.7 and above is certified.  Does it matter if it's db2 used on as/400 or not?

      • Christopher Maggiulli

        Additionally I have found this https://blogs.oracle.com/angelo/using-soa-suite-11g-and-want-to-connect-to-an-as400

         

        However hasn't really helped