title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Netezza Architecture
Duration
1 Day
Course
NETZ-105
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Netezza Architecture course is designed to provide students with knowledge of the Netezza Data Warehouse Architecture (Basics).Objectives
Upon completion of the Netezza Architecture course, students will be able to:
- Understand the architecture of Netezza
- Create tables
- Create performance tune queries
- Manage and design a Netezza system
Audience
- IT Professionals
Prerequisites
- None
Topics
- 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
- Question – How Many Blocks Move Into Memory?
- Answer – How Many Blocks Move Into Memory?
- Quiz – Master that Query with the Zone Map
- Answer to Quiz – Master that Query with the Zone Map
- Netezza has Linear Scalability
- The Netezza Architecture
- The Host
- Netezza Disks
- There Are Three Options for Backup and Restore
- The IBM Netezza Family
- 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
- 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
- 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 0
- 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
- Quiz – How Many Blocks Move into SPU Memory?
- Answer – How Many Blocks Move into SPU Memory?
- 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
- Netezza Join Quiz
- Netezza Join Quiz Answer
- 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?
- Quiz – Redistribute the Employees by their Dept_No
- Quiz – Employees' Dept_No landed on SPU with Matches
- When Rows are on the same SPU they can be joined
- Quiz – Redistribute the Orders to the Proper SPU
- Answer to Redistribute the Employees by their Dept_No Quiz
- 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 1
- 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 6
- A Join with No Redistribution or Duplication
- 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 8
- Creating a Cluster Based Table (CBT9
- 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
- Groom Command Syntax
- Checking Groom Progress
- How to know if your CBT Table Needs to be GROOMED?
- 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 1
- 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?
- A Materialized View
- Good Information to know about Materialized Views
- Syntax/Example to Create a Materialized View 5
- Replacing a Materialized View
- Zone Maps for Materialized Views
- Materialized View Restrictions
- Maintaining Materialized Views
- Materialized View Best Practices
- 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
- 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
- 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
- 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 2
- 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
- _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 4
- _v_table
- _v_table_dist_map
- _v_user
- _v_usergroups
- _v_view
- _v_sys_index (System Administrators Only)
- _v_sys_priv 1 (System Administrators Only)
- _v_sys_table 2 (System Administrators Only)
- _v_sys_user_priv 93 (System Administrators Only)
- _v_sys_view (System Administrators Only)
- 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
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.