Troubleshooting
Problem
you can use RESTORE to transport a database schema from one database into a different existing database. However if the target database is located in a different instance from the source database, the schema may be not transported as expected although the restore command returns successfully.
Symptom
The restore command returns successfully, however if you check the target database, the tablespaces are created successfully but the objects in the schema to be transported are not created at all.
2014-09-25-08.45.54.972139-240 I84153A410 LEVEL: Info
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, database application extension for utilities, transport, probe:7738
MESSAGE : Transport:Begin Extract DDL phase
2014-09-25-08.45.56.278473-240 I84564A1266 LEVEL: Error
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrrbck, probe:399
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL10053
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL10053
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
2014-09-25-08.45.56.281408-240 E85831A696 LEVEL: Warning
PID : 29163578 TID : 1 PROC : db2fmp (16707)
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPID : *LOCAL.db2inst1.140925124555
HOSTNAME: host1
EDUID : 1 EDUNAME: db2fmp (16707)
FUNCTION: DB2 UDB, routine_infrastructure, sqlerFencedTruncateTokensUserSqlState, probe:100
DATA #1 : signed integer, 4 bytes
-443
DATA #2 : String with size, 5 bytes
38553
DATA #3 : String with size, 26 bytes
SYSPROC.DB2LK_GENERATE_DDL
DATA #4 : String with size, 18 bytes
DB2LK_GENERATE_DDL
DATA #5 : String with size, 43 bytes
SQL0031 Reason code or token: /db2lkfun.bnd
2014-09-25-08.45.56.285179-240 E86528A805 LEVEL: Error
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:30
MESSAGE : ADM14005E The following error occurred: "AppErr". First Occurrence
Data Capture (FODC) has been invoked in the following mode:
"Automatic". Diagnostic information has been recorded in the
directory named
"/home/db2inst1/sqllib/db2dump/FODC_AppErr_2014-09-25-08.45.56.283593_
27984050_16707_000/".
2014-09-25-08.45.56.286079-240 E87334A587 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:200
MESSAGE : ADM0001C A severe error has occurred. Examine the administration
notification log and contact IBM Support if necessary.
2014-09-25-08.45.56.286736-240 I87922A572 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:250
RETCODE : ZRC=0x8800000B=-2013265909=SQLZ_RC_INV_LENGTH_BUF
"Invalid output or indicator buffer specified - CLI0110E"
2014-09-25-08.45.56.292604-240 I95293A733 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_sibling, probe:140
MESSAGE : section stmt
DATA #1 : Hexdump, 40 bytes
0x0A000200D4D5F160 : 4341 4C4C 2053 5953 5052 4F43 2E44 4232 CALL SYSPROC.DB2
0x0A000200D4D5F170 : 4C4B 5F47 454E 4552 4154 455F 4444 4C28 LK_GENERATE_DDL(
0x0A000200D4D5F180 : 272D 6C27 2C20 3F29 '-l', ?)
At the same time, db2diag.log may have similar messages as follows:
2014-09-25-08.45.54.972139-240 I84153A410 LEVEL: Info
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, database application extension for utilities, transport, probe:7738
MESSAGE : Transport:Begin Extract DDL phase
2014-09-25-08.45.56.278473-240 I84564A1266 LEVEL: Error
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrrbck, probe:399
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL10053
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0
sqlerrmc:
sqlerrp : SQL10053
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
2014-09-25-08.45.56.281408-240 E85831A696 LEVEL: Warning
PID : 29163578 TID : 1 PROC : db2fmp (16707)
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPID : *LOCAL.db2inst1.140925124555
HOSTNAME: host1
EDUID : 1 EDUNAME: db2fmp (16707)
FUNCTION: DB2 UDB, routine_infrastructure, sqlerFencedTruncateTokensUserSqlState, probe:100
DATA #1 : signed integer, 4 bytes
-443
DATA #2 : String with size, 5 bytes
38553
DATA #3 : String with size, 26 bytes
SYSPROC.DB2LK_GENERATE_DDL
DATA #4 : String with size, 18 bytes
DB2LK_GENERATE_DDL
DATA #5 : String with size, 43 bytes
SQL0031 Reason code or token: /db2lkfun.bnd
2014-09-25-08.45.56.285179-240 E86528A805 LEVEL: Error
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:30
MESSAGE : ADM14005E The following error occurred: "AppErr". First Occurrence
Data Capture (FODC) has been invoked in the following mode:
"Automatic". Diagnostic information has been recorded in the
directory named
"/home/db2inst1/sqllib/db2dump/FODC_AppErr_2014-09-25-08.45.56.283593_
27984050_16707_000/".
2014-09-25-08.45.56.286079-240 E87334A587 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:200
MESSAGE : ADM0001C A severe error has occurred. Examine the administration
notification log and contact IBM Support if necessary.
2014-09-25-08.45.56.286736-240 I87922A572 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:250
RETCODE : ZRC=0x8800000B=-2013265909=SQLZ_RC_INV_LENGTH_BUF
"Invalid output or indicator buffer specified - CLI0110E"
2014-09-25-08.45.56.292604-240 I95293A733 LEVEL: Severe
PID : 27984050 TID : 16707 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-152 APPID: *LOCAL.db2inst1.140925124555
AUTHID : db2inst1 HOSTNAME: host1
EDUID : 16707 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_sibling, probe:140
MESSAGE : section stmt
DATA #1 : Hexdump, 40 bytes
0x0A000200D4D5F160 : 4341 4C4C 2053 5953 5052 4F43 2E44 4232 CALL SYSPROC.DB2
0x0A000200D4D5F170 : 4C4B 5F47 454E 4552 4154 455F 4444 4C28 LK_GENERATE_DDL(
0x0A000200D4D5F180 : 272D 6C27 2C20 3F29 '-l', ?)
Cause
When performing database schema transporting, DB2 internally restores the source database to a staging database first and extract the DDL from the staging database. The above db2diag.log basically indicate when extracting DDL from the staging database(SAMPLE) via db2look utility, there is an error "SQL0031 Reason code or token: /db2lkfun.bnd" when binding the db2look bind file - db2lkfun.bnd.
The issue is often caused by the instance owner who is restoring the database holding insufficient privileges to perform the bind. Since DB2 V9.7, the user who restores the database by default is not granted with SYSADM and DBA authority after the database is restored. If the user happens to be different from the instance user in the source database, this will cause an issue like this.
For more details on this behaviour change, please check the other technote at http://www.ibm.com/support/docview.wss?uid=swg21568865
The issue is often caused by the instance owner who is restoring the database holding insufficient privileges to perform the bind. Since DB2 V9.7, the user who restores the database by default is not granted with SYSADM and DBA authority after the database is restored. If the user happens to be different from the instance user in the source database, this will cause an issue like this.
For more details on this behaviour change, please check the other technote at http://www.ibm.com/support/docview.wss?uid=swg21568865
Resolving The Problem
Please run the command:
db2set DB2_RESTORE_GRANT_ADMIN_AUTHORITIES=YES
and recycle the db2 instance before running restore again.
[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Recovery - Restore","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.1;10.5;9.5;9.7;9.8","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Was this topic helpful?
Document Information
Modified date:
07 December 2022
UID
swg21685941