GROUP BY leaves PSORT_DBTEMP directories empty

Greetings. This started as an attempt to help my client with a long-running query (actually a mass update) that he was trying to break up into smaller transactions. I've just been perusing the thread "PSORT_DBTEMP does not work" by Heinz. I find myself in a similar situation and Art's explanations don't seem to fit here. My client is on IDS 11.5 (I know - need go get upgraded - later.. :-) and I am trying to run a group-by query on a table with over 15 million rows. When I ran it w/o trying psort, is took 15-17 minutes to complete, showing 264 unique values for the "group-by" column. It is an indexed column but some of the more frequent values had over half-million occurrences. I tried it again today, setting export PSORT_NPROCS=6 and export PSORT_DBTEMP=/d1:/d1:/d3:/d4:/d5:/d6 (shortening the paths for this posting.) onstat -g env confirms I had this right. Oh, and I set PDQPRIORITY to 25, although the table is not fragmented. While it was running in one window, I kept examining the directories listed in PSORT_DBTEMP. Persistently empty. I finally killed it after about 6 minutes, though I'm running again as I type this. Any clues why nothing shows up in the directories? If it's all happening in memory (15 million rows? YIKES!!) what onstat command can I use to find the in-memory data and associate it back to my sort? Thanks for ideas. -- Jacob S.

Run onstat -g ses for the session and onstat -g mgm. Also what is the query plan for the session? Regards, David. > On 12 May 2014 at 18:40 JACOB SALOMON <jakesalomon@yahoo.com> wrote: > > > Greetings. > > This started as an attempt to help my client with a long-running query > (actually a mass update) that he was trying to break up into smaller > transactions. > > I've just been perusing the thread "PSORT_DBTEMP does not work" by Heinz. I > find myself in a similar situation and Art's explanations don't seem to fit > here. > > My client is on IDS 11.5 (I know - need go get upgraded - later.. :-) and I am > trying to run a group-by query on a table with over 15 million rows. When I > ran it w/o trying psort, is took 15-17 minutes to complete, showing 264 unique > values for the "group-by" column. It is an indexed column but some of the more > frequent values had over half-million occurrences. > > I tried it again today, setting > export PSORT_NPROCS=6 and > export PSORT_DBTEMP=/d1:/d1:/d3:/d4:/d5:/d6 > (shortening the paths for this posting.) > onstat -g env confirms I had this right.> > Oh, and I set PDQPRIORITY to 25, although the table is not fragmented. > > While it was running in one window, I kept examining the directories listed in > PSORT_DBTEMP. Persistently empty. > > I finally killed it after about 6 minutes, though I'm running again as I type > this. > > Any clues why nothing shows up in the directories? If it's all happening in > memory (15 million rows? YIKES!!) what onstat command can I use to find the > in-memory data and associate it back to my sort? > > Thanks for ideas. > > -- Jacob S. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >