DB2 Warehouse Manager

1

• DB2 Warehouse Manager
• Components, Hints and Tips
• DB2 User Group - Sydney

Tony Winch Datasync Consulting tonywinch_at_datasync.
com.au
2

• Introduction

• Components in DB2 Warehouse Manager
• Installing DB2 Warehouse Manager
• Building an Extract, Transform and Load System
• Focus on DB2 Unix and PC but also applies to
  DB2/400 and DB2/390

3

• DB2 WM Components

4

• DB2 WM Components

5

• DB2 Warehouse Centre

• GUI to administer the ETL system
• Part of the DB2 Control Centre
• Building by parts
• Extendable interface

6

• DWC View

7

• DB2 Warehouse Manager Server

• Brains of the system
• Directs traffic and schedules work to agents
• Runs from one control DB
• One Server per machine/system

8

• Agents

• Work Horse for the system
• Started by the Agent Daemon per job
• Starts - Runs a task - Ends
• Available on various platforms
• PC, Unix, 400, 390
• Could be many per machine and system

9

• Installation

• DWC - Part of DB2 administration tools
• Apply DB2 FP3 (March/April)
• 128 meg (or more like 256) NT Workstation
• Server - Component of DB2 WG EE or separate
  install
• NT 4.0 Service Pack 5
• Apply DB2 FP1 or 3
• Agents - Component of DB2 WG EE or separate
  install
• DB2 FP1 or 3 or later
• Apply recent Java Runtime
• Good DB2 practices (many disks, SMP CPU RAM)
• Fast Network access

10

• Building an ETL

• Graphical build from parts interface
• Sources
• files, databases, or programs
• Targets
• files, databases, programs or cubes
• Steps - smallest building block
• Processes - collection of steps
• Warehouse - collection of processes and a server

11

• Building with Steps

12
(No Transcript)
13

• Creating Your Own Steps

• Used to implement unique requirements
• Start creating them early in development
• Types of User Defined Programs
• command program
• stored procedure
• program/executables
• Try to build the system with just DWC

14

• Steps to Create

• Comments/signposts
• use as start/end markers
• an empty job that always succeeds for a trigger
• Messaging steps
• interacts with external consoles
• email notifications/errors recovery
• Third Party Programs

15
(No Transcript)
16

• Database Utilities

• Commands versus Programs
• Greater flexibility and error checking
• Runstats (wildcards)
• Activate/Deactivate
• Reorg (wildcard)
• Backups

17

• Database Steps

• Empty Tables
• export load or delete not logged
• to use loads consider tablespace contention
• Security
• Index Creation

18

• Complex Possibilities

• Scheduling functions (and/or logic)
• Conditional steps (eg day of week)
• Run numbers/run type
• Parallel streams

19

• Source Files

• Many sites have a lot of external reference data
• Set guidelines early
• Create as CSV
• Upper case column names in first row (lt18 char)
• Use the SQL step to read data into a table

20

• Naming Standards

• Apply good practices early for better growth
• lt 18 column names except for end user tables
• replication and DWC fields
• Step names must be unique
• task - object
• Use DB2 schema, table and tablespace fields
• task - object

21

• Using Triggered Starts

• External trigger program provided with agent
• Create one for non agent platforms
• stored procedure

22

• Use the Control DB

• Create views or programs over the tables
• Job Histories
• Run Times
• whole jobs
• individual steps in a job
• Error reports
• Recursive dependancy charts

23

• Use Processes

• Use to aid Design
• Create a "Schedule" process
• Decide a layout style and use across your project
• Collect processes into Subject Areas