Index example: Ordering and grouping on the same columns with ALWCPYDTA(*OPTIMIZE) and a unique-weight sort sequence table

Ordering and grouping on the same columns with ALWCPYDTA(*OPTIMIZE) and a unique-weight sort sequence table (SRTSEQ(*LANGIDUNQ) LANGID(ENU)).

  SELECT JOB, SALARY FROM STAFF
   GROUP BY JOB, SALARY
   ORDER BY JOB, SALARY

When using the OPNQRYF command, specify:

OPNQRYF FILE((STAFF)) FORMAT(FORMAT3)
   GRPFLD(JOB SALARY)
   KEYFLD(JOB SALARY)
   SRTSEQ(*LANGIDUNQ) LANGID(ENU)
   ALWCPYDTA(*OPTIMIZE)

DB2 UDB for AS/400 could use UNQIX2 to satisfy both the grouping and ordering requirements. If index UNQIX2 did not exist, DB2 UDB for AS/400 would either:

• Create an index using a sort sequence table of *LANGIDUNQ or
• Use index HEXIX2 to satisfy the grouping and to perform a sort to satisfy the ordering