Insufficient Memory error

dataservices performance
1

Hi,

I am running a job using DS 12.2 which was upgraded from 11.7.3.8. The job runs fine in 11.7 but in 12.2 I am getting this error:

3512 1204 SYS-055005 4/20/2011 9:44:34 AM |Data flow df_load_mo_hist_monthly_inserts|Reader TCSort_1
3512 1204 SYS-055005 4/20/2011 9:44:34 AM Insufficient memory in allocation attempt. Details: <trying to allocate 976 * 100 bytes>.

It should be caching around 4 million rows in the table comparison but after around 1.5 million rows this error comes up. Does anyone have any ideas on why this is happening?

Thanks

NT

Nilz07 (BOB member since 2007-05-22)

2

You are using sorted mode in the TC transform and it is not guaranteed the sort order of the database to be binary with the same or similar codepage. Hence the sorting will happen inside the engine and given your setting does fail.

Things that should align are:

  • the two codepage values of the target datastore align with the source/engine codepage
  • target database supports binary sorts - what db are you using?
  • at least one of the TC input-primary-key columns is of type varchar, correct?

Werner Daehn :de: (BOB member since 2004-12-17)

3

We have figured out why this is happening and the reason is that the job we have migrated from 11.7.3 to 12.2 had the dataflow defined as in-memory cache type. We have moved from AIX environment to Windows Server 2008 x64. As windows is 32 bit this is the reason it is happening.

We need to switch the dataflows to in-memory. Do you know any easy of querying the repository and finding these dataflows as well updating the dataflows to have a default cache type of ‘Pageable’?

Thanks

NT

Nilz07 (BOB member since 2007-05-22)

4

The following SQL can be used to find the Dataflows that are set to In-Memory:

SELECT AL_LANG.NAME
FROM AL_LANG
     JOIN AL_LANGTEXT ON (AL_LANGTEXT.PARENT_OBJID = AL_LANG.OBJECT_KEY AND 
                                    AL_LANGTEXT.TEXT_VALUE LIKE '%"Cache_type" = ''in_memory_cache''%' AND
                                    AL_LANG.OBJECT_TYPE = 1)
WHERE AL_LANG.VERSION = (SELECT MAX(SUBQ.VERSION) 
                         FROM AL_LANG SUBQ
                         WHERE SUBQ.OBJECT_TYPE = AL_LANG.OBJECT_TYPE
                           AND SUBQ.GUID        = AL_LANG.GUID)
ORDER BY AL_LANG.NAME;

The above should should work on both Oracle and SQL Server repositories.

Updating of Dataflows, while theoretically could be done by a SQL statement, should be performed through Designer. Alternatively, you could export the problem Dataflows using the al_engine command, do a search/replace on each file and then import them back in.

EDIT: It should be pointed out that the query is not 100% foolproof. The source code stored in the TEXT_VALUE column can be split between rows. In this case the LIKE expression will not return the Dataflow as it did not find the complete string.

eganjp :us: (BOB member since 2007-09-12)