invantive documentation
Invantive SupportforenAnmelden / Eintragen

AFAS documenten: itgenclr007 OutOfMemoryException

Antworten erfolgen im Forum.

Zur Quelle

Question

Een klant van mij heeft de onderstaande foutmelding op 1 tabel. Ze proberen documenten te downloaden met dit SQL-script:

-- In case any document to which access seems impossible: ignore it.
--
-- Raises 404 Not Found when not accessible:
--
-- The requested OData URL could not be found on the AFAS Online server.
-- Please contact Invantive Support or use another table.
-- (https://93926.rest.afas.online/profitrestservices/subjectconnector/9520/90b21cfe240a4213805a164886ba6927?take=50000).
--
-- The 404 can not be answered from document cache on Subject connector, so it will consume
-- approximately 125 ms for each row on every run.
--
set ignore-http-404-errors@afas true
2026-10-13 04:59:43.017 Information itgensql264: Invantive UniversalSQL statement started.
2026-10-13 04:59:43.035 Information itgendhb017: 1 Rows Affected (19 ms).
2026-10-13 04:59:43.035 Information itgendhb050: Creation date: 13-10-2026 04:59:43 (UTC)
2026-10-13 04:59:43.035 Information itgendhb053: Fill CPU time (ms):
2026-10-13 04:59:43.035 Information itgendhb056: Fingerprint:
2026-10-13 04:59:43.035 Information itgendhb057: SQL statement: --
-- In case any document to which access seems impossible: ignore it.
--
-- Raises 404 Not Found when not accessible:
--
-- The requested OData URL could not be found on the AFAS Online server.
-- Please contact Invantive Support or use another table.
-- (https://93926.rest.afas.online/profitrestservices/subjectconnector/9520/90b21cfe240a4213805a164886ba6927?take=50000).
--
-- The 404 can not be answered from document cache on Subject connector, so it will consume
-- approximately 125 ms for each row on every run.
--
set ignore-http-404-errors@afas true
2026-10-13 04:59:43.035 Information itgendhb058: Parameter values:
2026-10-13 04:59:43.035 Information itgendhb059: User full name:
2026-10-13 04:59:43.035 Information itgendhb060: User email address:
2026-10-13 04:59:43.035 Information itgendhb061: User log on code:
2026-10-13 04:59:43.035 Information itgendhb063: Active data container provider name: Invantive SQL 25.0.95-PROD+5629
2026-10-13 04:59:43.035 Information itgendhb064: Execution steps: 0
2026-10-13 04:59:43.035 Information itgendhb044: Run SQL statement: create or replace table tstdoc@sql
as
select m.attachment_id
,      m.subject_id
,      m.file_id
,      m.file_name
,      s.filedata
,      s.mimetype
from   AfasOnline.GetConnector.profit_subject_attachments@afas m
join   Subject@afas(m.subject_id, m.file_id) s
--
-- Possibly forwardable server-side filter.
--
where  m.file_name like 'I________.pdf'
--
-- Fine-tuned check that the invoice file name pattern is matched.
--
and    regexp_instr(m.file_name, '^I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].pdf$') = 1
2026-10-13 04:59:43.036 Information itgensql264: Invantive UniversalSQL statement started.
2026-10-13 05:17:34.925 Warning itgendhb173: Context: c:\jobs\sql\repl.sql: create or replace table tstdoc@sql
as
select m.attachment_id...le_name, '^I[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9].pdf$') = 1
2026-10-13 05:17:53.255 Error itgencun016: Error itgenclr007: External error in Invantive Data Hub.

Exception of type 'System.OutOfMemoryException' was thrown.
2026-10-13 05:17:55.405 Error itgenclr007: System.OutOfMemoryException
ValidationException
   at Invantive.Data.ParallelSelectManyEnumerator`2.System.Collections.IEnumerator.MoveNext()
   at Invantive.Sql.V1.DataSourceOrFunctionWithRotatorTree.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, SparseArrayPool sparseArrayPool, QueryObject queryObject, ParameterList parameters)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.CleanSparseArrayIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at Invantive.Data.ExtensionMethods.CacheFirstEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.JoinIterator.InnerLeftRightOuterJoinRegular(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, IEnumerable`1 outerRows, IEnumerable`1 innerRows, Boolean leftIsOuter, Boolean isRhsTableFunctionOrNestedSql, SparseArray emptyInnerRow, Int64 outerRowsCount, JoinStatistics joinStatistics)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.InnerLeftRightOuterJoin(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, JoinStatistics joinStatistics, Boolean isRhsTableFunctionOrNestedSql)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.JoinInternal(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.JoinIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FilterIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.ChainedFirehose.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.SelectListIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.GetDataEnumerator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SqlExecutionStep parentStep, SparseArrayPool sparseArrayPool, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.MaterializeSparseArrayIterator.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Data.ResultSet..ctor(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String originCode, Nullable`1 queryTreeType, IColumnDefinitionCollection fields, IEnumerable`1 rows, ResultSetAuditInformation auditInformation)
   at Invantive.Sql.V1.DdlStatementTree.CreateTable(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, FirehoseResultSet results, IDdlProvider ddlProvider, Int64 rowsAffectedStatement, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow, Nullable`1 maxBatchSizeRows)+MoveNext()
   at Invantive.Data.CompressedEnumerable`1.GetEnumerator()+MoveNext()
   at Invantive.Sql.V1.FirehoseResultSet.Iterator(GlobalState owner, ExecutionOptions executionOptions, IQueryStatePerExecution queryStatePerExecution, SparseArrayPool sparseArrayPool, SqlExecutionStep parentStep, ISparseArray outerRow, ISparseArray pSqlVariableRow, SqlColumnDefinitionCollection oldNewTriggerFields, ISparseArray oldTriggerRow, ISparseArray newTriggerRow, SqlColumnDefinitionCollection sourceTriggerFields, ISparseArray sourceTriggerRow)+MoveNext()
   at Invantive.Sql.V1.QueryPlan.CorrectAndCheckData(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, SparseArrayPool sparseArrayPool, IEnumerable`1 data, String sqlStatement)+MoveNext()
   at Invantive.Data.ExtensionMethods.IteratorWithFinally`1.IteratorWithFinallyEnumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Invantive.Data.ConnectionManager.ExecuteAndCombineQueryPlansV1(GlobalState owner, ExecutionOptions executionOptions, SqlExecutionStep sqlExecutionStep, QueryPlan[] queryPlans, String sqlStatement, ParameterList parameters)
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
--- End of stack trace from previous location ---
   at Invantive.Data.ConnectionManager.ExecuteProviderPassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, SqlExecutionStep sqlExecutionStep, String callSafeNameOverrule)
   at Invantive.Data.ConnectionManager.b(GlobalState owner, ExecutionOptions, SqlExecutionStep, String actionSql, ParameterList, String)
--- End of stack trace from previous location ---
   at Invantive.Data.ConnectionManager.b(GlobalState owner, ExecutionOptions, SqlExecutionStep, String actionSql, ParameterList, String)
   at Invantive.Data.ConnectionManager.PassthroughSqlActionTable(GlobalState owner, ExecutionOptions executionOptions, String actionSql, ParameterList parameters, String callSafeNameOverrule)
   at Invantive.Producer.Data.Hub.QueryEngine.ProcessStatement(GlobalState owner, ExecutionOptions executionOptions, String statement, ResultSet& resultSet, Action`3 fetchMoreRows, Action`9 executeStatement, Boolean isInInteractiveMode, ScriptingLanguageNextStepSpecification& nextStepSpecification) in c:\smoke\ws-25.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 2582
   at Invantive.Producer.Data.Hub.QueryEngine.ProcessStatements(GlobalState owner, ExecutionOptions executionOptions, Action`3 fetchMoreRows, Action`9 executeStatement, IEnumerable`1 statements, String sourceFileName, ScriptingLanguageNextStepSpecification& exitSpecification) in c:\smoke\ws-25.0\Invantive.Data.Hub\src\Invantive.Producer.Data.Hub\QueryEngine.cs:line 1582
2026-10-13 05:17:56.076 Information itgenqtl137: Exit program. (0)
2026-10-13 05:17:56.218 Information itgendhb105: Executed statement to exit application.

Akzeptierte Antwort

Aus einer Analyse ergibt sich folgende Hypothese.

Das create or replace table ohne bulk insert und über den gesamten Datensatz hinweg wird unter der Version 25.0 etwa 8 oder 16 GB Arbeitsspeicher benötigen. Diese passt wahrscheinlich nicht in den Arbeitsspeicher.

Das größte Dokument ist ungefähr 41 MB groß.

Es wird empfohlen, auf die Version 27.0 umzusteigen. Diese enthält Optimierungen, um das Streaming des Datensatzes auch bei create or replace zu verarbeiten. Dies senkt den Speicherbedarf erheblich.

Paginagrootte van de AFAS-driver heeft geen invloed op het probleem; paginagrootte is alleen relevant aan de zijde richting AFAS en de Subject-connector gaat per bestand.

Workarounds:

  • Verdoppeln Sie den Arbeitsspeicher des Servers (vorübergehende Lösung).
  • Verwenden Sie einen inkrementellen Algorithmus.

Antworten 6

Diese Frage wurde nach 1 Woche Inaktivität automatisch geschlossen. Die zuletzt gegebene Antwort wurde als Lösung markiert.

Bitte stellen Sie eine neue Frage in einem separaten Thema, falls das Problem erneut auftritt. Bitte fügen Sie in der neuen Frage einen Link zu diesem Thema ein, indem Sie dessen URL in den Text einfügen.

Antworten 2

Möglicherweise ist 1 der Dokumente sehr groß. In totaal wordt grofweg 7,6 GB aan JSON-data verwerkt.

Ein paar vertiefende Fragen:

Außerdem scheint die Seitengröße zu hoch eingestellt zu sein.

  • Wurde diese manuell angepasst?
  • Welche Werte werden verwendet?
  • Hilft es, den Wert niedriger einzustellen?

Antworten 3

  • Version: 25.0.95-PROD+5630, 64-Bit
  • Alleen Invantive Data Hub.
  • Die Meldung ist seit heute

Soweit ich weiß, hat sich an der Seitengröße nichts geändert.

Wo könnte ich dies gegebenenfalls anpassen?

Antworten 4

Meestal via een set requested-page-size SQL-instructie aan het begin van het script.

Hoewel er niet veel is veranderd aan de AFAS-implementatie, is er veel werk verricht aan de SQL-engine, waaronder veel geheugenoptimalisaties. Sie könnten einen Test mit der neuesten Version durchführen, die unter https://download.invantive.eu verfügbar ist.

Im Forum antworten