Attempts to create nickname for a remote table from DB2 on IBM
iSeries (IBM i) resulted in SQL0901N error with Reason "Index
column name not found - search 1" and also Db2 dumps are
generated into FODC_AppErr folder.
The error happens because DB2 LUW does not support indexes using
expressions, as a result Federation Server cannot find a
counterpart object on DB2 for the index using expressions on
data source DB2 for IBM i.
The error returned is correct, but the problem fixed by this
APAR is that FODC_AppErr shouldn't be generated, but the command
should fail only with errors reported in the db2diag.log
db2diag.log returns:
2020-07-28-18.19.49.022713+120 E11030296E1685 LEVEL: Info
(Origin)
PID : 253547 TID : 139874673878784 PROC :
db2sysc 0
INSTANCE: db2inst2 NODE : 000 DB :
DWHDEV
APPHDL : 0-27342 APPID:
*LOCAL.db2inst2.200728161948
UOWID : 2 ACTID: 1
AUTHID : DB2INST2 HOSTNAME: xxxxxxxxx
EDUID : 18138 EDUNAME: db2agent (DWHDEV) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
MESSAGE : ZRC=0x80040001=-2147221503=SQLD_NOREC "NO MORE RECORDS
FOUND ON FETCH"
DIA8000C An unexpected end of file was reached "".
DATA #1 : String, 62 bytes
An unexpected error was detected during statement compilation.
DATA #2 : Boolean, 1 bytes
false
DATA #3 : Boolean, 1 bytes
DATA #4 : Boolean, 1 bytes
true
DATA #5 : Boolean, 1 bytes
DATA #6 : Hex integer, 4 bytes
0x00000000
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: 100 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLRL25C
sqlerrd : (1) 0x80040001 (2) 0x00000001 (3)
0x00000000
(4) 0x00000000 (5) 0x00000000 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
DATA #8 : Hex integer, 4 bytes
0x00000040
DATA #9 : String with size, 88 bytes
create or replace nickname "XXXXXXX"."YYYYYY" for
"ZZZZZZZ"."KKKKK"."WWWW"
DATA #10: String, 172 bytes
Compiler error stack for rc = -2147221503:
sqlnn_cmpl[300]
sqlnp_main[250]
sqlnp_parser[510]
sqlnp_smactn[180]
sqlnq_create_nickname_stmt[60]
sqlnq_create_nickname_two[80]
In the FODC_AppErr , the generated trap has the following call
stack
0x00007FA21A9C2996
_Z25ossDumpStackTraceInternalmR11OSSTrapFileiP7siginfoPvmm +
0x0356
0x00007FA21A9C25DB ossDumpStackTraceV98 + 0x002b
0x00007FA21A9BE7C7 _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm +
0x00d7
0x00007FA22422EA57 sqlo_trce + 0x03c7
0x00007FA2243AA98B sqloDumpDiagInfoHandler + 0x020b
0x00007FA22CE065F0 address: 0x00007FA22CE065F0 ; dladdress:
0x00007FA22CDF7000 ; offset in lib: 0x000000000000F5F0 ;
0x00007FA22CE03A61 pthread_kill + 0x0031
0x00007FA2243AA542 sqloDumpEDU + 0x0052
0x00007FA21D93CBB3 _Z15sqldDumpContextP9sqeBsuEduiiiiiPKcPvi +
0x13a3
0x00007FA224A1F5D5 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x0695
0x00007FA225F3F267 _Z13sqlzeDumpFFDCP8sqeAgentjP5sqlcai + 0x00a7
0x00007FA225F3F089
_Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x0199
0x00007FA224B3A6BD sqlrrSqlCode + 0x00dd
0x00007FA225029B0D _Z16sqlri_ddl_commonP8sqlrr_cb + 0x207d
0x00007FA22502C02A _Z14sqlri_djx_rddlP8sqlrr_cb + 0x004a
0x00007FA22505D333 _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm
+ 0x04c3
0x00007FA224A52C83 _Z23sqlrr_execute_immediateP8sqlrr_cbi +
0x0593
0x00007FA224A33E31
_Z14sqlrr_execimmdP14db2UCinterfaceP16db2UCprepareInfo + 0x0841
0x00007FA222AB30DF
_Z19sqljs_ddm_excsqlimmP14db2UCinterfaceP13sqljDDMObject +
0x03ef
0x00007FA222A7A82B
_Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2U
Cinterface + 0x011b
0x00007FA222A7B8DE
_Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb |
There are 2 options
A)To avoid only the dump creation, create/alter the wrapper as
FENCED:
alter wrapper wrapper_name options(set DB2_FENCED 'Y')
This will avoid the dump creation and command will fail
correctly because the existing of remote index using expression.
B)Allow the CREATE NICKNAME command complete successfully .
To create nickname for a remote table which contains indexes
using expressions, you have two options:
1. Create a view for the table, then create nickname for the
view instead of the table itself.
2. Remove the indexes using expressions from data source server
before creating nickname. |
