Bij het draaien onder 26.0.166 van Invantive Hub krijg ik de volgende foutmelding:
07:54:34.979 itgengpr064: Reset data container metadata. (Create table., XXDRU_TEMP_NEEDED_ACTIONS, InMemoryStorage)
**** 07:54:35.067 Warning itgendhb173: Context: D:\jobs\rgotools\sql\acme-download-exact-online.sql: begin
xxdru_maintain_table_partitions(true, false);
end;
07:54:35.101 Error itgenclr003: External error in Invantive Data Hub.
Enumeration already finished.
07:54:37.113 System.InvalidOperationException
ValidationException
at System.SZGenericArrayEnumerator`1.get_Current()
at Invantive.Data.SqlUtility.ClientSideFilterSparseArrays(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, DatabaseColumnDefinitionCollection fields, IEnumerable`1 rows, QueryObject queryObject)+MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Invantive.Data.Providers.DataCache.DataCacheProvider.FetchInternal(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, EntityFieldCollection entityFields, QueryObject queryObject, ParameterList parameters, Boolean...utionOptions executionOptions, SparseArrayPool sparseArrayPool, String statement, ResultSet& resultSet, Action`3 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification) in c:\smoke\ws-26.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 2626
at Invantive.Producer.Data.Hub.QueryEngine.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, SparseArrayPool sparseArrayPool, Action`3 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification) in c:\smoke\ws-26.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 1605
07:54:37.130 itgenqtl162: Show message.
Onder 24.0 werkte dit zonder fouten.
Het PSQL-block is:
create or replace procedure xxdru_maintain_table_partitions
( p_execute_missing boolean
, p_execute_superfluous boolean
)
is
l_error_cnt number;
l_error_txt varchar2;
begin
create or replace table xxdru_temp_needed_actions@inmemorystorage
as
select full_qualified_name
tbe_full_qualified_name
, partition_code
ptn_name
, data_container_alias
from xxdru_desired_tables@inmemorystorage
where false = true
;
create or replace table xxdru_temp_present_actions@inmemorystorage
as
select tbe.tbe_full_qualified_name
, ptn.ptn_name
, sdr.alias data_container_alias
from dc_table_partitions@DataCache tpn
join dc_tables@datacache tbe
on tbe.tbe_id = tpn.tbe_id
join dc_data_containers@datacache dcr
on dcr.dcr_id = tbe.dcr_id
join dc_partitions@datacache ptn
on ptn.ptn_id = tpn.ptn_id
--
-- Assume that ID matches the data container name, and that the
-- user is currently connected to all data containers used.
--
left
outer
join systemdatacontainers@datadictionary sdr
on sdr.id = dcr.dcr_name
;
--
-- Explode required replication set to list of tables and partitions.
--
for r_tbe in
( select *
from xxdru_desired_tables@inmemorystorage
)
loop
if r_tbe.partition_filter = 'ACTIVE'
then
insert into xxdru_temp_needed_actions@inmemorystorage
( tbe_full_qualified_name
, data_container_alias
, ptn_name
)
select r_tbe.full_qualified_name
, r_tbe.data_container_alias
, spn.code
from systempartitions@DataDictionary spn
where ( r_tbe.data_container_alias is null
or
spn.provider_data_container_alias = r_tbe.data_container_alias
)
and spn.is_active = true
and spn.code not in (select partition_code from xxdru_excluded_partitions@inmemorystorage)
order
by spn.code
;
elsif r_tbe.partition_filter = 'ACTIVESUB'
then
insert into xxdru_temp_needed_actions@inmemorystorage
( tbe_full_qualified_name
, data_container_alias
, ptn_name
)
select r_tbe.full_qualified_name
, r_tbe.data_container_alias
, min(spn.code)
from systempartitions@DataDictionary spn
where ( r_tbe.data_container_alias is null
or
spn.provider_data_container_alias = r_tbe.data_container_alias
)
and spn.is_active = true
and spn.code not in (select partition_code from xxdru_excluded_partitions@inmemorystorage)
group
by spn.subscription_holder_code
;
elsif r_tbe.partition_filter = 'SELECTED'
then
insert into xxdru_temp_needed_actions@inmemorystorage
( tbe_full_qualified_name
, data_container_alias
, ptn_name
)
select r_tbe.full_qualified_name
, r_tbe.data_container_alias
, spn.code
from systempartitions@DataDictionary spn
where ( r_tbe.data_container_alias is null
or
spn.provider_data_container_alias = r_tbe.data_container_alias
)
and spn.is_selected = true
and spn.code not in (select partition_code from xxdru_excluded_partitions@inmemorystorage)
order
by spn.code
;
elsif r_tbe.partition_filter = 'SPECIFIC'
then
insert into xxdru_temp_needed_actions@inmemorystorage
( tbe_full_qualified_name
, ptn_name
, data_container_alias
)
values
( r_tbe.full_qualified_name
, r_tbe.partition_code
, r_tbe.data_container_alias
);
else
raise_application_error(-20163, 'Unknown partition filter ' || r_tbe.partition_filter || '.');
end if;
end loop;
--
-- Translate into SQL actions.
--
create or replace table xxdru_temp_sql_foj@inmemorystorage
as
select coalesce(ist.ptn_name, soll.ptn_name) ptn_name
, coalesce(ist.tbe_full_qualified_name, soll.tbe_full_qualified_name) tbe_full_qualified_name
, coalesce(ist.data_container_alias, soll.data_container_alias) data_container_alias
, case
when ist.ptn_name is null
then 'MISSING'
else 'SUPERFLUOUS'
end
action
from xxdru_temp_present_actions@inmemorystorage ist
full
outer
join xxdru_temp_needed_actions@InMemoryStorage soll
on ist.tbe_full_qualified_name = soll.tbe_full_qualified_name
and ist.ptn_name = soll.ptn_name
and coalesce(ist.data_container_alias, '#') = coalesce(soll.data_container_alias, '#')
where ( ist.ptn_name is null
or
soll.ptn_name is null
)
;
--
-- Parallelizes new downloads.
--
create or replace table xxdru_sql_actions@inmemorystorage
as
select t.action
, t.tbe_full_qualified_name
, t.data_container_alias
, 'declare l_cnt number; begin use '
|| t.ptn_names
|| '; select /*+ ods(true, interval ''0 seconds'') */ count(*) into l_cnt from '
|| t.tbe_full_qualified_name
|| case
when t.data_container_alias is not null
then '@' || t.data_container_alias
else ''
end
|| '; end;'
sql_statement
from ( select t.action
, t.tbe_full_qualified_name
, t.data_container_alias
, listagg
( distinct
t.ptn_name
|| case
when t.data_container_alias is not null
then '@' || t.data_container_alias
else ''
end
)
ptn_names
from xxdru_temp_sql_foj@inmemorystorage t
where t.action = 'MISSING'
group
by t.action
, t.tbe_full_qualified_name
, t.data_container_alias
) t
where t.action is not null
union all
select t.action
, t.tbe_full_qualified_name
, t.data_container_alias
, 'alter persistent cache drop table '
|| t.tbe_full_qualified_name
|| case
when t.data_container_alias is not null
then '@' || t.data_container_alias
else ''
end
|| ' partition '''
|| t.ptn_name
|| ''''
sql_statement
from xxdru_temp_sql_foj@inmemorystorage t
where t.action = 'SUPERFLUOUS'
union all
--
-- Drop partitions deemed superfluous
--
select 'SUPERFLUOUS'
, ''
, epn.data_container_alias
, 'alter persistent cache drop partition '
|| partition_code
|| case
when epn.data_container_alias is not null
then '@' || epn.data_container_alias
else ''
end
from xxdru_excluded_partitions@inmemorystorage epn
join dc_partitions@datacache ptn
on ptn.ptn_name = epn.partition_code
;
--
-- Execute SQL actions.
--
l_error_cnt := 0;
l_error_txt := '';
--
for r in
( select sql_statement
from xxdru_sql_actions@inmemorystorage
where sql_statement is not null
and ( ( p_execute_missing = true and action = 'MISSING' )
or
( p_execute_superfluous = true and action = 'SUPERFLUOUS' )
)
)
loop
begin
dbms_trace.put_line('XXDRU: Add data to Data Replicator repository using ' || r.sql_statement);
execute immediate r.sql_statement;
exception
when others
then
l_error_cnt := l_error_cnt + 1;
if l_error_txt is null
then
l_error_txt := sqlerrm;
else
l_error_txt := l_error_txt || chr(13) || chr(10) || sqlerrm;
end if;
end;
end loop;
--
if l_error_cnt > 0
then
raise_application_error
( -20163
, to_char(l_error_cnt)
|| ' errors occurred during maintenance of table partitions: '
|| l_error_txt
);
end if;
end;
Het achterliggende SQL-statement:
create or replace table xxdru_temp_present_actions@inmemorystorage
as
select tbe.tbe_full_qualified_name
, ptn.ptn_name
, sdr.alias data_container_alias
from dc_table_partitions@DataCache tpn
join dc_tables@datacache tbe
on tbe.tbe_id = tpn.tbe_id
join dc_data_containers@datacache dcr
on dcr.dcr_id = tbe.dcr_id
join dc_partitions@datacache ptn
on ptn.ptn_id = tpn.ptn_id
--
-- Assume that ID matches the data container name, and that the
-- user is currently connected to all data containers used.
--
left
outer
join systemdatacontainers@datadictionary sdr
on sdr.id = dcr.dcr_name