back ground jod getting cancelled-database error

[Former Member]

hi all,

problem with the background job- jobs getting cancelled.

For a paticular variant the bankground job is getting cancelled when i am running the report .

And i checked in sm21 for the system log it's showing that database errors are there and i checked with my basis team but the problem is the internal table size is exceeding and the job is getting cancelled.they suggest me to tune the sql query or restrict the data selection on selection screen. but for the other coutries which are having even more reocrds compare to this variant working fine but for this particular variant jobs are getting cancelled.

In sm21 i am getting this system log..

BZ  Y Unexpected return value 1 when calling up DbSlR

BY  J Function ROLLBACK on connection R/3 failed

BY  4 Database error 0 at UPD access to table TST01

BY  0 >

F6  H Database error: TemSe->XRTAB(3)->1 for table TST01 key

Q0  2 Stop Workp. 28, PID 20662

Q0  1 Start Workp. 14, 16 times since system startup. PID 9 646770

F6  F TemSe object JOBLGX01405500X62317 was closed remotely

R4  7 Delete session 001 after error 023

R2 G HTTP/RFC session has been deleted following timeout

Q0  I Operating system call getaddrinfo failed (error no. 0 )

Q0  I Operating system call getaddrinfo failed (error no. 0 )

BZ  Y Unexpected return value 1 when calling up DbSlR

BY  J Function ROLLBACK on connection R/3 failed

R3  9 Error in DB rollback/SyFlush, return code 016384

BY  4 Database error 0 at UPD access to table TST01

BY  0 >

F6  H Database error: TemSe->XRTAB(3)->1 for table TST01 key

can any one help me regarding this issue...

Thanks,

Bharath.

[Former Member]

Hi Bharath,

Please refer to the below link:

http://scn.sap.com/thread/1846681

Regards,

Shyam

[Former Member]

hi shyam,

thanks for the quick response, already i read the solution provided in that link and i follw up with my basis team to do the same process which is mentioned in that link but they said problem is not with the temse, jobs are cancelled because of the long ruuning job and the time is taking too much in the sql statements and internal table is exceeding the max memory because of that jobs are getting cancelled.

And they suggested me to tune the query or restrict the selection of data ..

but i am not satisfied with that solution because the logic is working fine for the countries which are having the more records compare to the given variant.

so if you have any other solution please provide me the soltion.

Thanks,

Bharath. 

[edgar_nagasaki]

Hi Bharath,

Could you share that SQL query? Maybe it will be better, actually quicker, to change program (and/or improve involved tables with new indexes) instead of to wait for any system setting change...

Edgar

[Former Member]

  hi Edgar,

below is the code & taking too much of time..

   SELECT lifnr
                banks
                bankl
                bankn
                FROM lfbk
                INTO CORRESPONDING FIELDS OF TABLE gt_lfbk 

                FOR ALL ENTRIES IN gt_lifname
                WHERE lifnr EQ gt_lifname-lifnr.

  IF gt_lfbk[] IS NOT INITIAL.

    LOOP AT gt_lfbk ASSIGNING <ls_lfbk>.

     CALL FUNCTION 'CONVERSION_EXIT_ZBNKN_OUTPUT'
          EXPORTING
            input  = <ls_lfbk>-bankn
          IMPORTING
            output = <ls_lfbk>-bankn1.

    ENDLOOP.

  ENDIF.

  IF gt_lfbk IS NOT INITIAL.

    SELECT
          a~lifnr             "Vendor number
          a~land1             "Country Key
          a~name1             "Name1
          a~name2             "Name2
          a~adrnr             "Address Number
          a~ktokk             "Account Group
          a~sperr             "Central posting bloc
          a~stcd1             "Tax Number1
          a~stcd2             "Tax Number2
          a~stceg             "VAT Registration No.
          b~banks             "Bank country key
          b~bankl             "Bank keys
          b~bankn             "Bank Account Number
          c~bukrs             "Company Code
          c~erdat             "Created Date
          c~ernam             "Created by
          c~sperr             "Posting block for company code
          FROM lfa1 AS a
          INNER JOIN lfbk AS b
          on a~lifnr = b~lifnr
          INNER JOIN lfb1 as c
          on a~lifnr = c~lifnr
          INTO CORRESPONDING FIELDS OF TABLE gt_lfbk_fd
          FOR ALL ENTRIES IN gt_lfbk
          WHERE bankl EQ gt_lfbk-bankl
          AND land1 EQ p_land1
          AND a~sperr EQ ''.

i am using the function module "conversion_exit_zbnkn_output" to decrypt the bank account number here the time is taking too much(around 9000 records are looping here) and while i am checking in debugging almost it's taking 70 minutes of time and then after executing this query in the next select the session is terminated because of timeout (in debugging).i think the problem is in the next select(FAE)but for the other variant which have the same no.of records but working fine.

if u have any solution kindly provide me the solution ...

Thanks,

Bharath.

[edgar_nagasaki]

Hummmm... This kind of conversion FM calls are not expected to take this longer... Once it's not standard (please correct me if I'm wrong) you could have some code issue there. Could you also share that CONVERSION_EXIT_ZBNKN_OUTPUT code?

Edgar

[edgar_nagasaki]

In time, also do an initial checking in internal table gt_lifname content (if not initial) in very first select statement (once you're already doing a for all entries there).

Another point of improvement I see is vendor selection (from lfa1, lfb1...) where you could perform individual selection for each table (instead of a single one with inner join) into internal tables and then work with it afterwards.

[raymond_giuseppi]

On which database/OS is your query running, Look like a Filespace/Tablespace is full (UNDO tablespace or filespace behind ?) Does the memory trigger the overflow or vice-versa...

Did you check for classical errors like SELECT FOR ALL ENTRIES with an empty internal table, or so, Find the SELECT INTO itab statement that run when job get cancelled and look for the WHERE clause.

Regards,

Raymond