|
Problem IC69211
|
Status: Closed |
MIGRATION FROM V8 TO V95 FAILED DUE TO ORPHAN ROWS IN SYSROUTINEAUTH |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When orphan rows exist in SYSROUTINEAUTH, migration may fail
with the following db2diag.log entries.
2009-05-12-11.32.12.457885-240 I1804695A483 LEVEL: Warning
PID : 962776 TID : 3125 PROC : db2sysc 0
INSTANCE: inst1 NODE : 000 DB : GTM
APPHDL : 0-32 APPID: *LOCAL.inst1.090512152942
AUTHID : INST1
EDUID : 3125 EDUNAME: db2agent (GTM ) 0
FUNCTION: DB2 UDB, catalog migration, sqlrlm_catalog_migrate,
probe:2912
DATA #1 : <preformatted>
Begin migrating catalog table: SYSROUTINEAUTH
2009-05-12-11.32.13.086093-240 E1805179A618 LEVEL: Warning
(Origin)
PID : 962776 TID : 3125 PROC : db2sysc 0
INSTANCE: inst1 NODE : 000 DB : GTM
APPHDL : 0-32 APPID: *LOCAL.inst1.090512152942
AUTHID : INST1
EDUID : 3125 EDUNAME: db2agent (GTM ) 0
FUNCTION: DB2 UDB, catalog migration,
sqlrlm_8to9_sysroutineauth,
probe:6779
DATA #1 : String, 166 bytes
Warning: Execute privilege has been granted on all methods of
type GTM
.CHANGE_I to DCHG , but that type name does not exist. This
privilege is
being revoked.
-------Frame------ ------Function + Offset------
0x09000000004796BC pthread_kill + 0x88
0x090000000431788C sqloDumpEDU + 0x24
0x0900000003DFCC5C sqldDumpContext__FP8sqeAgentiN42PCcPvT2 +
0x10C
0x0900000003DFCA84
sqldDumpContext__FP8sqeAgentiN42PCcPvT2@glueA0A + 0x94
0x09000000041DA084 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x310
0x09000000037C442C sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x24
0x09000000037C4270 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
0x104
0x090000000431A760 sqlrrSqlCode + 0x338
0x0900000005BDB010 sqlrlmLogErr__FP8sqlrr_cbUiUlilPcT3 + 0x214
0x0900000005C0F844
sqlrlm_catalogFixup__FP8sqlrr_cbUslT3UlC17sqlrlm_catChangesPc8sq
lr_tidP8sqlr_tid + 0x4A78
0x0900000005C08484 sqlrlm_catalog_migrate__FP8sqeAgentsP5sqlca +
0x436C
0x0900000005DA0C6C sqlefmig__FPcP8SQLE_BWAP5sqlca + 0x2C
0x0900000005C02524 sqlemigr__FP8SQLE_BWAP14db2UCinterface +
0x1018
0x090000000434F9F8
sqlemigr__FP8SQLE_BWAP14db2UCinterface@glueAC3 + 0x70
0x0900000003C825D4
AppLocalStart__14sqeApplicationFP14db2UCinterface + 0x4AC
0x09000000039920E8 sqlelostWrp__FP14db2UCinterface + 0x34
0x0900000003992164 sqleUCengnInit__FP14db2UCinterfaceUs + 0x18
0x0900000003A69F48 sqleUCagentConnect + 0x284
0x0900000003A5FF60
sqljsConnectAttach__FP13sqljsDrdaAsCbP14db2UCinterface + 0xE8
0x0900000003A5F700
sqljs_ddm_accsec__FP14db2UCinterfaceP13sqljDDMObject + 0x314
0x0900000003A5F840
sqljsParseConnect__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinte
rface + 0x64
0x0900000003A5F900
sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterface + 0x24
0x090000000397D3F0 @63@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x224
0x0900000003A46EF8
@63@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0x98
0x0900000003A46C04
@63@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0xE0
0x0900000003A4698C sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0x13C
0x0900000003A23850 RunEDU__8sqeAgentFv + 0x11C
0x0900000003A235B0 EDUDriver__9sqzEDUObjFv + 0x94
0x0900000003A1E2EC sqloEDUEntry + 0x54C
</StackTrace>
One of the query could be used to determine whether there's
duplicate rows that may cause the database migration to hit APAR
IZ52606
with datatypes as (select NAME, SCHEMA from SYSIBM.SYSDATATYPES)
select RA.SCHEMA, RA.SPECIFICNAME, RA.TYPESCHEMA, RA.TYPENAME,
RA.GRANTEE
from SYSIBM.SYSROUTINEAUTH RA
where RA.ROUTINETYPE = 'M' and RA.TYPENAME IS NOT NULL and
RA.SPECIFICNAME IS NULL and
not exists (select NAME, SCHEMA from datatypes DT where
DT.NAME = RA.TYPENAME and DT.SCHEMA = RA.TYPESCHEMA)
with routines as (select ROUTINESCHEMA, SPECIFICNAME from
SYSIBM.SYSROUTINES)
select RA.SCHEMA, RA.SPECIFICNAME, RA.TYPESCHEMA, RA.TYPENAME,
RA.GRANTEE
from SYSIBM.SYSROUTINEAUTH RA
where RA.ROUTINETYPE = 'M' and RA.TYPENAME IS NOT NULL and
RA.SPECIFICNAME IS NOT NULL and
not exists (select R.ROUTINESCHEMA, R.SPECIFICNAME from
routines R where R.ROUTINESCHEMA = RA.SCHEMA and R.SPECIFICNAME
= RA.SPECIFICNAME) |
| Problem Summary: |
Problem Description:
MIGRATION FROM V8 TO V95 FAILED DUE TO ORPHAN ROWS IN
SYSROUTINEAUTH
Problem Summary:
When orphan rows exist in SYSROUTINEAUTH, migration may fail
with the following db2diag.log entries.
2009-05-12-11.32.12.457885-240 I1804695A483 LEVEL: Warning
PID : 962776 TID : 3125 PROC : db2sysc 0
INSTANCE: inst1 NODE : 000 DB : GTM
APPHDL : 0-32 APPID: *LOCAL.inst1.090512152942
AUTHID : INST1
EDUID : 3125 EDUNAME: db2agent (GTM ) 0
FUNCTION: DB2 UDB, catalog migration, sqlrlm_catalog_migrate,
probe:2912
DATA #1 : <preformatted>
Begin migrating catalog table: SYSROUTINEAUTH
2009-05-12-11.32.13.086093-240 E1805179A618 LEVEL: Warning
(Origin)
PID : 962776 TID : 3125 PROC : db2sysc 0
INSTANCE: inst1 NODE : 000 DB : GTM
APPHDL : 0-32 APPID: *LOCAL.inst1.090512152942
AUTHID : INST1
EDUID : 3125 EDUNAME: db2agent (GTM ) 0
FUNCTION: DB2 UDB, catalog migration,
sqlrlm_8to9_sysroutineauth,
probe:6779
DATA #1 : String, 166 bytes
Warning: Execute privilege has been granted on all methods of
type GTM
.CHANGE_I to DCHG , but that type name does not exist. This
privilege is
being revoked.
-------Frame------ ------Function + Offset------
0x09000000004796BC pthread_kill + 0x88
0x090000000431788C sqloDumpEDU + 0x24
0x0900000003DFCC5C sqldDumpContext__FP8sqeAgentiN42PCcPvT2 +
0x10C
0x0900000003DFCA84
sqldDumpContext__FP8sqeAgentiN42PCcPvT2@glueA0A + 0x94
0x09000000041DA084 sqlrr_dump_ffdc__FP8sqlrr_cbiT2 + 0x310
0x09000000037C442C sqlzeDumpFFDC__FP8sqeAgentUiP5sqlcai + 0x24
0x09000000037C4270 sqlzeSqlCode__FP8sqeAgentUiUlT2P5sqlcaiUsPc +
0x104
0x090000000431A760 sqlrrSqlCode + 0x338
0x0900000005BDB010 sqlrlmLogErr__FP8sqlrr_cbUiUlilPcT3 + 0x214
0x0900000005C0F844
sqlrlm_catalogFixup__FP8sqlrr_cbUslT3UlC17sqlrlm_catChangesPc8sq
lr_tidP8sqlr_tid + 0x4A78
0x0900000005C08484 sqlrlm_catalog_migrate__FP8sqeAgentsP5sqlca +
0x436C
0x0900000005DA0C6C sqlefmig__FPcP8SQLE_BWAP5sqlca + 0x2C
0x0900000005C02524 sqlemigr__FP8SQLE_BWAP14db2UCinterface +
0x1018
0x090000000434F9F8
sqlemigr__FP8SQLE_BWAP14db2UCinterface@glueAC3 + 0x70
0x0900000003C825D4
AppLocalStart__14sqeApplicationFP14db2UCinterface + 0x4AC
0x09000000039920E8 sqlelostWrp__FP14db2UCinterface + 0x34
0x0900000003992164 sqleUCengnInit__FP14db2UCinterfaceUs + 0x18
0x0900000003A69F48 sqleUCagentConnect + 0x284
0x0900000003A5FF60
sqljsConnectAttach__FP13sqljsDrdaAsCbP14db2UCinterface + 0xE8
0x0900000003A5F700
sqljs_ddm_accsec__FP14db2UCinterfaceP13sqljDDMObject + 0x314
0x0900000003A5F840
sqljsParseConnect__FP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinte
rface + 0x64
0x0900000003A5F900
sqljsParse__FP13sqljsDrdaAsCbP14db2UCinterface + 0x24
0x090000000397D3F0 @63@sqljsSqlam__FP14db2UCinterfaceP8sqeAgentb
+ 0x224
0x0900000003A46EF8
@63@sqljsDriveRequests__FP8sqeAgentP14db2UCconHandle + 0x98
0x0900000003A46C04
@63@sqljsDrdaAsInnerDriver__FP18SQLCC_INITSTRUCT_Tb + 0xE0
0x0900000003A4698C sqljsDrdaAsDriver__FP18SQLCC_INITSTRUCT_T +
0x13C
0x0900000003A23850 RunEDU__8sqeAgentFv + 0x11C
0x0900000003A235B0 EDUDriver__9sqzEDUObjFv + 0x94
0x0900000003A1E2EC sqloEDUEntry + 0x54C
</StackTrace>
One of the query could be used to determine whether there's
duplicate rows that may cause the database migration to hit APAR
IZ52606
with datatypes as (select NAME, SCHEMA from SYSIBM.SYSDATATYPES)
select RA.SCHEMA, RA.SPECIFICNAME, RA.TYPESCHEMA, RA.TYPENAME,
RA.GRANTEE
from SYSIBM.SYSROUTINEAUTH RA
where RA.ROUTINETYPE = 'M' and RA.TYPENAME IS NOT NULL and
RA.SPECIFICNAME IS NULL and
not exists (select NAME, SCHEMA from datatypes DT where
DT.NAME = RA.TYPENAME and DT.SCHEMA = RA.TYPESCHEMA)
with routines as (select ROUTINESCHEMA, SPECIFICNAME from
SYSIBM.SYSROUTINES)
select RA.SCHEMA, RA.SPECIFICNAME, RA.TYPESCHEMA, RA.TYPENAME,
RA.GRANTEE
from SYSIBM.SYSROUTINEAUTH RA
where RA.ROUTINETYPE = 'M' and RA.TYPENAME IS NOT NULL and
RA.SPECIFICNAME IS NOT NULL and
not exists (select R.ROUTINESCHEMA, R.SPECIFICNAME from
routines R where R.ROUTINESCHEMA = RA.SCHEMA and R.SPECIFICNAME
= RA.SPECIFICNAME) |
| Local Fix: |
n/a |
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem is first fixed in Version 9.7 Fixpack 4 |
| Workaround |
n/a |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.06.2010
29.04.2011
29.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
