JDBC receiver adapter for AS/400 DB2

[Former Member]

Hi guys,

We have developed an Idoc to JDBC scenario for inserting data in a Z-table of SAP R/3 that sits on an AS/400 DB2.

When testing it, message Mapping is executed successfully, but in communication channel monitoring, we get the following error log:

Error while parsing or executing XML-SQL document: Error processing request in sax parser: Error when executing statement for table/stored proc. 'ZSBU_MATERIALS' (structure 'STATEMENT'): java.sql.SQLException: [SQL0204] ZSBU_MATERIALS in SBU type *FILE not found.

The Z-table name is correct. It seems to me that it needs something more (like a library name).

Any ideas?

Evaggelos

[ravi_raman2]

Hi,

This is generally thrown from db2..when a referenced value is not found in the db..

along with the sqlerror..you should also get a sqlState from the db on as400..

check..An application can also send the SQL message corresponding to any SQLCODE to the job log by specifying the message ID and the replacement text on the CL commands Retrieve Message (RTVMSG), Send Program Message (SNDPGMMSG), and Send User Message (SNDUSRMSG).

Regards

Ravi Raman

[Former Member]

Can you post the structure of the message to JDBC and Can you please check what Vijay said

[Former Member]

That's the structure of the XML message:

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_JDBC_IN_REQ xmlns:ns0="http://famar.gr:DemoJDBC">

<STATEMENT>

<TABLENAME ACTION="INSERT">

<TABLE>ZSBU_MATERIALS</TABLE>

<ACCESS>

<ZMATNR>000000000001005011</ZMATNR>

<ZMAKTX>RIB ZIP CARDI ... TEST</ZMAKTX>

<ZMATL_GROUP>T38</ZMATL_GROUP>

<ZMEINS>ST</ZMEINS>

<ZNTGEW>640.000</ZNTGEW>

</ACCESS>

</TABLENAME>

</STATEMENT>

</ns0:MT_JDBC_IN_REQ>

Evaggelos

[Former Member]

Hi,

1. what are the key fields of your SAP table ZSBU_MATERIALS ?

Maybe you forget to provide one with your SQL request...

2. In your SAP table, are you sure that your material "<ZMATNR>000000000001005011</ZMATNR> is not already present" ? because as you use action "INSERT", it's not possible to have a duplicated entry. Please check that point, even if normally the SQL exception in that case is usually "exception in duplication...".

Mickael

Edited by: Mickael Huchet on Apr 16, 2008 8:45 AM

[Former Member]

ZMATNR is the key field.

[Former Member]

Thank you all for your replies.

The Z-table is empty.

Do I need to add the key field ZMATNR in my data type definition ??

Evaggelos

[Former Member]

Hi,

No. inside XI you just define a structure (called Data Type) without to know the key fields. the behavior is like a SAP structure: just a list of field !

So your exception is not due to a mandatory field (key fields)

Else in your SAP table, have you define something with a type "FILE" (see your exception) ?

Mickael

[Former Member]

In message mapping, I have changed the name of the table to R3SBUDATA.ZSBU_MATERIALS instead of ZSBU_MATERIALS, that I map to field "table".

And now In RWB --> CC monitoring, I can see that "Processing finished successfully", however, I cannot see the inserted row in my table when I go in SAP R/3. Any ideas ??

Do I need to add the field MANDT as well ?

[Former Member]

Hi guys,

Thanks to all of you for your replies. Problem is now solved. I had to add the library under which the db table is found, in the format "LibraryName".'TableName'

Also, I had to add the client field 'MANDT' as an extra field in the data type definition in XI.

Best Regards

Evaggelos

[Former Member]

Question is answered.

[ravi_raman2]

Hi,

There is a difference between db2..and db2 on as400 as on as400 db2 comes as part of the OS install itself..See if you can connect to db2 on the as400 system from qshell..

If you really want to manually configure the connection via the DB2 command line, then you will need to enter catalog commands similar to the following:

1. > db2 catalog tcpip node as400 remote as400.somecompany.com server 446

2. > db2 catalog db as400db at node as400 authentication dcs

3. > db2 catalog dcs db as400db as mydbsvr

Where "as400db" is your local alias for the database and "mydbsvr" is the local RDB name of the AS/400 (set via WRKRDBDIRE).

If you catalogued the DB2 for i5/OS server incorrectly, you may get an SQL5048N error message. SQL7008N is another common error is that the DB2 for i5/OS tables being accessed on the server are not being journaled. To correct the SQL7008N error, you need to start journaling your tables or change the isolation level to No Commit.

Regards

Ravi Raman

[VijayKonam]

hi,

the JDBC channel is very case sensitive. Check if that is making any problems on the database side. Also try to execute the same XML query in normal query form on the DB2 if it executes normally.

VJ

[Former Member]

Hi,

Please update JDBC drive on AS400.

Regards

Ben