topic select stmt going to dump in Application Development and Automation Discussions

select stmt going to dump

ricky_shaw — Thu, 27 Dec 2012 06:08:14 GMT

Hello Experts,

This is a simple select query in STD SAP Function Module /1PWB/20020120307183419.

This should actually fail as there are no matching entries between the tables DFKKKO & DFKKOP.

But, code stops here, does not go thru and is taking 2 hrs and finally dumps.

* Select any collective document cleared after the previous invoice and before or during the current collective invoice.

select * appending corresponding fields of table lt_abwbl_docs
    from dfkkko as h inner join dfkkop as l
      on h~opbel = l~augbl
where augst = 9
     and abwtp = ' '
     and ( vkont = x_cdoc_head-vkont or abwkt = x_cdoc_head-vkont )
     and ( cpudt > wa_cprev_doch-cpudt or
( cpudt = wa_cprev_doch-cpudt and cputm > wa_cprev_doch-cputm ) )
     and ( cpudt < gt_ccurr_doch-cpudt or
( cpudt = gt_ccurr_doch-cpudt and cputm <= gt_ccurr_doch-cputm ) ).

Please suggest.

Re: select stmt going to dump

Former Member — Thu, 27 Dec 2012 06:54:06 GMT

Hi Ricky,

I suggest you to open an OSS Note.

Regards,

Christian

Re: select stmt going to dump

ThomasZloch — Thu, 27 Dec 2012 14:16:17 GMT

Please provide details about the dump, especially title and error analysis.

Thomas

Re: select stmt going to dump

ricky_shaw — Thu, 27 Dec 2012 15:26:04 GMT

Hello Thomas,

Its a time out dump.

Title:

The program "/1PWB/SAPL23120121219072017" has exceeded the maximum permitted

runtime without nterruption and has therefore been terminated.

Error Analysis:

After a specific time, the program is terminated to make the work area
available to other users who may be waiting.
This is to prevent a work area being blocked unnecessarily long by, for
example:
- Endless loops (DO, WHILE, ...),
- Database accesses with a large result set
- Database accesses without a suitable index (full table scan)

The maximum runtime of a program is limited by the system profile
parameter "rdisp/max_wprun_time". The current setting is 1200 seconds. If this
time limit is
exceeded, the system attempts to cancel any running SQL statement or
signals the ABAP processor to stop the running program. Then the system
waits another 60 seconds maximum. If the program is then still active,
the work process is restarted.

Re: select stmt going to dump

venkatapavan_sinagam — Tue, 08 Jan 2013 13:30:46 GMT

HI Ricky,

may be due to large size of internal table it go for a dump,

rather than using select * condition, define structure with required fields and select the records from table

Regards,

Sinagam.

Re: select stmt going to dump

Former Member — Wed, 09 Jan 2013 13:04:43 GMT

Ricky,

There are several option eo avoid this timeout dump from select queries.

1. Check how much data is there into those 2 tables and actually how much it is going to select .

2. Use index and data base hint to make it faster.

3. You can use open cursor and try to fetch a chunk of data at one go and multiple times

4. check the select stmt and where condition is anything possible to add over there or not.

5. some time it works like remove the greater than less than option from select stmt, and after select 'delete itab where field1 GT ......' - try this. sometime it works. But it will select more than so may be it will not work in your case.

6. remove appending corresponding fields of, take a fresh new itab after sleection you can append those data into this itab.

try it. it may work.

Thanks,

Re: select stmt going to dump

Former Member — Thu, 10 Jan 2013 09:45:42 GMT

Hi Ricky,

I tried to open the FM name provided by you, but could not find it in system.

As, you have stated that its a Standard FM, so kindly try to search for some Note.

Some basis activities can also help you, pls take take from your Basis people, activities like Table Statistics Update , or chack whether the table actually exists in Database, may help you.

If possible, pls provide the FM name.

Thanks.

Re: select stmt going to dump

yuri_ziryukin — Thu, 10 Jan 2013 16:10:09 GMT

Hello Ricky,

the coding seems to be generated.

There is no index for the table DFKKKO that could help database to select required data. No surprise that it dumps due to timeout.

I suggest trying to create an index for the table DFKKKO with fields:

MANDT, AUGST, ABWTP, CPUDT, VKONT, ABWKT

Fields VKONT and ABWKT are unfortunately specified with OR condition. I have included them into index to avoid table access for checking them and you don't get the ultimate improvement, but hopefully your program does not dump anymore.

Regards,

Yuri