DB2 - Problembeschreibung
Problem IT02352 | Status: Geschlossen |
DEPENDENT OBJECTS MAY NOT GET INVALIDATED WHEN A COLUMN OF A TEMPORAL TABLE IS ALTERED OR DROPPED, LEADING TO SQL0901N ERRORS | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 | |
Problembeschreibung: | |
When a temporal table, either system-period or application-period, is referenced in a view or a global variable, altering or dropping of a column of the temporal table doesn't invalidate the dependent objects, which may result in SQL0901N errors. There are two scenarios when you may experience this problem. Scenario 1. 1) A temporal table is referenced in a view or a global variable. 2) You're changing the data type of a temporal table column, or dropping a temporal table column. 3) You're accessing the view or the global variable once the temporal table has been altered. For example, create table emp (id int not null, name varchar(20), sys_start timestamp(12) not null generated always as row begin, sys_end timestamp(12) not null generated always as row end, trans_start timestamp(12) not null generated always as transaction start id, period system_time (sys_start, sys_end)); create table emp_hist like emp; alter table emp add versioning use history table emp_hist; create or replace view emp_view as (select * from emp for system_time as of current timestamp); alter table emp alter column name set data type varchar(30); select * from emp_view; The last statement will produced the following error: SQL0901N The SQL statement or command failed because of a database system error. (Reason "Describe: Invalid svar len".) SQLSTATE=58004 db2diag.log will contain a corresponding entry: 2014-06-10-11.23.42.497196+060 I206927E890 LEVEL: Severe PID : 6548 TID : 46913034381632 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 DB : TESTDB0 APPHDL : 0-27 APPID: *LOCAL.db2inst1.140610092325 AUTHID : DBUSER1 HOSTNAME: s57-520 EDUID : 45 EDUNAME: db2agent (TESTDB0) FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, probe:300 DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -901 sqlerrml: 26 sqlerrmc: Describe: Invalid svar len sqlerrp : SQLRA003 sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0xFFFFFDA8 (6) 0x00000000 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: A generated stack trace file will contain the following call stack: 0x00002AAAB60B7EB5 _Z25ossDumpStackTraceInternalmR11OSSTrapFileiP7siginfoPvmm + 0x0385 0x00002AAAB60B7ABC ossDumpStackTraceV98 + 0x002c 0x00002AAAB60B2BBD _ZN11OSSTrapFile6dumpExEmiP7siginfoPvm + 0x00fd 0x00002AAAB0CF7DC4 sqlo_trce + 0x0404 0x00002AAAB0D4DD4E sqloDumpDiagInfoHandler + 0x010e 0x00002AAAAABD5E00 address: 0x00002AAAAABD5E00 ; dladdress: 0x00002AAAAABC8000 ; offset in lib: 0x000000000000DE00 ; 0x00002AAAAABD30A5 pthread_kill + 0x0035 0x00002AAAB0D4E132 sqloDumpEDU + 0x0052 0x00002AAAAFDC0AE9 _Z15sqldDumpContextP9sqeBsuEduiiiiiPKcPvi + 0x0b39 0x00002AAAAEFABE46 _Z15sqlrr_dump_ffdcP8sqlrr_cbii + 0x0c96 0x00002AAAAF608A5C address: 0x00002AAAAF608A5C ; dladdress: 0x00002AAAAACE1000 ; offset in lib: 0x0000000004927A5C ; 0x00002AAAAF608838 _Z12sqlzeSqlCodeP8sqeAgentjmjP5sqlcaitP13__va_list_tag + 0x01c8 0x00002AAAAF00D221 sqlrrSqlCode + 0x00e1 0x00002AAAAF08DED3 _Z15sqlraFixupSqlDDP8sqlrr_cbPhP14dataDescriptorl + 0x05e3 0x00002AAAAF0979AD _Z14sqlra_fill_varP8sqlrr_cbP14sqlrr_cmpl_envP5sqlcahbP19sqlra_e xecutable_idP16sqlra_cached_var + 0x088d 0x00002AAAAF07FCF4 _Z17sqlra_compile_varP8sqlrr_cbP14sqlra_cmpl_envPhitiiiiiP14SQLP _LOCK_INFOP16sqlra_cached_varPi + 0x0d74 0x00002AAAAF09183A _Z14sqlra_find_varP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idj jPhjthP14sqlra_cmpl_env15sqlra_fill_modePiiS8_iiiS8_P14SQLP_LOCK _INFOPP16sqlra_cached_varS8_b + 0x067a 0x00002AAAAF0E485E _Z13sqlra_get_varP8sqlrr_cbiibPbS1_ + 0x07ae 0x00002AAAAEFB7752 _Z13sqlrr_prepareP14db2UCinterfaceP16db2UCprepareInfo + 0x0192 ... Scenario 2. 1) A temporal table is referenced in a view or a global variable. 2) You're changing the data type of a temporal table column, or dropping a temporal table column, *and* you're adding a new column in the same ALTER TABLE statement. For example (using the temporal table DDL from the Scenario 1), alter table emp alter column name set data type varchar(30) add column salary dec(10,2); The ALTER TABLE statement will fail with the following error: SQL0901N The SQL statement or command failed because of a database system error. (Reason "column number out of range".) SQLSTATE=58004 db2diag.log will contain a corresponding entry and a call stack: 2014-06-10-11.36.42.168013+060 I340040E4651 LEVEL: Severe PID : 6548 TID : 46913034381632 PROC : db2sysc INSTANCE: db2inst1 NODE : 000 DB : TESTDB0 APPHDL : 0-27 APPID: *LOCAL.db2inst1.140610092325 AUTHID : DBUSER1 HOSTNAME: s57-520 EDUID : 45 EDUNAME: db2agent (TESTDB0) FUNCTION: DB2 UDB, SW- query graph, sqlnq_ftb::num2fcs, probe:100 MESSAGE : column number out of range DATA #1 : signed integer, 4 bytes 5 DATA #2 : String with size, 0 bytes Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: Address is NULL CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol) [0] 0x00002AAAB1CB6B50 _ZN9sqlnq_ftb7num2fcsEiPP9sqlnq_fcsiP3loc + 0x290 [1] 0x00002AAAAE9427AA _ZN9sqlnq_qun19change_to_lower_boxEP9sqlnq_qtbP12sqlnq_colmap15s qlnq_typeCheck + 0x1AA [2] 0x00002AAAB0A7B837 /home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x5D9A837 [3] 0x00002AAAB0A7C7CC _Z24sqlnq_temporal_query_semPP8stknode_i10actiontypePhP3loc + 0x3AC [4] 0x00002AAAB1CA5E7D _Z12sqlnp_smactnP8sqlnp_cbi + 0xA9D [5] 0x00002AAAAE70FDB1 _Z12sqlnp_parserP8sqlnp_cb + 0x791 [6] 0x00002AAAAE71783E _Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x22E [7] 0x00002AAAAE95F23D _Z21sqlnq_handle_new_viewP9sqlnq_qtb + 0x29D [8] 0x00002AAAAE95D5B2 _Z26sqlnq_check_referenced_qtbPP9sqlnq_qtbP9sqlnq_qunP3loc21sqln q_hierarchy_usage + 0x2D2 [9] 0x00002AAAAE95D1B2 _Z26sqlnq_check_referenced_qtbP9sqlnq_qunP3loc21sqlnq_hierarchy_ usage + 0x42 [10] 0x00002AAAAE95BD5A _Z27sqlnq_handle_from_table_refP20sqlnq_multipart_namePhiPP9sqln q_qunP9sqlnq_qtbP3locP9sqlnq_opr21sqlnq_hierarchy_usagePP8stkno + 0xFA [11] 0x00002AAAB0658B03 _Z16sqlnn_regen_viewPhsS_sS_sS_sbP17sqlrl_table_parmsPbS2_S2_b + 0x1943 [12] 0x00002AAAB152411A _Z16sqlrl_regen_viewP8sqlrr_cbPhsS1_sS1_sS1_ssbP17sqlrl_table_pa rmsPbS4_b + 0x59A [13] 0x00002AAAB149F6C7 /home/db2inst1/sqllib/lib64/libdb2e.so.1 + 0x67BE6C7 [14] 0x00002AAAAF2AD892 _Z8sqlrlaltP8sqlrr_cbPhsS1_sS1_sS1_sP8sqlrg_pdP17sqlrl_table_par msilP18sqlrg_datapartinfoPbbP9sqlnq_ftb + 0x7D62 [15] 0x00002AAAB14AB63E _Z28sqlrlPropagateAlterToHistoryP8sqlrr_cbP17sqlrl_table_parmsP8 sqlrg_pdS4_ + 0x85E [16] 0x00002AAAAF2AABEA _Z8sqlrlaltP8sqlrr_cbPhsS1_sS1_sS1_sP8sqlrg_pdP17sqlrl_table_par msilP18sqlrg_datapartinfoPbbP9sqlnq_ftb + 0x50BA [17] 0x00002AAAAE81B973 _Z21sqlnq_alter_table_endP9sqlnq_qtb + 0x1A43 [18] 0x00002AAAAE819E01 _Z22sqlnq_alter_table_stmtPP8stknode_i10actiontypePhP3loc + 0x71 [19] 0x00002AAAB1CA5E7D _Z12sqlnp_smactnP8sqlnp_cbi + 0xA9D [20] 0x00002AAAAE70FDB1 _Z12sqlnp_parserP8sqlnp_cb + 0x791 [21] 0x00002AAAAE71783E _Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x22E | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 10.5 Fix Pack 4 * **************************************************************** | |
Local-Fix: | |
Drop all dependent objects of a temporal table being altered, perform table alteration, recreate the dropped objects. | |
verfügbare FixPacks: | |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in DB2 Version 10.5 Fix Pack 4 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 09.06.2014 08.09.2014 08.09.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.5.0.4 |