none
db2 adapter - polling RRS feed

  • Question

  • using db2 adapter 2010, for polling a db2 table...I have specified a simple select statment

    1.How does  the adapter handle reading data ?does it support streaming ..because some times my select statement returns 250K rows...which is abt 500mb of data..I have noticed the memory of my host instance spike above 1 GB in those instnace till the data gets into the pipeline...I have a custome pipe line which reads the stream and  does some processing .Is the data is getting streamed ???? I noticed the memory spike just before the data gets into the pipe line.

    Friday, March 4, 2011 3:56 AM

Answers

  • 1) It is possible you may have to do that, depending on the type of query, and version/platform of DB2. I find I have to do that with DB2/400 more than other platform (DB2/MVS, DB2/NT), when using the adapter, more so with stored procedure calls when not using the adapter.

    2) http://msdn.microsoft.com/en-us/library/gg164980(BTS.70).aspx describes all the parameters.

    AutoCommit

    Optionally, you can instruct the Data Provider to execute an implicit COMMIT on all SQL statements by specifying TRUE. By default, this Boolean property is set to FALSE.

    The AutoCommit mode is appropriate for most common transactions that consist of a single SQL statement. However, this mode does not allow for unit of work rollback


    Charles Ezzell - MSFT
    • Marked as answer by Kyrn Monday, March 7, 2011 10:02 PM
    Saturday, March 5, 2011 1:51 PM

All replies

  • Kym,

    DB2 delivers the data in a steady stream to the provider. There is much the provider has to do with the data (EBCDIC to ASCII conversion for instance) before it is sent to the rest of BizTalk, so yes, it will use memory. If your select statement is returning that many rows, you may want to look at using SSIS to query the data, as that is what it is designed to do, retrieve and move data fast and efficiently.


    Charles Ezzell - MSFT
    Friday, March 4, 2011 7:19 PM
  • Charles,

    Thanks for the quick reply...My DEV server has  3 GIG of ram ..This big file is a one time read..After that its not going to be a problem..
    Memory shoots up to close to 3Gigs during the initial part of the data read..that is when the adapter is reading and before it gets into the pipeline..Then once inside the pipe line the memory shoots down immediately  ..So Its not leaking meory....If it was handlign this in a streaming manner shouldn't  the memory usage should have been flat..Is the usage of 3 Gig of ram an expected behaviour ?

    Because my pipe line is a custom pipe line and there I have used the virtual stream class for handlign this big file and my memory usage remain flat thru out the pipe line processing

    1 Also for the db2 adapter if I  have  a table I have to say select * from schema.tablename even thoguh I have mentioned a default schema as part of the connections tring ..Now I will ahve to change the  select statement also when I move to Qa and prod because the schema name for the prod and qa are different ..

    2. Also  waht does the Auto Commit = true , in the conenction string do in the context of the adapter..?

    Friday, March 4, 2011 7:34 PM
  • 1) It is possible you may have to do that, depending on the type of query, and version/platform of DB2. I find I have to do that with DB2/400 more than other platform (DB2/MVS, DB2/NT), when using the adapter, more so with stored procedure calls when not using the adapter.

    2) http://msdn.microsoft.com/en-us/library/gg164980(BTS.70).aspx describes all the parameters.

    AutoCommit

    Optionally, you can instruct the Data Provider to execute an implicit COMMIT on all SQL statements by specifying TRUE. By default, this Boolean property is set to FALSE.

    The AutoCommit mode is appropriate for most common transactions that consist of a single SQL statement. However, this mode does not allow for unit of work rollback


    Charles Ezzell - MSFT
    • Marked as answer by Kyrn Monday, March 7, 2011 10:02 PM
    Saturday, March 5, 2011 1:51 PM