Native SQL failing to fetch data from external database.

[Former Member]

The Native SQL used for fetching data from external database is not working. The select statement is neither giving any dump nor fetching anything. It is executing for very long time giving time out dump when run in foreground. We have other reports where the same SQL is working fine with same fields fetched from the view used in DB link. Only for one such report we are facing this issue.

We have checked the view from the external database end and it seems to be fine.

Please help to solve this issue with your valuable inputs.

[Former Member]

have you tested the connections between the database and sap. Are you able to get  data from other tables

[Former Member]

can you provide the statement you are using for fetching and the database you are using

[Former Member]

Hi Prakash,

This is the statement i am using.

EXEC SQL.      

select hr_id, lastname, firstname, smtp_extern, unique_id,
              prop_addr1, prop_addr3, hr_db_name
              from peda_gddb into :gs_gddb
               where hr_db_pers_id = :lf_pernr
ENDEXEC.

[Former Member]

is it a cluster table

[Former Member]

YOU DINT MENTIONED WHICH DATABASE YOU ARE USING. IS THE DATABASE IS OF YOU SAP SYSTEM OR OUT SIDE SAP. IF ITS OUT SIDE HOW YOU HAVE PROVIDED THE CREDENTIALS

[Former Member]

Hi Prakash,

peda_gddb is a view created in a DB link to connect our SAP system to one non sap external database.

We are trying to fetch data using this DBlink.

[Former Member]

have you tested the connections between the database and sap. Are you able to get  data from other tables

[Former Member]

if connection is ok try your staments like this. this will give whether its going in infinite loop due to wrong config of view or due to indexing problem of tables in other database.

types: begin of ty_lfa1,

          lifnr type lfa1-lifnr,

          name1 type lfa1-name1,

         end of ty_lfa1.

data: wa_lfa1 type ty_lfa1.

EXEC SQL PERFORMING loop_output.

  SELECT LIFNR, NAME1

  INTO  :WA_LFA1

  FROM  LFA1;

ENDEXEC.

form loop_output.

  write: / wa_lfa1-lifnr.

endform.

[raphael_almeida]

Prakash,

Please, don't use UPPERCASE when you writing.

Warm regards,

Raphael Pacheco.

[Former Member]

Hi Raphael,

            Thanks for your suggestions. I started activities from few days only. I have written Caps only in code.

[raphael_almeida]

Oks

[raphael_almeida]

Hi Nithin,

This is a third party table right?

I suggest the utilization of classes CL_SQL* instead EXEC_SQL.

For examples, please open in SE38 the program ADBC_DEMO.

Warm regards,

Raphael Pacheco.

[Former Member]

Hi Pacheco,

Thanks for your inputs,

But this EXEC_SQL was working fine all this while and it was implemented in 2007.

And also surprisingly the same query is working fine in UAT and development systems and these two are also using the same DB_link as production to the external system.

[raphael_almeida]

Great!

I put an example he used when I used the EXEC_SQL, I believe this will help you.

Warm regards,

Raphael Pacheco.

[Former Member]

Hi Raphael,

Actually i tried ST05 for tracing the SQL. I found that the FETCH statement is missing and ROLLBACK WORK ON CONNECTION is taking very long time.

Please help me in identifying the root cause here.