Features _ Functionality

Document Sample
Features _ Functionality Powered By Docstoc
					                             Dataupia™ Satori Server
                  Features and Functionality Training

Friday, September 02, 2011
Introduction – What Are We Doing Here?

• 1 day course
      • Features & Functionality of Satori Blade
      • Hands-on labs
• Goals
      • Integrating with Host Database
      • Working with a Dataupia Array
• Non-goals
      • Deploying a Dataupia Array
      • Supporting a Customer Deployment
      • Extensive Troubleshooting
• Logistics
      • Restroom, food, breaks, phones, etc.

2009 | Confidential -             1

•   Unit 1: Technical Overview
•   Unit 2: The Management Console -- Demo
•   Unit 3: Using the CLI – Demo
•   Unit 4: The Dynamic Aggregation Studio -- Demo
•   Unit 5: Delegating Tables -- Lab
•   Unit 6: Loading Data -- Lab
•   Unit 7: Basic Troubleshooting

2009 | Confidential -               2
Unit 1: Technical Overview

•   Host Client, Blades and Arrays
•   High Availability - Drives
•   Typical DBMS Stack
•   MPP Database Architecture
•   Query Stack
•   Dataupia Data Loader
•   Host DB Connectors: Oracle, SQL
•   Management Back Plane

2009 | Confidential -   3
1: Host Client, Blades and Arrays

        OS: Solaris, Windows
        Database Server: Oracle, MS-SQL Server

Host System
 (with DT client)

2009 | Confidential -           4
 1: Dataupia Client Components

                                      • Native Database: Oracle, SQL Server
                                      • Data Loader
                                      • Dataupia Client Software
Host System                           • Database Plug-in
(with DT client)                      • Dataupia Drivers

 2009 | Confidential -                                       5
1: Dataupia Satori Server Components

  • Management Backplane
  • Management Console
  • CLI
  •Database Engine

2009 | Confidential -   6
1: Typical Dataupia Array - Physical Components

 Network Switch
                                         Stack 10G   1   2        3   4     5   6     7   8    9        11        13        15            17   18       19   20   21   22   23   24   25            27        29        31            33   34   35   36    37    38        39   40   41        43        45             47                  45x   46x    47x   48x
                                                                                                   10        12        14        16                                                        26            28        30            32                                                       42        44        46               48                                    Solid ON = Link
                                         1     1                                                                                                                                                                                                                                                                                                                     Blinking = Activity
                                         2    2

                                         STACK NO.



                                                                                                                                                                                                                                                                                                                             Shared Ports                                CONSOLE
                                                                                                                                                                                                                                                     Sum m it X450a-48p

 Terminal Server
                                                                 PortServer TS/16             ETHERNET                  1             2             3             4              5              6             7              8             9          10              11             12             13             14                 15                16

                                                             PWR      LI 10/100 TX   RX

                                                                                                                                                                                                                                                                                                                                                                                           Additional Components

    Dataupia                                                                                                                                                                                                                                                                                                                                                                                              DA Blade
  Satori Servers
                                                                                                                                                                                                                                                                                                                                                                                                          Loader Blade

                                                                                                                                                                                                                                                                                                                                                                                           KVM            KVM

                                                                                                                                                                                                                                                                                                                                                                                                          Switched PDU (2)

2009 | Confidential -                                                                                                                                                                                                                                                                                                                                                                                   7
1: High Availability – Drives

   • OS - Loaded on internal Flash Drive
   • Data Storage - 8 hot swappable drives

            Flash Drive                  RAID Controller – RAID-5
                                                                       hot spare

    Read-only Flash drive
    with 2 boot partitions
    used for OS.
                                            7 drives in RAID-5 array
                                            1 drive allocated as „hot spare‟

2009 | Confidential -                                             8
         1: Typical IT Architecture

                                                  Standard App Interface (ODBC, JDBC, SQL)
Users                                                                                                   Platform
 Users                    ERP

                                                                                                                   Standard Interconnect Interface (SAN / NAS)
                   SAP, Oracle Apps                                                          Database
 Users                  Users
                  Sieble, JD Edwards                                                                                                                              EMC
                                                                                             Database     SUN

Users                    DSS                                                                                                                                      Net
                MicroStrategy, Cognos,
                   SAS, SPSS, etc.
                         Users                                                               Database     SUN

Users                                                                                        MS-SQL
 Users             ―Home-grown‖                                                              Database     wintel

         2009 | Confidential -                                                                                                                            9
            1: Dataupia‟s MPP Architecture

                                Standard App Interface (ODBC, JDBC, SQL)
                                                                                                                     Dataupia Satori Blades
  Users           ERP


          SAP, Oracle Apps                                                                                                     Dynamic Indexing
  Users   Sieble, JD Edwards
                   Users                                                   Database                                 Database
                     Users                                                                                           Engine




                                                                                       SUN                                     Dynamic Indexing
                                                                           Database                                 Database

                                                                                                 Dataupia Drivers
 Users           DSS


Users          Cognos,

                  Users                                                                                                        Dynamic Indexing
           SAS, SPSS, etc.
                                                                            Oracle                                   Engine


                                                                                                                               Dynamic Indexing
Users                                                                      MS-SQL
  Users     ―Home-grown‖
                                                                           Database    wintel



Users             Users                                                                                                        Dynamic Indexing
  Users                                                                                                             Database

            2009 | Confidential -                                                                                                         10
1: Query Stack - Transparency

        Host Server
                                            Dataupia Array               Global Services



                                                      Dynamic Indexing
            Oracle                       Database
           Database                      Primitives     Replication

                                                                                       Management Back Plane

                                                      Dynamic Indexing
                                         Primitives     Replication


                                                      Dynamic Indexing
       HS Database                       Primitives     Replication



                                                      Dynamic Indexing
       ODBC Driver                       Primitives     Replication



                                                      Dynamic Indexing
        dtODBC                           Primitives     Replication

2009 | Confidential -                                                                         11
1: Management Backplane

      •   Provides wrapper for OS – user has a protected shell
      •   Compact Flash drive (2 OS images on blade)
      •   Diagnostic utilities
      •   Broadcast upgrades (new image installed to non-booted partition)
      •   Centralized Management Framework
      •   CLI language

2009 | Confidential -                                       12
1: Global Services

      Users                  Users                                       Bind blades into an array

                                                                         •   Root Service
                                                                         •   Blade Service
                                                                         •   Database Service
                                            Storage                      •   ID Service
       DT Client
                                                                         •   Transaction Service

                                                 Dataupia Satori Blade
                                                                         Blade Daemon runs on
                                                                          each blade
        Global Services                     Storage

   2009 | Confidential -                                                            13
1: Dataupia Data Loader

                Host Server

         System with DTclient                    Dataupia Array



2009 | Confidential -                            14
Unit 2: The Management Console

• General navigation
• Health of blades and array
• Administration features
• Personalization
• Query management
• Upgrading blade software
• Online help

2009 | Confidential -   15
2: General Navigation
                             Navigation Pane


                                                                        Health Charts

                                               Statistical Snapshot

2009 | Confidential -                                                  16
2: Health of Blades and Array

2009 | Confidential -   17
Blade Health

2009 | Confidential -   18
2: Administration Features

                                         Tab Menus

             Unassigned Blades

2009 | Confidential -               19
2: Personalization

2009 | Confidential -   20
2: Notifications

2009 | Confidential -   21
2: Query Management

2009 | Confidential -   22
2: Query Actions

• Terminate
  Forces a query process to terminate within a short time, giving it a
  chance to finish its work and produce partial results before ending

• Kill
  Ends a query process immediately, with no chance for any results

• Raise Priority
  Gives a query process a higher processing priority on the array,
  which may enable it to fully execute at a normal or near-normal

• Lower Priority
  Leaves a query process active (for example, if you want to
  preserve the current situation for further analysis) but lessens its
  impact on overall system performance.

2009 | Confidential -                                   23
2: Upgrading Blade Software

2009 | Confidential -   24
2: Online Help

2009 | Confidential -   25
Demo Unit 2: The Management Console

• Configure health charts, warnings, logging
• Set up email recipients
• Review and take action on queries
• Upgrade the software image

2009 | Confidential -         26
Unit 3: CLI

      • Types of Users
      • Dataupia User Commands
      • Dataupia Support commands
      • Using CLI commands
      • Writing a script

2009 | Confidential -   27
3: CLI Overview

• Provides alternatives to the DMC for information and some actions

• Connect a keyboard and monitor to the blade, or use an SSH
  connection to the head blade’s IP (same address you load in your
  browser for the DMC

• Log in with the same username & password as on DMC

• Three modes: standard, enable and configure

• Interactive help lets you check the usage and options for any
  command and subcommand.

2009 | Confidential -                                28
3: CLI Command Modes

Standard mode
           Active when you first log in.
Enable mode
           Enter with enable command. View all available information.
           Take some actions but not configuration changes.
Configure mode
           Enter with configure terminal command. Make configuration changes.

Prompts indicate mode you are in:
• blade101 >                               (standard)
• blade101 #                               (enable)
• blade101 (config) #                      (configure)

2009 | Confidential -                                          29
3: Command Help and Completion
• Enter ? on the command line to see a list of commands available in
  the current mode.

• Use ? following a partial word to narrow the list; for example t? in
  standard mode displays terminal, telnet, traceroute commands.

• Follow a command or subcommand with ? to see usage and
  options. For example, in configure mode:
      blade101 (config) # image ?
        boot             Specify which system image to boot by default
        fetch            Download a system image from a remote host
        install          Install an image file onto a system partition

      blade101 (config) # image boot ?
        ocation         Specify location from which to boot system
        next             Boot system from next location after one currently booted

      blade101 (config) # image boot location ?
        1                Boot from location 1
        2                Install to location 2

• Use Tab key to complete unambiguous commands, options, and

2009 | Confidential -                                               30
3: Show Commands
Use show to display information about the blade you are logged into and
the array it is part of. Use ? to check the usage and options. Important show commands:

 show array                              Display array properties, status and blade membership

 show blade                              Display Dataupia blade configuration

 show dtstore                            Display dtstore configuration

 show queries                            Display queries running on a blade

 show query                              Display details of a specific query

 show logging                            Display logging configuration

 show raid                               Display RAID controller, unit, and drive status.

 show email                              Display current email notification settings

2009 | Confidential -                                                           31
3: Array and Blade Commands
These commands let you configure the blade and the array.
(Remember to use ? to check the usage and options, and see the User Guide.)

                                         Create a new array and make the current blade the head blade. Services
   array createjoin                      are restarted. You can set the array name, the array ID, and the database
                                         port number, or the system will generate them for you.

                                         Join the local blade to an existing array and restart services. If you
                                         specified a database port# in the createjoin command, you must specify
   blade join
                                         the same port# here. In this release you cannot join a blade to an array
                                         with data on it.

   blade restart                         Restart all services on the local blade, or only global services.

   blade reload                          Reboot the blade without powering off.

   blade shutdown                        Shut down the blade and power it off.

                                         Fetch a software image, install a software image, boot the partition on
                                         which you have installed an image.

                                         Enable and configure email notifications to be sent to specified addresses
   email                                 when specified events occur. The no prefix lets you cancel email
                                         configuration. (Many options, use email ? to get started.)

   Write memory                          Save configuration changes to the configuration database.

2009 | Confidential -                                                                                32
3: Other Useful Commands
Use ? to check the usage and options.

   cli                            Configure CLI options

   clock                          Set the time, date, and timezone.

                                  A network tool used to test whether a particular host is reachable across an IP
                                  A network tool used to determine the route taken by packets across an IP

   slogin                         Connect to another blade or system using ssh

   telnet                         Connect to another blade or system using telnet

   terminal                       Configure terminal display options

2009 | Confidential -                                                                              33
 Demo Unit 3: CLI

• Log in and use different CLI modes

• Use show commands to display available information
  about the array, blades, other arrays, software
  images, and various settings

• Configure email notification settings

• Kill a query

• Display the logging configuration

 2009 | Confidential -                34
Unit 4: The Dynamic Aggregation Studio

• You can use the DA Studio to create Aggregates, or Data Cubes
• With Aggregates you can view and manipulate data in multiple
• Aggregates consist of dimensions and measures.
• Measures – items that are counted, summarized, averaged, etc.,
  such as costs or units of service.
• Dimensions – the columns that the measures will be grouped by,
  such as dates or locations.

2009 | Confidential -                             35
4: General Navigation

                                         Navigation Pane

                                                                 Detailed Information for
                                                                 Selected Folder

                                            Server Information

2009 | Confidential -                                                      36
4: Select Input Data

2009 | Confidential -   37
4: Select and Compile Dimensions

2009 | Confidential -   38
4: Create and Build the Aggregate

2009 | Confidential -   39
4:Query the Aggregate

2009 | Confidential -   40
Demo Unit 4: Creating an Aggregate with the Demo Project.

• For training purposes, a demo project is provided as part of the
  Dynamic Aggregation Studio installation.
• The demo project comes with an input data file already loaded.
• In this demo we will create an aggregate using the demo project.

2009 | Confidential -                               41
Unit 5: Delegating Tables

• Data distribution on Dataupia arrays

• Delegating native database tables to Dataupia

2009 | Confidential -            42
  5: Data Distribution Methods for Array Tables

          Method                           Data Allocation

          round robin                      Uniform serial across blades (default)
          single                           All on one blade
          hashed                           Hashed by column across blades
          all                              All on all blades

                                                               hash by column       all
round-robin                           single

  2009 | Confidential -                                                  43
5: Choosing Round-Robin Distribution

   •     Records are distributed serially
         and uniformly across blades -
         one row to each blade in
         repeated sequence.
   •     The default method.
   •     Records are distributed this way:

                                                   •   Guarantees* even distribution
                                                       of data across blades with no
                     1,5,9,13 …          Blade 1       data analysis required.
                                                   •   Use when there is no „natural‟
                     2,6,10,14 …         Blade 2       distribution key.
                                                   •   Best suited to fact tables.
                     3,7,11,15 …         Blade 3

                     4,8,12,16 …         Blade 4

2009 | Confidential -                                                  44
    5: Choosing All-Blade Distribution

•     Tables are co-located on every
      active blade in the array. All
      records are copied to all blades.
•     Records are distributed this way:

                                                       •   Required for dimension tables
                                                           that participate in joins
                                                       •   Ensures that fact:dimension
                       1,2,3,4 …             Blade 1       joins will process in parallel and
                                                           not require cross-blade
                       1,2,3,4 …             Blade 2
                       1,2,3,4 …             Blade 3

                       1,2,3,4 …             Blade 4

    2009 | Confidential -                                                      45
5: Choosing Hashed Distribution

   •     Records are distributed by a
         deterministic hash function
         using the specified column(s)
         as distribution key.
   •     Record distribution depends on
         key but should be close to
                                       •               Requires unique or nearly
                                                       unique distribution key to
                        1,9,12,15 …      Blade 1       ensure acceptably even
                        3,6,11,16 …      Blade 2
                                                   •   The distribution key must be
                        2,7,8,14 …       Blade 3       non-volatile and not nullable, as
                                                       well as unique or nearly unique.
                        4,5,10,13 …      Blade 4

2009 | Confidential -                                                     46
5: Choosing Single-Blade Distribution

   •     Each table is located entirely
         on a single blade
   •     Records are distributed this

                                                   •   Single distribution is
                                                       appropriate for smaller tables
                     1,2,3,4 …           Blade 1       and less frequently used
                                         Blade 2

                                         Blade 3

                                         Blade 4

2009 | Confidential -                                                  47
5: Delegating Tables and Data

              1.     Extract Oracle tables to CSV (or Binary) files

              2.     Register a table‟s data distribution method

              3.     Create the table on the array, including indexing

              4.     Create synonym or view in host database

2009 | Confidential -                                   48
5: Delegation Process Overview

         Host DB                                          Dataupia array

            Table A                                           Table A

              data                                             data
                                         delegation                        dttable &
            Table B                                           Table B

              data                                             data

               Extract                                dtldr


2009 | Confidential -                                                 49
5: Creating Array Tables Manually

At shell prompt:

1. Register a data distribution specification for the array table using
   the regtable command
2. Create the array table using the dttable create command

In Oracle:

3. Change the name of the Oracle table
4. Create a synonym and/or view in Oracle to the array table

2009 | Confidential -                                    53
5: Manual Step 1 - Distribution Commands


 Register a table with the specified distribution method. If no method specified defaults to round

        regtable <DT_systemid> <tbname> {single|rr|distmap|all} [col1,]


 Change a table’s existing distribution method.

        chtable <DT_systemid> <tbname> {single|rr|distmap|all} [col1,]

 DO NOT change the distribution method of table with data in it.

2009 | Confidential -                                                               54
5: Manual Step 2 - The dttable Command
Creates, alters, truncates, drops tables on the Dataupia array
• Usage
dttable <command> [-t tablename] [options]

dttable create -t <tablename> { {-c <column name> <column data type> [, ...] } [...] | -f <column definition file> }

dttable rename -t <old tablename> -n <new tablename>

dttable add_column -t <tablename> -c <column name> <column data type>

dttable alter_column -t <tablename> -c <column name> -n <new column data type>

dttable rename_column -t <tablename> -c <old column name> -n <new column name>

dttable drop_column -t <tablename> -c <column name>

dttable create_index -t <tablename> -i <indexname> {-c {<column name> [, <column name>...]} [...]

dttable rename_index -i <indexname> -n <new indexname>

dttable drop_index -i <indexname>

dttable truncate -t <tablename>

dttable drop -t <tablename>

dttable describe -t <tablename>

2009 | Confidential -                                                                                 55
5: Manual Step 3 - Rename Oracle Tables

• Rename the Oracle tables so that queries that reference the now-
  delegated tables bypass the original tables.

my_table1 is now on the Dataupia array.
Rename my_table1 to my_table1_orig

Oracle syntax:

alter table my_table1 rename to my_table1_orig

2009 | Confidential -                               56
5: Manual Step 4 - Create Oracle References to the Array Table

• Use the original names as a reference to the array table in one of two

     • Create an Oracle synonym for the array table as a remote object:

     • Create a view of the array table in Oracle:

• The reference then replaces the Oracle table:
         SELECT * from MYSYN; = SELECT * from “MYTABLE”@”DTNAS”;
         - or -
         SELECT * from MYVIEW; = SELECT * from “MYTABLE”@”DTNAS”;

2009 | Confidential -                                     57
5: Indexing
• Native indexes delegated to Dataupia are retained
• Additionally, Dataupia uses indexing approaches optimized for large data
• Disk indexing supports record-based optimized storage and rapid retrieval
• Dataupia indexing is transparent to the application

• Optimized Hilbert r-tree Index
      • Built-in index for every table
      • Designed for clustered data in which target rows are physically close
      • Example: time-sequenced data loaded in chronological order and often queried
        by date or time

• Balanced Bucket Index (BBI)
      • Explicit definition occurs when you use the dt_cli utility
      • Designed for data in which the target rows are physically dispersed
      • Example: in queries against non-chronological columns such as phone number

2009 | Confidential -                                                 58
Lab Unit 5: Delegating Tables

•   Unload Oracle tables prior to delegating
•   Delegate existing tables using delegator
•   Create and register array tables using dttable
•   Rename tables on Oracle
•   Create and test Oracle view/synonym

2009 | Confidential -               59
 Unit 6: Dataupia Data Loader

• How it works

• Writing data description files for CSV and binary data

• Command line options and scripting

• dtlscan testing utility

• Potential errors and troubleshooting the Loader

2009 | Confidential -                     60
6: How the Data Loader Works

2009 | Confidential -   61
6: How the Data Loader Works

                 data file

    field 1       field 2              field 3

                                                              data description/DAP              dtloader
                                                                                 parsing     column   universal
                                                            field   field name
                                                                                 directive    name      type
                                                             1          colA      string()    colA    STRING
                                                             2          colB       int()      colB      INT
                                                             3          colC      float()     colC     FLOAT

                                       Dataupia array table

                              colA                colB           colC
                             varchar             integer   double precision

2009 | Confidential -                                                                            62
6: Data Type Mapping
  Oracle Data Type   Dataupia Data Type            Dataupia Storage Size             Description                Range

                                                                                     Variable-length with
  VARCHAR2(n)        varchar(n)                    4 bytes plus the actual string
                                                                                        limit n

                                                   4 bytes plus the actual string    fixed-length, blank
  CHAR(n)            char(n)
                                                      and padding                        padded

  DATE               timestamp [w/out time zone]   8 bytes                           both date and time         4713 BC to 5874897 AD
  DATE               date                          4 bytes                           date only                  4713 BC to 5874897 AD

  DATE               time [w/out time zone]        8 bytes                           time of day only

                     smallint                      2 bytes                           small-range integer        -32768 to +32767
    p<=4, s=0

                     integer                       4 bytes                           usual choice for integer   -2147483648 to +2147483647
    5<=p<=9, s=0

  NUMBER(p,s)                                                                                                   -9223372036854775808
                     bigint                        8 bytes                           large-range integer
    10<=p<=18, s=0                                                                                                 +9223372036854775807

                                                   2 bytes for each group of 4
                     numeric(p,s)                     decimal digits, plus 8 bytes   number                     Up to 1000 decimal digits
    p>18, s=0

  NUMBER(p,s)                                      4 bytes variable-precision,       variable-precision,
                     real                                                                                       6 decimal digits precision
    p<=6, s≠0                                         inexact                            inexact

  NUMBER(p,s)                                      8 bytes variable-precision,       variable-precision,
                     double precision                                                                           15 decimal digits precision
    6<p<=15, s≠0                                      inexact                            inexact

                                                   2 bytes for each group of 4
                     numeric(p,s)                     decimal digits, plus 8 bytes   number                     Up to 1000 decimal digits
    p>15, s≠0

                                                   4 bytes variable-precision,       variable-precision,
  BINARY_FLOAT       real                                                                                       6 decimal digits precision
                                                      inexact                            inexact

                                                   8 bytes variable-precision,       variable-precision,
  BINARY_DOUBLE      double precision                                                                           15 decimal digits precision
                                                      inexact                            inexact

2009 | Confidential -                                                                                                        63
     6: Data Description Files
        Description file for CSV (ascii) data file
        with directive to omit trailing characters
        %CONTROL                                                  Description file for binary data file with
        set-mode(ascii);                                             modification directive for first field
        set-record-size(variable);            %VERSION(1)
        %ENDCONTROL                           %CONTROL
        %DATA                                 set-mode(binary);
        (                                     %ENDCONTROL
        RECORD_TYPE           string(1);      %DATA
        SEQ_NUM                int(8);        (
        RECORD_NUM             int(3);        CALL_DATE               int(8) | string(14) |
        ORIG_NUM               string(32);                                  datetime(―%Y%m%d%H%M%S‖);
        ROUTE                  string(7);     OPERATOR                string(5) | int(4);
        JUNK                   skip(1);       HR_NUM                  int(1);
        )                                     ST_CALL_TIME            int(2);
        %ENDDATA                              POI_NNI                 int(4);
                                              SEQNO                    int(8);
%EXTENSION / %ENDEXTENSION is                 DIALLED_DIG_STR         string(31);
an optional section for defining              WHOLESALE_PRC           float(8);
transformations and operations                )

     2009 | Confidential -                                                                    64
   6: Description File %CONTROL Section Directives

Directive              Description                                           Use

set-mode()             binary or ascii                                       required
set-record-size()      fixed (binary or ASCII) or variable (ASCII only)      required (even for binary files)
set-endian()           little or big (binary only)                           optional for binary files
set-delimiter()        Define characters used in a variable record           required for variable record
set-terminator()       (delimited) ASCII file to:                            ASCII files; however, default
set-quote()                                                                  delimiter is comma (,) and
                       • separate fields within a record                     default terminator is RETURN,
                       • indicate end of each record                         so set-delimiter() and
                       • quote data within a field (when parsing directive   set-terminator() not required
                         extracts quoted data)                               for standard CSV files.

                       Arguments can be single literal character


                       or back-quoted escape sequence such as \t (tab),
                       \r (return) or \n (newline)


   2009 | Confidential -                                                                       65
   6: Description File %DATA Section Parsing Directives

                                                                          Creates         Compatible With
Directive       Reads from Data Stream                                   Univ Type        Dataupia Datatypes
int(n)          integer of                                              INT               • ―char‖, smallint, integer, bigint (if
                • size n = 1-8 (binary files)                           (integer)           values fit datatype)
                • n = 1-20 digits (fixed record ASCII files)                              • date, time, timestamp (if values
                • up to n = 1-20 digits (variable record ASCII files)                       fit UNIX-style UTC time)
string(n)       string of                                               STRING            ―char‖, char(), varchar()
                • n >= 1 chars (binary and fixed ASCII files)           (string)
                • up to n =>1 chars (variable ASCII files)
float(n)        floating point number (IEEE 754 format) of              FLOAT             double precision, real (if values fit
                • size n = 4 or 8 (binary files)                        (single/double    datatype)
                • n = 1-20 digits (fixed record ASCII files)            precision
                • up to n = 1-20 digits (variable record ASCII files)   floating point)
datetime        date or time string; format determines which digits     DATETIME          date, time, timestamp
 (format)       represent which time units (see User Guide)             (date & time)
number(m,n)     arbitrary precision decimal number with                 NUMBER            • numeric
                • m digits before decimal and n after                   (general          • smallint, integer, bigint, double
                • up to m digits before decimal , up to n after         numeric)            precision, real (if values fit
skip(n)         Read nothing, instead skip
                • next n >= 1 bytes of input stream (binary and                            none—no data
                  fixed ASCII files)
                • until next delimiter (variable record ASCII files)

   2009 | Confidential -                                                                                           66
6: Description File Modification Directives
Use int(), string(), and datetime() to modify parsed data as needed. For example:

     CALL_DATE                int(8) | string(14) | datetime(“%Y%m%d%H%M%S”);

1.   Binary data file contains eight-byte binary integers encoding ten-digit decimal
     timestamps, e.g. timestamp 2007-02-10 16:09:22 is represented as integer
     20070210160922 and encoded in the binary value 0x00001240f5b4491a

2.   Parsing directive int(8) converts eight bytes of input stream to decimal integer

3.   Modification directive string(14) converts digits of integer to 14-character string

4.   Modification directive datetime(“…”) converts string to timestamp, with first four
     characters as year and remainder as two-digit month, day, hour, minute, seconds

5.   Field is loaded into Dataupia table column CALL_DATE of type timestamp

2009 | Confidential -                                                     67
6: Scripting the Loader
Here is a script to

• load all data files with extension .data from directory datadir

• load into table bigtable on array with ID 12345678

• using description file bigtable.f

• logging to bigtable.log

• writing information about loaded files to array table loaded_bigtable

• writing information allowing for 10 error records to bad_records_bigtable

find datadir -name "*.data" | dtldr –C dtarrayid=12345678 -D bigtable.f
-T bigtable -E 10 -L bigtable.log

2009 | Confidential -                                        68
     6: Loader Command Line Options
-C dtarrayid=<array_id>        Array on which target table is located (required)
-T <table name>                Specify the target array table (required)

                               (Can also be used to specify names of the files loaded table and error table on the array, as
                                 well as the templates used to create these tables. See the User Guide.)
-D <description file>          Data description file (path) (required)

-f <load file>                 Load the specified file. Without this option, loader reads file names from standard input as
                                 long as it remains open.
-u                             Remove each file upon successful loading.
-X <# records>                 Create a transaction checkpoint and commit data after loading the specified number of
-E <count>                     Error threshold. If <count> is negative, abort dtloader after <count> errors. If count is positive,
                                 do not about and record no more than <count> errors in the error table. Default is -1 (abort
                                 on first error).
-Q                             ―Quiesce‖ (hold a lock on) the table, preventing execution of queries against it during the load.

-L <file>                      Loader log file (path).

-r <file>                      Report loading rates into the specified file (path) at every checkpoint (diagnostic option).

     2009 | Confidential -                                                                                        69
6: Loader Diagnostics

• Use the –T option for dtldr to name status tables and specify templates

• loaded table
      •   fkey A file key (integer) to identify the bad record’s source
      •   fname Name of data file (varchar(200)) passed to the loader
      •   fmtime The file’s system time (timestamp without time zone)
      •   nrecords Number (bigint) of records in the specified data file

• bad record table
      • fkey A file key (integer) to identify the bad record’s source
      • field Name (varchar(120)) of the field being parsed when the error was detected
      • rec_offset Offset (bigint) from the beginning of the data file (in bytes) of the bad
      • error Code (integer) describing the nature of the error
      • input_data The bad record as a hex-encoded string (varchar(32000))

2009 | Confidential -                                                         70
6: dtlscan Utility

dtlscan –D data_description -f data_file [–p –E –o –t –n –e -r]

Scans, analyzes and converts data files using data description files to predict or isolate errors
encountered by dtloader. Does not interact with the database server or the array.

-D                       Parse data using the specified data description file (required)

-f data_file             Parse data from the specified file (required)

-p                       Parse the data file specified by –f using the data description specified by -D
-E                       Continue parsing after errors
-o                       Write parsed data to standard output in CSV fornat
-t                       Embed parsed datatypes in CSV output when –o is used

-n                       Output NULLs as ‗(NULL)‘ (to render them visible) when –o is used
-e                       Echo data in the file specified by –f to standard output
-r                       Report data record statistics

2009 | Confidential -                                                                    71
6: Potential Data Loader Problems

• Description File
      •   Syntax errors
      •   Does not match input data
      •   Does not match target table
      •   Field needs further modification to be compatible with target

• Data Errors
      • NULL data
      • Mismatched or unsupported format
      • Illegal value

2009 | Confidential -                                    72
6: Troubleshooting

• Four ways to investigate errors
      • Use dtlscan with –p and –E options to verify description file and isolate
        bad records before loading.
      • Review the loaded files table on the array for information about data files
        loaded into the target table.
      • Review the bad records table on the array for information about records
        that could not be loaded into target-table and generated an error.
      • Use the dtldr –l logfile option to write information about errors to a log file
        and review the log contents.

• Setting the error limit with the dtldr –E [-]count option
      • Negative count - # of errors to record before dtldr aborts
      • Positive count - # of errors to record before dtldr stops recording (but
                               continues execution)

2009 | Confidential -                                                    73
    6: The dtunload Command
• Purpose

Unloads data from Dataupia array tables. Useful for archiving or back-ups.

• Usage

dtunload -C “dtarrayid=<array_ID>” -q “<query>” [-o <options>]

• Arguments
<array_ID> Array ID of the array on which the table is located
<query> Query to obtain desired rows from table, of the form select <col1>[,col2,…] from <table>
<options> Options for formatting unloaded data, including –CSV for CSV format and options to specify
           delimiter, null, quote, and escape characters

     • Arguments with spaces or other separators must be quoted.
     • Default is standard output. Redirect to a file.

• Example

dtunload -C "dtarrayid=62674212" -q 'select * from table23' -o "CSV DELIMITER '|'"             > myfile

    2009 | Confidential -                                                             74
Lab Unit 6: Loading Data

• Analyze and understand input and
  data description files
• Use dtlscan to test and correct a
  description file
• Write a data description file
• Use dtldr to load data
• Review results of load including
  loaded_ and bad_records_ tables on the array
• Truncate target table, fix errors, and reload

2009 | Confidential -            75
Unit 7: Troubleshooting

• RAID-5 failover

• Replacing a failed drive

• Troubleshooting network problems

• Restarting a blade

• Replacing a blade

• Getting support

2009 | Confidential -   76
7: RAID-5 Disk Drive Failover

• Each blade has eight drives - seven active drives in a RAID5
  configuration + one hot spare

• If a drive fails, RAID fails over to the spare, no data lost

• Blade operates in degraded mode (low disk space, degraded
  performance) until failed drive is rebuilt on spare

• After rebuild, the spare should be replaced as soon as possible

2009 | Confidential -                              77
7: Replacing a Failed Drive
 • Directly verify that you are working on correct blade
 • Locate drive to be removed
 • Record number/location of failed drive

 •   Move release lever to right and pull on release tab
 •   Pull drive by gripping with fingers and pulling out
 •   Push new drive into bay as far as possible, close release lever fully
 •   The new drive becomes the hot spare

2009 | Confidential -                                       78
7: Troubleshooting Network Problems

    • ping blade with suspected problem from another device on the network
      (or all blades if problem is not yet isolated).

    • If ping fails, confirm that blade is on and operating.

    • Connect keyboard/monitor and try ping from suspect blade.

    • Check blade’s cable connections to the network switch. Are lights green
      at both ends? Try replacing cable or a different switch port.

    • Try bypassing switch and connecting directly to network. If blade’s HBA
      has failed, chassis must be replaced.

    • Use the blade restart command to restart network services, or blade
      reload for a warm reboot.

2009 | Confidential -                                          79
7: Restarting a Blade
   Different restart commands have different effects:

   • blade restart
          Restart all services on the local blade.
          If issued with globalsvcs argument, restart only global services.

   • blade reload
          Reboot the blade without powering it off (”warm restart”)

   • blade shutdown
          Shut down the blade and power it off (turn on again to reboot)

2009 | Confidential -                                        80
7: Replacing a Chassis

    • If you have eliminated a failed drive or network issue as the root
      problem, the chassis must be replaced.

    • Generally a chassis must be replaced if the CPU, HBA, memory,
      disk controller, power supply, or fan fails.

    • The array will be inaccessible while the chassis is getting

    • No data loss is incurred by a chassis replacement.

    • Contact Dataupia Support to arrange for chassis replacement.

2009 | Confidential -                                     81
    7: Getting Support

•   Information resources
•   Logging a case
•   Checking status
•   Communication

    2009 | Confidential -   82
 7: Finding Information

• Product Usage
   •   Product Documentation
   •   DMC Online Help
   •   Release Notes
   •   Knowledge Base
   •   Engineering Briefs

• Troubleshooting
   •   DMC Health Tab
   •   Release Notes
   •   Dataupia Satori Server User Guide, Chapter 5, “Troubleshooting”
   •   Knowledge Base
   •   Log Files

 2009 | Confidential -                                  83
7: Logging a Case

The Dataupia Helpdesk is staffed 9-6 EST (GMT-5)
      • Phone: 866-259-5971
      • Email:

The Portal is always open:
      Click the Customer Login link at the top right of the window.

        provided to you
          by Dataupia

2009 | Confidential -                                84
7: Customer Portal Home Page

2009 | Confidential -   85
     7: Logging a Case

Helpful information to include in
  the Description field:
• DataupiaTM Satori Server
  serial number
• Contact information for the
  person that will troubleshoot the
  problem with the Dataupia
  Support Engineer
• Error codes recorded on the
  equipment displays or trapped
  by the host
• What has been done so far to
  isolate the problem

     2009 | Confidential -   86
    7: Reviewing Cases

 Double-click on a Case
number to view status or
  solution and provide
 additional information

        Knowledge base of

    2009 | Confidential -   87

• Survey
      • Is there anything else we should add to class?
      • Do you feel confident about what you learned?

2009 | Confidential -                   89

Shared By: