Topic
  • 9 replies
  • Latest Post - ‏2013-03-03T04:48:34Z by rajendrak
stoned99
stoned99
44 Posts

Pinned topic CONCENTRATE STATEMENTS WITH LITERALS

‏2013-02-20T19:51:42Z |
We just switched to DB2 10 NFM and I've been researching how to best use the dynamic statement cache and it's new ability to better handle literals.

My .NET applications use a combination of dynamic SQL, prepared stored procedures and sql executed via a stored procedure using execute immediate and prepare statements. I'm using DDTEK as my data provider.

Some Dynamic SQL is executed via Stored Procedure using the 2 following methods.

ex 1.

DECLARE SQLString VARCHAR(1000);
SET SQLString = vSQLInput;

EXECUTE IMMEDIATE SQLString;

and

ex 2.

DECLARE PreparedSQL VARCHAR(7000);
DECLARE PrepareSQL VARCHAR(7000);

DECLARE SQLCursor CURSOR WITH RETURN FOR PreparedSQL;

SET PrepareSQL = vSQLInput;

PREPARE PreparedSQL FROM PrepareSQL;

OPEN SQLCursor;

I believe I can leverage the enhancement in ex. 2 using the following.

DECLARE PreparedSQL VARCHAR(7000);
DECLARE PrepareSQL VARCHAR(7000);
DECLARE PrepareAttrib VARCHAR(50);

DECLARE SQLCursor CURSOR WITH RETURN FOR PreparedSQL;

SET PrepareSQL = vSQLInput;
SET PrepareAttrib = 'CONCENTRATE STATEMENTS WITH LITERALS’;
PREPARE PreparedSQL FROM PrepareSQL ATTRIBUTES PrepareAttrib;

OPEN SQLCursor;

But I'm at a loss on how to enable the same feature for the direct SQL calls and the EXECUTE IMMEDIATE. As far as I can tell I'm unable to pass the directive via the DDTEK connection string.

Has anyone had any success with this new feature ?

Thanks

Mark.
Updated on 2013-03-03T04:48:34Z at 2013-03-03T04:48:34Z by rajendrak
  • stoned99
    stoned99
    44 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-22T22:55:00Z  
    Is anyone from IBM monitoring this forum ?
  • rajendrak
    rajendrak
    40 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-25T08:09:42Z  
    • stoned99
    • ‏2013-02-22T22:55:00Z
    Is anyone from IBM monitoring this forum ?
    Hi,

    We shall have recommendations on your specific questions on 'dynamic statement cache' very soon.

    Additionally,
    • Since you are using the DDTEK provider we would recommend you to approach Data Direct for the same. This forum is primarily for queries on the .NET application interface provided by the IBM .NET Data Provider(targeting IBM data servers include DB2 for z/OS v10).

    • Also, we would recommend you to consider the IBM DB2Connect offering (the IBM .NET Data Provider is part of this).
    Ref:
    1) http://www-142.ibm.com/software/products/us/en/db2connfami/
    2) http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.intro/src/tpc/db2z_db2connect.htm
    3) http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.swg.im.dbclient.adonet.doc/doc/c0024472.html

    Regards,
    Raj
  • stoned99
    stoned99
    44 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-25T18:12:40Z  
    • rajendrak
    • ‏2013-02-25T08:09:42Z
    Hi,

    We shall have recommendations on your specific questions on 'dynamic statement cache' very soon.

    Additionally,
    • Since you are using the DDTEK provider we would recommend you to approach Data Direct for the same. This forum is primarily for queries on the .NET application interface provided by the IBM .NET Data Provider(targeting IBM data servers include DB2 for z/OS v10).

    • Also, we would recommend you to consider the IBM DB2Connect offering (the IBM .NET Data Provider is part of this).
    Ref:
    1) http://www-142.ibm.com/software/products/us/en/db2connfami/
    2) http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db2z10.doc.intro/src/tpc/db2z_db2connect.htm
    3) http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.swg.im.dbclient.adonet.doc/doc/c0024472.html

    Regards,
    Raj
    Actually we do have DBConnect in house in a number of environments. How would we enable the feature via Connect ?

    All I can find is references to JDBC when I search on the Net.
  • rajendrak
    rajendrak
    40 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-26T10:03:54Z  
    • stoned99
    • ‏2013-02-25T18:12:40Z
    Actually we do have DBConnect in house in a number of environments. How would we enable the feature via Connect ?

    All I can find is references to JDBC when I search on the Net.
    Hi,

    Could you please confirm the version of DB2Connect that you are using.

    Have you tried setting the 'StatementConcentrator' property?
    Ref: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2ConnectionStringBuilderClassStatementConcentratorProperty.html

    Also, if you can share the .NET code snippet and error details if any it would help us understand your problem better.

    Regards,
    Raj
  • stoned99
    stoned99
    44 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-26T15:38:44Z  
    No real need to go much further, you provided the clue I was looking for. Is there a connection string equivalent or can it only be set via the .Net connection object ?

    Thanks
  • rajendrak
    rajendrak
    40 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-26T16:29:11Z  
    • stoned99
    • ‏2013-02-26T15:38:44Z
    No real need to go much further, you provided the clue I was looking for. Is there a connection string equivalent or can it only be set via the .Net connection object ?

    Thanks
    Hi,

    Yes it is also supported as a connection string attribute. I would recommend you to spend some time going thru the documentation.
    Ref: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2ConnectionClassConnectionStringProperty.html

    Glad that our inputs helped you make progress. So does that mean you would now be able to use DB2Connect (instead of DDTEK)?

    Regards,
    Raj
  • stoned99
    stoned99
    44 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-26T17:08:16Z  
    I have been doing a lot of reading and all I could find was references to how to implement the feature in Stored Procedures via the prepare statement. As usual the .NET community has been left in the dust in regards to white papers, red book content etc. IBM's love of JDBC is infuriating sometimes.

    And to answer your question about Connect vs DDTEK, you have a pricing issue. I've got a ticket open with them to make sure this feature is supported as we're already seeing benefits and I want to ensure we leverage it at every opportunity.

    But thanks for the info, hopefully someones next google search will find this thread helpful.
  • stoned99
    stoned99
    44 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-02-27T20:22:49Z  
    Well .NET developers considering using DataDirect's Connect.NET product with DB2 10 NFM beware. The following is Progress softwares response to my inquiry. I've posted it here to ensure google picks this up for future developer searches.

    To: Stone, Mark
    Subject: RE: Case Comment 00229081 - CONCENTRATE STATEMENTS WITH LITERALS

    Hi Mark,

    Thank you for the thoughtful response. I have spoken to our Development Team and the enhancement you have requested is not on our roadmap at this time. If you would be interested in custom engineering work, our team could scope out the cost of the project and provide you with pricing.

    If this solution would be of interest, please let me know.

    Thank you.

    With Kind Regards,

    From: Stone, Mark
    Sent: Wednesday, February 27, 2013 12:27 PM
    To: ...
    Subject: FW: Case Comment 00229081 - CONCENTRATE STATEMENTS WITH LITERALS
    Importance: High

    xxxxxxx, I’m following up per John S.’s response below about critical DB2 functionality missing from the current .NET provider. As you’re aware, mainframe resources are a precious and expensive commodity, to best use those resources we need to be able to leverage ALL aspects of DB2 as they become available. This particular feature became available in DB2 10 NFM (New function mode), and can avoid expensive prepare operations by using DB2’s dynamic statement cache more effectively.

    I initially reached out to IBM for help in enabling this feature, and they were quick to point out it was available via configuration in their DBConnect product. I had hoped to receive a similar response from Progress.

    Could you escalate this change in particular and Full DB2 10 support in general to your .NET development team.

    I appreciate any help you can provide.

    Thanks

    Mark Stone
  • rajendrak
    rajendrak
    40 Posts

    Re: CONCENTRATE STATEMENTS WITH LITERALS

    ‏2013-03-03T04:48:34Z  
    • stoned99
    • ‏2013-02-27T20:22:49Z
    Well .NET developers considering using DataDirect's Connect.NET product with DB2 10 NFM beware. The following is Progress softwares response to my inquiry. I've posted it here to ensure google picks this up for future developer searches.

    To: Stone, Mark
    Subject: RE: Case Comment 00229081 - CONCENTRATE STATEMENTS WITH LITERALS

    Hi Mark,

    Thank you for the thoughtful response. I have spoken to our Development Team and the enhancement you have requested is not on our roadmap at this time. If you would be interested in custom engineering work, our team could scope out the cost of the project and provide you with pricing.

    If this solution would be of interest, please let me know.

    Thank you.

    With Kind Regards,

    From: Stone, Mark
    Sent: Wednesday, February 27, 2013 12:27 PM
    To: ...
    Subject: FW: Case Comment 00229081 - CONCENTRATE STATEMENTS WITH LITERALS
    Importance: High

    xxxxxxx, I’m following up per John S.’s response below about critical DB2 functionality missing from the current .NET provider. As you’re aware, mainframe resources are a precious and expensive commodity, to best use those resources we need to be able to leverage ALL aspects of DB2 as they become available. This particular feature became available in DB2 10 NFM (New function mode), and can avoid expensive prepare operations by using DB2’s dynamic statement cache more effectively.

    I initially reached out to IBM for help in enabling this feature, and they were quick to point out it was available via configuration in their DBConnect product. I had hoped to receive a similar response from Progress.

    Could you escalate this change in particular and Full DB2 10 support in general to your .NET development team.

    I appreciate any help you can provide.

    Thanks

    Mark Stone
    Hi,

    We would certainly be glad to discuss with you on the benefits of using IBM DB2Connect (such as this particular scenario and many more) and also listen to any concerns you might have. Do let me know.

    Regards,
    Raj