DSXchange

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 =' '))) )

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.

You can increase the UDF_MEM size and APP_CTL_HEAP_SZ size
with the following statements.
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.

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.

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

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.

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.