IDOC missing for SQL to IDOC scenario

[Former Member]

Hello everyone

We have an interface through which entries created in a SQL database table are updated in a Z-Table via SQL-IDOC mapping in PI.

The issue is that i am not able to find a few entries in z-table even though they have been created in SQL table.

I checked in WE02 in ECC, the IDOCS with the relevant(missing) data have not been received as well.

I checked in SXMB_MONI in PI for the interface. No relevant XML messages.

Any help would be appreciated.

Regards

Xineohpi

[gagandeep_batra]

hi,

have u  such functionality when u read ur database also update some flag

if yes then check for that. or if there is any log created for that check for that also

if reading  is OK  then come to check for CC log for particular timing

Regards

gagandeep

[Former Member]

HI gagandeep

There is a field in the SQL table which changes value to 'REC' once ECC receives the IDOC.

This has also been set.

How can i check the log in CC for past date?

Regards

Xineohpi

[baskar_gopalakrishnan2]

You can go to RWB and see audit log for the past date messages. Or you can use SXMB_MONI for the same.

[rajasekhar_reddy14]

If there is no entries on MONI for your interface then check Sedner JDBC communication channel [Runtime workbench-->adapter engine-->communication channel monitoring] and check the log of communictaion channel.

Possible reasons are

1)There is no data in SQL tables .

or

2)Problem with sender communication channel may be network issues.

Regards,

Raj

[Former Member]

HI Raj

There are entries in the SQL table.

I did check the communication channel. However, the time period is 2 days in the past. As a result, i cannot view the log.

Regards,

Xineohpi

[rajasekhar_reddy14]

Hi,

If you not in production then stop your JDBC communication and start it, then monitor in the same in Communictaion channel monitoring.

I suspect yoru channel not pulling the data may be there is no valid data to satisfy your SQL query witten in JDBC channel

or

JDBC adapter got locked,restart JDBC adpater service in NWA.

Regards,

Raj

[Former Member]

Hi Raj

There is no lock on the adapter. Even if i restart the adapter, how will i get the data posted in ECC table?

Even i feel that the SQL Query is not being satisfied.

I am checking on the same.

Regards

Xineohpi

[rajasekhar_reddy14]

after restring the channel, if there is data in tables to satisfy SQL statement then it genarates message incommunication chennel monitoring and you can see entry in SXMB_MONI.

if both not happening then check your SQL statement and if required alter it.

[Former Member]

SELECT MSG_REC_ID,ITEM_CODE,QUANTITY,BATCH_NR,PALLET_NO FROM KNPWMS.WMS_TO_HOST  WHERE MSG_TRANS_TYPE='ART_IN' AND MSG_STAT='DEF'

The msg_stat field value for records gets changed to 'REC' via the UPDATE query.

This field value has changed for my missing records. So i dont think restarting the adapter will help me..

[rajasekhar_reddy14]

so there is no data to satfsy yoru SQL statement hence no entries made in adapter/moni .

[Former Member]

no data now.. i cant check the log for what happened two day earlier..

[Former Member]

When is the update query executed? whenever acknowledgement is received from ECC/PI or when the SELECT query is executed? Can you shed some light on this. The reason i am asking you dis is because I am guessing the delay in UPDATE and the next SELECT must be the reason behind the loss of data...

[Former Member]

According to SAP help

a valid SQL statement that is to be applied to the database once the data (determined from the Query SQL Statement) has been successfully sent to the Integration Server/PCK.  

It must be an INSERT, UPDATE, or DELETE statement.

So, update will come into picture once the selct query is succsfully sent to Integration engine...

http://help.sap.com/saphelp_nw04/helpdata/en/7e/5df96381ec72468a00815dd80f8b63/content.htm

Message was edited by: Amit Srivastava