invantive documentation
Invantive Support ForumsLog In / Sign Up

AFAS documenten: itgenclr007 OutOfMemoryException

Replies happen on the forum.

Go to source

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.

Accepted answer

An analysis has led to the following hypothesis.

The create or replace table, without the bulk insert, and across the entire dataset, will require approximately 8 or 16 GB of working memory under release 25.0. This is unlikely to fit into the working memory.

The largest document is roughly 41 MB.

We recommend upgrading to release 27.0. This version includes optimisations to handle dataset streaming even with create or replace. This significantly reduces the memory load.

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:

  • Double the server’s working memory (temporary solution).
  • Use an incremental algorithm.

Reply 6

This question was automatically closed after 1 week of inactivity. The last answer provided has been marked as the solution.

Please ask a new question in a separate thread if the problem reoccurs. Please include a link to this thread in the new question by pasting its URL into the text.

Reply 2

It is possible that 1 of the documents is very large. In totaal wordt grofweg 7,6 GB aan JSON-data verwerkt.

A few follow-up questions:

  • Welke versie van Data Hub is dit?
  • Is the process running as a 64-bit process on a 64-bit operating system?
  • Als er sprake is van Data Hub met Data Replicator: staat de optie om data te cachen op disk uit? Zie Error itgendhb212: External error in Invantive Data Hub. (out of memory)
  • Has this message been appearing recently, or has it been happening for some time?

It also appears that the page size is set too high.

  • Has this been manually adjusted?
  • What values are used?
  • Does setting the value lower help?

Reply 3

  • Version: 25.0.95-PROD+5630, 64-bit
  • Alleen Invantive Data Hub.
  • The error message has been present since today

As far as I am aware, nothing has changed regarding the page size.

Where might I need to adjust this?

Reply 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. You could run a test using the latest version, which is available at https://download.invantive.eu.

Reply on the forum