IBM Support

SQL1101N error returned for a procedue accessing a nickname executed from admin task

Troubleshooting


Problem

SQL1101N error  is returned when invoking a store procedure accessing a nickname from Administrative Task Scheduler:
2019-01-18-16.23.00.348416-480 E2703E700 LEVEL: Error
PID : 19239 TID : 139779762833152 PROC : db2acd 0
INSTANCE: db2inst2 NODE : 000 DB : D01
APPID : *LOCAL.db2inst2.190119002300
HOSTNAME: TURING11
FUNCTION: DB2 UDB, Administrative Task Scheduler, AtsTask::executeTask, probe:600
MESSAGE : ZRC=0xFFFFFBB3=-1101
SQL1101N Remote database "" on node "" could not be accessed with
the specified authorization id and password.

DATA #1 : <preformatted>
[IBM][CLI Driver][DB2/LINUXX8664] SQL1101N Remote database "D03" on node "" could not be accessed with the specified authorization id and password. SQLSTATE=08004
 

Symptom

We can reproduce the same error with the following steps and settings:
db2inst2@TURING11:~/work> cat setup.sh

b2set DB2COMM=TCPIP
db2set DB2_ATS_ENABLE=YES
db2 update dbm cfg using FEDERATED YES
db2 update dbm cfg using svcename 55000
db2stop force
db2start
db2 terminate

db2 create db d01
db2 create db d02
db2 activate db d01
db2 activate db d02
db2 connect to d02
db2 "create table test_table(id int)"
db2 "insert into test_table values (1)"
db2 catalog tcpip node n_test remote 127.0.0.1 server 55000
db2 catalog db d02 as d03 at node n_test
db2 terminate

db2 connect to d01
db2 "CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP"
db2 "create wrapper drda"
db2 "create server serv type DB2/UDB version '10.5' wrapper DRDA authorization \"db2inst2\" password \"passw0rd\" options (dbname 'D03')"
db2 "create user mapping for tom SERVER serv OPTIONS (REMOTE_AUTHID 'db2inst2', REMOTE_PASSWORD 'passw0rd')"
db2 "create user mapping for db2inst2 SERVER serv OPTIONS (REMOTE_AUTHID 'db2inst2', REMOTE_PASSWORD 'passw0rd')"

db2 connect to d01 user tom using passw0rd
db2 "create table log_table (id int, name varchar(1024), tims timestamp)"
db2 "CREATE NICKNAME nick1 FOR serv.db2inst2.test_table"
$ cat proc1.ddl
create or replace procedure tom.proc1()
language sql
modifies sql data
begin
insert into log_table select id, 'current user:'||current user||': Data from NICKNAME', current timestamp from nick1;
commit;
end@

$ db2 -td@ -f proc1.ddl

## no error when call the procedure from CLP, no matter db2inst2 or tom is used

db2inst2@TURING11:~/work> db2 connect to d01

   Database Connection Information

Database server        = DB2/LINUXX8664 10.5.10
SQL authorization ID   = DB2INST2
Local database alias   = D01

db2inst2@TURING11:~/work> db2 "call  tom.proc1()"

  Return Status = 0

db2inst2@TURING11:~/work> db2 connect to d01 user tom using passw0rd

   Database Connection Information

Database server        = DB2/LINUXX8664 10.5.10
SQL authorization ID   = TOM
Local database alias   = D01


db2inst2@TURING11:~/work> db2 "call  tom.proc1()"

  Return Status = 0

##  but error when call from admin task

db2 "call SYSPROC.ADMIN_TASK_ADD ('MYTASK1', NULL, NULL, NULL, '* * * * *', 'TOM', 'PROC1', NULL, NULL, NULL )"

  Return Status = 0


db2inst2@TURING11:~/work> db2 "SELECT BEGIN_TIME, taskid, sqlcode, status, invocation FROM SYSTOOLS.ADMIN_TASK_STATUS order by begin_time desc "

BEGIN_TIME                 TASKID      SQLCODE     STATUS     INVOCATION
-------------------------- ----------- ----------- ---------- -----------
2019-01-18-16.26.00.575964           1       -1101 COMPLETE             9
2019-01-18-16.25.00.467099           1       -1101 COMPLETE             8
2019-01-18-16.24.00.364603           1       -1101 COMPLETE             7
2019-01-18-16.23.00.221857           1       -1101 COMPLETE             6
2019-01-18-16.22.00.063245           1       -1101 COMPLETE             5
2019-01-18-16.21.00.939142           1       -1101 COMPLETE             4
2019-01-18-16.20.00.762875           1       -1101 COMPLETE             3
2019-01-18-16.19.00.563356           1       -1101 COMPLETE             2
2019-01-18-16.18.00.426097           1       -1101 COMPLETE             1

  9 record(s) selected.

2019-01-18-16.23.00.347078-480 I2119E583             LEVEL: Error
PID     : 19207                TID : 140310776637184 PROC : db2sysc 0
INSTANCE: db2inst2             NODE : 000            DB   : D01
APPHDL  : 0-115                APPID: *LOCAL.db2inst2.190119002300
AUTHID  : TOM                  HOSTNAME: TURING11
EDUID   : 199                  EDUNAME: db2agent (D01) 0
FUNCTION: DB2 UDB, Query Gateway, sqlqg_fedstp_hook, probe:40
MESSAGE : Unexpected error returned from outer RC=
DATA #1 : Hexdump, 4 bytes
0x00007F9CA5FFA510 : 1200 2681                                  ..&.

2019-01-18-16.23.00.348416-480 E2703E700             LEVEL: Error
PID     : 19239                TID : 139779762833152 PROC : db2acd 0
INSTANCE: db2inst2             NODE : 000            DB   : D01
APPID   : *LOCAL.db2inst2.190119002300
HOSTNAME: TURING11
FUNCTION: DB2 UDB, Administrative Task Scheduler, AtsTask::executeTask, probe:600
MESSAGE : ZRC=0xFFFFFBB3=-1101
          SQL1101N  Remote database "" on node "" could not be accessed with
          the specified authorization id and password.

DATA #1 : <preformatted>
[IBM][CLI Driver][DB2/LINUXX8664] SQL1101N  Remote database "D03" on node "" could not be accessed with the specified authorization id and password.  SQLSTATE=08004

Document Location

Worldwide

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Log InLog in to view more of this document

This document has the abstract of a technical article that is available to authorized users once you have logged on. Please use Log in button above to access the full document. After log in, if you do not have the right authorization for this document, there will be instructions on what to do next.

Document Information

Modified date:
01 May 2025

UID

ibm10843470