Shulou(Shulou.com)05/31 Report--

This article shows you the solution to the error of ATS calling stored procedures with nickname. The content is concise and easy to understand, and it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Problem description:

The db2 version on the customer's side is V10.5, running on Linux. An oracle database is connected through the nickname of the federated database, the version of oracle is 11.2. The customer has developed a stored procedure to grab data from the oracle database. There is no problem with manually running the stored procedure (db2 "call * *"), but an error will be reported as long as it is called with ATS (the user who created the ATS TASK only has dbadm permission, not instance owner):

SQL30080N A communication error "NET8: 24315" occurred sending or receiving data from the remote database. SQLSTATE=08001 POINT:0001

Finally, if you coordinate with the customer to use instance ID to call this ATS task, there is no error.

Resolution process:

-- reproduce the error:

[root@dba_test:/] {20170630018:21:38} # useradd-g staff-d / home/shenft shenft

[root@dba_test:/] {20170630018:21:38} # cd / home

[root@dba_test:/home] {20170630018:21:47} # mkdir shenft

[root@dba_test:/home] {20170630018:22:36} # chown shenft:staff shenft

Su-nbuinst1

Db2 connect to FEDORADB

[nbuinst1@dba_test:/db/dbhome/nbuinst1] {20170630018:17:32} $db2 "grant connect,dbadm on database to user shenft"

DB20000I The SQL command completed successfully.

[root@dba_test:/home] {20170630018:22:50} # su-shenft

[shenft@dba_test:/home/shenft] {20170630 018:24:07} $vi .profile

".profile" [New file]

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.

Export PATH

# The following three lines have been added by IBM DB2 instance utilities.

If [- f / db/dbhome/nbuinst1/sqllib/db2profile]; then

. / db/dbhome/nbuinst1/sqllib/db2profile

Fi

There are no errors in manually calling stored procedures.

[shenft@dba_test:/home/shenft] {20170630018:28:00} $db2 connect to FEDORADB

Database Connection Information

Database server = DB2/AIX64 10.5.5

SQL authorization ID = SHENFT

Local database alias = FEDORADB

[shenft@dba_test:/home/shenft] {20170630018:28:06} $db2 "call KPI.INFO_TASK ()"

Return Status = 0

[shenft@dba_test:/home/shenft] {20170630018:32:20} $db2 "call SYSPROC.ADMIN_TASK_ADD ('Shenft test', NULL,' 35 INFO_TASK', NULL 38 18 *', 'KPI',' INFO_TASK', NULL, NULL, NULL)"

Return Status = 0

[shenft@dba_test:/home/shenft] {20170630018:38:01} $db2 "select * from kpi.tasklog"

TASKNAME BEGINTIME ENDTIME RTCODE FIRSTTIME SUCCNUM FAILNUM

CMDB.ORA_TEST 2017-06-30-18.38.00.837737 2017-06-30-18.38.01.016671-30080 2017-06-30-18.38.00.837737 01

1 record (s) selected.

[shenft@dba_test:/db/dbhome/nbuinst1/sqllib/db2dump] {20170630018:42:35} $grep-ip "ORA" db2diag.log

2017-06-30-18.38.01.001931 E2470A651 LEVEL: Error

PID: 17498252 TID: 17872 PROC: db2sysc 0

INSTANCE: nbuinst1 NODE: 000 DB: FEDORADB

APPHDL: 0-34655 APPID: * N0.nbuinst1.170630103800

AUTHID: SHENFT HOSTNAME: dba_test

EDUID: 17872 EDUNAME: db2agent (FEDORADB) 0

FUNCTION: DB2 UDB, net8 wrapper, Net8_Connection::error_report, probe:40

DATA # 1: String, 28 bytes

Oracle Error-Current User

DATA # 2: String with size, 6 bytes

SHENFT

DATA # 3: String, 22 bytes

Oracle Error Received

DATA # 4: String, 26 bytes

ORA-24315:.

2017-06-30-18.38.01.0148480 E3122A700 LEVEL: Error

PID: 17498252 TID: 17872 PROC: db2sysc 0

INSTANCE: nbuinst1 NODE: 000 DB: FEDORADB

APPHDL: 0-34655 APPID: * N0.nbuinst1.170630103800

AUTHID: SHENFT HOSTNAME: dba_test

EDUID: 17872 EDUNAME: db2agent (FEDORADB) 0

FUNCTION: DB2 UDB, net8 wrapper, Net8_Connection::error_report, probe:40

DATA # 1: String, 28 bytes

Oracle Error-Current User

DATA # 2: String with size, 6 bytes

SHENFT

DATA # 3: String, 22 bytes

Oracle Error Received

DATA # 4: String, 75 bytes

ORA-03113:.

.... ID: 46989330

.... ID: 274.: 14361

2017-06-30-18.38.01.014852 E3823A671 LEVEL: Error

PID: 17498252 TID: 17872 PROC: db2sysc 0

INSTANCE: nbuinst1 NODE: 000 DB: FEDORADB

APPHDL: 0-34655 APPID: * N0.nbuinst1.170630103800

AUTHID: SHENFT HOSTNAME: dba_test

EDUID: 17872 EDUNAME: db2agent (FEDORADB) 0

FUNCTION: DB2 UDB, net8 wrapper, Net8_Connection::error_report, probe:40

DATA # 1: String, 28 bytes

Oracle Error-Current User

DATA # 2: String with size, 6 bytes

SHENFT

DATA # 3: String, 22 bytes

Oracle Error Received

DATA # 4: String, 46 bytes

ORA-24327:.,.

After that, a variety of methods were tried, including adding shenft to instance ID's group and creating its own uesr mapping, nick_name, and store procedure for shenft.

I also found a phenomenon, that is, although the two users in the database each have their own user mapping, after changing the user password on the Oracle side, I only change the user mapping password of shenft, so there is no problem when using shenft to manually call the stored procedure, but when I call it with ATS, the password error is reported. Changed the password of instance ID's user mapping and reported a-30080 error.

From the point of view of the phenomenon, I think it is the bug of ATS, that is, although the ATS task is created by other users, the database still tries to run under the identity of instance ID, resulting in conflicts and errors.

Finally, I turned to IBM level 2 to confirm that this is a bug, and that other users in ATS still use instance ID to execute when they call ATS task.

The final solution given by IBM level 2, the problem is fix:

Db2 "alter wrapper net8 options (add DB2_FENCED'N')"

Db2 "alter server * options (add fold_ID 'Natures, PW 'N')"

Db2 "alter user mapping for * * server * * options (add use_trusted_context'Y')"

At the same time, run with DBA permissions on the Oracle side (for users who connect to the oracle database in user mapping):

ALTER USER GRANT CONNECT THROUGH

The above is the solution to errors in ATS calling stored procedures with nickname. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.