IBM Support

DB2LUW: Why db2look failed with sql10004c while connect to the same db succeed?

Technical Blog Post


Abstract

DB2LUW: Why db2look failed with sql10004c while connect to the same db succeed?

Body

1. db2look failed with following error:

$ db2look -d sample -e -o db2look.ddl
-- Generate statistics for all creators
-- Creating DDL for table(s)
-- Output is sent to file: sample.ddl

SQL10004C  An I/O error occurred while accessing the database directory. SQLSTATE=58031

2. error in db2diag.log
2017-04-11-02.48.11.615491-240 I38860E2300           LEVEL: Error (OS)
PID     : 28473                TID : 140707258795808 PROC : DB2LOOK
INSTANCE: db2inst1             NODE : 000
HOSTNAME: host01
FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100
CALLED  : OS, -, read                             OSERR: EACCES (13)
DATA #1 : String, 115 bytes
A total of 3 analysis will be performed :
 - User info
 - Target file info
 - I/O attempt

 Target file handle = 5
DATA #2 : String, 188 bytes
  Real user ID of current process       = 500
  Effective user ID of current process  = 500
  Real group ID of current process      = 200
  Effective group ID of current process = 200
DATA #3 : String, 41 bytes
current sbrk(0) value: 0x00000000054a8000
DATA #4 : String, 259 bytes
Target File Information :
  Size               = 2812
  Link               = No
  Reference path     = N/A
  Type               = 0x8000
  Permissions        = rw-rw-r--
  UID                = 500
  GID                = 200
  Last modified time = 1488280118
DATA #5 : String, 33 bytes
I/O attempt not implemented yet.


2017-04-11-02.48.11.616582-240 I41161E1306           LEVEL: Warning
PID     : 28473                TID : 140707258795808 PROC : DB2LOOK
INSTANCE: db2inst1             NODE : 000
HOSTNAME: host01
FUNCTION: DB2 UDB, base sys utilities, sqledogd, probe:220
MESSAGE : ZRC=0xFFFFD8EC=-10004
          SQL10004C  An I/O error occurred while accessing the database
          directory.

DATA #1 : String, 61 bytes
Perform directory file switch, failed reading sqldbdir header
 

3. Check the database directory information

$  db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /db2data1/database
 Database release level               = 10.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.7
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE


$ db2 LIST DB DIRECTORY on /db2data1/database
SQL10004C  An I/O error occurred while accessing the database directory.
SQLSTATE=58031 

 

Here, we can see, connect to database succeed, while list local db directory also failed with sql10004C.

 

4. To create a new database and see the result

 

$ db2 "create db testdb on /db2data1/database "
  DB20000I The CREATE DATABASE command completed successfully
 
$ db2 connect to testdb

   Database Connection Information

 Database server        = DB2/AIX64 10.5.7
 SQL authorization ID   = DB2INST1
 Local database alias   = TEST
 
$ db2 list db directory on /db2data1/database
SQL10004C An I/O error occured while accessing the database directory

We can create a new database and connect to it, but when try to list local db directory, it failed.

 

5. Check the file permission

tested to copy/touch a new file under /db2data1/database/fazheng/NODE0000/sqldbdir/sqldbdir, all failed with file permission issue, while checking from OS side, the permission all looks good

drwxrwxr-x 2 db2inst1 db2iadm1 4096 Feb 28 06:06 sqldbdir

 

The possible cause for the problem is the file system /db2data1/database is encrypted by third party tools, and the encryption policy is not correct.

Check message files on OS, can find Gardium process is running on the system.

After Gardium process is stopped, db2look+list local db directory work well.

 

To solve the problem, engage Gardium administrator/support to check the encryption policy, and make sure all the file permission/user/group settings are good.

 

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140568