What are the common errors that DB2 encounters when creating a stored procedure?

This article describes some of the errors that DB2 often encounters when creating a stored procedure. It provides a more detailed description of the errors, we will explain it in a single answer. The following is a detailed description of the main content of the article.

Q: In Unix operating systems, sometimes DB2 users use the following features:

DB2-td @-vf <script file created in the stored procedure>

DB2 fails to create a stored procedure, and the following error message is displayed when you view the db2diag. log file:

 
 

  
  
2002-10-05-13.47.40.075759 Instance:db2inst1 Node:000  
  
  
PID:1355876(db2agent (ABC) 0) TID:1 Appid:OC10103F.OD12.017302185202  
  
  
oper system services sqloChangeFileOwnership Probe:100 Database:ABC  
  
  
errno:  
  
  
0x0FFFFFFFFFFF7AF0 : 0x00000001 ....  
  
  
PID:1355876 TID:1 Node:000 Title: Path/Filename  
  
  
/home/db2inst1/sqllib/function/routine/sqlproc/ABC/DB2INST1/tmp/  
  
  
2002-10-05-13.47.40.166289 Instance:db2inst1 Node:000  
  
  
PID:1355876(db2agent (ABC) 0) TID:1 Appid:OC10103F.OD12.017302185202  
  
  
PSM - SQL Procedure psm_ctrl::psm_init_backend Probe:230 Database:ABC  
  
  
DIA8402C A disk error has occurred.  
  
  
ZRC=0x860F0004 
  
  
PID:1355876 TID:1 Node:000 Title: SQL procedure initialization:  
  
  
0x09000000031213BC : 696E 7374 616E 6365 206F 776E 6572 2064 instance owner d  
  
  
0x09000000031213CC : 6F65 7320 6E6F 7420 6265 6C6F 6E67 2074 oes not belong t  
  
  
0x09000000031213DC : 6F20 6665 6E63 6564 2075 7365 7227 7320 o fenced user's  
  
  
0x09000000031213EC : 7072 696D 6172 7920 6772 6F75 70 primary group  
 
 

A: DB2 stored procedures on Unix platforms have a constraint on the relationship between instance users and protected users, that is, the DB2 instance user must be a user in the master Group of the protected user. The above problem occurs because the instance user is not added to the master Group of the protected user on the system, resulting in insufficient access permissions, it is not because a disk error is reported in the log.

To solve this problem, you only need to add the instance user to the master group. However, you may find that the problem persists even if you have added the user to the specified group, at this time, You should also check whether the group to which the instance user is added is the master Group of the protected user corresponding to the instance user, that is, check whether the group to which the instance user is added is correct.

You can use the following method to find the protected users and the master Group of the protected users:

1. sqllib/adm path under the Home path of the user to be transferred to the instance

2. Execute the command ls-l. fenced and the output is similar to the following:

-R -- 1 db2fencj db2fgrp2 0 Jul 30 09:57. fenced

3. The output indicates that the user (db2fencj) to which the file belongs is a protected user and the group (db2fgrp2) is the master Group of the protected user.

Then, the user can verify whether the instance user is added to the correct group. If the result is correct, the problem can be solved.

The above content is an introduction to the errors encountered during the creation of the stored procedure in DB2. I hope you will gain some benefits.