CODEWITHSUNDEEP
hibernatejpadb2-400jt400
Patrick Grimard
Patrick Grimard

Reputation: 7126

Using Hibernate to query an IBM i not all data is returned if records were added from green screen

I have run into an issue where data created on our IBM i through a traditional green screen application isn't reflected by a web app I'm writing. It would seem that records I add from the web app are immediately available to both the web app and the IBM i, but records I create on the IBM i are not immediately available to the web app. I thought it might be a cache problem, but after restarting my web app, the record is still not there. I've tried setting javax.persistence.cache.retrieveMode to BYPASS and that setting is reflected when my Hibernate entity manager is created, but the data returned from my query still is missing the record added through the green screen.

At this stage, I don't think it's a cache problem. So here is the testing I've done so far.

The files I am working with are WOHDR2 and WOJOBS, where WOHDR2 is the header file and WOJOBS is the detail file.

Here is what I have tested so far.

Record added to file WOJOBS through web app and immediately associated to WOHDR2 - These records reflect the ID field of the parent WOHDR2 and are visible in both the web app and the green screen.

Record added to file WOJOBS through green screen app with no ID association to WOHDR2, ie: it's in pending state until a user assigns it to a WOHDR2 later - This record is visible through the green screen app, but not the web app

Associate a WOJOBS record to a WOHDR2 through green screen - I can now see the WOJOBS record through the web app

Record added to file WOHDR2 through web app or green screen app - Records are visible through the web app and the green screen application simultaneously

Upvotes: 0

Views: 215

Answers (1)

Charles
Charles

Reputation: 23813

There's a few reasons you wouldn't see the records you expect.

  1. You're not reading/writing to the same file (table)
  2. You're running under an isolation level were you don't see uncommitted records and the records you've written have not been committed.
  3. The records haven't actually been written to the table yet.
  4. Your SQL app is using a copy of the data, instead of the live data.

Solutions

  1. If using unqualified references, make sure both applications are resolving to the same file. Assuming *SYS naming convention, make sure both apps are using the same library list.
  2. See isolation levels
  3. If you're using an RPG program to write the records from the green screen, beware that by default, the RPG compiler will buffer records when a file is opened for output only. This is also known as "record blocking". The records won't be sent to the DBMS until the buffer fills and/or the file is closed. You'll often see FRCRATION(1) set via CHGPF or OVRDBF to work around this issue. But that's bad practice. The best practice is to use the Force End of Data (FEOD) op-code with the N extender, FEOD(N) mytable; in your RPGLE program prior to returning from it. This will flush the RPGLE buffered output to the DBMS.
  4. if your SQL is opening a cursor prior to the RPG app adding the data, it's possible the default of allow copy data (ALWCPYDTA) = *YES means your web app is working with stale data. You can control this at the connection level or via the SET OPTION statement.

It should be noted that RPG doesn't buffer output if there's a unique key defined over the table being written to; otherwise the DBMS wouldn't be able to ensure uniqueness. FRCRATIO(1) is left over from way back before journaling and commitment control....it allowed you to force records not only to the DBMS but all the way to disk. Turning it on today is a great way to slow down your application.

Upvotes: 1

Related Questions