topic Re: Problem in Standard program while selecting Index in Application Development and Automation Discussions

Problem in Standard program while selecting Index

Former Member — Mon, 10 Oct 2011 14:33:08 GMT

Hi,

I am using the Standard SAP Program ' RFKKZE00' for creating Payment-Lot in my Custom Program ( Z-program ).

The Std. Program inturn calls 'RFKKZE01' and which inturn calls 'RFKKZE02' via Background Job.

The problem is that the program 'RFKKZE02' fails in a Select Query ( so its not clearing documents in the Payment-Lot )

( SELECT * FROM DFKKOP WHERE ABWBL EQ PAR_OPBEL

AND ( WHANG EQ '000' OR OPUPW NE '000' )

AND AUGST EQ SPACE )

stating ' An SQL error occurred when accessing a table '

( DBIF_RSQL_SQL_ERROR ).

The problem happens only in testing server where the select query fetches data from DFKKOP using Custom Index ( Z-index ) wherein in the Pre-Integration server, the select query uses the Std. Index for fetching and the program is working fine.

I am really confused why the program uses Different Indexes in Different Servers.

The order of the fields in the Select Query matches the order in the Std. Index ( as I said it happens in Pre-Integration Server )but it is not matching with the order of the Z-index ( still the program uses this Index in Testing Server & fails ).

Please Help me to solve this Issue.

Regards,

Yogesh.

Re: Problem in Standard program while selecting Index

arseni_gallardo — Mon, 10 Oct 2011 14:47:41 GMT

The database selects one index or another based on some calculated statistics. It looks like you have to force the (re)calculation for your table. Use transaction DB20.

[http://help.sap.com/saphelp_nw04/helpdata/en/df/455e97747111d6b25100508b6b8a93/frameset.htm]

Re: Problem in Standard program while selecting Index

Former Member — Thu, 13 Oct 2011 06:38:48 GMT

Hi Arseni,

We have done the Statistics Update on the table DFKKOP.

But still its fetching the wrong Z Index in that particular testing environment.

In other systems , its fetching the Correct standard Index.

Regards,

Yogesh.

Re: Problem in Standard program while selecting Index

Former Member — Wed, 19 Oct 2011 11:21:18 GMT

Hi,

The problem happens only in testing server where the select query fetches data from DFKKOP using Custom Index ( Z-index )

and it uses RANGE-SCAN to select the indexes whereas in the Pre-Integration server, where it uses SKIP - SCAN and selects Std. Index and the program works fine.

I came to know that database optimizer helps in looking for indexes at DB level. Can you suggest what makes it to select two different methods of scan (skip/range).

Also please suggest how we can analyze the problem

We are still in dilemma why the program uses Different Indexes in Different Servers.

Please Advise.

Re: Problem in Standard program while selecting Index

Former Member — Wed, 19 Oct 2011 12:04:34 GMT

I'd be much more concerned about why the DBIF_RSQL_SQL_ERROR is occurring. One doesn't usually see that because the program selected a different index. Is the dump occurring because of a problem in the secondary index table or in the definition of that index? The Basis folks and DBA need to see which table is a problem and why. If not because of a problem in the secondary index, what is causing the database read failure is more important than why the program utilizes a different index in the production instance.

Re: Problem in Standard program while selecting Index

Former Member — Wed, 19 Oct 2011 12:34:48 GMT

Hi DaveL,

The error mentioned in the Dump analysis was ' Error while accessing a table '.

I found some other ORACLE DB error also in the same Dump as mentioned below :

To prevent the exception, note the following:

Database error text........: " ORA-01555: snapshot too old: rollback segment

number 3 with name "_SYSSMU3$" too small "

Internal call code.........: "[RSQL/FTCH/DFKKOP ]"

Since I am not much aware of DB related issues, I couldn't proceed further.

Please advise regarding the same.

Re: Problem in Standard program while selecting Index

Former Member — Wed, 19 Oct 2011 13:32:13 GMT

I've seen this problem before. As I recall, it was due to a long running report reading a database table that was being updated by the users. Oracle dumped when the rollback segment was out of date.

The solution was either to reduce the run time (difficult because it is a standard program) or run it in off peak hours when fewer users are on the system.

Rob

Re: Problem in Standard program while selecting Index

yuri_ziryukin — Wed, 19 Oct 2011 13:55:30 GMT

Hello Rob,

this error is so old that I did not see it for ages. Now the rollback segments are normally large enough to avoid it.

Possible solutions are described in the SAP note 185822.

Yuri

Re: Problem in Standard program while selecting Index

Former Member — Wed, 19 Oct 2011 14:03:23 GMT

Hi Rob,

As you said the standard program executes successfully at times ( say 1 out of 5 times ) after 12 hrs ( on an average )completing the payment lot and at other times it gets Interrupted within an hour or so with the same SQL error making the payment lot incomplete.

One incident was like I have scheduled that standard program in the night and the next morning the program got completed.

At times when I executed the program in theday time , it got interrupted with in an hour.

The problem is like the Z-program ( which inturn uses the standard program ) is working fine in Production environment ( Z-index is also existing ) and we have modified the program recently and it gives this problem in the testing environment ( for the past few days ) but not in development & Pre-integration environment.

Regards,

Yogesh

Re: Problem in Standard program while selecting Index

yuri_ziryukin — Wed, 19 Oct 2011 15:17:31 GMT

Yogesh,

as I said, refer to the SAP note 185822.

Yuri

Re: Problem in Standard program while selecting Index

volker_borowski2 — Thu, 20 Oct 2011 19:10:46 GMT

Hi,

what fields does your Z-Index have?

And what index (with what fields) is it using in production?

Volker

Re: Problem in Standard program while selecting Index

Former Member — Fri, 21 Oct 2011 05:50:41 GMT

Hi Volker,

We couldn't check the Index with which that particular Select query in the standard SAP program is using to fetch the data from DFFKOP table in Production.

In pre-integration environment wherein the program is getting executed successfully the Standard Index '3' is being used.

In testing environment, the query uses one of the Z-indexes ( the program fails ) for the same query.

The order of the fields in the select query :

MANDT

ABWBL

WHANG

OPUPW

AUGST

The order of the fields in the Standard Index ( 3 ) :

MANDT

ABWBL

AUGST

WHGRP

The order of the fields in the Z-index :

MANDT

AUGST

VKONT

BUDAT

AUGBD

Regards,

Yogesh