Wednesday, August 8, 2012

Alexander's Sword: UNLOAD SELECT



Question: How do I compare the before-and-after result sets from two different versions of a SELECT statement? I want to verify that performance improvements haven't introduced any errors.

Hard Answer: Struggle with a complex query involving both result sets and a fancy FROM clause, perhaps involving a FULL OUTER JOIN, or...

Easy Answer: Dump the result sets to text files and do a file compare.

"Dump" as in UNLOAD SELECT, and "file compare" as in comp or fc or whatever utility program floats your boat:

UNLOAD [select-1] TO 'filespec-1';

UNLOAD [select-2] TO 'filespec-2';

COMP filespec-1 filespec-1

-- or --

FC filespec-1 filespec-1
UNLOAD
SELECT *
  FROM SYSTAB KEY JOIN SYSTABCOL
 ORDER BY SYSTAB.table_id, 
       SYSTABCOL.column_id
TO 'a.txt';

UNLOAD
SELECT *
  FROM SYSTAB INNER JOIN SYSTABCOL
          ON SYSTAB.table_id = SYSTABCOL.table_id
 ORDER BY SYSTAB.table_id, 
       SYSTABCOL.column_id
TO 'b.txt';

COMP a.txt b.txt

C:\projects\$SA_templates>COMP a.txt b.txt
Comparing a.txt and b.txt...
Files compare OK
Compare more files (Y/N) ?

FC a.txt b.txt

C:\projects\$SA_templates>FC a.txt b.txt
Comparing files a.txt and B.TXT
FC: no differences encountered



No comments: