title: How to select top 10 records from DB2 database using OpenJPA?
body: <p>I am trying to select TOP 10 records from DB2 database using OpenJPA. I am trying to get results as specified in query:</p>
  
  <pre><code>SELECT * FROM CACHE_REFRESH_TABLE FETCH FIRST 10 ROWS ONLY
  </code></pre>
  
  <p>For that I have enabled below property in my <code>persistence.xml</code>. </p>
  
  <pre><code>&lt;property name="openjpa.jdbc.DBDictionary" value="db2(SupportsSelectStartIndex=true,SupportsSelectEndIndex=true)"/&gt;
  </code></pre>
  
  <p>But after enabling the property it throws an exception for other simple SELECT queries as I had mentioned <a href="https://stackoverflow.com/questions/34270152/how-to-get-the-exact-details-of-openjpa-exception-for-invalid-data-conversion">here</a>.</p>
  
  <p>The Java code I am using to retrieve the TOP 10 records is as follows. But it does not work as it forms the SELECT as <code>SELECT TOP 10 FROM CACHE_REFRESH_TABLE</code> and not as </p>
  
  <pre><code>SELECT * FROM CACHE_REFRESH_TABLE FETCH FIRST 10 ROWS ONLY
  </code></pre>
  
  <p>Below is the Java code.</p>
  
  <pre><code>public List&lt;CacheHistoryOTO&gt; fetchRefreshHistory(
                  int someInput1, String someInput2) throws SomeException {
              LOGGER.info(EventMessages.METH_START, "fetchRefreshHistory");
              List&lt;CacheHistoryOTO&gt; cacheHistoryOTOList = null;
              try {
                  EntityManager entityManager = entityManagerProvider
                          .getEntityManager();
                  final Query query = entityManager
                          .createNamedQuery("topHistoryRecords");
                  query.setMaxResults(someInput1);//Depending on parameter someInput1 number of records to be fetched will be provided.
  
                  cacheHistoryOTOList = (List&lt;CacheHistoryOTO&gt;) query
                          .getResultList();
              } catch (Exception e) {
                  LOGGER.error(EventMessages.ERROR, e);
                  throw new SomeException(Constant.ERROR_PERSISTENCE, e);
              }
              LOGGER.info(EventMessages.METH_END, "fetchRefreshHistory");
              return cacheHistoryOTOList;
      }
  </code></pre>
  
  <p>Named query is declared as follows. </p>
  
  <pre><code>@NamedQueries({
          @NamedQuery(name = "topHistoryRecords", query = "SELECT cacheRefreshHistoryOTO FROM CacheHistoryOTO cacheRefreshHistoryOTO ORDER BY cacheRefreshHistoryOTO.refreshTimeStamp DESC")
          })
  </code></pre>
  
  <p>Can someone please suggest to use the right way to do this?</p>
  
  <p>I have looked at <a href="https://stackoverflow.com/questions/11655870/jpa-2-criteriaquery-using-a-limit">JPA 2 CriteriaQuery, using a limit</a> but did not help.</p>