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 |
