Problem in Standard program while selecting Index

[Former Member]

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.

[arseni_gallardo]

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]

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

[yuri_ziryukin]

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

[Former Member]

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

[yuri_ziryukin]

Yogesh,

as I said, refer to the SAP note 185822.

Yuri

[volker_borowski2]

Hi,

what fields does your Z-Index have?

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

Volker

[Former Member]

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

[Former Member]

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.

[Former Member]

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.