One day, my development colleague told me that the database couldn’t be used and reported “table space access is not allowed”. So I logged into the server to check db2diag. Log
and found that the log stopped in the morning
[14:51:46]db2inst1@gdtmsdb:~/sqllib/db2dump> ls -l|grep db2diag
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 23195331 May 12 09:37 db2diag.log
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 24481792 April 7 2016 db2diag.log_2016-04-08-13.49.13
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 41023003 Jun 2 2017 db2diag.log_2017-06-02-10.27.09
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 53019005 April 8 2019 db2diag.log_2019-04-09-07.10.34
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 85065 April 9 2019 db2diag.log_2019-04-09-07.27.43
[14:51:46]-rw-rw-rw- 1 db2inst1 db2iadm1 63379 April 9 2019 db2diag.log_2019-04-09-07.33.49
2.Db2top – D tmsdb view the database table space
you can see that four file spaces are offline<Second, view the log information
[14:51:55]db2inst1@gdtmsdb:~/sqllib/db2dump> tail -n 1000 db2diag.log|more
14:52:07]2021-05-12-08.55.52.809736+480 E23154237E1192 LEVEL: Error (OS)
[14:52:07]PID : 19571 TID : 47162285090560 PROC : db2sysc 0
[14:52:07]INSTANCE: db2inst1 NODE : 000 DB : TMSDB
[14:52:07]APPHDL : 0-7 APPID: M000E092.CED2.210512005537
[14:52:07]AUTHID : TMSUSR HOSTNAME: gdtmsdb
[14:52:07]EDUID : 25 EDUNAME: db2agent (TMSDB) 0
[14:52:07]FUNCTION: DB2 UDB, oper system services, sqloopenp, probe:80
[14:52:07]MESSAGE : ZRC=0x840F0001=-2079391743=SQLO_ACCD "Access Denied"
[14:52:07] DIA8701C Access denied for resource "", operating system return code
[14:52:07] was "".
[14:52:07]CALLED : OS, -, open OSERR: EACCES (13)
[14:52:07]DATA #1 : Codepath, 8 bytes
[14:52:07]4:11:18:20:24:40
[14:52:07]DATA #2 : File name, 13 bytes
[14:52:07]/dev/raw/raw1
[14:52:07]DATA #3 : SQO Open File Options, PD_TYPE_SQO_FILE_OPEN_OPTIONS, 4 bytes
[14:52:07]SQLO_REVISE, SQLO_READWRITE, SQLO_SHAREWRITE, SQLO_USE_RAW_DEVICE, SQLO_WRITETHRU, SQLO_NO_FLUSH_ON_CLOSE
[14:52:07]DATA #4 : Hex integer, 4 bytes
[14:52:07]0x00000180
[14:52:07]DATA #5 : signed integer, 4 bytes
[14:52:07]0
[14:52:07]DATA #6 : Hex integer, 4 bytes
[14:52:07]0x00004000
[14:52:07]DATA #7 : String, 105 bytes
[14:52:07]Search for ossError*Analysis probe point after this log entry for further
[14:52:07]self-diagnosis of this problem.
[14:52:28]
[14:52:28]2021-05-12-08.55.52.811932+480 I23155430E3586 LEVEL: Error (OS)
[14:52:28]PID : 19571 TID : 47162285090560 PROC : db2sysc 0
[14:52:28]INSTANCE: db2inst1 NODE : 000 DB : TMSDB
[14:52:28]APPHDL : 0-7 APPID: M000E092.CED2.210512005537
[14:52:28]AUTHID : TMSUSR HOSTNAME: gdtmsdb
[14:52:28]EDUID : 25 EDUNAME: db2agent (TMSDB) 0
[14:52:28]FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100
[14:52:28]CALLED : OS, -, open OSERR: EACCES (13)
[14:52:28]DATA #1 : String, 111 bytes
[14:52:28]A total of 2 analysis will be performed :
[14:52:28] - User info
[14:52:28] - Path access permission
[14:52:28]
[14:52:28] Target file = /dev/raw/raw1
[14:52:28]DATA #2 : String, 184 bytes
[14:52:28] Real user ID of current process = 503
[14:52:28] Effective user ID of current process = 503
[14:52:28] Real group ID of current process = 304
[14:52:28] Effective group ID of current process = 304
[14:52:28]DATA #3 : String, 41 bytes
[14:52:28]current sbrk(0) value: 0x000000000073a000
[14:52:28]DATA #4 : String, 204 bytes
[14:52:28]Information of each subdirectory leading up to the first inaccessible one is shown in the format below :
[14:52:28] <UID>:<GID>:<permissions> (subdirectories)
[14:52:28]
[14:52:28] 0:0:755 (dev)
[14:52:28] 0:0:755 (raw)
[14:52:28] 0:6:660 (raw1)
[14:52:28]CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
[14:52:28] [0] 0x00002AE4BD0AF4AB /db2home/db2inst1/sqllib/lib64/libdb2osse.so.1 + 0x2244AB
[14:52:53] [1] 0x00002AE4BD0B0A33 ossLogSysRC + 0xB3
[14:52:53] [2] 0x00002AE4BD0A28D4 /db2home/db2inst1/sqllib/lib64/libdb2osse.so.1 + 0x2178D4
[14:52:53] [3] 0x00002AE4BD09FB9D ossErrorAnalysis + 0x2D
[14:52:53] [4] 0x00002AE4B7034E6C sqloSystemErrorHandler + 0x77C
[14:52:53] [5] 0x00002AE4B50036A6 sqloopenp + 0xA36
[14:52:53] [6] 0x00002AE4B211D6FD _Z20sqlbDMSDoContainerOpP12SQLB_POOL_CBPK9SQLP_LSN8iP16SQLB_POOLCONT_CB12SQLB_CONT_OPjS3_P12SQLB_GLOBALSb + 0x
[14:52:53]18D
[14:52:53] [7] 0x00002AE4B2136931 _Z16sqlbDMSStartPoolP12SQLB_GLOBALSP12SQLB_POOL_CBb + 0x221
[14:52:53] [8] 0x00002AE4B1FED9AB _Z14sqlbStartPoolsP12SQLB_GLOBALS + 0x61B
[14:52:53] [9] 0x00002AE4B209EA56 sqlbinit + 0x1D78
[14:52:53] [10] 0x00002AE4B4098D02 /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x3247D02
[14:52:53] [11] 0x00002AE4B409206D _ZN16sqeLocalDatabase12FirstConnectEP8SQLE_BWARcP8sqeAgentP8sqlo_gmtiiPb + 0x18FD
[14:52:53] [12] 0x00002AE4B4080FF8 _ZN8sqeDBMgr23StartUsingLocalDatabaseEP8SQLE_BWAP8sqeAgentRccP8sqlo_gmtPb + 0x1558
[14:52:53] [13] 0x00002AE4B403339F _ZN14sqeApplication13AppStartUsingEP8SQLE_BWAP8sqeAgentccP5sqlcaPc + 0x43F
[14:52:53] [14] 0x00002AE4B402A19C _ZN14sqeApplication13AppLocalStartEP14db2UCinterface + 0x57C
[14:52:53] [15] 0x00002AE4B4252310 _Z11sqlelostWrpP14db2UCinterface + 0x40
[14:52:53] [16] 0x00002AE4B4251237 _Z14sqleUCengnInitP14db2UCinterfacet + 0x6F7
[14:52:53] [17] 0x00002AE4B424FB08 sqleUCagentConnect + 0x4A8
[14:52:53] [18] 0x00002AE4B4363B36 _Z18sqljsConnectAttachP13sqljsDrdaAsCbP14db2UCinterface + 0xB6
[14:52:53] [19] 0x00002AE4B432804E _Z16sqljs_ddm_accsecP14db2UCinterfaceP13sqljDDMObject + 0x3CE
[14:52:53] [20] 0x00002AE4B431B628 _Z17sqljsParseConnectP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinterface + 0x58
[14:52:53] [21] 0x00002AE4B431BE6B _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb + 0x36B
[14:52:53] [22] 0x00002AE4B4316049 /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x34C5049
[14:52:53] [23] 0x00002AE4B43144DC /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x34C34DC
[14:52:53] [24] 0x00002AE4B4311519 /db2home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x34C0519
[14:52:53] [25] 0x00002AE4B431110B _Z17sqljsDrdaAsDriverP18SQLCC_INITSTRUCT_T + 0xEB
[14:52:53] [26] 0x00002AE4B400CC77 _ZN8sqeAgent6RunEDUEv + 0xAD7
[14:52:53] [27] 0x00002AE4B58821F7 _ZN9sqzEDUObj9EDUDriverEv + 0xF7
[14:52:53] [28] 0x00002AE4B5017C83 sqloEDUEntry + 0x303
[14:52:53] [29] 0x00002AE4B0C3B806 /lib64/libpthread.so.0 + 0x7806
[14:52:53] [30] 0x00002AE4BDE7F64D clone + 0x6D
You can see the key words [14:52:28] target file =/dev/raw/raw1. It seems that there is something wrong with this file system
check whether the device is bare or not with db2pd – D tmsdv – tab
[15:04:47]db2inst1@gdtmsdb:~> db2pd -d tmsdb -tab
[15:04:48]
[15:04:48]Database Member 0 -- Database TMSDB -- Active -- Up 0 days 04:25:13 -- Date 2021-05-12-13.20.50.628719
[15:04:48]
[15:04:48]Tablespace Configuration:
[15:04:48]Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
[15:04:48]0x00002AEB8D5540C0 0 SMS Regular 32768 32 Yes 192 1 1 Def 1 0 31 No SYSCATSPACE
[15:04:48]0x00002AEB8D561260 1 SMS SysTmp 32768 32 Yes 192 1 1 On 1 0 31 No TEMPSPACE1
[15:04:48]0x00002AEB8D56E400 2 DMS Large 32768 32 No 32 2 2 Def 1 0 31 Yes EOSWFCTX
[15:04:48]0x00002AEB8D57B5A0 3 DMS Large 32768 4 Yes 24 1 1 Def 1 0 3 Yes SYSTOOLSPACE
[15:04:48]0x00002AEB8D588740 4 DMS UsrTmp 32768 32 No 32 2 2 Def 1 0 31 No TEMP32K
[15:04:48]0x00002AEB8D5958E0 5 DMS Large 32768 32 No 32 2 2 Def 1 0 31 Yes TMSDBUSR
[15:04:48]0x00002AEB8D5A2A80 6 DMS Large 32768 32 No 32 3 3 Def 1 0 31 Yes TMSDBIDX
[15:04:48]
[15:04:48]Tablespace Statistics:
[15:04:48]Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
[15:04:48]0x00002AEB8D5540C0 0 5143 5143 5143 0 0 - - 0x00000000 0 0 No n/a
[15:04:48]0x00002AEB8D561260 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
[15:04:48]0x00002AEB8D56E400 2 163840 163808 0 0 0 0 0 0x00004000 0 0 No n/a
[15:04:48]0x00002AEB8D57B5A0 3 1024 1020 80 0 940 80 80 0x00000000 0 0 No n/a
[15:04:48]0x00002AEB8D588740 4 163840 163808 0 0 0 0 0 0x00004000 0 0 No n/a
[15:04:48]0x00002AEB8D5958E0 5 6553600 6553568 0 0 0 0 0 0x00004000 0 0 No n/a
[15:04:48]0x00002AEB8D5A2A80 6 3276800 3276768 0 0 0 0 0 0x00004000 0 0 No n/a
[15:04:48]
[15:04:48]Tablespace Autoresize Statistics:
[15:04:48]Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
[15:04:48]0x00002AEB8D5540C0 0 No No 0 0 No 0 None No
[15:04:48]0x00002AEB8D561260 1 No No 0 0 No 0 None No
[15:04:48]0x00002AEB8D56E400 2 No No 0 0 No 0 None No
[15:04:48]0x00002AEB8D57B5A0 3 No Yes -32768 -1 No None None No
[15:04:48]0x00002AEB8D588740 4 No No 0 0 No 0 None No
[15:04:48]0x00002AEB8D5958E0 5 No No 0 0 No 0 None No
[15:04:48]0x00002AEB8D5A2A80 6 No No 0 0 No 0 None No
[15:04:48]
[15:04:48]Tablespace Storage Statistics:
[15:04:48]Address Id DataTag Rebalance SGID SourceSGID
[15:04:48]0x00002AEB8D5540C0 0 0 No - -
[15:04:48]0x00002AEB8D561260 1 0 No - -
[15:04:48]0x00002AEB8D56E400 2 0 No - -
[15:04:48]0x00002AEB8D57B5A0 3 0 No - -
[15:04:48]0x00002AEB8D588740 4 0 No - -
[15:04:48]0x00002AEB8D5958E0 5 0 No - -
[15:04:48]0x00002AEB8D5A2A80 6 0 No - -
[15:04:48]
[15:04:48]Containers:
[15:04:48]Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
[15:04:48]0x00002AE4FDA42A40 0 0 Path 5143 5143 - 0 /db2data/tmsdb/db2inst1/NODE0000/SQL00001/SQLT0000.0
[15:04:48]0x00002AE4FDA3EC00 1 0 Path 1 1 - 0 /db2data/tmsdb/db2inst1/NODE0000/SQL00001/SQLT0001.0
[15:04:48]0x00002AE4FDA32C40 2 0 Disk 163840 163808 - 0 /dev/raw/raw1
[15:04:48]0x00002AE4FDA36C60 3 0 File 1024 1020 - 0 /db2data/tmsdb/db2inst1/NODE0000/SQL00001/SYSTOOLSPACE
[15:04:48]0x00002AE4FDA37C60 4 0 Disk 163840 163808 - 0 /dev/raw/raw2
[15:04:48]0x00002AE4FDA38C60 5 0 Disk 6553600 6553568 - 0 /dev/raw/raw3
[15:04:48]0x00002AE4FDA39C60 6 0 Disk 3276800 3276768 - 0 /dev/raw/raw4
You can see that four bare devices are used. Considering that the restart permission of bare devices will change, check the startup script of this machine
[15:13:00]gdtmsdb:/myscript # cat startServer.sh
[15:13:00]chown db2inst1:db2iadm1 /dev/raw/raw1
[15:13:00]chown db2inst1:db2iadm1 /dev/raw/raw2
[15:13:00]chown db2inst1:db2iadm1 /dev/raw/raw3
[15:13:00]chown db2inst1:db2iadm1 /dev/raw/raw4
[15:13:00]
[15:13:00]su - db2inst1 -c "db2start"
[15:13:00]su - db2inst1 -c "db2 activate db tmsdb"
Power on needs to be re empowered. Check the permissions under/dev/raw, and it’s not db2inst1. Execute the self startup script written in advance, and the problem is solved.
Read More:
- Insufficient table space ORA-00604 unable to extend table SYS.AUD by 8192
- Oracle quick replacement undo table space method
- An error occurred when opensips started the service“ ERROR:core :db_ check_ table_ Version: invalid version 7 for Table dialog found “
- Error 1146 (42s02): table ‘database name. Table name’ doesn’t exist ‘
- DB: the solution of table already exist when migrating
- DB2 table annotation pull
- U disk installation Linux system could not boot, / dev / root does not exist and the identified hard disk space is not available
- DB2 encountered the problem of sqlcode = 911 lock table when updating record update
- Django. DB. Utils. Programmingerror: (1146, “table * doesn’t exist”) causes and Solutions
- DB2 create table error – 104 42601 illegal symbol encountered in SQL statement
- openssl TXT_DB error number 2 failed to update database
- ERROR 1148 (42000): The used command is not allowed with this MySQL version
- hive is not allowed to impersonate anonymous
- [Error][IntelliJ IDEA] Element XXX is not allowed here
- Array initializer is not allowed here
- Function definition is not allowed here
- 2021 / 06 / 28 error – because the script PowerShell is not allowed to run on this system
- A case diagnosis and solution of DB2 error code 1639 and SQL state 08001 is described in detail
- Solve the problem that the delete request is not available under SpringBoot. There was an unexpected error (type=Method Not Allowed, status=405).
- mysql workbench Error Code: 1046. No database selected Select the default DB to be used