Not enough storage is available in the "QUERY_HEAP"

harryhome · Post by **harryhome** » Fri Dec 15, 2006 1:00 am

Hi

Its a plane data load job , which was running fine for last tw0 years. nw suddenly its failing giving following error , plz provide some inputs to this problem

SrcEStrusToSeq..Src_EStrus: [IBM][CLI Driver] CLI0108E Communication link failure. SQLSTATE=40003
SQLSetStmtAttr: Failed to set row array size statement attribute for prefetch.
[IBM][CLI Driver][DB2] SQL0973N Not enough storage is available in the "QUERY_HEAP" heap to process the statement. SQLSTATE=57011

SQLTables: Error retrieving system catalog information for requested tables(s).

SELECT I_MOD_YR,C_STAGE_DVLP,C_FAM,I_VSC,I_CNTL_USAGE,I_END_ITEM,I_END_ITEM_SEQ,I_NEXT_ASSY,I_PART,I_BOM_CNTL,I_USAGE_DESC,L_DEASGD,C_PART_USE_STAT,C_UPG,C_CLS,C_LINE_1,C_LINE_2,C_LINE_3,C_LINE_4,C_LINE_5,C_LINE_6,C_LINE_7,C_LINE_8,C_MKT,C_SERIES,C_BDY_STYLE_1,C_BDY_STYLE_2,C_BDY_STYLE_3,C_BDY_STYLE_4,C_BDY_STYLE_5,C_BDY_STYLE_6,C_BDY_STYLE_7,C_BDY_STYLE_8,C_TRIM,C_ENG_1,C_ENG_2,C_ENG_3,C_ENG_4,C_ENG_5,C_TRANSM_1,C_TRANSM_2,C_TRANSM_3,C_TRANSM_4,C_TRANSM_5,X_COND_DESC,C_LU_PROC_1,C_LU_SRCE_1,C_LU_OPER_1,C_LU_DEST_1,C_LU_PROC_2,C_LU_SRCE_2,C_LU_OPER_2,C_LU_DEST_2,C_SLCT_OPTL,I_SLCT_OPTL,I_LVL,I_BOM_SEQ,C_UM,Q_PART_REQD,I_DEPT_REL,X_COND_TRIM_DESC,X_USAGE_DESC,I_PART_IN_WHITE,L_EXPLD_ON_REL,I_TBL_UPG_FROM,I_PART_LST_RMK,Q_PART_COMP_EXTNDD,I_SEQ_EBUCREL,I_PCHNG,C_EFFCTVY,C_DSPSN,C_PART_SPSD,I_PART_SPSD,X_RMKS_NAT_OF_CHNG,I_PCHNG_HIST,C_EFFCTVY_HIST,C_DSPSN_HIST,C_PART_SPSD_HIST,I_PART_SPSD_HIST,X_NAT_OF_CHNG_HIST,D_EFF_OUT
FROM E.ESTRUS WHERE ((C_STAGE_DVLP='3' and L_DEASGD =' ') or (C_STAGE_DVLP='2' and not (L_DEASGD ='B' or ( L_DEASGD ='D' and C_PART_USE_STAT=' ' and I_PCHNG =' '))) )

narasimha · Post by **narasimha** » Fri Dec 15, 2006 1:32 am

Contact your DBA's they can help you out.

Looks like they need to increase the heap size.
They will give you a better explaination.

I_Server_Whale · Post by **I_Server_Whale** » Fri Dec 15, 2006 2:45 am

You can increase the UDF_MEM size and APP_CTL_HEAP_SZ size
with the following statements.

Code: Select all

db2 update dbm cfg using udf_mem_sz nnnn

Code: Select all

db2 update db cfg for <db> using app_ctl_heap_sz nnnn

Depending on which version of DB2 you're on, you may have to do a db2start to restart the instance.

Again, I would advise the same as Narasimha. Contact your DBA(s).

Whale.

kumar_s · Post by **kumar_s** » Fri Dec 15, 2006 4:01 am

Or you can use your front end tool also.
You can check the Performance tab available on Instances folder of Control Center. You can change the Query heap size. By default it is 1000, you can change to 2048 or even more.

ray.wurlod · Post by **ray.wurlod** » Fri Dec 15, 2006 2:55 pm

Is there any formula or heuristic for determining a new value, other than trial and error?

harryhome · Post by **harryhome** » Mon Dec 18, 2006 12:59 am

Thanks ALL for ur replies. i m trying all soln still.

This is a plane load job , there is no variable value getting calculated in this job.

But i will like know if any variable is getting calculated then how will it affect and give any error after some time. Plz guide me.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 18, 2006 2:23 pm

Talk with your DBA. It is DB2 that is unable to allocate enough storage in the query heap, not DataStage. The suggestions others have offered are likely to help, but you may not have sufficient privilege to execute them. You also need to make sure that they are executed in the same process as the query - in a server job that's typically the process that is executing the Transformer stage.