create tablespace problem in db2 HADR environment

Question

We have Db2 10.5.0.7 on centos 6.9 and TSAMP 3.2 as our high availability solution, when we create a tablespace in primary database we encounter the following errors in the standby:

2019-08-31-08.47.32.164952+270 I87056E2779 LEVEL: Error (OS) PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 Common, OSSe, ossGetDiskInfo, probe:130 MESSAGE : ECF=0x90000001=-1879048191=ECF_ACCESS_DENIED Access denied CALLED : OS, -, fopen OSERR: EACCES (13) DATA #1 : String, 12 bytes /proc/mounts DATA #2 : String, 25 bytes /dbdata1/samdbTsContainer DATA #3 : unsigned integer, 8 bytes

2019-08-31-08.47.32.185625+270 E89836E494 LEVEL: Error PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 UDB, high avail services, sqlhaGetLocalDiskInfo, probe:9433 MESSAGE : ECF=0x90000001=-1879048191=ECF_ACCESS_DENIED Access denied

2019-08-31-08.47.32.186258+270 E90331E484 LEVEL: Error PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 UDB, high avail services, sqlhaCreateMount, probe:9746 RETCODE : ZRC=0x827300AA=-2106392406=HA_ZRC_FAILED "SQLHA API call error"

2019-08-31-08.47.32.186910+270 I90816E658 LEVEL: Error PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbDMSAddContainerRequest, probe:812 MESSAGE : ZRC=0x827300AA=-2106392406=HA_ZRC_FAILED "SQLHA API call error" DATA : String, 36 bytes Cluster add mount operation failed: DATA #2 : String, 37 bytes /dbdata1/samdbTsContainer/TSPKGCACH.1 DATA #3 : String, 8 bytes SAMDB

2019-08-31-08.47.32.190537+270 E113909E951 LEVEL: Error PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 UDB, buffer pool services, sqlblog_reCreatePool, probe:3134 MESSAGE : ADM6106E Table space "TSPKGCACH" (ID = "49") could not be created during the rollforward operation. The most likely cause is that there is not enough space to create the containers associated with the table space. Connect to the database after the rollforward operation completes and use the SET TABLESPACE CONTAINERS command to assign containers to the table space. Then, issue another ROLLFORWARD DATABASE command to complete recovery of this table space.

2019-08-31-08.47.32.200949+270 E114861E592 LEVEL: Error PID : 4046 TID : 47669095425792 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : SAMDB APPHDL : 0-8 APPID: *LOCAL.DB2.190725231126 HOSTNAME: samdb-b EDUID : 155 EDUNAME: db2redom (SAMDB) 0 FUNCTION: DB2 UDB, buffer pool services, sqlbIncPoolState, probe:4628 MESSAGE : ADM12512W Log replay on the HADR standby has stopped on table space "TSPKGCACH" (ID "49") because it has been put into "ROLLFORWARD PENDING" state.

There is free space available for the database and the specified path (/dbdata1/samdbTsContainer) exists on the server and we can create file manually on it . all settings are equivalent on the primary and standby. db2inst1 is the owner of /dbdata1/samdbTsContainer and permission is drwxr-xr-x, the result of su - db2inst1 “ulimit -Hf” is unlimited and ext3 is file system type and create tablespace statement is as follows:

CREATE LARGE TABLESPACE TSPKGCACH IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8 K MANAGED BY DATABASE USING (FILE '/dbdata1/samdbTsContainer/TSPKGCACH.1' 5120) ON DBPARTITIONNUM (0) EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL BP8KPKGCACH OVERHEAD 10.5 TRANSFERRATE 0.14 DATA TAG NONE   NO FILE SYSTEM CACHING;

SELinux is disabled and the sector size is 512 bytes. The mount options are as follws:

/dev/sdf1 /dbdata1 ext3 rw,relatime,errors=continue,barrier=1,data=ordered 0 0

We can not recreate the problem sometimes this problem occur and we don't know the reason of it, but the problem remains until server reboot. When we restart the standby server problem solves but we need to drop the tablespace and recreate it, is there any idea for this problem?

What are the OS permissions on the /dbdata1/samdbTsContainer directory on the standby? What’s the result of su - db2inst1 “ulimit -Hf” on the standby? What’s the exact create tablespace statement? — Mark Barinstein
all settings are equivalent on the primary and standby. db2inst1 is the owner of /dbdata1/samdbTsContainer and permission is drwxr-xr-x, the result of su - db2inst1 “ulimit -Hf” is unlimited and ext3 is file system type and create tablespace statement is as follows: CREATE LARGE TABLESPACE TSPKGCACH IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 8 K MANAGED BY DATABASE USING (FILE '/dbdata1/samdbTsContainer/TSPKGCACH.1' 5120) ON DBPARTITIONNUM (0) EXTENTSIZE 64 PREFETCHSIZE 64 BUFFERPOOL BP8KPKGCACH OVERHEAD 10.5 TRANSFERRATE 0.14 DATA TAG NONE NO FILE SYSTEM CACHING; — kmhmd
You are using DMS FILE containers, which are deprecated since v10.1 (replaced by automatic storage) . Are there other interesting db2diag entries before the first ECF_ACCESS_DENIED (at DIAGLEVEL 4) , and are there any timestamp-matching entries in the operating system message log ? Please supply the mount options for the file system. Please add the new facts to the question (use EDIT to add new facts), instead of adding as comments. Please also add the CREATE TABLEPACE to the question by editing the question. — mao
@Sara Did you have the same problems with other tablespaces earlier? Do you use SELinux on the standby? What’s the sector size of disk(s) where this file system resides? — Mark Barinstein
There is not any message before ECF_ACCESS_DENIED in the db2 diag or syslog and we haven't this problem for already created tablespaces. I add other details to the question — kmhmd

kkuduk kkuduk · Accepted Answer · 2019-09-02T05:15:02

From the error it looks to me that problem is not with the file access itself but rather /proc/mounts, which Db2 uses to do the mapping between containers and filesystems (to know e.g. the FS type). Hence I suggest to test whether all:

cat /proc/mounts
cat /proc/self/mounts
mount

work OK run as Db2 instance owner ID (db2inst1). If not, this implies some odd OS issue that Db2 is a victim of and we would need more OS diagnostics (e.g strace from the cat /proc/mounts command) to understand it.

Edit:

To confirm this theory I've run a quick test with Db2 11.1. Note this must be TSA-controlled environment for Db2 to follow sqlhaCreateMount code path (because if this will be a separate mount, Db2 will add it to the TSA resource model)

On both primary and standby:

mkdir /db2data
chown db2v111:db2iadm /db2data

then on standby:

chmod o-rx /proc

(couldn't find a "smarter" way to hit EACCES on mount info).

When I will run on primary:

db2 "create tablespace test managed by database using (file '/db2data/testts' 100 M)"

it completes fine on primary but standby hits exactly the error you are seeing:

2019-06-21-03.00.37.087693+120 I1774E2661            LEVEL: Error (OS)
PID     : 10379                TID : 46912992438016  PROC : db2sysc 0
INSTANCE: db2v111              NODE : 000            DB   : SAMPLE
APPHDL  : 0-4492               APPID: *LOCAL.DB2.190621005919
HOSTNAME: rhel-hadrs.kkuduk.com
EDUID   : 61                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 Common, OSSe, ossGetDiskInfo, probe:130
MESSAGE : ECF=0x90000001=-1879048191=ECF_ACCESS_DENIED
          Access denied
CALLED  : OS, -, fopen                            OSERR: EACCES (13)
DATA #1 : String, 12 bytes
/proc/mounts
DATA #2 : String, 8 bytes
/db2data
DATA #3 : unsigned integer, 8 bytes
1
CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
  [0] 0x00002AAAB9CFD84B /home/db2v111/sqllib/lib64/libdb2osse.so.1 + 0x23F84B
  [1] 0x00002AAAB9CFED51 ossLogSysRC + 0x101
  [2] 0x00002AAAB9D19647 ossGetDiskInfo + 0xF07
  [3] 0x00002AAAAC52402C _Z21sqlhaGetLocalDiskInfoPKcjPcjS1_jS1_ + 0x26C
  [4] 0x00002AAAAC523C5F _Z16sqlhaGetDiskInfoPKcS0_jPcjS1_jS1_ + 0x29F
  [5] 0x00002AAAAC521CA0 _Z16sqlhaCreateMountPKcS0_m + 0x350
  [6] 0x00002AAAACDE8D5D _Z26sqlbDMSAddContainerRequestP12SQLB_POOL_CBP16SQLB_POOLCONT_CBP12SQLB_GLOBALSP14SQLB_pfParIoCbbm + 0x90D
  [7] 0x00002AAAACE14FF9 _Z29sqlbDoDMSAddContainerRequestsP12SQLB_POOL_CBP16SQLB_POOLCONT_CBjP26SQLB_AS_CONT_AND_PATH_INFOP12SQLB_GLOBALS + 0x2D9
  [8] 0x00002AAAACE0C20F _Z17sqlbDMSCreatePoolP12SQLB_POOL_CBiP16SQLB_POOLCONT_CBbP12SQLB_GLOBALS + 0x103F
  [9] 0x00002AAAACDB1EAC _Z13sqlbSetupPoolP12SQLB_GLOBALSP12SQLB_POOL_CBPKciiiihiP19SQLB_CONTAINER_SPECllblsib + 0xE4C

-> it is an issue with /proc/mounts access, not the target path itself, where i can write with no issues:

[db2v111@rhel-hadrs ~]$ echo "test" > /db2data/testfile

If that would be path access issue:

chmod o+rx /proc
chmod a-rw /db2data

then an error during the "CREATE TABLESPACE" redo on standby will be different:

2019-06-21-03.07.29.175486+120 I35023E592            LEVEL: Error
PID     : 10379                TID : 46912992438016  PROC : db2sysc 0
INSTANCE: db2v111              NODE : 000            DB   : SAMPLE
APPHDL  : 0-4492               APPID: *LOCAL.DB2.190621005919
HOSTNAME: rhel-hadrs.kkuduk.com
EDUID   : 61                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCreateAndLockParent, probe:918
MESSAGE : ZRC=0x8402001E=-2080243682=SQLB_CONTAINER_NOT_ACCESSIBLE
          "Container not accessible"
DATA #1 : <preformatted>
Failed at directory /db2data.

2019-06-21-03.07.29.175799+120 I35616E619            LEVEL: Severe
PID     : 10379                TID : 46912992438016  PROC : db2sysc 0
INSTANCE: db2v111              NODE : 000            DB   : SAMPLE
APPHDL  : 0-4492               APPID: *LOCAL.DB2.190621005919
HOSTNAME: rhel-hadrs.kkuduk.com
EDUID   : 61                   EDUNAME: db2redom (SAMPLE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCreateAndLockParent, probe:722
MESSAGE : ZRC=0x8402001E=-2080243682=SQLB_CONTAINER_NOT_ACCESSIBLE
          "Container not accessible"
DATA #1 : <preformatted>
Failed to create a portion of the path /db2data/testts2

(few more errors follow pointing directly to the permissions on /db2data)

This proves it is the /proc access issue and you need to debug it with your OS team. Perhaps /proc gets completely unmounted? In any case, the actual issue is db2sysc process hitting EACCES running fopen on /proc/mounts and you need debug it further with OS team.

Edit:

When it comes to the debugging and proving the error is returned by the OS, we would have to trace open() syscalls done by Db2. Strace can do that, but overhead is too high for a production system. If you can get SystemTap installed on the system, I suggest a script like this (this is a basic version):

probe nd_syscall.open.return 
{   
    if ( user_string( @entry( pointer_arg(1) ) ) =~ ".*mounts") 
    {   
        printf("exec: %s pid: %d uid: %d (euid: %d) gid: %d (egid: %d) run open(%s) rc: %d\n", execname(), pid(), uid(), euid(), gid(), egid(), user_string(@entry(pointer_arg(1)), "-"), returnval() )
    } 
}

it uses nd_syscall probe, so it will work even without kernel debuginfo package. You can run it like this:

$ stap open.stap 
exec: cat pid: 24159 uid: 0 (euid: 0) gid: 0 (egid: 0) run open(/proc/mounts) rc: 3
exec: cat pid: 24210 uid: 0 (euid: 0) gid: 0 (egid: 0) run open(/proc/mounts) rc: 3
exec: cat pid: 24669 uid: 1111 (euid: 1111) gid: 1001 (egid: 1001) run open(/proc/mounts) rc: 3
exec: cat pid: 24734 uid: 1111 (euid: 1111) gid: 1001 (egid: 1001) run open(/proc/mounts) rc: -13
exec: cat pid: 24891 uid: 1111 (euid: 1111) gid: 1001 (egid: 1001) run open(/proc/self/mounts) rc: -13
exec: ls pid: 24971 uid: 1111 (euid: 1111) gid: 1001 (egid: 1001) run open(/proc/mounts) rc: -13

-> at some point I've revoked access from /proc and open attempt failed with -13 (EACCES). You just need to enable it on the system when you see the error and see if something is logged when Db2 fails.

create tablespace problem in db2 HADR environment

1 Answers