Database Developer Training

INTRODUCTION TO TERADATA

• Architecture
• Comparative Study between Teradata an Other RDBMS
• Database Components and function
• Parallel Architecture and processing the table
• Factor affecting data storage and distribution
• RDBMS & Normalization { 1st – 3rd Normal form }
• Data-warehouse Concepts { Surrogate Keys , SCD etc}
• Administrative tools

TERADATA SQL

• Data Dictionary
• Primary keys, Foreign keys, Primary Indexes, and Secondary Indexes in a Teradata system
• Advance SQL
• SQL Performance Tuning

Teradata Application Utilities

• BTEQ (Export/Import), FastExport, FastLoad, MultiLoad and Tpump for batch processing
• Internal working of above utilities
• Using utilities – Choose above utilities when and why
• TPT and its advantage over traditional utilities like FastLoad, MultiLoad etc.

Overview of IBM Informix Dynamic Server

• IBM Informix Dynamic Server
• System Architecture
• Informix Components

Preparing for Installation & Configuration

• Configuring the Operating System
• Configuring Windows Memory
• Modifying UNIX Kernel Parameters
• Allocating Disk Space
• Creating Chunk Files on UNIX
• Raw or Unbuffered Disk Access
• Cooked Files
• Setting Permissions, Ownership, and Group
• Creating Standard Device Names
• Setting Environment Variables
• Setting Environment Variables on UNIX
• Setting Environment Variables on Windows
• Configuring Connectivity
• The sqlhosts File on UNIX
• Network-Configuration Files
• Network-Security Files
• The sqlhosts Registry on Windows
• Configuring Connectivity Using ISA

Configuring Client-Server connectivity

• Client/Server Architecture
• Network Protocol
• Network Programming Interface
• Connections That the Database Server Supports
• Local Connections
• Shared-Memory Connections
• Connectivity Files
• Network-Configuration Files
• TCP/IP Connectivity Files
• Multiple TCP/IP Ports

Configuring a Database Server

• Preparing space for data storage
• Raw Chunks
• Cooked Chunks
• Setting Environment variables
• Sqlhosts file
• Onconfig file
• Server initialization
• Server status
• Server Message Log

Monitoring Database Server

• Monitoring Utilities
• SMI
• Sysmaster database
• How SMI works
• Sysmaster tables and Views
• Onstat sysntax
• Onstat Options
• Oncheck syntax
• ISA

Managing Shared Memory

• Server Operating Modes
• Normal Server Processes
• Setting Database Server Shared-Memory Parameters
• Setting Parameters for Resident
• Setting Parameters for Virtual Shared
• Setting Parameters for Shared-
• Setting Shared-Memory Parameters
• Setting Shared-Memory Parameters
• Setting Shared-Memory Parameters
• Reinitializing Shared Memory
• Changing Server Modes
• UNIX Level Shared Memory

Virtual Processors and Threads

• What Is a Virtual Processor?
• What Is a Thread?
• What Is a User Thread?
• Types of Virtual Processors
• Advantages of Virtual Processors
• Sharing Processing
• Saving Memory and Resources
• Processing in Parallel
• Adding and Dropping Virtual Processors
• On-Line Mode
• Binding Virtual Processors to CPUs

Disk Architecture

• Physical Disk Space
• Logical Disk Space
• Database
• DBspace
• Tables
• Extents
• Pages
• Logical Log Files
• Physical Log Files

Managing and Monitoring Disk Space

• Creating a Dbspace with onspaces
• Creating a Blobspace with onspaces
• Dropping Spaces with onspaces
• Adding a chunk to dbspace
• Dropping a chunk from dbspace
• Renaming Dbspace
• Managing Spaces with ISA

Physical and Logical Logging

• Log Initialization
• Changing the Physical Log
• Adding a Logical Log
• Dropping a Logical Log
• Switching Logical Log
• Changing Database Logging Modes
• Monitoring Logs
• Long Transactions
• Dynamic Allocation of Locks

Introduction to Backup and Restore

• What Is a Recovery System?
• What Is a Backup?
• What Is a Logical-Log Backup?
• When You Do Not Use Logging . . . . . . . . .
• What Are Manual and Continuous Logical-Log Backups?
• What Is a Log Salvage?
• Why You Need to Save Logical-Log Backups
• What Is a Restore?
• What Are Warm, Cold, and Mixed Restores?
• What Are Physical and Logical Restores?
• Comparing ON-Bar and ontape
• Planning a Recovery Strategy
• What Types of Data Loss Can Occur?
• Scheduling Backups
• Planning a Backup System for a Production Database Server
• Evaluating Hardware and Memory Resources
• Evaluating Backup and Restore Time

Backup and Restore methods

• Physical Backup Methods
• Logical Backup Methods

User activity

• Monitoring User Activity
• Session Information
• SQL Information
• Deadlocks
• Monitoring Locks
• Table Locks
• Row Lock

Performance Features

• Parallel Data Query
• Read-Ahead
• SQL Cache
• ONCONFIG Parameters
• ENV Variables

Troubleshooting

• Common Problems
• Chunk Permissions and Ownership
• Shared Memory Allocation
• Long Transactions
• Freeing Logs
• Consistency Check errors

Introduction

• DB Concepts
• Transact – Structured Query Language (T-SQL)
• Data Definition Language
• Data Manipulation Language
• Data Control Language
• Identifiers
• T-SQL Extensions

Data Definition Language (DDL)

• CREATE
• ALTER
• DROP
• SQL Constraints
• Primary key
• Foreign key
• Unique
• Not null
• Check
• Default
• identity
• Data types
• Implicit conversions
• Explicit conversions

Data Manipulation Language

• Selecting data from table
• Eliminating duplicates
• Operators used
• Comparison operators
• Ranges
• Lists
• Where clause
• Matching patterns
• Aggregate functions
• Organizing query results into groups
• group by clause
• having clause
• null values and group by
• aggregates with and without group by
• Sorting query results
• order by clause
• Summarizing group of data (compute clause)
• Combining queries
• Union operator

Joins and Sub-queries

• Joins
• How joins works
• How joins are processed
• Types of Joins
• Equi-joins and natural joins
• Self joins
• Inner and Outer joins
• Inner joins
• Left outer joins
• Right outer joins
• How null values affect joins
• Which columns to join
• Sub-queries
• How sub-queries works
• Restrictions
• Sub-queries in update/insert/delete statement
• Types of sub-queries
• Correlated sub-queries

SQL Derived tables and views

• What is a derived table
• How SQL derived table works
• Partitions
• Views
• How view works
• Creating views
• Retrieve and modify data through views
• Dropping views

Indexes

• How indexes works
• Types of indexes
• Clustered index
• Non-clustered index
• Differences
• Creating indexes
• Using clustered and non-clustered indexes
• Index options
• Defaults
• Rules

Stored Procedures, Cursors and Triggers

• Control flow Language
• Local and Global variables
• How SP works
• Create and execute procedures
• Return values from SP
• System Stored procedures
• Cursors
• Triggers
• Using triggers to maintain referential integrity

Transactions

• How transaction works
• Consistency
• Recovery
• Transactions and its usage
• Transactions mode
• Using transaction with SP, cursors

Intro to MongoDB

• Concepts
• Scaling
• SQL and Complex Transactions
• Documents Overview
• Installing MongoDB (windows)
• Installing MongoDB (linux)
• JSON
• Dynamic Schema
• Mongoimport
• Cursors Introduction
• Query Language: Basic Concepts
• Query Language: Projection
• Query Language: Advantages of a Dynamic Schema
• Shell: Queries
• Sorting
• Query Language: Cursors

CRUD,ADMIN COMMANDS

• Insertion
• Update
• save() Command
• Partial Updates & Document Limits
• Removing Documents
• Multi Update
• Upsert
• Wire Protocol
• Bulk() Operations and Methods
• Common Commands
• db.runCommand()
• db.isMaster()
• db.serverStatus()
• db.currentOp() & db.killOp()
• collection.stats() & collection.drop()
• Review of Commands

Storage Engine

• Introduction
• Storage Engine: WiredTiger
• createIndex(), getIndexes() & dropIndex()
• Collection Scans
• Index Notes
• Unique Indexes
• Sparse Indexes
• TTL Indexes
• Geospatial Indexes
• Text Indexes
• Background Index Creation
• Explain Plans
• Covered Queries
• Read & Write Recap
• currentOp() & killOp()
• The Profiler
• mongostat and mongotop

Replication Overview

• Asynchronous Replication
• Statement-based vs. Binary Replication
• Replication Concepts
• Automatic Failover
• Recovery
• Starting Replica Sets
• Initiating a Replica
• Replica Set Status
• Replica Set Commands
• Reading & Writing
• Failover
• Read Preference
• Read Preference Options

Reconfiguring a Replica Set

• Arbiters
• Priority Options
• Hidden Option & Slave Delay
• Voting Options
• Applied Reconfiguration
• Write Concern Principles
• Examining the ‘w’ Parameter
• Write Concern Use Cases & Patterns
• Reexamining the Page View Counter Pattern
• wtimeout & Capacity Planning
• Replica Sets in a Single Datacenter
• Replica Sets in Multiple Datacenters
• Replica Sets and Storage Engine Considerations

Scalability

• Sharding & Data Distribution
• Replication with Sharding
• Chunks & Operations
• Sharding Processes
• Cluster Topology
• Running on localhost
• The Config Database
• Adding the Initial Shards
• Enable Sharding for a Collection
• Working with a Sharded Cluster
• Cardinality & Monotonic Shard Keys
• Shard Key Selection Example
• Process and Machine Layout
• Bulk Inserts and Pre-splitting

Backup and Restore

• Overview of Security
• Security continued: Authentication and Authorization
• SSL and Keyfiles
• Security and Clients
• Intra-cluster Security
• Overview of Backing Up
• Mongodump
• Filesystem Snapshotting
• Backing Up a Sharded Cluster
• Backup Strategies
• Additional Features of MongoDB
• GridFS

Overview (Tools,Cloud Manager,Bash Scripting)

• Tools -RoboMongo
• Cloud Manager (Configuring and Working with MMS, Automation)
• Bash Scripting (Basics)

Cassandra Installation and Configuration

• Course Map
• Objectives
• Cassandra Versions
• Steps to Install and Configure Cassandra on Ubuntu System
• Operating System Selection
• Machine Selection
• Preparing for Installation
• Setup Repository
• Install CassandraCheck the Installation
• Configuring Cassandra
• Configuration for a Single-Node Cluster
• Configuration for a Multi-Node and Multi-Datacenter Clusters
• Setup Property File
• Configuration for a Production Cluster
• Setup Gossiping Property File
• Starting Cassandra Services
• Connecting to Cassandra
• Installing on CentOS
• Demo-Installing and Configuring Cassandra on Ubuntu

Creation of Sample Application

• Database Design
• Sample Application RDBMS Design
• Sample Application Cassandra Design
• Application Code
• Creating Database
• Loading Schema
• Data Structures
• Setting Connections
• Population of database
• Application Features

Cassandra Data Model

• Advance Modelling
• Rules of Cassandra data modelling
• increasing data writes
• duplication
• reducing data reads
• modelling data around queries
• creating table for data queries

CQL

• Data Definition language(DDL) Statements
• Data Manipulation Language (DML)
• User permission
• Create and modify Users
• Capture CQL output to a file
• Import and export data
• CQL scripts from within CQL
• CQL Scripts from the command prompt