db2 load因为实例用户对load文件无读取权限报错SQL2061N案例分享

问题现象
db2用户appuser(非实例用户)在做load操作的时候失败了，报错SQL2061N/SQL1652N。

数据文件是appuser生成的，appuser对该文件有读写权限，怎么还会失败呢？ 经过测试发现，是因为DB2实例用户对该文件无读取权限。

问题重现
db2用户是appuser，实例用户是db2inst1，数据库名sample

使用appuser生成一个数据文件t1.del，由于是umask027，其他用户无读取权限。 再做load时，报错了。

node01:/home/appuser$. /home/db2inst1/sqllib/db2profile
node01:/home/appuser$echo 1 > t1.del
node01:/home/appuser$cat t1.del
1
node01:/home/appuser$db2 connect to sample

   Database Connection Information

 Database server        = DB2/AIX64 10.5.5
 SQL authorization ID   = appuser
 Local database alias   = SAMPLE

node01:/home/appuser$db2 "create table t1(id int)"
DB20000I  The SQL command completed successfully.
node01:/home/appuser$db2 "load from t1.del of del insert into t1 nonrecoverable"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD 
parallelism.  Load parallelism of "7" will be used

SQL3109N  The utility is beginning to load data from file 
"/home/appuser/t1.del".

SQL2061N  An attempt to access media "/home/appuser/t1.del" is denied.

SQL3500W  The utility is beginning the "LOAD" phase at time "07/20/2020 
15:29:14.828116".

SQL1652N  File I/O error occurred.

SQL1652N  File I/O error occurred.

 

问题原因

A load is executed by the DB2 engine, meaning the DB2 instance owner, so the instance owner must have read+execute on the directory and read on the file to load on a Unix system.

node01:/home/appuser$ls -l t1.del
-rw-r-----    1 appuser appuser          2 Jul 20 15:27 t1.del

问题解决
修改数据文件权限，让db2实例用户能读取到它

node01:/home/appuser$chmod o+r t1.del
node01:/home/appuser$db2 "load from t1.del of del insert into t1 nonrecoverable"
SQL3501W  The table space(s) in which the table resides will not be placed in 
backup pending state since forward recovery is disabled for the database.

SQL3039W  The memory available to LOAD for DATA BUFFER prohibits full LOAD 
parallelism.  Load parallelism of "7" will be used

SQL3109N  The utility is beginning to load data from file 
"/home/appuser/t1.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "07/20/2020 
15:29:33.900691".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "1" rows were read from the 
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "1".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "07/20/2020 
15:29:34.278348".

Number of rows read         = 1
Number of rows skipped      = 0
Number of rows loaded       = 1
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 1

 

Why is DB2 load failing with SQL2036N?