Some New Features of DB2 v7

1
Some New Features of DB2 v7

• Pam Odden

2
Objectives

• To try out some new SQL supported in DB2 v7
• Order by using an expression
• Using a union in a view
• Self-referencing subselect in a searched update
  or delete
• Declared temporary tables
• Fetch first n rows only

3
ORDER BY Using an Expression

• A new option, ORDER BY expression, lets you
  specify an expression with operators as the sort
  key for the result table of a SELECT statement
• SELECT FIRSTNAME, CUMMGPA1, CUMMGPA1 100 FROM
  SSASIDB1.ASTU_STUDENT
• WHERE SCHOOLNUM '945'
• AND CUMMGPA1 gt 0
• ORDER BY CUMMGPA1 100
• Result in v5
• ---------------------------------------------
  ---------------------------
• DSNT408I SQLCODE -104, ERROR ILLEGAL SYMBOL
  "". SOME SYMBOLS THAT MIGHT BE
• LEGAL ARE , FOR WITH OPTIMIZE
• Result in v7
• FIRSTNAME CUMMGPA1
  
• ---------------------------------------------
  ---------------------------
• MEAGAN JEAN .0556
  5.5600

4
Using a UNION in a View

• Version 7 adds support for the UNION and UNION
  ALL operators in a view definition.
• This enhancement increases programmer
  productivity by allowing complex unions in views,
  rather than in each cursor.
• SELECT MAX(EFFDATE) FROM
  
• ( SELECT PERMNUM, ENTERCODE, LEAVECODE,
  EFFDATE
• FROM SSASIDB1.AENR_HISTORY
  
• WHERE PERMNUM ' 400004'
  
• UNION
  
• SELECT PERMNUM, ENTERCODE, LEAVECODE,
  ENTERDATE AS EFFDATE
• FROM SSASIDB1.ASTU_STUDENT
  
• WHERE PERMNUM ' 400004' )
  
• AS STUD_HISTORY
• Result in v5
• DSNT408I SQLCODE -199, ERROR ILLEGAL USE OF
  KEYWORD UNION, TOKEN ) WAS EXPECTED
• Result in v7
• ---------------------------------------------
  ------------
• 08/25/2003

5
Self-referencing Subselect in Searched Update

• In previous releases of DB2, in a searched UPDATE
  and DELETE statement, the WHERE clause cannot
  refer to the object being modified by the
  statement. Version 7 removes the restriction for
  the searched UPDATE and DELETE statements, but
  not for the positioned UPDATE and DELETE
  statements.
• UPDATE PJO.DEMO2
  
• SET CREDEARNED CREDEARNED 10
  
• WHERE ENTERDATE '01/28/2002'
  
• AND CREDEARNED lt
  
• ( SELECT AVG(CREDEARNED) FROM PJO.DEMO2 )
  
• Result in v5
• DSNT408I SQLCODE -118, ERROR THE OBJECT TABLE
  OR VIEW OF THE INSERT, DELETE, OR UPDATE
  STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE
• Result in v7
• ---------------------------------------------
  ---------
• DSNE615I NUMBER OF ROWS AFFECTED IS 82
  
• DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL,
  SQLCODE IS 0

6
Declared Temporary Table

• Declare in one of three ways
• Specify the columns
• Use a LIKE clause to copy the definition of a
  base table, created temporary table, or view.
• Use a select statement to choose specific columns
  from a base table, created temporary table, or
  view.
• Populate the declared temporary table using
  INSERT statements, modify the table using
  searched or positioned UPDATE or DELETE
  statements, and query the table using SELECT
  statements.
• You can also create indexes on the declared
  temporary table.
• The qualifier, SESSION, must be specified in any
  statement that references the table

7
FETCH FIRST n ROWS ONLY Clause

• This clause actually limits the number of rows
  that can be fetched.
• An attempt to fetch n 1 rows is handled the
  same way as normal end of data, with SQL code
  100.
• This improves the performance of queries with
  potentially large result sets when only a limited
  number of rows are needed.
• This could be used as in the example below to
  fill an online screen
• SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
• FROM EMPLOYEE
• WHERE EMPNO gt HV-LAST-EMPNO
• ORDER BY SALARY DESC
• FETCH FIRST 20 ROWS ONLY
• You can specify FETCH FIRST ROW ONLY on a SELECT
  INTO statement when the query can return more
  than one row in the answer set. This tells DB2
  that you are only interested in the first row,
  and allows a singleton select instead of a cursor.

8
FETCH FIRST n ROWS ONLY Clause

• CURSOR TO RETRIEVE FIRST N ROWS
• EXEC SQL
• DECLARE CURS-FIRST-ROWS CURSOR FOR
• SELECT FIRSTNAME,
• ENTERCODE,
• ENTERDATE,
• GRADE
• FROM PJO.DEMO2
• WHERE ENTERDATE '01/28/2002'
• FETCH FIRST 20 ROWS ONLY
• FETCH FIRST ROW ONLY
• END-EXEC.
• Run with FETCH FIRST clauses commented out
• ROWS FETCHED 000000105
• Run with FETCH FIRST 20 ROWS ONLY
• ROWS FETCHED 000000020
• Run with FETCH FIRST ROW ONLY
• ROWS FETCHED 000000001

9
Schedule

• During the next 2 weeks, use the following
  STEPLIB statements to run IKJEFTIA (TSO terminal
  monitor program), DSN1COPY (to restore TSN or BSN
  from a DSN image copy), or batch spufi commands
• //STRTU00 EXEC PGMIKJEFT1A,DYNAMNBR20,COND(4,LT
  )
• //STEPLIB DD DSNSYS2.DSN710.SDSNLOAD,DISPSHR
  
• // DD DSNSYS2.DSN710.TEST.SDSNEXIT,DISP
  SHR
• Continue to use TSO logon procs TSODB2T and
  TSODB2B when you want to use SPUFI or other DB2
  online
• New version of PRF will be installed, meanwhile
  old version can be used in TSN/BSN using new
  logon procs
• New File-AID for DB2 and Expeditor have been
  ordered