f



error whil truncating a huge table

This is a data warehouse environment.

My devlopers face the below mentioned issue while truncating a huge
table

using the following command

ALTER TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
INITIALLY WITH EMPTY TABLE

get the following error PROD_PERI,0: Fatal Error: Fatal: [IBM][CLI
Driver][DB2/LINUXX8664] SQL1218N There are no pages currently
available in bufferpool "4098". SQLSTATE=57011
DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'ALTER
TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
INITIALLY WITH EMPTY TABLE'. See following DB2 message for details.


does some one have an idea as to why this error occues ? Iam not able
to understand how is this related to bufferpool ?

2009-05-25-04.32.47.772229-300 E16686389E552       LEVEL: Error
PID     : 6340                 TID  : 47260427610432PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000          DB   : BCUDB
APPHDL  : 0-1592               APPID: 170.118.125.11.37355.0905250932
AUTHID  : LAADMIND
EDUID   : 2516                 EDUNAME: db2agntp (BCUDB) 0
FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:122
MESSAGE : ADM6019E  All pages in buffer pool "IBMSYSTEMBP16K" (ID
"4098") are
          in use.  Refer to the documentation for SQLCODE -1218.
 Please help!!
0
paddypl
5/25/2009 11:42:44 AM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

2 Replies
1435 Views

Similar Articles

[PageSpeed] 53

On 25 Maj, 13:42, padd...@gmail.com wrote:
> This is a data warehouse environment.
>
> My devlopers face the below mentioned issue while truncating a huge
> table
>
> using the following command
>
> ALTER TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
> INITIALLY WITH EMPTY TABLE
>
> get the following error PROD_PERI,0: Fatal Error: Fatal: [IBM][CLI
> Driver][DB2/LINUXX8664] SQL1218N There are no pages currently
> available in bufferpool "4098". SQLSTATE=3D57011
> DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'ALTER
> TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
> INITIALLY WITH EMPTY TABLE'. See following DB2 message for details.
>
> does some one have an idea as to why this error occues ? Iam not able
> to understand how is this related to bufferpool ?
>
> 2009-05-25-04.32.47.772229-300 E16686389E552 =A0 =A0 =A0 LEVEL: Error
> PID =A0 =A0 : 6340 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 TID =A0: 4726042761043=
2PROC : db2sysc 0
> INSTANCE: db2inst1 =A0 =A0 =A0 =A0 =A0 =A0 NODE : 000 =A0 =A0 =A0 =A0 =A0=
DB =A0 : BCUDB
> APPHDL =A0: 0-1592 =A0 =A0 =A0 =A0 =A0 =A0 =A0 APPID: 170.118.125.11.3735=
5.0905250932
> AUTHID =A0: LAADMIND
> EDUID =A0 : 2516 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 EDUNAME: db2agntp (BCUDB=
) 0
> FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:122
> MESSAGE : ADM6019E =A0All pages in buffer pool "IBMSYSTEMBP16K" (ID
> "4098") are
> =A0 =A0 =A0 =A0 =A0 in use. =A0Refer to the documentation for SQLCODE -12=
18.
> =A0Please help!!

I assume 9.5 LUW. One possible reason is that the normal 16K
bufferpool is not started, perhaps due to insufficient amount of
memory. Check out for:

Database will come up with hidden buffer pools.

in db2diag.log. I'm not sure how to determine whether a BP is started
or not, so I just try to alter the number of pages a bit and then back
again. If the BP is not started you will be notified. The only way I'm
familiar with to activate the normal BP, is to stop/start the
database.


/Lennart
0
Lennart
5/25/2009 2:51:37 PM
paddypl@gmail.com wrote:
> This is a data warehouse environment.
> 
> My devlopers face the below mentioned issue while truncating a huge
> table
> 
> using the following command
> 
> ALTER TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
> INITIALLY WITH EMPTY TABLE
> 
> get the following error PROD_PERI,0: Fatal Error: Fatal: [IBM][CLI
> Driver][DB2/LINUXX8664] SQL1218N There are no pages currently
> available in bufferpool "4098". SQLSTATE=57011
> DSTAGE-DB2CLI-0027`:`SQLExecDirect: Error executing statement 'ALTER
> TABLE FSP_AHOLDGC_DEV2.PRODUCT_PERISHABLE ACTIVATE NOT LOGGED
> INITIALLY WITH EMPTY TABLE'. See following DB2 message for details.
> 
> 
> does some one have an idea as to why this error occues ? Iam not able
> to understand how is this related to bufferpool ?
> 
> 2009-05-25-04.32.47.772229-300 E16686389E552       LEVEL: Error
> PID     : 6340                 TID  : 47260427610432PROC : db2sysc 0
> INSTANCE: db2inst1             NODE : 000          DB   : BCUDB
> APPHDL  : 0-1592               APPID: 170.118.125.11.37355.0905250932
> AUTHID  : LAADMIND
> EDUID   : 2516                 EDUNAME: db2agntp (BCUDB) 0
> FUNCTION: DB2 UDB, Common Trace API, sqlbFreeUpSlot, probe:122
> MESSAGE : ADM6019E  All pages in buffer pool "IBMSYSTEMBP16K" (ID
> "4098") are
>           in use.  Refer to the documentation for SQLCODE -1218.
>  Please help!!

As Lennart says - your primary bufferpool was not started; therefore "hidden 
bufferpool was started.

You can issue following command:

db2pd - bufferpools -db BCUDB

to list bufferpoools started.


For example:

db2pd -bufferpools -db sample

Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:03

Bufferpools:
First Active Pool ID      1
Max Bufferpool ID         1
Max Bufferpool ID on Disk 1
Num Bufferpools           5

Address            Id   Name               PageSz     PA-NumPgs  BA-NumPgs  ...
0x00002BA92A50EB00 1    IBMDEFAULTBP       8192       1000       0          ... 

0x00002BA92A50D9C0 4096 IBMSYSTEMBP4K      4096       16         0          ...
0x00002BA92A50DDE0 4097 IBMSYSTEMBP8K      8192       16         0          ...
0x00002BA92A50E240 4098 IBMSYSTEMBP16K     16384      16         0          ...
0x00002BA92A50E6A0 4099 IBMSYSTEMBP32K     32768      16         0          ...


Buffelpools with ID > 4095 are those "hidden" buffer pools started by DB2 just 
in case one of user defined bufferpools would not start as Lennart explains.


Jan M. Nelken
0
Jan
5/25/2009 3:32:54 PM
Reply: