cancel
Showing results for 
Search instead for 
Did you mean: 

Can DB Generic Ouput adapter be used in "warehouse" mode?

Former Member
0 Kudos

Hello,

I'm using Greenplum (Generic DB Output adapter in ESP) and started getting this response now after a few million

records are in my target table :

ConnectionWriter(ESPToDbTableB) is started.

2014-05-27 16:42:26.301 | 20622 | container | [SP-3-100005] (2468.241) sp(3349) Error executing SQL statement select * from wh._esp_b_stream. java.lang.OutOfMemoryError: GC overhead limit exceeded

2014-05-27 16:42:26.301 | 20622 | container | [SP-3-148011] (2468.241) sp(3349) ConnectionWriter(ESPToDbTableB) connection reset error: 2014-05-27 16:42:26 DBOutput_Adapter::resetConnection - executeQuery not successful - resetConnection() ended abnormally

The adapter is connected to a Stream in my ESP project, but only recently.  Previous to being a Stream, it was a Flex Window.  I would like

simple - blind inserts from the ESP project.....but it appears the adapter does not know this and is trying to pull in the entire contents of the table before

initializing and getting started.

Any advice greatly appreciated, as usual.

Thank you

Michael

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Michael,

I'm a little confused by the statement "it appears the adapter does not know this and is trying to pull in the entire contents of the table before initializing and getting started".  The DB Output Adapter would not read the contents of a database table.  It only writes to the DB table.

Do you have a WINDOW with the Generic Database Input Adapter attached to it somewhere else in your project?

Assuming that you are using ESP 5.1 SP04, there is a memory leak in the Generic DB Input Adapter:

    751392 - Memory leak in Generic DB Input adapter causes ESP to crash

This has been fixed in ESP 5.1 SP08 scheduled for release this week and a special ESP 5.1 SP04 ONE-OFF patch available from technical support.

The DB Output Adapter does not have a "warehouse" mode but this could be achieved by passing the data through a stream (or a flex stream):

   Comparing Streams, Windows, and Delta Streams

SP08 introduces a new feature for the Database Input Adapter whereby you can specify a "key" column that it uses to remember where it left off so it only pulls in rows that are new since the last time it polled the database.

Thanks,

  Neal

Former Member
0 Kudos

Hi Neal,

I'm confused as well. 

My CCL statement is :

ATTACH OUTPUT ADAPTER ESPToDbTableB TYPE db_out TO ESPToStorage PROPERTIES service = 'my_db' ,

  table = 'wh._esp_b_stream' ;

Yes - we are using ESP 5.1 SP04, and no, this is not an input from a DB but rather an output to a DB (from a stream).

Why the "select * " rears up as the adapter is initializing?

Michael

Former Member
0 Kudos

Hello,

I turned on ODBC tracing and I see that the Generic Database Output Adapter does indeed issue a "select *" against the database.  It issues the query to get a list of the columns and their types.  It should be doing a "select * from table where 1=2" so that no rows are ever returned.

I have logged the following bug for this issue:

   764785 - Generic Database Output Adapter issues select * against database

ESP uses the result set from this query to get a description of the table and then it disposes of the result set.  I don't see this causing it to use excessive amounts of memory.  I have a table with 3 million rows and five columns.  When I start my project with the Generic Database Output Adapter, I never see it consume more than 55 MB of physical memory.

At this point you should probably open a technical support case so this bug can be prioritized with engineering.  In the technical support case, if you could provide the project's complete "esp_server.log" log file, the "stdstreams.log" log files and your CCL, it might help us see why you are getting the out of memory error.

Thanks,

  Neal

Former Member
0 Kudos

Thanks Neal.  That makes sense now.  I don't like it, but it makes sense.

I'll work around it for now, and try to find some time to book the case to get the fix prioritized.

Cheers,

Michael

Answers (0)