DSXchange

Hi All,

I am facing the problem while loading the data to DB2 using the DSDB2 stage.

It gives the error as

[b]Read caching disabled, file size exceeds cache size[/b]
[b]staodsSapCONTRAINFLoadUpd..xfmUpdate: [IBM][CLI Driver][DB2] SQL0973N Not enough storage is available in the "DRDA_HEAP_SZ" heap to process the statement. SQLSTATE=57011[/b]

the job is running fine with warnings as above and the data is not getting loabed into the DB2.

I also disabled the preload to memory option on the hash file stage where I lookuo the data before loading.

Could any one please help me on this

Thanks in adavance to all

regards
Kalyan



Could any one please help me on this.

So, it is running fine or is it not loading data into DB2?

The first message is an informational message (green) only from a hashed file and is self explanitory. In other words, it's not your problem. The second one is your problem - and is unrelated to the first.

Just a guess as I don't use DB2, but - does the stage have an 'array size' setting? If so, what it is set to?

I think in this case you are running out of disk space in DS. If you change your load method to upsert it will be a lot slower but I am fairly certain it will run to completion. If it does run correctly, then you need to look on the DS server and find some more space. Also, is your filesize limited to 2Gb and could the heap be trying to go beyond this? You can check with the ulimit command to make sure you can exceed 2Gb.

kalyanvinnakota wrote:staodsSapCONTRAINFLoadUpd..xfmUpdate: [IBM][CLI Driver][DB2] SQL0973N Not enough storage is available in the "DRDA_HEAP_SZ" heap to process the statement. SQLSTATE=57011

This is an error message from the CLI Driver, indicating that there's no heap space available in the database.

Seems like a database issue to me.

And to me as well - was wondering if it was caused by an overly optimistic array size or some such.

This is definitely a DB2 error. Check with your dba. I believe that you are trying to update / insert too many rows without a commit.

I didn't know that DB/2 also used a 'heap' - normally when I see that word I know it's happening on the DS side of things. If this does pan out to be a database error I'll have learned something new & important

Error message from DB2 manual:

SQL0973N Not enough storage is available in the heap-name heap to process the statement. Explanation: All available memory for this heap has been used. The statement cannot be processed. User Response: Terminate the application on receipt of this message (SQLCODE). Modify the heap-name configuration parameter to increase the heap size. For example, to update a database configuration parameter, issue the following command: db2 update db cfg for db-name using heap-name heap-size To view a list of the database configuration parameters, use the GET DATABASE CONFIGURATION command. To update a database manager configuration parameter, issue the following command: db2 update dbm cfg for db-name using heap-name heap-size To view a list of the database manager configuration parameters, use the GET DATABASE MANAGER CONFIGURATION command. For application group shared heap size, the following three database configuration parameters control its size and usage: APPGROUP_MEM_SZ, GROUPHEAP_RATIO, and APP_CTL_HEAP_SZ. The number of applications in one application group is calculated by: APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ. The application group shared heap size is calculated by: APPGROUP_MEM_SZ * GROUPHEAP_RATIO / 100.

General P. :D

Thanks, I've cut-and-pasted that to my personal DataStage factoids file!