JDBC sender issue no data in PI

[Former Member]

Hello,

We have a scenario Database to SAP we are using JDBC adapter at sender side and the Qos is EO.

Now the issue is database sender complained that they have sent the data from database but the data is not updating in SAP system as a result no Idoc created.

In the DB trace the DBA said that the SELECT executed successfully.

But I checked in PI and no failed or scheduled message in the Component Channel monitoring. The message id is greyed out. All I see is that Polling started and Processing finished successfully.

In SXMB_MONI there are no xml messages.

SELECT * FROM sys.tables works fine and I can see xml messages in SXMB_MONI and also see the audit trace log in RWB under CC monitoring.

Why is it not picking up data from other user tables in SQL server database?

I have analyzed the issue end to end .

Where is the adapter sending the converted xml messages if it has picked up data? If it has not then why cannot i see the trace and why is the message id greyed out in the CC monitoring?

Please help me to resolve this problem .

Thank you,

Teresa

[baskar_gopalakrishnan2 · Accepted solution]

Few tips....

1)Use the same SQL Query and search outside pi using TOAD or some db client tool to check whether data exists or not..

2) Also add logSQLStatement parameter in the advance tab of jdbc cc and set the value true for log tracing.

3) Looks like data issue.

[Former Member · Accepted solution]

Yes the SELECT from user tables works fine and there is data in the tables that returns after the SELECT is executed using TOAD.

But it does not return anything using PI.

[former_member672060 · Accepted solution]

Hi Theresa,

Is your issue resolved?

I am also getting same error.I can execute SELECT * from sys.tables from sql 2005 and i can see logs in sxmb_moni.But if i change the query to user table like SELECT * from dbo.BKPF_BSEG nothing is received in sxmb_moni.

Even message id in CC is greyed out and log say it processed successful with 120 poll intervals.

I am using logSQLStatement true parameter in the advance tab.Please let me know your solution that will be great.

Thanks,

Thomas.

[Former Member · Accepted solution]

Hi Thomas,

No solution yet. I am not sure if there is a SAP note. We are still researching the issue. Did you try anything different to make it work?

We cannot even see the Message id to check the trace as it is greyed out. We are not sure if it is PI Issue or sql issue. What are your thoughts?

Thanks,

Teresa

[Former Member · Accepted solution]

Hello Teresa,

Try to call a stored procedure with select statement, instead of the direct selection.

PI db username have right permissions?

[Former Member · Accepted solution]

I can try using the Stored Procedure.

[Former Member · Accepted solution]

No luck with Stored Procedure. The Database admin created the user id user1 with the same permissions as his user id. There is no error showing up but still cannot see any data in PI . Please help.

[former_member672060 · Accepted solution]

Hi Theresa,

I just find the solution for this issue.It might help you.

Before we used the command like

SELECT * from dbo.BKPF_BSEG where STATUS='N'

UPDATE dbo.BKPF_BSEG set STATUS= 'Y

using above select and update we didn't receive any logs or reply xml from SQL server.

After changing the SQL syntex like [schema].[tablename]

SELECT * FROM [dbo].[BKPF_BSEG] where STATUS='N'

I am getting xml in sxmb_moni and i can see message id audit logs.

Thanks,

Thomas

[former_member672060 · Accepted solution]

Hi Theresa,

sorry in my message brace symbol is not updated in sdn.

try this

SELECT * from [dbo].[BKPF_BSEG] where STATUS='N'

U need to insert just SQUARE BRACKETS for fileds, like "[dno]"."["BKPF_BSEG"]", unfortunately, when I insert SQUARE BRACKETS, its showing up as \ in SDN.

Thanks,

Thomas.

[former_member190681 · Accepted solution]

Dear Thomas,

Same issue i am facing in Production,

Please suggest me,

my scenario is also like JDBC -->XI--> SAP( Proxy).

The database server sending data to xi, here signals are coming xi perfectly but suppose 100 messages db people sending only 1 or 2 messages lost and it is not shown in xi.

when i checked with SXMB_MONI, RWB(Messages Monitoring, component monitoring) and logs no error message.

DB side it is showing XI_Read time status also changed 'N' to 'S'.

Query:

Select Query:SELECT * FROM MCON_INTF_OPS WHERE CSTATUS = 'N' FOR UPDATE

Update Query:UPDATE MCON_INTF_OPS set CSTATUS  = 'S',CXI_READ = sysdate WHERE CSTATUS = 'N'

why few signals are lost please suggest me

[former_member190681]

set retry interval in communication channel, it may resolve the issue of your issue.

Regards,

Kiran Polani

[Former Member]

Try executing your SQL statement directly in TOAD tool & ensure, it is working properly.

If message is not coming in SXMB_MONI, i think, its not picked up in DB.

Also, check in CC Monitoring...

-santosh.