3

As part of a migration, I created a new bufferpool - say BP8K - size 8K, and a tablespace using that bp, and a table in that tablespace, that is loaded with data. However, I get a:

 SQL1218N There are no pages currently available in bufferpool "4097"

during that process. Looking at db2top it appears as if the default bufferpool IBMSYSTEMBP8K is used instead of BP8K which explains the phenomena.

I've seen similar when there is not sufficient shared memory to start the bufferpool, but in this case BP8K shows up in both db2top, and db2pd -d ... -bufferpools. It is also possible to change the size of the bp which in the case of insufficient memory, usually results in a warning that the bufferpool is not started.

A db2stop; db2start fixes the problem, but should that really be necessary? The migration is a sql-script that runs from an upgrade framework, so I would rather not add some hook that forces all applications. Thoughts?

db2level DB21085I This instance or install (instance name, where applicable: "db2inst1") uses "64" bits and DB2 code release "SQL11050" with level identifier "0601010F". Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64", and Fix Pack "0". Product is installed at "/opt/ibm/db2/V11.5". db2licm -l Product name: "DB2 Enterprise Server Edition" License type: "Restricted" Expiry date: "Permanent" Product identifier: "db2ese" Version information: "11.5" Max amount of memory (GB): "128" Max number of cores: "16" cat /proc/meminfo MemTotal: 164759044 kB MemFree: 4267032 kB MemAvailable: 131089520 kB

Mark Barinstein helped me determine that the bufferpool is not started after all:

ADM6073W The table space "TBSPC8K" (ID "9") is configured to use buffer pool ID "3", but this buffer pool is not active at this time. In the interim the table space will use buffer pool ID "4097".

Most memory is set to AUTOMATIC including the new BP, so I'm not sure why it can't start it. There is plenty of memory available on the server, and the db is using ~1.8Gb (mostly bufferpool) som I'm not sure why Db2 can't start the new bufferpool. I tried to add a commit after bp, tbspace creation but that did not help.

Question now becomes: Why Db2 can't start this BP. According to the docs:

If there is sufficient memory available, the buffer pool can become active immediately. By default new buffer pools are created using the IMMEDIATE keyword, and on most platforms, the database manager is able to acquire more memory.

There seems to be plenty of memory available on the server. The database roughly allocated 1.8Gb memory before creating new tablespaces. Statements for bp creation looks like:

CREATE BUFFERPOOL BP8K SIZE AUTOMATIC PAGESIZE 8K @ CREATE BUFFERPOOL BP16K SIZE AUTOMATIC PAGESIZE 16K @ CREATE BUFFERPOOL BP32K SIZE AUTOMATIC PAGESIZE 32K @ CREATE LARGE TABLESPACE TBSPC8K PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP8K @ CREATE LARGE TABLESPACE TBSPC16K PAGESIZE 16K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP16K @ CREATE LARGE TABLESPACE TBSPC32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP32K @

I halted the script after bp, tbspace creation, forced all applications, then ran the rest of it and it worked out fine. I don't get that impression from the docs, but perhaps that has to be done.

I updated db cfg memory config according to:

db2 update db cfg for <db> using SELF_TUNING_MEM ON db2 update db cfg for <db> using DATABASE_MEMORY AUTOMATIC db2 update db cfg for <db> using SORTHEAP AUTOMATIC db2 update db cfg for <db> using SHEAPTHRES_SHR AUTOMATIC db2 connect reset db2 connect to <db>

and checked that SEL_TUNING_MEMORY was active:

db2 get db cfg for <db> show detail | grep SELF

but I still encounter the same problem.

After restoring database (i.e. all memory settings), I did a small test:

~]$ cat test.sh #!/bin/sh OPTS=`getopt -o d:u:p: -- "$@"` eval set -- "$OPTS" user="" passwd="" while true ; do case "$1" in -d) db="$2"; shift 2;; -u) user="$2"; shift 2;; -p) passwd="$2"; shift 2;; --) shift; break;; esac done db2 connect to $db user $user using $passwd if [ $? -ne 0 ]; then exit 1 fi db2diag -A db2 +c -td@ "BEGIN DECLARE EXIT HANDLER FOR SQLWARNING SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'The bufferpool BP8K is not allocated'; EXECUTE IMMEDIATE 'CREATE BUFFERPOOL BP8K SIZE AUTOMATIC PAGESIZE 8K'; END @" db2 +c -td@ "BEGIN DECLARE EXIT HANDLER FOR SQLWARNING SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'The bufferpool BP8K is not allocated'; EXECUTE IMMEDIATE 'CREATE LARGE TABLESPACE TBSPC8K PAGESIZE 8K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP8K'; END " db2 +c -td@ "BEGIN DECLARE EXIT HANDLER FOR SQLWARNING SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'The bufferpool BP8K is not allocated'; EXECUTE IMMEDIATE 'COMMIT'; END " db2diag -A

Then I ran this test:

db2diag: Moving "/opt/nya/users/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log" to "/opt/nya/users/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log_2020-03-28-12.20.57" DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. DB20000I The SQL command completed successfully. db2diag: Moving "/opt/nya/users/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log" to "/opt/nya/users/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log_2020-03-28-12.20.57"

so all 3 sql statements succeded, but in diaglog I find:

2020-03-28-12.20.57.162214+060 I1800E409 LEVEL: Event PID : 5301 TID : 140165787223936 PROC : db2diag INSTANCE: db2inst1 NODE : 000 HOSTNAME: gollum FUNCTION: DB2 UDB, RAS/PD component, pdDiagArchiveDiagnosticLog, probe:88 CREATE : DB2DIAG.LOG ARCHIVE : /opt/nya/users/db2inst1/sqllib/db2dump/DIAG0000/db2diag.log_2020-03-28-12.20.57 : success IMPACT : Potential 2020-03-28-12.20.57.228408+060 E2210E868 LEVEL: Warning PID : 17468 TID : 140189351536384 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : STUDERA APPHDL : 0-637 APPID: *LOCAL.db2inst1.200328112127 UOWID : 1 ACTID: 4 AUTHID : DB2INST1 HOSTNAME: gollum EDUID : 2442 EDUNAME: db2agent (STUDERA) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbAssignBufferPool, probe:2 MESSAGE : ADM6073W The table space "TBSPC8K" (ID "9") is configured to use buffer pool ID "3", but this buffer pool is not active at this time. In the interim the table space will use buffer pool ID "4097". The inactive buffer pool should become available at next database startup provided that the required memory is available. 2020-03-28-12.20.57.272773+060 I3079E557 LEVEL: Info PID : 17468 TID : 140189351536384 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : STUDERA APPHDL : 0-637 APPID: *LOCAL.db2inst1.200328112127 UOWID : 1 ACTID: 5 AUTHID : DB2INST1 HOSTNAME: gollum EDUID : 2442 EDUNAME: db2agent (STUDERA) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbCreateBufferPoolAct, probe:98 MESSAGE : Creating bufferpool "BP8K" Size: "1000" <automatic>

Bottom line, I don't have a clue on how to detect whether the bufferpool is started or not.

Committing each statement in test.sh (and also in my original script), succeeds, and there is no entry in diaglog. Despite this seems to solve the problem, I also added a delay after creating bufferpools:

CREATE BUFFERPOOL BP8K SIZE AUTOMATIC PAGESIZE 8K @ -- delay commit BEGIN DECLARE now TIMESTAMP; DECLARE end TIMESTAMP; SET now = TIMESTAMP(GENERATE_UNIQUE()); SET end = now + 5 seconds; WHILE (now < end) DO SET now = TIMESTAMP(GENERATE_UNIQUE()); END WHILE; END @

This is my lab machine and there is pretty much no other activity on it.

Improve this question
2
  • Memory allocation may depend on your db & dbm cfg parameters. You may have non-automatic [INSTANCE | DATABASE ]_MEMORY, and db2 might not be able to allocate an additional memory for the new bufferpool due to this, because other memory consumers might grow up, which would prevent this bufferool allocation at the time of its creation. But it might be allocated successfully, when you restarted the database, because other memory consumers were not so big. Anyway, you should handle SQL1478W warning in your scripts, if you are going to prevent such a situation in future automatically. Commented Mar 27, 2020 at 13:28
  • FWIW, I created ibm-data-and-ai.ideas.ibm.com/ideas/DB24LUW-I-2205 that suggests a mechanism to be able to synchronize bufferpool and tablespace creation. Commented Nov 18 at 10:36

2 Answers 2

Reset to default
4

db2stop/db2start is not required to make a newly created bufferpool useable, but in most cases you must deactivate and reactivate the database for the tablespaces to be able to use the new bufferpool.

This is because, even if the IMMEDIATE option is specified or implicitly assumed

If there is not enough reserved space in the database shared memory to allocate the new buffer pool (SQLSTATE 01657) the statement is executed as DEFERRED.

Depending on the various memory configuration parameters in effect, particularly database_memory, there may not be enough memory reserved by the database manager to immediately allocate the new bufferpool.

There could be also an issue of timing, which is evident from the diagnostic log fragment added to the question later, where one can see that the tablespace creation occurs (with a warning) before the bufferpool for it is successfully allocated. It takes some time for the new shared memory for the BP to be allocated -- the database manager does a "memory walk", visiting every page in it to make sure it's committed by the operating system. Introducing a pause between create bufferpool and create tablespace might solve the problem.

Improve this answer
0
2

You should handle possible SQLCODE = 20189 (SQLSTATE = '01657') message after CREATE BUFFERPOOL.
The corresponding message looks like below:

db2 "create bufferpool BP8K SIZE XXXXXX" SQL20189W The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory. SQLSTATE=01657

The corresponding db2diag.log message:

2020-03-27-17.35.31.377000+180 E6844329F842 LEVEL: Warning PID : 7260 TID : 1612 PROC : db2syscs.exe INSTANCE: DB2 NODE : 000 DB : SAMPLE APPHDL : 0-146 APPID: *LOCAL.DB2.200317091324 UOWID : 12 ACTID: 1 AUTHID : DB2ADMIN HOSTNAME: xxx EDUID : 1612 EDUNAME: db2agent (SAMPLE) FUNCTION: DB2 UDB, buffer pool services, sqlbCreateBufferPool, probe:5655 MESSAGE : ADM6053W The CREATE BUFFERPOOL statement for buffer pool "BP8K" (ID "3") could not be performed immediately because not enough free memory existed in the database shared memory. The bufferpool will be created on the next database restart. Refer to the documentation for SQLCODE 20189.

I may suggest the following command for this, which raises an sqlexception, if the bufferpool can't be allocated at the time of creation.

--#SET TERMINATOR @ BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '01657' SIGNAL SQLSTATE '75001' SET MESSAGE_TEXT = 'The bufferpool BP8K is not allocated'; -- EXECUTE IMMEDIATE 'CREATE BUFFERPOOL BP8K SIZE XXXXXX'; -- Just for test: ---CALL SYSIBMINTERNAL.SQLEML_RAISE_ERROR(20189, NULL, NULL); END @
Improve this answer
1

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.