report (uses LDB's) giving dump due to memory prob...

mallikarjun_vaja · ‎2007 May 16

Hi,

I have a report which is giving dump in production due to memory problem.

Memory is increased from basis part,but still getting same error.

the report uses Logical DataBases.

Can anyone suggest solution to solve this problem.

regards

Mallik

amit_khare · ‎2007 May 16

Logical Databases are very handy tool but they always have some or the other adverse effects when come to performances.

And apart fom some general changes at the coding base you cant do much.

Since it is exceeding on time that means the data it is reading is very large in quantity and very large means exceptionally big in amount.This thing actually taken care of while doing testing in the Quality Server, but seems the object was not tested on so much data.

Apat from exceeding the time you cant do much in Produc tion.

Otherwise make changes in development and write the logic to read records in chunks.

This might make it some better but that will be only like a straw in the hay...

Regards,

Amit

Reward all helpful replies.

mallikarjun_vaja · ‎2007 May 17

Hi Amit,

thanks for ur quick reply and ur idea "make changes in development and write the logic to read records in chunks".

I have n't worked on LDB's till now.So i Don't know how I can read the data in chunks from LDB's.

Can u Plz give some idea how to read data in chunks.

Iam also sending the code,if possible ,plz go thru it and give me some idea.

Hope u will help me.

CODE.

REPORT ZAROP028

ABLES: PROJ, " Project definition

PRPS_R, " WBS element

PRPS, " WBS elements

TJ02T, " System Status Values

TCJ1, " Project Types

JEST, " Active System Status

COSP1, " cost totals - external postings

COSS1, " cost totals - internal postings

COVP11, " Line Items by Prd & Doc Header

COVP12, " Line Items by Prd & Doc Header

BHDGD, " Page heading table.

AUFK, " Order Master Data for WBS (LDB)

ANEP, " Asset line item

COBRB, "Distribution Rules Settlement Ru

SETHIER, "Hierarchy Structure

TKA02, "Controlling Area Assignment

CEPC, "Profit Center Master Data

PSDYPD, "Dummy struct for GET proj late

PSDYPR, "Dummy struct for GET PRPS_R late

PSDYNP. "Dummy struct for GET AUFK late

CONSTANTS:

C_E TYPE C VALUE 'E',

C_I TYPE C VALUE 'I',

C_N TYPE C VALUE 'N',

C_X TYPE C VALUE 'X',

C_Y TYPE C VALUE 'Y',

C_NE(2) TYPE C VALUE 'NE',

C_EQ(2) TYPE C VALUE 'EQ',

C_USD(3) TYPE C VALUE 'USD',

C_04(2) TYPE C VALUE '04',

C_I0046(5) TYPE C VALUE 'I0046'

various working storage

DATA:

W_TXT04(4) TYPE C. " make status uppercase

DATA: ACTIVE(1) TYPE C VALUE 'A', " type for status_flag

INACTIVE(1) TYPE C VALUE 'I', " type for status_flag

STATUS_FLAG(1) TYPE C VALUE 'I', " wbs active or inactive?

INACTIVEDT LIKE SY-DATUM, " inactive date for wbs

REC_FLAG TYPE C VALUE 'N', " records retrieved?

DAYS_INACTIVE TYPE I, " days wbs has been inac

W_LOWINACT TYPE I, " smallest inactive days

WBS_CNT TYPE I, " cnt for wbs in status

W_AUC_COST TYPE P DECIMALS 2, " unsettled auc cost

W_VNAME LIKE PRPS_R-VNAME. " venture number storage

DATA: BEGIN OF ITAB OCCURS 0, "MELI-5F2KMH

COMPCODE LIKE PROJ-VBUKR, " company code

PLANT LIKE PROJ-WERKS, " plant

PROJECT LIKE PROJ-PSPID, " project

NAME LIKE PROJ-POST1, " project description

DINACT TYPE I, " days proj inactive

PRCTR LIKE PROJ-PRCTR, " profit center

SETID LIKE SETHIER-SETID, " level 2 pc hier

DESCRIPT LIKE SETHEADERT-DESCRIPT,

VERNA LIKE PROJ-VERNA, " person responsible

AUC TYPE P DECIMALS 2, " AUC unsettled amount

END OF ITAB.

DATA: BEGIN OF I_INACT OCCURS 0,

STAT LIKE JEST-STAT,

INACT LIKE JEST-INACT,

END OF I_INACT.

DATA: EQ_FLAG TYPE C, " system status flag

NE_FLAG TYPE C. " system status flag

TYPES: BEGIN OF T_COSTS,

PSPID LIKE PROJ-PSPID,

BUDAT LIKE COVP11-BUDAT,

END OF T_COSTS.

DATA: I_PROJ LIKE PROJ OCCURS 0 WITH HEADER LINE,

I_PRPS_R LIKE PRPS_R OCCURS 0 WITH HEADER LINE,

I_COSTS TYPE T_COSTS OCCURS 0 WITH HEADER LINE.

DATA: W_RECORDS TYPE I.

DATA: FOUND_FLAG TYPE C. " CHAD-4PRPDE

DATA: BEGIN OF I_HIER OCCURS 0. "FEAT-5BNRRP

INCLUDE STRUCTURE SETHIER. "FEAT-5BNRRP

DATA: END OF I_HIER. "FEAT-5BNRRP

RANGES: R_TJ02T FOR TJ02T-ISTAT.

DATA: W_LVL2 LIKE SETHIER-SETID. "MELI-5F2KMH

SELECTION-SCREEN BEGIN OF BLOCK PROJECT WITH FRAME TITLE TEXT-B01.

PARAMETERS: P_BUDAT LIKE COVP11-BUDAT,

DAYINACT(3) TYPE N DEFAULT '090'.

SELECTION-SCREEN SKIP. "FEAT-5BNRRP

PARAMETERS: P_PC-SET LIKE CEPC-KHINR OBLIGATORY "FEAT-5BNRRP

DEFAULT 'SCH_STREAM'. "FEAT-5BNRRP

SELECTION-SCREEN END OF BLOCK PROJECT.

SELECTION-SCREEN BEGIN OF BLOCK SORT WITH FRAME TITLE TEXT-B02.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(13) TEXT-C01.

SELECTION-SCREEN POSITION 15.

PARAMETERS: P_PLANT RADIOBUTTON GROUP SORT DEFAULT 'X'.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(13) TEXT-C03. "FEAT-5BNRRP

SELECTION-SCREEN POSITION 15.

PARAMETERS: P_PRCTR RADIOBUTTON GROUP SORT. "FEAT-5BNRRP

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(13) TEXT-C04.

SELECTION-SCREEN POSITION 15.

PARAMETERS: P_DAYS RADIOBUTTON GROUP SORT.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK SORT.

AT SELECTION-SCREEN OUTPUT.

PERFORM USER_PARM_INIT.

AT SELECTION-SCREEN.

PERFORM CHECK_PROJ_CRITERIA.

START-OF-SELECTION.

PERFORM BUILD_PC_HIER USING P_PC-SET.

----

Project Definition *

----

GET PROJ.

MOVE-CORRESPONDING PROJ TO I_PROJ. "MELI-5F2KMH

APPEND I_PROJ. "MELI-5F2KMH

CLEAR I_PROJ.

----

WBS element table

----

GET PRPS_R.

MOVE-CORRESPONDING PRPS_R TO I_PRPS_R.

APPEND I_PRPS_R.

CLEAR I_PRPS_R.

----

GET all order and cost objects associated with order and elements

----

GET AUFK.

GET COSP1.

CHECK COSP1-WRTTP EQ C_04. " check if value type '04'

----

Get last date that the wbselement was updated and determine status

----

GET COVP11.

MOVE PROJ-PSPID TO I_COSTS-PSPID.

MOVE COVP11-BUDAT TO I_COSTS-BUDAT.

APPEND I_COSTS.

CLEAR I_COSTS.

----

Loop through Internal cost statements

----

GET COSS1.

CHECK COSS1-WRTTP EQ C_04. " if value type is 04

*----

Get last date that the wbselement was updated and determine status

*----

GET COVP12.

MOVE PROJ-PSPID TO I_COSTS-PSPID.

MOVE COVP12-BUDAT TO I_COSTS-BUDAT.

APPEND I_COSTS.

CLEAR I_COSTS.

END-OF-SELECTION.

PERFORM BUILD_TABLE.

DELETE ITAB WHERE DINACT < DAYINACT.

DESCRIBE TABLE ITAB LINES W_RECORDS.

IF W_RECORDS = 0.

MESSAGE I024.

ENDIF.

PERFORM WRITE_REPORT.

************************************************************************

FORM CHECK_PROJ_CRITERIA *

************************************************************************

This form verifies correct selection criteria has been entered from *
the selection screen inputs. If incorrect data has been entered the*
valid information messages are displayed. *

************************************************************************

FORM CHECK_PROJ_CRITERIA.

check for either posting date or inactivity criteria but not both

IF NOT P_BUDAT IS INITIAL AND NOT DAYINACT IS INITIAL.

MESSAGE E119.

ENDIF.

IF NOT DAYINACT IS INITIAL. "check s/o day inactive

IF NOT DAYINACT <= 998.

MESSAGE E018.

ELSE.

INACTIVEDT = SY-DATUM - DAYINACT.

ENDIF.

if days inactive is initial set INACTIVEDT using the posting date

ELSE.

INACTIVEDT = P_BUDAT.

ENDIF.

ENDFORM. " CHECK_PROJ_CRITERIA

************************************************************************

FORM GET_AUC_COSTS *

************************************************************************

This form retrieves AUC costs for records based on AUFK2 where they *
meet project criteria. This pulls the unsettled amount on a AUC as *
a settlement receiver. *

************************************************************************

FORM GET_AUC_COSTS USING F_OBJNR F_BUKRS.

CONSTANTS:

C_SETLEMENT_ANNUAL LIKE COBRB-PERBZ VALUE 'JHR', "Annual Settlement

C_DEP_AREA_STD LIKE ANEP-AFABE VALUE '01'. "Std depreciation

DATA: L_ANLC LIKE ANLC OCCURS 0 WITH HEADER LINE,

L_ANLCV LIKE ANLCV OCCURS 0 WITH HEADER LINE,

BEGIN OF L_COBRB OCCURS 0, "SRIN-6ZNEAR

ANLN1 LIKE COBRB-ANLN1,

ANLN2 LIKE COBRB-ANLN2,

END OF L_COBRB,

F_H_COST TYPE P DECIMALS 2.

SELECT ANLN1 ANLN2 FROM COBRB

INTO TABLE L_COBRB " SRIN-6ZNEAR

WHERE OBJNR = F_OBJNR

AND PERBZ = C_SETLEMENT_ANNUAL.

IF SY-SUBRC NE 0. " gary-463qbd

EXIT. " gary-463qbd

ENDIF. " gary-463qbd

LOOP AT L_COBRB. " gary-463qbd

CLEAR: F_H_COST.

SELECT SINGLE * FROM ANLC

INTO L_ANLC

WHERE BUKRS = F_BUKRS AND

ANLN1 = L_COBRB-ANLN1 AND

ANLN2 = L_COBRB-ANLN2 AND

GJAHR = SY-DATUM(4) AND

AFABE = C_DEP_AREA_STD AND

ZUJHR = '0000' AND

ZUCOD = '0000'.

IF SY-SUBRC = 0.

CALL FUNCTION 'FI_AA_VALUES_CALCULATE'

EXPORTING

I_ANLC = L_ANLC

IMPORTING

E_ANLCV = L_ANLCV.

F_H_COST = L_ANLCV-BCHWRT_LFD.

ENDIF.

ADD: F_H_COST TO W_AUC_COST.

ENDLOOP. " gary-463qbd

ENDFORM. " GET_AUC_COSTS

************************************************************************

FORM CHECK_INACTIVE_DATE *

************************************************************************

This form checks the computed inactive date based on the formal *
parameter DAYS_INACTIVE. If DAYS_INACTIVE is more current than the *
current lowest inactive date W_LOWINACT then W_LOWINACT is replaced *
with this value. *

************************************************************************

FORM CHECK_INACTIVE_DATE USING DAYS_INACTIVE.

IF DAYS_INACTIVE < W_LOWINACT.

MOVE DAYS_INACTIVE TO W_LOWINACT.

ELSEIF W_LOWINACT EQ 0.

MOVE: DAYS_INACTIVE TO W_LOWINACT,

W_LOWINACT TO ITAB-DINACT.

ENDIF.

ENDFORM. " CHECK_INACTIVE_DATE

----

FORM build_table *

----

collect fields to the internal table ITAB *

----

FORM BUILD_TABLE.

SORT I_COSTS BY PSPID ASCENDING BUDAT DESCENDING.

LOOP AT I_PROJ.

CLEAR W_AUC_COST.

READ TABLE I_COSTS WITH KEY PSPID = I_PROJ-PSPID

BINARY SEARCH.

IF SY-SUBRC = 0.

ITAB-DINACT = SY-DATUM - I_COSTS-BUDAT.

ELSE.

ITAB-DINACT = SY-DATUM - I_PROJ-ERDAT.

ENDIF.

LOOP AT I_PRPS_R WHERE PSPHI = I_PROJ-PSPNR AND

BELKZ = 'X'.

IF NOT I_PRPS_R-IMPRF IS INITIAL.

PERFORM GET_AUC_COSTS USING I_PRPS_R-OBJNR

I_PRPS_R-PBUKR.

ELSE.

CONTINUE.

ENDIF.

ENDLOOP.

ITAB-COMPCODE = I_PROJ-VBUKR.

ITAB-PLANT = I_PROJ-WERKS.

ITAB-PROJECT = I_PROJ-PSPID.

ITAB-NAME = I_PROJ-POST1.

ITAB-PRCTR = I_PROJ-PRCTR.

ITAB-VERNA = I_PROJ-VERNA.

ITAB-AUC = W_AUC_COST.

IF ITAB-DINACT > DAYINACT.

PERFORM GET_HIER_INFO.

ENDIF.

APPEND ITAB.

CLEAR ITAB.

ENDLOOP.

ENDFORM. " BUILD_TABLE

----

FORM STATUS_CHECK *

----

Checks the active status of the project definition for the *
parameter values assigned. *

----

FORM STATUS_CHECK USING F_OBJNR.

begin of GARY-463QBD

CLEAR: I_INACT.

REFRESH: I_INACT.

MOVE: C_N TO EQ_FLAG,

C_Y TO NE_FLAG.

retrieve status for a given record

SELECT STAT INACT

INTO TABLE I_INACT

FROM JEST

WHERE OBJNR EQ F_OBJNR AND

INACT NE C_X.

sort internal status entered by user

SORT R_TJ02T BY OPTION DESCENDING.

check all current object statuses meet user entered criteria

LOOP AT R_TJ02T WHERE OPTION EQ C_NE.

check for user selected options

READ TABLE I_INACT WITH KEY STAT = R_TJ02T-LOW.

IF SY-SUBRC EQ 0.

MOVE C_N TO NE_FLAG.

EXIT.

ENDIF.

ENDLOOP.

LOOP AT R_TJ02T WHERE OPTION EQ C_EQ.

check for user selected options

READ TABLE I_INACT WITH KEY STAT = R_TJ02T-LOW.

IF SY-SUBRC EQ 0.

MOVE C_Y TO EQ_FLAG.

EXIT.

ENDIF.

ENDLOOP.

check for no statuses entered as EQ option

LOOP AT R_TJ02T WHERE OPTION EQ C_EQ.

EXIT.

ENDLOOP.

if so move c_y to eq_flag

IF SY-SUBRC NE 0.

MOVE C_Y TO EQ_FLAG.

ENDIF.

if both eq_flag or ne_flag are true(c_y) move true to valid_flag

IF ( EQ_FLAG EQ C_N OR

NE_FLAG EQ C_N ).

REJECT 'PRPS_R'.

ENDIF.

end of GARY-463QBD

ENDFORM. " STATUS_CHECK

----

Form WRITE_REPORT

----

FORM WRITE_REPORT.

IF P_PLANT EQ 'X'.

SORT ASCENDING BY ITAB-BUSAREA "FEAT-5BNRRP

SORT ITAB ASCENDING BY PLANT

PROJECT.

ELSEIF P_PRCTR EQ 'X'.

SORT ITAB ASCENDING BY PRCTR "FEAT-5BNRRP

PROJECT.

ELSE.

SORT ITAB ASCENDING BY DINACT

PROJECT.

ENDIF.

FORMAT COLOR COL_BACKGROUND INTENSIFIED ON.

"MELI-5F2KMH

LOOP AT ITAB. "MELI-5F2KMH

WRITE: / ITAB-COMPCODE,

9(4) ITAB-PLANT,

17(12) ITAB-SETID,

33(40) ITAB-DESCRIPT,

77(10) ITAB-PRCTR,

91(24) ITAB-PROJECT,

119(40) ITAB-NAME,

163(25) ITAB-VERNA,

192(15) ITAB-AUC CURRENCY C_USD,

211(8) ITAB-DINACT.

ENDLOOP.

ENDFORM. " WRITE_REPORT

----

Form USER_PARM_INIT

----

Initializes user parameter settings

----

FORM USER_PARM_INIT.

ENDFORM. " USER_PARM_INIT

*----

At top of every page, generate Amoco-standard header.

*----

TOP-OF-PAGE.

MOVE: '0' TO BHDGD-INIFL,

SY-LINSZ TO BHDGD-LINES,

SY-UNAME TO BHDGD-UNAME,

SY-REPID TO BHDGD-REPID,

SY-TITLE TO BHDGD-LINE1,

SPACE TO BHDGD-DOMAI,

SPACE TO BHDGD-SEPAR,

SPACE TO BHDGD-MIFFL.

IF P_PLANT EQ 'X'.

MOVE TEXT-H01 TO BHDGD-LINE2.

ELSE.

MOVE TEXT-H03 TO BHDGD-LINE2. "FEAT-5BNRRP

ENDIF.

temporarily clear sy-mandt to remove "europe" from header "FEAT-5BNRRP

DATA: W_MANDT LIKE SY-MANDT.

BHDGD-BUKRS = SPACE.

W_MANDT = SY-MANDT.

CLEAR SY-MANDT.

FORMAT COLOR COL_BACKGROUND INTENSIFIED OFF.

PERFORM BATCH-HEADING(RSBTCHH0).

FORMAT RESET.

reset sy-mandt "FEAT-5BNRRP

SY-MANDT = W_MANDT.

PERFORM PGH_WRITE_HEADING USING TEXT-003 INACTIVEDT. "MELI-5F2KMH

SKIP 1.

ULINE.

write heading info

FORMAT COLOR COL_TOTAL INTENSIFIED ON.

WRITE: /1(5)'CCode',

9(5)'Plant',

17(12)'Level 2 Node',

33(40)'Level 2 Node Description',

77(10)'Profit Ctr',

91(18)'Project Definition',

119(19)'Project Description',

163(18)'Person Responsible',

192(15)' AUC Unsettled',

211(13)'Days Inactive'.

end FEAT-5BNRRP

BEGIN FEAT-5BNRRP NEW FORMS

************************************************************************

FORM BUILD_PC_HIER

************************************************************************

FORM BUILD_PC_HIER USING SETID.

DATA: W_SETID LIKE SETHIER-SETID,

W_NEWSETID LIKE SETHIER-SETID.

CONCATENATE '0H' SETID INTO W_SETID.

CALL FUNCTION 'G_SET_GET_ID_FROM_NAME'

EXPORTING

SHORTNAME = W_SETID

TABNAME = 'GLPCT'

SETCLASS = '0106'

SUPRESS_POPUP = 'X'

OLD_SETID = W_NEWSETID

IMPORTING

NEW_SETID = W_NEWSETID.

CALL FUNCTION 'G_SET_TREE_IMPORT'

EXPORTING

CLIENT = SY-MANDT

LANGU = SY-LANGU

SETID = W_NEWSETID

TABNAME = 'GLPCT'

TABLES

SET_HIERARCHY = I_HIER

EXCEPTIONS

SET_NOT_FOUND = 1

ILLEGAL_FIELD_REPLACEMENT = 2

ILLEGAL_TABLE_REPLACEMENT = 3

OTHERS = 4.

CLEAR I_HIER.

ENDFORM.

************************************************************************

FORM GET_HIER_INFO

************************************************************************

FORM GET_HIER_INFO.

DATA: W_SETID LIKE SETHIER-SETID,

W_KHINR LIKE CEPC-KHINR,

W_INDEX TYPE I.

CLEAR: W_SETID,

W_KHINR,

W_INDEX.

DETERMINE PROFIT CENTER NODE

SELECT SINGLE KHINR

FROM CEPC

INTO W_KHINR

WHERE PRCTR EQ ITAB-PRCTR.

CONCATENATE '0106' I_PROJ-VKOKR W_KHINR

INTO W_SETID.

READ TABLE I_HIER WITH KEY

SETID = W_SETID.

IF SY-SUBRC EQ 0.

W_INDEX = SY-TABIX.

DO.

READ TABLE I_HIER INDEX W_INDEX.

IF W_INDEX = 0.

ITAB-SETID = 'Not Found'.

EXIT.

ENDIF.

IF I_HIER-LEVEL = 1.

ITAB-SETID = I_HIER-SETID+8(12).

ITAB-DESCRIPT = I_HIER-DESCRIPT.

EXIT.

ENDIF.

IF I_HIER-LEVEL = 2.

ITAB-SETID = I_HIER-SETID+8(12).

ITAB-DESCRIPT = I_HIER-DESCRIPT.

EXIT.

ENDIF.

W_INDEX = W_INDEX - 1.

ENDDO.

ELSE.

ITAB-SETID = 'Not Found'.

ENDIF.

ENDFORM.

END FEAT-5BNRRP NEW FORMS

&----

*& Form PGH_WRITE_HEADING WR MELI-5F2KMH

&----

FORM PGH_WRITE_HEADING USING L_V1 L_V2.

DATA: L_LINSZ LIKE SY-LINSZ.

DATA: L_TITLEL LIKE SY-TABIX.

DATA: L_START LIKE SY-TABIX.

DATA: L_DATE LIKE SY-DATUM.

DATA: L_FORMATTED_DATE(10) TYPE C.

DATA: L_TITLE(27) TYPE C.

MOVE L_V2 TO L_DATE.

WRITE L_DATE TO L_FORMATTED_DATE.

CONCATENATE L_V1 L_FORMATTED_DATE INTO L_TITLE

SEPARATED BY SPACE.

L_LINSZ = SY-LINSZ.

L_TITLEL = STRLEN( L_TITLE ).

L_START = ( L_LINSZ / 2 ) - ( L_TITLEL / 2 ) - 2.

NEW-LINE.

POSITION L_START.

WRITE: L_TITLE.

regards

Mallik

.

report (uses LDB's) giving dump due to memory problem