Topic
3 replies Latest Post - ‏2014-03-08T04:12:36Z by krmilligan
G.LANG
G.LANG
2 Posts
ACCEPTED ANSWER

Pinned topic Enable Concurrent Write Support

‏2014-03-07T18:03:27Z |

We have a question regarding the best way to Enable Concurrent Write Support?  Is it Reuse(*yes) or Reuse(*no)?
 
There documentation we are finding seems to be conflicting documentation.
 
We have many jobs that do parallelism/multi-threading where each job is writing to the same work file.
 
Is it best to make that file Reuse(*yes) or Reuse(*no).  We have read documentation that said for large batch processes with lots of writes, make it *no.  Then we read that in this situation we should use Concurrent Write support by doing Reuse(*yes).
 
Which one is it?  Do we define the table with Reuse Deleted (*YES) and then do OVRDBF reuse deleted (*no) in the CLP?  Is this the best practice when doing large amounts of inserts and take full advance of concurrent write and optimize blocking, parallel index maint., etc?

  • krmilligan
    krmilligan
    445 Posts
    ACCEPTED ANSWER

    Re: Enable Concurrent Write Support

    ‏2014-03-07T21:12:15Z  in response to G.LANG

    Of course the answer is "It Depends", so that's the primary reason why the documentation conflicts a little bit. Enable Concurrent Write does require Reuse(*YES) to allow multiple jobs to write rows to a table concurrently.

    On the other hand, if you have processes that are doing blocked writes into a table that has a significant number of indexes (and keyed LFs) that need to be maintained,  then your workload might have better performance if you enable parallel processing of the indexes.  Parallel index maintenance requires DB2 SMP, blocked inserts of 8 or more rows, and Reuse = *NO. 

    That was the purpose of the OVRDBF reuse deleted support added in 7.1.  This enables reuse to be turned off for those jobs that would benefit from parallel index maintenance where the interactive jobs can still benefit from concurrent write.

    In the end, it really depends on what bottleneck you're hitting to determine the best value for your environment.  If you need help identifying your bottleneck, the STG Lab Services team that I work on has the skills to assist.  Just let me know and I'll pass along the appropriate contact info.

  • G.LANG
    G.LANG
    2 Posts
    ACCEPTED ANSWER

    Re: Enable Concurrent Write Support

    ‏2014-03-07T22:11:23Z  in response to G.LANG

    So if I understand this correctly that means I cannot have it both ways?  I cannot have ECW and parallel index maintenance occuring during the running of a batch job with alot of inserts?  So depending on the specifics of the job, I will have to choose between one or the other?  Thank you...

    • krmilligan
      krmilligan
      445 Posts
      ACCEPTED ANSWER

      Re: Enable Concurrent Write Support

      ‏2014-03-08T04:12:36Z  in response to G.LANG

      A single job cannot have it both ways, but you can have one set of jobs having ECW and another set of jobs with parallel index maintenance. I suppose you could use OVRDBF within the batch job and turn the reuse mode on and off.