ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL Queries & Object Locks

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Queries & Object Locks

    Has anyone used SQL Explorer? It's a neat program I use in Windows that connects to the AS400 to run SQL statements or queries.

    I noticed on the AS400 if you run a 'SELECT *' statement on an object, it creates an object lock under the job name "QZDASOINIT."

    Now, once the query is done, the object is still locked with *SHRRD. It finally releases the lock if you forcefully end SQL Explorer. Is this normal behavior? I've been testing with ALCOBJ/DLCOBJ at the moment which at least drops the lock so another program can place its exclusive lock on it. Thanks.

  • #2
    Normal? Yes and no. It's "normal" for server jobs such as QZDASOINIT. AFAIK, not only normal but in fact intentional. If you run the same SELECT statement in STRSQL, the lock will likely be gone when you you close the statement and return to the SQL command line.

    For a server job, though, tables are often used over and over again as transactions come in and are processed. Tables will remain allocated in a server job until released, in order to avoid needing to spend time doing hard opens and closes over and over again.

    If another job needs an exclusive lock, that is, if *SHRRD somehow interferes for some reason, the other job can run ALCOBJ OBJ(( mytable *FILE *EXCL)) CONFLICT(*RQSRLS) to request the server to release its lock. DLCOBJ should subsequently also be run.
    Tom

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

    Comment


    • #3
      We believe we solved this problem by adding in a unique column in the SELECT statement each time it is ran. We use the current timestamp like this (in Groovy),
      SELECT current date, '' as "${System.currentTimeMillis()}" FROM sysibm.sysdummy1 This does remove the caching performance benefit, but the *SHRRD locks seem to have disappeared.

      Comment

      Working...
      X