Informatica Presentation

1
Release Control with PowerCenter at UBS /
Switzerland
Friedrich Lehn Configuration Release
Manager October 2000
2
Agenda

• Project Overview
• PowerCenter Configuration
• Configuration Management
• Deployment Process
• Release Process
• Summary

3
Project Overview
UBS

• Global, integrated investment services firm
  offering- the full range of wealth management,
  asset management, investment banking and
  securities services- to the full spectrum of
  clients
• Worlds largest private bank
• Top ten financial services firm by market
  capitalization- 62 billion dollars
• Total client assets of US1 trillion

4
Project Overview
System Environments
Development
Production
Test

• IBM AIX SP2 cluster
• DB2 UDB EEE
• PowerCenter 1.6

5
Project Overview
System of Records
FilePreprocessing
Load Process
Conditioning
AutoLoader
PowerCenter
AutoLoader
SQL Apply
Common Data Warehouse
6
Project Overview
Delivery Stream

• Main organizational element
• Three letter acronym as base for naming standards
• Each delivery stream gets the same
  infrastructureUNIX directories, meta data area,
  PowerCenter folders
• Each delivery stream has business responsible,
  data modeler, database administrator, delivery
  stream manager
• Team size typically between 1 and 5
• Delivery streams release independently from each
  other

7
Project Overview
System of Records
Application Structure
RCL
MDR
FDS
DSF
DSF
ltSDAgt
ltSDAgt
...
Common Data Warehouse

• Delivery streams
• RCL release control tools
• MDR meta data repository
• DSF DWP sourcing framework
• FDS feed specific data processing
• ltSDAgt subject data area
• ltMARgt data mart

DWP Sourcing Framework
...
ltMARgt
ltMARgt
8
Project Overview
Directory Structure

• Two areas/dwp_root UNIX based files, version
  controlled/dwp_data dynamic data, archival on
  demand
• Additional directory level for the environment in
  order to support more than one environment on one
  system
• /dwp_root is organized by delivery streams

9
Project Overview
Directory Structure (continued)

• /dwp_data is organized by logical processing
  stepsand by delivery streams
• Symbolic links from PowerCenter program areato
  /dwp_data
• Tool support for generation of directories(delta
  processing, integration with release procedures)

10
PowerCenter Configuration
Design Principles

• Start with one single (local) repository
• Support different, clearly separated environments
  with different responsibilities
• All environments have identical structure
  (products, databases, PowerCenter server
  configurations)
• All program changes (UNIX, PowerCenter) are done
  on the development system
• All changes on test and production systems go
  through the release process and are clearly
  tracked

11
PowerCenter Configuration
Repository Structure
D
S
V
S
A
S
P
Server
Server
Server

• D development
• S staging
• V versioning
• A integration / user acceptance test
• P production

12
PowerCenter Configuration
Folder Structure
user area
(avoid shortcuts)
team area
release area

• Release area is identical in all environments.

13
PowerCenter Configuration
Roles and Responsibilities
Role
Responsibility

• developer development, unit and integration
  testing
• delivery stream manager manager, release
  planning
• release manager deployment, tracking,
  configuration control PowerCenter administration
• session manager session management for test and
  production systems

14
Configuration Management
ClearCase

• External tool for software configuration
  management (Rational Corporation)
• Powerful version control, work space andbuild
  management
• Same directory structure as in development
  environment
• Fully automated access layer

15
Configuration Management
Why external version control?

• Integrate all release specific datasourcing
  framework, DDL, documentation, scripts SQL
• Avoid problems with current PowerCenter version
  mechanism (shortcuts, sessions)
• Enable automation of release process
• Different responsibilities for different
  environments
• Enforce change tracking

16
Configuration Management
Release Naming

• ltdelivery streamgt_ltmajorgt.ltminorgt.ltpatchgt
• e. g. ACC_1.0.1
• ltmajorgt major release number (high level wave
  planning)
• ltminorgt minor release number (delivery stream
  development plan)
• ltpatchgt patch level (bug fixes, emergency
  releases)

17
Configuration Management
Versioning Example
reset_acc.sql
/main/1

• Labeling Schema
• ACC_x.y.z release label
• RLS_A current version in user acceptance test
• RLS_P current version in production

18
Deployment Process
PowerCenter Deployment Facilities

• Repository Copy- via GUI / database backup
  restore- trackable (result is a file)- problem
  overwrites server configuration
• Folder Copy- only via GUI- and therefore not
  trackable
• Mapping Copy- only via GUI- used only within
  development repository

19
Deployment Process
Approach

• Repository Copy is the only trackable method
• Delivery Streams require folder level deployment

20
Deployment Process
Freeze, Deliver and Receive Processes
D
S
V
S
A
S
P

• manual folder copy
• database unload / load
• automated process
• delivery package

ClearCase
21
Deployment Process
Repository Unload

• sub unload
• system("db2 connect to repository user user
  using password")
• db2select(\_at_tables, SELECT tabname FROM
  syscat.tables
• WHERE tabschema 'PCSTAGE'
• and (tabname LIKE 'OPB_' or tabname LIKE
  'REP_') and type 'T'
• ORDER BY tabname")
• foreach table (_at_tables)
• system("db2 export to table of del modified by
  coldel0x02 chardel0x03
• select \\ from user.table)
• system("tar cvf tarfile ")

22
Deployment Process
Repository Load

• sub load
• system("db2 connect to repository user user
  using password")
• system("tar xvf tarfile")
• foreach file (("OPB_REPOSIT",
  "OPB_REPOSIT_INFO"))
• system("sed -e 's/CDW_StagingD/name/g' file gt
  tmp.out")
• system("sed -e 's/DDBPCR/dbs/g' tmp.out gt
  file")
• foreach table (ltgt)
• system("db2 delete from user.table)
• system("db2 import from table of del
  modified by coldel0x02 chardel0x03
  delprioritychar insert into user.table)

23
Release Process
Change Control Board

• Responsible for high level planning and impact
  analysis
• Defines release scope and release numbers on
  baseof delivery streams
• Assigns responsibilities (delivery stream
  manager, data modeler, database administrator,
  business responsible)
• Result is documented in wave plan document
• Wave plan is base for all subsequent design,
  implementation and quality assurance tasks

24
Release Process
Release Procedure
Responsible

• 1. Preparation of UNIX and PowerCenter release
  areas delivery stream manager
• 2. Issue release request delivery stream manager
• 3. Create new release (Freeze) release manager-
  Copy to staging repository, unload- Create /
  update ClearCase file versions- Create release
  label and attach to new release
• 4. Create release package (Deliver) release
  manager- delta / full processing- lock release
  in ClearCase
• 5. Install release (Receive) IT integration / IT
  operation - Load, copy from staging repository-
  Post install script
• 6. Create / update database objects in target
  environment database administrator
• 7. Restart PowerCenter server IT integration / IT
  operation

25
(No Transcript)
26
Release Process
Release Database

• Provide easy-to-use information on all releases
• Today ClearCase based
• In future Integration with meta data repository

27
Summary
Experiences

• PowerCenter was successfully introduced at UBS
  and proved to work well within the sourcing
  framework
• Configuration control with ClearCase is efficient
  and straight forward
• Release process meets the requirements and is
  efficient(so far 200 successful releases,
  effort usually below ½ hour)
• Generally little administration overhead although
  some improvements in PowerCenter would make
  administratorsand release managers life easier

28
Summary
Wish List

• Copy of sessions
• Export / import folders via (UNIX) command
  linefor further automation of release process
• Avoid restart after deployment
• Logical backups / restores via command line with
  result on server side (more flexible than
  database backup, avoid ODBC overhead)
• User administration via command line

29
Contact Information

• Friedrich Lehn
• UBS AG, Switzerland
• phone [phone]
• email friedrich.lehn_at_t-online.de