Transcript Features & Functionality

Dataupia™ Satori Server
Features and Functionality Training
Friday, April 8, 2016
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 - www.dataupia.com
1
Agenda
•
•
•
•
•
•
•
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 - www.dataupia.com
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 - www.dataupia.com
3
1: Host Client, Blades and Arrays
OS: Solaris, Windows
Database Server: Oracle, MS-SQL Server
Host System
(with DT client)
2009 | Confidential - www.dataupia.com
4
1: Dataupia Client Components
• Native Database: Oracle, SQL Server
Host System
(with DT client)
• Data Loader
• Dataupia Client Software
• Database Plug-in
• Dataupia Drivers
2009 | Confidential - www.dataupia.com
5
1: Dataupia Satori Server Components
• Management Backplane
• Management Console
• CLI
•Database Engine
2009 | Confidential - www.dataupia.com
6
1: Typical Dataupia Array - Physical Components
Network Switch
Terminal Server
Stack 10G
1
1
2
1
3
2
4
5
7
6
8
9
10
11
12
13
14
15
17
16
19
18
20
21
22
23
24
25
27
26
28
29
30
31
32
33
34
35
36
37
39
38
40
41
42
43
44
45
47
46
45x
48
46x
47x
48x
Solid ON = Link
Blinking = Activity
2
MGMT
STACK NO.
FAN
PSU-I
PSU-E
TM

PortServer TS/16
PWR
LI 10/100 TX
ETHERNET
1
2
3
4
5
6
7
8
9
10
11
CONSOLE
Shared Ports
Sum m it X450a-48p
12
13
14
15
16
RX
Additional Components
Dataupia
Satori Servers
DA Blade
Loader Blade
KVM
KVM
Switched PDU (2)
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
8
1: Typical IT Architecture
Storage
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
ERP
SAP, Oracle Apps
Sieble, JD Edwards
Users
Users
DSS
MicroStrategy, Cognos,
SAS, SPSS, etc.
Users
Users
“Home-grown”
Applications
Users
Users
2009 | Confidential - www.dataupia.com
Platform
Database
Existing
Oracle
Database
Existing
Oracle
Database
Existing
MS-SQL
Database
SUN
SUN
wintel
Standard Interconnect Interface (SAN / NAS)
Users
Standard App Interface (ODBC, JDBC, SQL)
Users
Users
EMC
Net
Apps
HP
Hitachi
9
1: Dataupia’s MPP Architecture
Users
Users
Users
Users
Users
Users
Users
Users
Users
Users
DSS
MicroStrategy,
Cognos,
Usersetc.
SAS, SPSS,
Users
“Home-grown”
Applications
Users
Users
Existing
Oracle
Database
Existing
MS-SQL
Database
2009 | Confidential - www.dataupia.com
SUN
SUN
Dynamic Indexing
Database
Engine
Dynamic Indexing
Database
Engine
Dynamic Indexing
Database
Engine
Dynamic Indexing
Database
Engine
Dynamic Indexing
wintel
Record
based
system
Database
Engine
Record
based
system
Existing
Oracle
Database
Dataupia Drivers
Users
Users
Platform
Database
Record
based
system
Users
Record
based
system
Users
Users
ERP
SAP, Oracle Apps
Sieble, JD
Edwards
Users
Users
Dataupia Satori Blades
Record
based
system
Users
Standard App Interface (ODBC, JDBC, SQL)
Users
Users
10
1: Query Stack - Transparency
ODBC Driver
Manager
dtODBC
Driver
2009 | Confidential - www.dataupia.com
Database
Primitives
Dynamic Indexing
Database
Primitives
Dynamic Indexing
Database
Primitives
Dynamic Indexing
Replication
Replication
Replication
Replication
Record
based
system
Dynamic Indexing
Record
based
system
Database
Primitives
Replication
Record
based
system
Dynamic Indexing
Record
based
system
Oracle
Optimizer
HS Database
Abstraction
Database
Primitives
Global Services
Management Back Plane
Existing
Oracle
Database
Dataupia Array
Record
based
system
Host Server
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 - www.dataupia.com
12
1: Global Services
Application
Users
Users
Application
Users
Bind blades into an array
Users
Existing
Database
Local
Storage
DT Client
Dataupia Satori Blade
Dataupia
Global Services
2009 | Confidential - www.dataupia.com
•
•
•
•
•
Root Service
Blade Service
Database Service
ID Service
Transaction Service
Blade Daemon runs on
each blade
Storage
13
1: Dataupia Data Loader
Host Server
Dataupia Array
System with DTclient
installed
dtldr
Binary
/CSV
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
15
2: General Navigation
Menus
Navigation Pane
Tabs
Health Charts
Statistical Snapshot
2009 | Confidential - www.dataupia.com
16
2: Health of Blades and Array
2009 | Confidential - www.dataupia.com
17
Blade Health
2009 | Confidential - www.dataupia.com
18
2: Administration Features
Tab Menus
Unassigned Blades
2009 | Confidential - www.dataupia.com
19
2: Personalization
2009 | Confidential - www.dataupia.com
20
2: Notifications
2009 | Confidential - www.dataupia.com
21
2: Query Management
2009 | Confidential - www.dataupia.com
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
rate
• 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 - www.dataupia.com
23
2: Upgrading Blade Software
2009 | Confidential - www.dataupia.com
24
2: Online Help
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
26
Unit 3: CLI
• Types of Users
• Dataupia User Commands
• Dataupia Support commands
• Using CLI commands
• Writing a script
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
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 >
• blade101 #
• blade101 (config) #
2009 | Confidential - www.dataupia.com
(standard)
(enable)
(configure)
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
arguments.
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
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.)
array createjoin
Create a new array and make the current blade the head blade. Services
are restarted. You can set the array name, the array ID, and the database
port number, or the system will generate them for you.
blade join
Join the local blade to an existing array and restart services. If you
specified a database port# in the createjoin command, you must specify
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.
image
Fetch a software image, install a software image, boot the partition on
which you have installed an image.
email
Enable and configure email notifications to be sent to specified addresses
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 - www.dataupia.com
32
3: Other Useful Commands
Use ? to check the usage and options.
cli
Configure CLI options
clock
Set the time, date, and timezone.
ping
A network tool used to test whether a particular host is reachable across an IP
network
traceroute
A network tool used to determine the route taken by packets across an IP
network
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 - www.dataupia.com
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 - www.dataupia.com
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
dimensions.
• 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 - www.dataupia.com
35
4: General Navigation
Navigation Pane
Detailed Information for
Selected Folder
Server Information
2009 | Confidential - www.dataupia.com
36
4: Select Input Data
2009 | Confidential - www.dataupia.com
37
4: Select and Compile Dimensions
2009 | Confidential - www.dataupia.com
38
4: Create and Build the Aggregate
2009 | Confidential - www.dataupia.com
39
4:Query the Aggregate
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
41
Unit 5: Delegating Tables
• Data distribution on Dataupia arrays
• Delegating native database tables to Dataupia
2009 | Confidential - www.dataupia.com
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
round-robin
single
2009 | Confidential - www.dataupia.com
hash by column
all
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:
•
1,5,9,13 …
Blade 1
2,6,10,14 …
Blade 2
3,7,11,15 …
Blade 3
4,8,12,16 …
Blade 4
2009 | Confidential - www.dataupia.com
•
•
Guarantees* even distribution
of data across blades with no
data analysis required.
Use when there is no ‘natural’
distribution key.
Best suited to fact tables.
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:
1,2,3,4 …
Blade 1
1,2,3,4 …
Blade 2
1,2,3,4 …
Blade 3
1,2,3,4 …
Blade 4
2009 | Confidential - www.dataupia.com
•
Required for dimension tables
that participate in joins
•
Ensures that fact:dimension
joins will process in parallel and
not require cross-blade
execution
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
even:
•
1,9,12,15 …
Blade 1
3,6,11,16 …
Blade 2
2,7,8,14 …
Blade 3
4,5,10,13 …
Blade 4
2009 | Confidential - www.dataupia.com
•
Requires unique or nearly
unique distribution key to
ensure acceptably even
distribution.
The distribution key must be
non-volatile and not nullable, as
well as unique or nearly unique.
46
5: Choosing Single-Blade Distribution
•
•
Each table is located entirely
on a single blade
Records are distributed this
way:
•
1,2,3,4 …
Blade 1
Blade 2
Single distribution is
appropriate for smaller tables
and less frequently used
tables
Blade 3
Blade 4
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
48
5: Delegation Process Overview
Host DB
Dataupia array
Table A
Table A
data
data
delegation
dttable &
regtable
Table B
Table B
data
data
data
A
Extract
dtldr
data
B
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
53
5: Manual Step 1 - Distribution Commands
regtable
Register a table with the specified distribution method. If no method specified defaults to round
robin.
regtable <DT_systemid> <tbname> {single|rr|distmap|all} [col1,]
chtable
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 - www.dataupia.com
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 - www.dataupia.com
55
5: Manual Step 3 - Rename Oracle Tables
• Rename the Oracle tables so that queries that reference the nowdelegated tables bypass the original tables.
Example:
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 - www.dataupia.com
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
ways:
• Create an Oracle synonym for the array table as a remote object:
CREATE SYNONYM MYSYNONYM FOR “MYTABLE”@”DTNAS”;
• Create a view of the array table in Oracle:
CREATE VIEW MYVIEW AS (SELECT * FROM “MYTABLE”@”DTNAS”);
• The reference then replaces the Oracle table:
SELECT * from MYSYN; = SELECT * from “MYTABLE”@”DTNAS”;
- or SELECT * from MYVIEW; = SELECT * from “MYTABLE”@”DTNAS”;
2009 | Confidential - www.dataupia.com
57
5: Indexing
• Native indexes delegated to Dataupia are retained
• Additionally, Dataupia uses indexing approaches optimized for large data
workloads
• 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 - www.dataupia.com
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 - www.dataupia.com
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 - www.dataupia.com
60
6: How the Data Loader Works
2009 | Confidential - www.dataupia.com
61
6: How the Data Loader Works
data file
field 1
field 2
field 3
VARCHAR2
NUMBER
BINARY_DOUBLE
data description/DAP
dtloader
field
field name
parsing
directive
column
name
universal
type
1
colA
string()
colA
STRING
2
colB
int()
colB
INT
3
colC
float()
colC
FLOAT
Dataupia array table
colA
colB
colC
char
varchar
integer
double precision
2009 | Confidential - www.dataupia.com
62
6: Data Type Mapping
Oracle Data Type
Dataupia Data Type
Dataupia Storage Size
Description
VARCHAR2(n)
varchar(n)
4 bytes plus the actual string
Variable-length with
limit n
CHAR(n)
char(n)
4 bytes plus the actual string
and padding
fixed-length, blank
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
NUMBER(p,s)
p<=4, s=0
smallint
2 bytes
small-range integer
-32768 to +32767
NUMBER(p,s)
5<=p<=9, s=0
integer
4 bytes
usual choice for integer
-2147483648 to +2147483647
NUMBER(p,s)
10<=p<=18, s=0
bigint
8 bytes
large-range integer
-9223372036854775808
+9223372036854775807
NUMBER(p,s)
p>18, s=0
numeric(p,s)
2 bytes for each group of 4
decimal digits, plus 8 bytes
overhead
number
Up to 1000 decimal digits
NUMBER(p,s)
p<=6, s≠0
real
4 bytes variable-precision,
inexact
variable-precision,
inexact
6 decimal digits precision
NUMBER(p,s)
6<p<=15, s≠0
double precision
8 bytes variable-precision,
inexact
variable-precision,
inexact
15 decimal digits precision
NUMBER(p,s)
p>15, s≠0
numeric(p,s)
2 bytes for each group of 4
decimal digits, plus 8 bytes
overhead
number
Up to 1000 decimal digits
BINARY_FLOAT
real
4 bytes variable-precision,
inexact
variable-precision,
inexact
6 decimal digits precision
BINARY_DOUBLE
double precision
8 bytes variable-precision,
inexact
variable-precision,
inexact
15 decimal digits precision
2009 | Confidential - www.dataupia.com
Range
63
6: Data Description Files
Description file for CSV (ascii) data file
with directive to omit trailing characters
%VERSION(1)
%CONTROL
set-mode(ascii);
set-record-size(variable);
%ENDCONTROL
%DATA
(
RECORD_TYPE
string(1);
SEQ_NUM
int(8);
RECORD_NUM
int(3);
ORIG_NUM
string(32);
ROUTE
string(7);
JUNK
skip(1);
)
%ENDDATA
%EXTENSION / %ENDEXTENSION is
an optional section for defining
transformations and operations
2009 | Confidential - www.dataupia.com
Description file for binary data file with
modification directive for first field
%VERSION(1)
%CONTROL
set-mode(binary);
%ENDCONTROL
%DATA
(
CALL_DATE
OPERATOR
HR_NUM
ST_CALL_TIME
POI_NNI
SEQNO
DIALLED_DIG_STR
WHOLESALE_PRC
)
%ENDDATA
int(8) | string(14) |
datetime(“%Y%m%d%H%M%S”);
string(5) | int(4);
int(1);
int(2);
int(4);
int(8);
string(31);
float(8);
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()
set-terminator()
set-quote()
Define characters used in a variable record
(delimited) ASCII file to:
required for variable record
ASCII files; however, default
delimiter is comma (,) and
default terminator is RETURN,
so set-delimiter() and
set-terminator() not required
for standard CSV files.
• separate fields within a record
• indicate end of each record
• quote data within a field (when parsing directive
extracts quoted data)
Arguments can be single literal character
set-delimiter(:);
or back-quoted escape sequence such as \t (tab),
\r (return) or \n (newline)
set-terminator(\n);
2009 | Confidential - www.dataupia.com
65
6: Description File %DATA Section Parsing Directives
Creates
Univ Type
Compatible With
Dataupia Datatypes
Directive
Reads from Data Stream
int(n)
integer of
• size n = 1-8 (binary files)
• n = 1-20 digits (fixed record ASCII files)
• up to n = 1-20 digits (variable record ASCII files)
INT
(integer)
• “char”, smallint, integer, bigint (if
values fit datatype)
• date, time, timestamp (if values
fit UNIX-style UTC time)
string(n)
string of
• n >= 1 chars (binary and fixed ASCII files)
• up to n =>1 chars (variable ASCII files)
STRING
(string)
“char”, char(), varchar()
float(n)
floating point number (IEEE 754 format) of
• size n = 4 or 8 (binary files)
• n = 1-20 digits (fixed record ASCII files)
• up to n = 1-20 digits (variable record ASCII files)
FLOAT
(single/double
precision
floating point)
double precision, real (if values fit
datatype)
datetime
(format)
date or time string; format determines which digits
represent which time units (see User Guide)
DATETIME
(date & time)
date, time, timestamp
number(m,n)
arbitrary precision decimal number with
• m digits before decimal and n after
• up to m digits before decimal , up to n after
NUMBER
(general
numeric)
• numeric
• smallint, integer, bigint, double
precision, real (if values fit
datatype)
skip(n)
Read nothing, instead skip
• next n >= 1 bytes of input stream (binary and
fixed ASCII files)
• until next delimiter (variable record ASCII files)
2009 | Confidential - www.dataupia.com
none—no data
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 - www.dataupia.com
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 - www.dataupia.com
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
records.
-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 - www.dataupia.com
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
record
• error Code (integer) describing the nature of the error
• input_data The bad record as a hex-encoded string (varchar(32000))
2009 | Confidential - www.dataupia.com
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
data_description
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 - www.dataupia.com
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
column
• Data Errors
• NULL data
• Mismatched or unsupported format
• Illegal value
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
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 '|'"
2009 | Confidential - www.dataupia.com
> myfile
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 - www.dataupia.com
75
Unit 7: Troubleshooting
• RAID-5 failover
• Replacing a failed drive
• Troubleshooting network problems
• Restarting a blade
• Replacing a blade
• Getting support
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
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 - www.dataupia.com
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 - www.dataupia.com
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 - www.dataupia.com
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
replaced.
• No data loss is incurred by a chassis replacement.
• Contact Dataupia Support to arrange for chassis replacement.
2009 | Confidential - www.dataupia.com
81
7: Getting Support
•
•
•
•
Information resources
Logging a case
Checking status
Communication
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
83
7: Logging a Case
The Dataupia Helpdesk is staffed 9-6 EST (GMT-5)
• Phone: 866-259-5971
• Email: [email protected]
The Portal is always open:
• http://www.dataupia.com
Click the Customer Login link at the top right of the window.
Password
provided to you
by Dataupia
2009 | Confidential - www.dataupia.com
84
7: Customer Portal Home Page
2009 | Confidential - www.dataupia.com
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 - www.dataupia.com
86
7: Reviewing Cases
Double-click on a Case
number to view status or
solution and provide
additional information
Knowledge base of
Solutions
2009 | Confidential - www.dataupia.com
87
Summary
• Survey
• Is there anything else we should add to class?
• Do you feel confident about what you learned?
2009 | Confidential - www.dataupia.com
89