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 =' '))) )
Not enough storage is available in the "QUERY_HEAP"
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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
Again, I would advise the same as Narasimha. Contact your DBA(s).
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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.
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.