IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 5 replies
  • Latest Post - ‏2012-09-27T20:48:54Z by sutter
Troy Zinderman
Troy Zinderman
3 Posts

Pinned topic Build-time SQL generation

‏2012-09-27T13:07:30Z |
I was wondering, is there a way to see all the permutations of SQL generated by OpenJPA at build-time? I have seen it done with Hibernate’s implantation of JPA 2.0, but we seem only able to capture the SQL at run-time within WebSphere’s OpenJPA deployment. We are a Websphere/DB2 shop, and we would like to run a trace in our Validation lifecycle phase, so we can then port all the SQL into our InfoSphere Optim Query Workload Tuner tool for possible index analysis.
Updated on 2012-09-27T20:48:54Z at 2012-09-27T20:48:54Z by sutter
  • sutter
    sutter
    94 Posts

    Re: Build-time SQL generation

    ‏2012-09-27T13:47:17Z  
    Hi TroyZinderman,
    Capturing SQL generation at build-time? OpenJPA doesn't generate SQL at build-time. All of the SQL generation is done dynamically during run-time. We have some caches that preserve some of the generated SQL so that it doesn't have to be generated over and over again, but all of this is at run-time.

    If you want to capture all of the SQL that OpenJPA generates at run-time, you can specify this property in your persistence.xml:

    <property name="openjpa.Log" value="SQL=TRACE"/>

    Or, if you are running within WebSphere, you could set the following trace specification for your server:

    openjpa.jdbc.SQL=all

    WebSphere's JPA solution does provide integration with DB2's pureQuery feature. The main focus of this integration is to provide Static SQL support for the finders and named queries within your JPA application. This process outputs an xml file that contains the SQL statements that need to be bound to your database. Maybe this process will provide you with more of what you are looking for. This is a build-time process. But, like I said, this only captures the finders and named queries. Any dynamic JPQL statements will not be captured by this process. More information on this process can be found here [1].

    Hopefully, this information will help you make progress. Good luck.

    Kevin

    [1] http://pic.dhe.ibm.com/infocenter/wasinfo/v8r0/topic/com.ibm.websphere.base.doc/info/aes/ae/tejb_jpapdq.html

    Kevin Sutter, JPA Architect, IBM WebSphere
  • Troy Zinderman
    Troy Zinderman
    3 Posts

    Re: Build-time SQL generation

    ‏2012-09-27T14:16:19Z  
    • sutter
    • ‏2012-09-27T13:47:17Z
    Hi TroyZinderman,
    Capturing SQL generation at build-time? OpenJPA doesn't generate SQL at build-time. All of the SQL generation is done dynamically during run-time. We have some caches that preserve some of the generated SQL so that it doesn't have to be generated over and over again, but all of this is at run-time.

    If you want to capture all of the SQL that OpenJPA generates at run-time, you can specify this property in your persistence.xml:

    <property name="openjpa.Log" value="SQL=TRACE"/>

    Or, if you are running within WebSphere, you could set the following trace specification for your server:

    openjpa.jdbc.SQL=all

    WebSphere's JPA solution does provide integration with DB2's pureQuery feature. The main focus of this integration is to provide Static SQL support for the finders and named queries within your JPA application. This process outputs an xml file that contains the SQL statements that need to be bound to your database. Maybe this process will provide you with more of what you are looking for. This is a build-time process. But, like I said, this only captures the finders and named queries. Any dynamic JPQL statements will not be captured by this process. More information on this process can be found here [1].

    Hopefully, this information will help you make progress. Good luck.

    Kevin

    [1] http://pic.dhe.ibm.com/infocenter/wasinfo/v8r0/topic/com.ibm.websphere.base.doc/info/aes/ae/tejb_jpapdq.html

    Kevin Sutter, JPA Architect, IBM WebSphere
    Thank you. I am familiar with pureQuery, but we really are just looking for a way to retrieve all the SQL without walking through each use case for run time executions (similar to Hibernate’s SHOW_SQL parameter). Since I saw this done within Hibernate implementation of JPA (at a training event), I thought it was more common to the standard and not specific to an implementation.
  • sutter
    sutter
    94 Posts

    Re: Build-time SQL generation

    ‏2012-09-27T14:49:33Z  
    Thank you. I am familiar with pureQuery, but we really are just looking for a way to retrieve all the SQL without walking through each use case for run time executions (similar to Hibernate’s SHOW_SQL parameter). Since I saw this done within Hibernate implementation of JPA (at a training event), I thought it was more common to the standard and not specific to an implementation.
    Hi TroyZinderman,
    It's my understanding that the SHOW_SQL property is the same as OpenJPA's SQL logging channel (my previous post). Take another look and see if I'm right.

    As an example, there is no way that Hibernate could generate all of the correct SQL without actually executing the SQL against the database. Each generated Insert or Update would update the values in the database. If subsequence Selects were dependent on various column values, how could the correct SQL be generated if the existing values were not known?

    I just took another look at Hibernate's documentation and there is no mention that this is done at build-time without executing the application. The only thing unique is that the generated SQL goes to the console via this property. You could specify a separate log file for the OpenJPA SQL logging channel and accomplish similar function.

    Thanks,
    Kevin Sutter, JPA Architect, IBM WebSphere
  • Troy Zinderman
    Troy Zinderman
    3 Posts

    Re: Build-time SQL generation

    ‏2012-09-27T16:00:20Z  
    • sutter
    • ‏2012-09-27T14:49:33Z
    Hi TroyZinderman,
    It's my understanding that the SHOW_SQL property is the same as OpenJPA's SQL logging channel (my previous post). Take another look and see if I'm right.

    As an example, there is no way that Hibernate could generate all of the correct SQL without actually executing the SQL against the database. Each generated Insert or Update would update the values in the database. If subsequence Selects were dependent on various column values, how could the correct SQL be generated if the existing values were not known?

    I just took another look at Hibernate's documentation and there is no mention that this is done at build-time without executing the application. The only thing unique is that the generated SQL goes to the console via this property. You could specify a separate log file for the OpenJPA SQL logging channel and accomplish similar function.

    Thanks,
    Kevin Sutter, JPA Architect, IBM WebSphere
    Well, I am sure I am wording this poorly (I am a data geek and have limited direct usage of the ORM).

    When I log from Hibernate (this description is from a DEBUG level); I see the AbstractEntityPersister building the static SQL for an entity. Shows me a SELECT, INSERT, UPDATE, & DELETE statement. Then the Loader adds Isolation Levels to the possible statements (e.g., NONE, PESSIMISTIC_READ, OPTIMISTIC). Then the SessionFactoryImpl will start translating HQL to SQL statements (even showing a node tree for the build).

    That really all I need for the validation process. I do not even have a connection to a DBMS and it will generate these parsed statements and bomb out on the no connection. When I have asked our developers to ship over the OpenJPA logs with the parms listed, I am not seeing any of that.

    Here is an example of that DEBUG log (just running it as an SE with Derby on my desktop for this one).
  • sutter
    sutter
    94 Posts

    Re: Build-time SQL generation

    ‏2012-09-27T20:48:54Z  
    Well, I am sure I am wording this poorly (I am a data geek and have limited direct usage of the ORM).

    When I log from Hibernate (this description is from a DEBUG level); I see the AbstractEntityPersister building the static SQL for an entity. Shows me a SELECT, INSERT, UPDATE, & DELETE statement. Then the Loader adds Isolation Levels to the possible statements (e.g., NONE, PESSIMISTIC_READ, OPTIMISTIC). Then the SessionFactoryImpl will start translating HQL to SQL statements (even showing a node tree for the build).

    That really all I need for the validation process. I do not even have a connection to a DBMS and it will generate these parsed statements and bomb out on the no connection. When I have asked our developers to ship over the OpenJPA logs with the parms listed, I am not seeing any of that.

    Here is an example of that DEBUG log (just running it as an SE with Derby on my desktop for this one).
    Hi TroyZinderman,
    Okay, I'm getting a better picture of what you are looking for. Hibernate's debug looks to be quite nice. Much more complete than OpenJPA's... :-)

    The output you have shown and the requirement for a connection demonstrates that this can't capture all of the SQL. And, if you allowed for database connections, you could get all of the generated SQL.

    In the OpenJPA case, we don't provide any of that SQL debug/trace unless we can get connected to the database. Even in the pureQuery example for Static SQL, a database connection is still required to get any of that data logged.

    In summary, it looks like Hibernate will allow you to log/show the SQL until it requires a database connection. WebSphere JPA or OpenJPA requires a connection from the get-go to log/show the generated SQL. Other than that, I don't think there's much difference.

    Thanks,
    Kevin Sutter, JPA Architect, IBM WebSphere