PDQ mixed with OLTP

Hi, First of all good morning to all of the members just want to ask if anyone had done mixing PDQ with OLTP on their database? Our database is mostly OLTP with a batching process running every 12 MN of weekdays up until 3AM, I am wondering if I can use PDQ for the batching process then the rest of the memory will be allocated to the buffer cache for oltp purposes. Will this work? will the batch process improve? as of now no PDQ is running on the server, total memory is 32 GB with 16 GB allocated for Buffer and 4 GB in SHMVIRTSIZE then 2MB for DS_TOTAL_MEMORY. I am planning on increasing DS_TOTAL_MEMORY to initially 50% of SHMVIRTSIZE on our staging server to test if the process will improve. If this is the case how can I instruct the queries to use PDQ? I've red somewhere that PDQPRIORITY no longer works after 7.1 is this true?

Hi Lester, Yes you can use PDQ in the latest versions, not sure what you might have read. The first step would be to determine whether your batch process will benefit from parallel processing by examining the query plans. A "set explain on" or "set explain on avoid_execute" would determine this and you would be looking for the keyword "parallel" in the query plan. If you haven't got partitioned (fragmented) tables this is not very likely. Your post concentrates on the memory management aspect. Typically large sorts (order by, group by, distinct etc.) benefit from more memory. Are you seeing use of temp dbspaces while the query runs? If you have 12.10.xC8+ you can monitor use of temp space in real time with: SELECT i.sid, hex(i.flags) flags, hex(i.partnum) partition, trim(n.dbsname) || ":" || trim(n.owner) || ":" || trim(n.tabname) table, i.nptotal allocated_pages FROM sysmaster:systabnames n, sysmaster:sysptnhdr i WHERE (sysmaster:bitval(i.flags, "0x0020") = 1) AND i.partnum = n.partnum; If you're not seeing temp space usage it's unlikely any memory adjustments will help. It might just be a slow query. If you won't get any benefit from parallel processing, have you considered adjusting DS_NONPDQ_QUERY_MEM as an alternative to using PDQ? You would also have to increase DS_TOTAL_MEMORY. Both of these parameters are dynamically configurable. https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/i ds_adr_0064.htm Ben.

Thanks for the reply BENJAMIN THOMPSON sadly as per checking we have 1 fragmented table but it is not included in the batch process =( does this mean PDQ would probably not applicable? DS_NONPDQ_QUERY_MEM is the counterpart for non-PDQ queries correct? is it part of the SHMTOTAL and different from the BUFFER space allocation? as I've checked value for DS_NONPDQ_QUERY_MEM is 512 KB, given below memory allocation how much can I adjust for the NONPDQ query? How does informix allocate memory from DS_NONPDQ_QUERY_MEM to each query? also as for the tempdb query will have to schedule to check if it is utilized upon batch processing. IBM Informix Dynamic Server Version 12.10.FC8 total memory is 32 GB 16 GB allocated for Buffer 4 GB in SHMVIRTSIZE 2MB for DS_TOTAL_MEMORY 512 KB for DS_NONPDQ_QUERY_MEM

You need to check the explain plan to see whether you would benefit from parallel processing. I believe that if you set PDQ you can still benefit from from access to the memory grant manager even without a parallel query. You can check this with "onstat -g mgm" while your query is running. 2 MB for DS_TOTAL_MEMORY is really not very much. DS_TOTAL_MEMORY is part of SHMVIRTSIZE but I think it is a limit rather than a pool. SHMTOTAL is a limit imposed on the size of SHMVIRTSIZE. Do you monitor use of SHMVIRTSIZE on your system? The most basic way is using "onstat -g seg". IBM doesn't document how the memory allocations work very well, presumably part of the policy of not documenting some internal workings which could be subject to change. What I can say is that I have DS_NONPDQ_QUERY_MEM set to 16 MB on one system and there are many sessions using much less than 16 MB of memory. I believe this would only be allocated to do a sort. Happy for someone with a better knowledge of the server workings to chip in! Btw this forum is moving to: https://community.ibm.com/community/user/hybriddatamanagement/communities/commun ity-home?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365 Ben.