Netezza Architecture Training

Course Number: NET101-W

Duration: 1 day

Format: Live, online

Overview

In this training class, students will learn the Netezza Architecture starting at the most basic level and going to the most advanced level with many examples.

Objectives

  • Learn to gain a deeper knowledge and understanding of the Netezza Architecture and how to tune it.

Attendee Setup Instructions

For details about what is required when attending this class, please refer to the these instructions (will open in a seperate browser window).

Outline

  • How Netezza Works
    • What is Parallel Processing?
    • The Basics of a Single Computer
    • Netezza Parallel Processes Data
    • Netezza is born to be parallel
    • Starts with a Linux User, a Database User and a Database
    • Each SPU holds a Portion of Every Table
    • The Rows of a Table are spread across All SPUs
    • The Brilliance of Netezza
    • Compress Engine II - Adaptive Stream Compression
    • FPGA Card and Zone Maps - The Netezza Secret Weapon
    • How Data Might Look Like on a SPU
    • Netezza has Linear Scalability
    • The Netezza Architecture
    • The Host
    • Netezza Disks
    • There Are Three Options for Backup and Restore
    • The IBM Netezza Family
  • A Chip off The Old Block
    • Netezza Stores Data in Tables
    • Each SPU is Assigned Specific Rows
    • Each SPU Organizes the Rows inside a Data Block (Extent)
    • SPUs Must Transfer Their Data Blocks to Memory
    • As Tables Get Bigger the SPU uses Multiple Extents
    • SPUs Process A Table One Block at a Time
    • The Slowest Processing is a Full Table Scan
    • The FPGA Card and the Zone Maps Eliminate Extents
    • The FPGA Card and the Zone Map Enlightenment
    • Netezza Systems Can Grow Forever
  • How Netezza Distributes the Data
    • Netezza Tables - Distribution Key or Random Distribution
    • Table CREATE Examples with 4 different Distribution Keys
    • Netezza Uses a Hash Formula
    • The Hash Map determines which SPU will own the Row
    • The Hash Formula, Hash Map and SPU
    • Placing rows on the SPU
    • Placing rows on the SPU Continued
    • A Review of the Hashing Process
    • Like Data Hashes to the Same SPU
    • Distribution Keys
    • Distribution Key in WHERE Clause - 1 SPU Retrieve
    • A Non-Unique Distribution Key
    • Distribution Key in the WHERE Clause - 1 SPU Retrieve
    • A conceptual example of a Multi-Column Distribution Key
    • Distribution Key in the WHERE Clause - 1 SPU Retrieve
    • A conceptual example of a Table with Random Distribution
    • A Full Table Scan - or a Sequential Scan
    • What happens when you forget the Distribution Key?
    • Fully Qualifying an Object
    • Checking for Skew
    • Educate the Business on the Business by Sharing the Model
    • Load Your Models and have the SQL Built Automatically
    • Netezza is Massively Parallel
  • Deep Dive inside a Netezza Extent and Row
    • Netezza Performance - Three Things to Keep in Mind
    • How Netezza Allocates Data Storage
    • Extents and Zone Maps
    • How Data Might Look in an Extent
    • Why Dates Are Generally Not Good Distribution Keys
    • When a Table is created, a Table Header is created
    • Every SPU has the Exact Same Tables
    • All Netezza Tables are spread across All SPUs
    • The Table Header and the Data Rows are Stored Separately
    • A SPU Stores Rows of a Table inside a Data Block (Extent)
    • To Read Rows, a SPU Moves the Data Block into Memory
    • A Full Table Scan Means All SPUs must Read All Rows
    • The "Achilles Heel", or Slowest Process, is Block Transfer
    • Each Table has a Distribution Key
    • A Query Using the Distribution Key uses a Single SPU.
    • As Rows are Added, Another Extent is added
    • A Full Table Scan Means All SPUs Read All Blocks
    • Distribution Key Query uses One SPU
    • Using a CTAS to Improve Zone Map Selectivity
    • How A CTAS with ORDER BY Improves Queries
    • Each SPU Can Have Many Blocks for a Single Table
    • A Full Table Scan Means All SPUs Read All Blocks
    • Netezza Rowid, CreateXid, DeleteXid
    • An Update of Multiple Rows
    • How to Undo an Update of Multiple Rows
    • An Update Undo in Action
    • A Delete Example
    • A Delete Example Query
    • How to Undo a Delete
    • How to Undo a Delete in Action
    • An Insert Example
    • An Insert Example Query
    • How to Undo an Insert
    • How to Undo an Insert in Action
    • What is the Purpose of the GROOM Command?
    • The Groom Command Refreshes Zone Maps
    • Groom Command Syntax
    • Checking Groom Progress
    • Automatic Database Statistics
    • Drop Table, Truncate and Delete Compared
  • How Joins Work Internally
    • Redistribution
    • Duplication of the Smaller Table across All-SPUs
    • If the Join Condition is the Distribution Key no Movement
    • Matching Rows landed on SPU because of Distribution Keys
    • What if the Join Condition Columns are Not Distribution Keys?
    • When Rows are on the same SPU they can be joined
    • A Visual of the Join in Action
    • The Joining of Two Tables
    • Netezza Moves Joining Rows to the Same SPU
    • Imagine Joining Two Random Distribution Tables
    • Both Tables are redistributed to Join Rows on the Same SPU
    • How do you join if One Table is Big and One Table is small?
    • Duplicate the Small Table on Every SPU (like a mirror)
    • What Could You Do If Two Tables Joined 1000 Times a Day?
    • Joining Two Tables with the same PK/FK Primary Index
    • A Join with No Redistribution or Duplication
  • CTAS and CBT
    • CTAS (Create Table AS)
    • Using the CTAS (Create Table AS) Table for Co-Location
    • CTAS Facts
    • Altering a CTAS Table to Rename It
    • FPGA Card and Zone Maps - The Netezza Secret Weapon
    • How A CTAS with ORDER BY Improves Queries
    • A CTAS Major Sort Benefits over the Minor Sort
    • A CBT (Cluster Based Table) Orders Data without Precedence
    • A CBT (Cluster Based Table) in Theory
    • Creating a Cluster Based Table (CBT
    • Creating a Temp Table as a Cluster Based Table (CBT)
    • Comparing Extents That Are Sorted Vs. A CBT
    • Benefits of A Cluster Based Table (CBT)
    • Altering a Cluster Based Table (CBT) back to a Normal Table
    • GROOM Command is used to Physically Change the Table
    • After Creating a CBT, You Must GROOM the Table
    • What the GROOM Does for a Table
    • Groom Command Syntax
    • Checking Groom Progress
    • How to know if your CBT Table needs to be GROOMED?
  • Temporary Tables
    • There are Three Types of Temporary Tables
    • The Same Derived Query shown Three Different Ways
    • Most Derived Tables Are Used To Join To Other Tables
    • Our Join Example with a Different Column Aliasing Style
    • Our Join Example With the WITH Syntax
    • Syntax for Creating a Temporary Table
    • Creating and Populating a Temporary Table
    • A Temporary Table in Action
    • A Temporary Table Can Be Used Again and Again
    • Alternative CREATE TEMPORARY TABLE Option
    • A CTAS Temp Table to Improve Zone Map Selectivity
    • Creating a Temp Table as a Cluster Based Table (CBT)
    • What Are External Tables?
    • External Tables Data Loading Formats
    • External Table Log Files
    • External Table Syntax
    • Exporting Data Off of Netezza into an External Table
    • Importing Data into Netezza Using an External Table
    • What is the Problem Here?
  • Materialized Views
    • A Materialized View
    • Good Information to know about Materialized Views
    • Syntax/Example to Create a Materialized View
    • Replacing a Materialized View
    • Zone Maps for Materialized Views
    • Materialized View Restrictions
    • Maintaining Materialized Views
    • Materialized View Best Practices
  • Collecting Statistics
    • The Basics on Collecting Statistics
    • Best Practices for Generating Statistics
    • Syntax to Collect Statistics
    • Syntax to Collect Express Statistics
    • The Basics on Collecting Statistics
    • Collecting Full Statistics
    • Just-In-Time (JIT) Statistics
    • How Netezza Collects Statistics on Small Tables
    • How Netezza Collects Statistics on Medium Tables
    • How Netezza Collects Statistics on Large Tables
    • Generating Statistics using NzAdmin
    • You Cannot Generate Statistics within a Begin-End Block
  • Using nzsql
    • Using nzsql
    • The nzsql Command Prompt
    • Exporting Variable and then using nzsql
    • Connecting to another database through nzsql
    • Displaying SQL User Session Variables
    • Inserts, Updates and Deletes Show the Number of Rows
    • Running a SQL Query from the nzsql Command Line
    • Nzsql Options That Might Come In Handy
    • Nzsql Internal Slash Options
    • Nzsql for External Tables
    • Why Would Anyone Use nzsql When They Can Use Nexus?
    • The Nexus Super Join Builder Shows Tables Visually
    • The Nexus Super Join Builder Builds the SQL Automatically
  • Creating Tables
    • CREATE TABLE Syntax
    • Viewing the DDL
    • Netezza Tables - Distribution Key or Random Distribution
    • Table CREATE Examples with 4 different Distribution Keys
    • The Worst Mistake You Can Make For a Distribution Key
    • Good things to know about Table and Object Names
    • Netezza Data Types
    • Netezza Data Types in More Detail
    • Netezza Data Type Extensions
    • Reserved Names within A Table
    • How to Query and See Non-Active Rows
    • Column Attributes
    • Constraints
    • Constraints
    • Column Level Constraint Example
    • Defining Constraints at the Table Level
    • Utilizing Default Values for a Table
    • CTAS (Create Table AS)
    • CTAS Facts
    • Using the CTAS (Create Table AS) Table for Co-Location
    • Altering a CTAS Table to Rename It
    • FPGA Card and Zone Maps - The Netezza Secret Weapon
    • How A CTAS with ORDER BY Improves Queries
    • A CTAS Major Sort Benefits over the Minor Sort
    • Altering a Table
    • Altering a Table Examples
    • Drop Table, Truncate, and Delete Compared
  • Creating Databases and Users and Managing Them
    • Creating and Dropping a Netezza Database
    • How to Determine the Database you are in?
    • Netezza Users
    • Altering a Netezza User
    • Reserved Words to find out about a User
    • Using Limit to bring back a Sample
    • The Super-User is Named Admin
    • Starts with a Linux User, a Database User and a Database
    • Creating and Managing a Database
    • Creating a User
    • CREATE USER Syntax
    • Forcing a Password Change for a User
    • Reserved Words to find out about a User
    • Altering a Netezza User
    • How to Determine the Database you are in?
    • Fully Qualifying a Database, Schema, and Table
    • Options for Handling Invalid Schema Names
    • An Example of Setting enable_schema_dbo_check
    • Creating and Managing a Group
    • Two Types of Permissions - Object and Admin
    • Netezza SQL Maximums
    • Admin Permissions
    • Object Permissions
    • Granting Object Permissions
    • Granting Admin Permissions
    • Table Permission Examples
    • Querying Cross-Database
    • Creating and Managing Synonyms
    • Creating a Table with Comments
    • Inserting Rows in a Table
  • Systems Views
    • _v_aggregate
    • _v_database
    • _v_datatype
    • _v_function
    • _v_group
    • _v_groupusers
    • _v_operator
    • _v_procedure
    • _v_relation_column_def
    • _v_relation_keydata
    • _v_sequence
    • _v_session
    • _v_table
    • _v_table_dist_map
    • _v_user
    • _v_usergroups
    • _v_view
    • (System Administrators Only) _v_sys_index
    • (System Administrators Only) _v_sys_priv
    • (System Administrators Only) _v_sys_table
    • (System Administrators Only) _v_sys_user_priv
    • (System Administrators Only) _v_sys_view
    • Explains
    • EXPLAIN
    • EXPLAIN Terms
    • EXPLAIN Terms Continued
    • EXPLAIN Syntax
    • EXPLAIN Example
    • EXPLAIN Verbose Example
    • EXPLAIN Example for a Join
    • EXPLAIN Verbose Example for a Join
    • Good Advice - Join Tables by the Same Distribution Key
    • EXPLAIN Verbose - Join With Matching Distribution Keys
    • EXPLAIN DISTRIBUTION
    • EXPLAIN PLANTEXT Example
    • EXPLAIN PLANGRAPH Example

Schedule

No public classes currenly scheduled. Please contact us about the possibility of a scheduling a private class.

No public classes currenly scheduled. Please contact us about the possibility of a scheduling a private class.

Learn faster

Our live, instructor-led lectures are far more effective than pre-recorded classes

Satisfaction guarantee

If your team is not 100% satisfied with your training, we do what's necessary to make it right

Learn online from anywhere

Whether you are at home or in the office, we make learning interactive and engaging

Multiple Payment Options

We accept check, ACH/EFT, major credit cards, and most purchase orders



Recent Training Locations

Alabama

Birmingham

Huntsville

Montgomery

Alaska

Anchorage

Arizona

Phoenix

Tucson

Arkansas

Fayetteville

Little Rock

California

Los Angeles

Oakland

Orange County

Sacramento

San Diego

San Francisco

San Jose

Colorado

Boulder

Colorado Springs

Denver

Connecticut

Hartford

DC

Washington

Florida

Fort Lauderdale

Jacksonville

Miami

Orlando

Tampa

Georgia

Atlanta

Augusta

Savannah

Hawaii

Honolulu

Idaho

Boise

Illinois

Chicago

Indiana

Indianapolis

Iowa

Cedar Rapids

Des Moines

Kansas

Wichita

Kentucky

Lexington

Louisville

Louisiana

New Orleans

Maine

Portland

Maryland

Annapolis

Baltimore

Frederick

Hagerstown

Massachusetts

Boston

Cambridge

Springfield

Michigan

Ann Arbor

Detroit

Grand Rapids

Minnesota

Minneapolis

Saint Paul

Mississippi

Jackson

Missouri

Kansas City

St. Louis

Nebraska

Lincoln

Omaha

Nevada

Las Vegas

Reno

New Jersey

Princeton

New Mexico

Albuquerque

New York

Albany

Buffalo

New York City

White Plains

North Carolina

Charlotte

Durham

Raleigh

Ohio

Akron

Canton

Cincinnati

Cleveland

Columbus

Dayton

Oklahoma

Oklahoma City

Tulsa

Oregon

Portland

Pennsylvania

Philadelphia

Pittsburgh

Rhode Island

Providence

South Carolina

Charleston

Columbia

Greenville

Tennessee

Knoxville

Memphis

Nashville

Texas

Austin

Dallas

El Paso

Houston

San Antonio

Utah

Salt Lake City

Virginia

Alexandria

Arlington

Norfolk

Richmond

Washington

Seattle

Tacoma

West Virginia

Charleston

Wisconsin

Madison

Milwaukee

Alberta

Calgary

Edmonton

British Columbia

Vancouver

Manitoba

Winnipeg

Nova Scotia

Halifax

Ontario

Ottawa

Toronto

Quebec

Montreal

Puerto Rico

San Juan