Know Db2

Db2 11 for z/OS - Migration for SQL Users and Application Programmers

Migration for SQL Users and Application Programmers hands-on workshop is designed to take the student through new advanced SQL coding techniques, and new functions and features in Db2 11 for z/OS.

Duration:3 day, Lecture, Hands-on remote labs

Audience: Application Programmers, DBAs and SQL Users.

Migration for SQL Users and Application Programmers

Class Outline:

SQL Enhancements

  • Global Variables
  • -Writing and Reading
  • -Creating Global Variables
  • Aliases for Sequences
  • Temporal Data Enhancements
  • -Special Registers
  • -Session Level Special Registers
  • -BUSTIMESENSITIVE
  • -SYSTIMESENSITIVE
  •  
  •  

Performance Enhancements

  • Benefits of REBIND
  • Query Rewrite
  • Indexability Improvements
  • Logging Enhancements
  • Latching contention relief
  • Dynamic Prefetch Enhancement
  • I/O Parallelism for Index Updates
  • Mass INSERT improvements
  • Bind / DBRMs in zFS input files
  • SET CURRENT APPLICATION COMPATIBILITY
  • Temporal Special Registers
  • RESET ACCESSPATH
  • DROP COLUMN
  • -Restrictions
  • -Impact on Catalog Tables
  • -Impact on Application Programs
  • LIKE _ BLANK _ INSIGNIFICANT
  • CREATE ALIAS on Sequences
  • Hands-on LAB
  •  
  •  

Native SQL PL Procedure

  • External vs. Native
  • Array Data Type Support
  • CREATE TYPE - an array type
  • Ordinary Arrays
  • Associative Arrays
  • ARRAY_EXISTS predicate
  • Creating a Native SQL Routine with Array Arguments
  • CREATE FUNCTION (SQL Scalar)
  • CREATE PROCEDURE (native)
  • DECLARE Ordinary and Associative arrays
  • Assigning values to Arrays
  • Building Array from Table Data
  • ARRAY_AGG function
  • SELECT from array
  • ARRAY_DELETE scalar function
  •  

Native SQL PL Procedure (cont.)

  • New Built-In Functions
  • - ARRAY _ AGG
  • - ARRAY _ FIRST
  • - ARRAY _ LAST
  • - ARRAY _ NEXT
  • - ARRAY _ PRIOR
  • - CARDINALITY
  • - MAX _ CARDINALITY
  • - TRIM _ ARRAY
  • - UNNEST
  • - MERGE and arrays
  • Work File Enhancements
  • - WFSTGUSE _ AGENT _ THRESHOLD
  • - WFSTGUSE _ SYSTEM _ THRESHOLD
  • DGTT – logging behavior
  • Transparent Archiving of Temporal Tables
  • GROUP BY
  • ROLLUP
  • CUBE
  • GROUPING SETS
  • SUPER GROUPS
  • Grand Total
  • Expressions
  • Page Range Screening
  • Has Join & Sparse Index
  • Hands-on LAB
  •  

Controlling Application Compatibility

  • PLAN MANAGEMENT
  • APPLCOMPAT DSNZPARM static SQL
  • BIND/REBIND OPTION
  • CURRENT APPLICATION COMPATIBILITY dynamic SQL
  • SET CURRENT APPLICATION COMPATIBILITY
  • DSN _ PROFILE _ ATTRIBUTES for IBM DRDA applications
  • RUNSTATS Enhancements
  • Optimizer Feedback
  • RELEASE (DEALLOCATE)
  • Hands-on LAB
  •  

Db2 and Big Data

  • Access data in Hadoop
  • IBM Big Insights
  • Review
  •  

Autonomous Transactions

  • Native SQL Procedures
  • Keyword AUTONOMOUS
  • CREATE & DISPLAY keywords
  • Hands-on LAB
  •  

Class Summary

  • ©2017 Computer Business International, Inc.
  •  

Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at info@cbi4you.com.