How to do Light Scans . . .

Over-simplified definition: LIGHT SCANS occur when you sequentially scan data without using the BUFFERS. This can be about 1 to 3 times faster than regular scans! It does not like locking, filtering or varchar but it will tolerate some of it. IBM tells me (and I'm paraphrasing) that it uses SHMVIRTSIZE to hold one or more "private buffers" and then processes them in parallel, asynchronously, and block-wise. It can run with or without PDQ. And if you set LIGHT_SCANS=FORCE, it will bypass the need for PDQ, big tables and small BUFFERS. Setup example: In ONCONFIG it uses very little SHMVIRTSIZE so the only things that I recommend that you "tune" are the RA_PAGES and RA_THRESHOLD. When you increase them you also increase the number of "bufcnt" in "onstat -g lsc". Then run "export LIGHT_SCANS=FORCE". An finally in your SQL put "SET ISOLATION TO DIRTY READ;" and "SET PDQPRIORITY 0;" More tips and tricks: 1. The "onstat -g lsc" and "onstat -g ses <session_id>" shows LIGHT SCAN info. 2. LIGHT SCANS by itself is usually faster than PDQ by itself. 3. Only combine LIGHT SCANS and PDQ when doing update statistics HIGH to make fewer passes through the table. 4. Update statistics HIGH can use PDQ and/or LIGHT SCANS. LOW and MEDIUM cannot. 5. LIGHT SCANS might occur even if you don't want them to. That happens when a scan first fills up the BUFFERS, then fills up the PDQ and then finally does LIGHT SCANS. Please help: I do not know everything about light scans. But I want to! So please add your comments, corrections, etc.