topic Re: Native SQL in Application Development and Automation Discussions

Native SQL

Former Member — Fri, 20 Feb 2009 19:18:45 GMT

Hi Experts,

Need help in rewriting the below Open SQL to Native SQL using DB cursor for opening, fetching and Closing to capture the runtime errors occured.

If not itab1[] is initial.

Select gl_sirid ryear poper rprctr sgtxt ps_psp_nr

from glpca

appending table i_itab2 package size 1000

for all entries in itab1

where kokrs = itab1-kokrs

and ryear = p_year "Parameter

and rprctr = itab1-prctr

and rvers = c_per_ver "Constant with value = '000'

and racct in s_racct. "Select-option

endselect.

endif.

Thanks in Advance.

Re: Native SQL

Former Member — Fri, 20 Feb 2009 19:31:47 GMT

since you have a SELECT-OPTION, it would be very difficult to convert this to native SQL.

But why do you want to do this??

Rob

Re: Native SQL

Former Member — Fri, 20 Feb 2009 19:43:44 GMT

After more than 6 hours the batch job is timing out without any short dump traces. Wanted to capture the

reason for short dump.

Re: Native SQL

Former Member — Fri, 20 Feb 2009 19:52:01 GMT

Well, I think you should stick with trying to analyze the open SQL code. Since there is no dump, is there any system log message? You will have to look at the log on the server where the program ran.

Rob

Re: Native SQL

Former Member — Fri, 20 Feb 2009 20:00:41 GMT

If your purpose is to find out what the OpenSQL is being translated to, you can run ST05 and capture the SQL in a trace. Then you can use the Explain Plan function to see what it is doing.

Re: Native SQL

Former Member — Fri, 20 Feb 2009 20:10:54 GMT

Tried ST05 to analyze the error but its not getting captured. To capture the exact reason for the error only the Native SQL catch can help .

Re: Native SQL

Former Member — Fri, 20 Feb 2009 20:28:47 GMT

You could run program RSDB0004, then navigate to "SQL Statement Analysis" and "Shared Cursor Cache" to view the actual Native SQL there.

If you are on Oracle, you could look in the Oracle trace files or dump files.

Re: Native SQL

Former Member — Fri, 20 Feb 2009 21:02:39 GMT

Where can i find SQL Statment Analysis after executing the program RSDB0004 ?

Re: Native SQL

Former Member — Fri, 20 Feb 2009 21:50:03 GMT

In my system it is under the "Performance" folder.