You can also try setting the CQD BMO_MEMORY_LIMIT_UPPER_BOUND to 200. This means it is limited to 200MB per operator instance. If you want to reduce this further you need to set both BMO_MEMORY_LIMIT_LOWER_BOUND_SORT and BMO_MEMORY_LIMIT_UPPER_BOUND to a value less than 200 MB.
Selva -----Original Message----- From: Prashanth Vasudev <[email protected]> Sent: Monday, July 23, 2018 8:03 AM To: [email protected] Subject: RE: Is there a simple way to force the use of scratch file? You can reduce the number of buffers and size of each buffer used by sort operator to minimal value. This will cause sort to run out of buffers and use scratch files. The following cqds can be reduced to minimal values. GEN_SORT_MAX_BUFFER_SIZE GEN_SORT_MAX_NUM_BUFFERS GEN_SORT_NUM_BUFFERS Regards, Prashanth -----Original Message----- From: Ming <[email protected]> Sent: Sunday, July 22, 2018 5:48 AM To: [email protected] Subject: Is there a simple way to force the use of scratch file? Hi, all, I am trying to reproduce an issue in STFS. So I need to use a query to use the STFS. I tried to generate 10 Million rows in a table and do an ORDER BY, but it doesn't use scratch file. I think I need to generate more rows maybe. But is there a simpler way to simulate that? No matter what query it is, I am fine, I just need that query can use scratch file. I am using a development machine, with very small disk and memory. So I hope I can find a simple way to simulate. Any idea will be very appreciated! thanks, Ming
