Unanswered: Question about some memory affecting parameters
does db2 allocate memory for SHEAPTHRES, BUFFPAGE and DBHEAP all at once or just as needed?
Our applications don't do any sorts so I just defined a high value of memory for SHEAPTHRES, because we want to do a reorg check/reorg soon and I was advised to have a very large SORTHEAP/SHEAPTHRES for that. I also increased BUFFPAGE a lot, even the snapshots at peaktimes show, that it is far too much.
Overall I recognize that about that the box (AIX) is using about 1 GByte of memory for processes instead for files like before I made these changes.
From the information I found at the DB2 Information Center I am not sure, if it allocates the memory or not. Following the snapshots, it doesn't, but looking with the monitor tool for memory usage it's showing other memory usage as stated above.
Else I will keep the values as low as needed and just increase them a lot for our reorg, so that it might be able to finish successfully in some hours.
Thanks for any advice in forward. If something isn't clearly explained, pls tell me, as english isn't my mother language.
"By default, applications use the buffer pool called IBMDEFAULTBP, which is created when the database is created. The DB2 database configuration parameter BUFFPAGE controls the size of a buffer pool when the value of NPAGES is -1 for that buffer pool in the SYSCAT.BUFFERPOOLS catalog table. Otherwise the BUFFPAGE parameter is ignored, and the buffer pool is created with the number of pages specified by the NPAGES parameter."
So unless you set NPAGES to -1, BUFFPAGE is not used. If it is used, it would be allocated when the database is activated.
"Private and shared sorts use memory from two different memory sources. The size of the shared sort memory area is statically predetermined at the time of the first connection to a database based on the value of sheapthres. The size of the private sort memory area is unrestricted.
The sheapthres parameter is used differently for private and shared sorts:
- For private sorts, this parameter is an instance-wide soft limit on the total amount of memory that can be consumed by private sorts at any given time. When the total private-sort memory consumption for an instance reaches this limit, the memory allocated for additional incoming
private-sort requests will be considerably reduced.
- For shared sorts, this parameter is a database-wide hard limit on the total amount of memory consumed by shared sorts at any given time. When this limit is reached, no further shared-sort memory requests will be allowed (until the total shared-sort memory consumption falls below the limit specified by sheapthres)."
I know this explanation from the manual page and I know that I have the BUFFPAGE parameter activated. But the text doesn't say, when the pages I have set as value for BUFFPAGE are allocated and how many of them, if they are just allocated as needed or just all at once. Maybe I miss a sentence or misunderstand one.
Ok, so everything is allocated with the 1st connect to the DB.
If BUFFPAGE is used to determine the bufferpool size (rather than NPAGES for each bufferpool), then it is used to allocate the bufferpools when database is activated. I know it doesn't explicitly say that, but that is how the bufferpools work.