IBM Systems Magazine, Mainframe - March/April 2017 - 51
the DB2 family of Linux*, UNIX* and Windows*. DB2 now fully supports and allows a table to be used as input, multiple MATCHED clauses, additional predicates with [NOT]MATCHED, the DELETE operation, and IGNORE and SIGNAL. SQL pagination dealing with complex OR statements was first introduced in DB2 V10. SQL pagination now allows for numeric-based and data-dependent pagination. An example of the numeric-based pagination is: absolute page number, used in prior releases of DB2 for PBR data sets. RPN data sets remove the dependency between the number of partitions and partition size. Part number is stored in the partition header page, while page number is stored in the data page, relative to start of the partition. With RPN, DSSIZE maximum of 256 GB per data set (partition) increases to 1 TB. Maximum table size increases from 128 TB for a 32K object to 4 PB. Maximum number of rows for 4K SELECT * FROM tab OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY An example of data-dependent pagination is: (E)JES Web: Raising the Bar and Your Bottom Line WHERE (LASTNAME = 'SMITH' AND FIRSTNAME >= 'JOHN') OR (LASTNAME > 'SMITH') Wednesday, April 5 | 10 PT / Noon CT / 1 ET With the new equivalent syntax: WHERE (LASTNAME, FIRSTNAME) > (SMITH, JOHN) DELETE statements, such as DELETE FROM T1 WHERE C1 > 7, could delete millions of rows, affecting locking and logging. Piecewise data modification allows for deletion of fewer rows as shown where a maximum of 5,000 rows are deleted: DELETE FROM T1 WHERE C1 > 7 FETCH FIRST 5000 ROWS ONLY; COMMIT; At times, DB2 clients are frustrated by the partition by range (PBR) data set maximum size and other limitations. PBR relative page number (RPN) is a new type of table space that lifts the limitations. The new RPN is mutually exclusive with Easy Enough for New Users; Fast Enough for Experts FEATURING: In this complimentary webinar, you will: 0Á'(Á##Á$*'&*1'+Á$(ÁÂÃ Á"'$ÄÁ#Á$&$+''&Á1#('&(#%'Á1#($ÁÂÃ ÄÁ#Á'-'('"Á"###)'"'#(Á ($$!Á ($&Á "$#1($&1#)ÄÁ "###)1#)Á ##&Á %$#(&$!!1#)Á-$)&Á.Ç Á !',Æ 0Á'#&#Á0$+ÁÂÃ Á"'$Á)'''Á(&1'&Å##&Å(&)'Á +'$Á&'*'!$%"'#(Á%&#%(1%''Á($Á%&$*1&'Á##Á 1#()1(1*'ÄÁ)#%$"%!1%#('&Á1#('&(#%'Æ Ed Jaffe Chief Technology Officer Phoenix Software International 0Á ''Á#Á&'"$Á$(ÁÂÃ Á"'$Á('#()&''Á')%0Á #'Á Å!1 'Á ÉÄÁ #&*##%'&Á /!('&1#)ÄÁ ##&Á (',(Á$&ÁÉÁ&$+#!$#&'Æ ')1'('&Á!$&#-ÈÁ+'$%#'('Æ%$"Ç1$"'-'('"'"#) sponsored advertising content ibmsystemsmag.com MARCH/APRIL 2017 // 51 pg 50-53.indd 3 2/7/17 2:12 PM