change management: il ciclo di vita di un pacchetto...

Matteo Ferrari Gianluca Alessi BMC Software

Change Management: Il ciclo di vita di un pacchetto applicativo DB2


Prologue

We want to follow the life of a release of an application or a single change from the beginning to the end

It is made of: - DB2 Packages (SQL Statement) changes - Schema Changes

It involves different areas: - DBA - Change Process owners - Application owners - Quality Control - Scheduling - IT Operations - Support

Birth

Childhood

Adolescence

Adult


What is the business need?

A new application package in the Production environment - Controlled - Optimized for Performance - Avoid any bad surprise

Before Production rollout - Lack of time/skills to analyze complex SQL - Lack of time/skills to analyze huge number of SQL - Lack of skill to implement a schema change in the most efficient way - Possibility to fall back to a previous version

After Production rollout - Schema Alignment/Sync of different systems (if CLONE-based architecture) - Risk of poor performance - Firefighting - Even good performing SQL can go bad over time

Typically the approach is reactive

Solution is Application Lifecycle Management

It is not a trivial task - Tools, Processes and Organization


Application Lifecycle

Time and resource to maintain multiple environments

Programs and schema follow a Change Management Process during their Lifecycle

Many customers do not plan a Lifecycle Process


The importance of a SQL Lifecycle Process

Standardize

Tune

Stabilize

Measure

THE WINNING RECIPE

One set of simple products

Sophisticated analisys tools

Integration to existing processes

Collaboration between Areas


The importance of a Change Management Process

Versioning

Rollback

Reconciliation

Delivery

THE WINNING RECIPE

One sophisticated change tool

Integration to existing processes

Bi-directional change management


Application Lifecycle Management environments

DEVELOPMENT - Used for Functional test of the single program - Schema changes are made during the development at different times - Normally contains a specific subset of data - Used to test program logic

TEST - Used for Functional test of the whole application - Normally contains a subset of Production data - Used to have a complete picture of the application

PrePROD - Used for Stress and Functional test of the whole application - Normally can contain complete Production data copy or a subset - Used to measure the application performance

PROD - Real Production environment - Here applications run together with a mixed workload


Development enviroment

DEVEL TEST PrePROD PROD

• Alter the definition of the DB2 objects Alter current Schema

• Create new object in a controlled way Create new

• Schema snapshot for each potential point of delivery (release)

Baseline

• DDL, Baseline Output

Alter, Baseline


Standardize SQL


• Reduce potential problems

• Avoid mismatch b/w pgm and SQL Eliminate ‘bad’ SQL

• Correct qualifiers, objects, SQL techniques can be suggested or forced Adhere to standards

• Execute before compiling Test SQL before program

• Check for impact on changes to structures Evaluate impact

Precompile + Explain

Mismatch analysis


Test Environment


• Receive the definition from the development

• Compare between DDL or Baseline and DB2 Catalog

• Apply DDL or Baseline Import

• Enforce Rules of the test environment Standardize

• Schema snapshot before and after Apply Baseline

• Backout to a previous version of the schema Rollback

Import

Migration Profiles


Tune SQL


• Preview performance problems

• Avoid mismatch b/w pgm and SQL Pinpoint inefficiencies

• Correct SQL, finding the best performing way

• Suggest changes to physical structures What-if analisys

• Use DB2 Prod statistics

• Analisys could simulate Production environment Forecast PROD execution

Precompile + Explain

Migrate Statistics

Collect Perf data


Pre Prod Environment


• Compare between the DDL or Baseline or source DB2 Catalog and the target DB2 catalog Compare

• Enforce Rules of the pre-prod environment Standardize

• Schema snapshot before and after the apply Baseline

•Make changes and reconciliation with the Development Environment Reconciliation

Compare b/w Catalog, DDL, Baseline


Stabilize SQL


• Verify what changed since last precompile. Possible warning Historical Compare

• Online analysis of the application workload

• Understand the possible optimizations Deep Perf Analisys

• Run on a real Production-like environment Performance tests

Collect Perf data

Migrate Statistics

Precompile + Explain + Compare


Production Environment

DEVEL TEST PrePROD

• Compare between DDL or Baseline or source DB2 Catalog and target DB2 catalog Compare

• Enforce Rules of the pre-prod environments Standardize

• Apply the changes to the different DB2 subsystems

• one shot

• at different time Apply

• Make changes and reconciliation with the Development Environment Reconciliation

Alter different locations

PROD


Data Changes in a glance

DB2 Catalog

Baselines CDL

Mask, Change & Suppress Rules

Compare

Migrate Profile T

DB2 Catalog

Analysis

Import

CD Tables

ALTER Worklist

Execute

DDL

DB2 Catalog Prod C

Migrate Profile PP

Migrate Profile C

DB2 Catalog Prod B

Migrate Profile B

DB2 Catalog Prod A

Migrate Profile A


Measure SQL


• Verify what changed since last precompile. Possible stop Historical Compare

• Online analysis of the whole workload

• Dynamic SQL analysis

• Understand the possible optimizations Deep Perf Analisys

• Batch Analysis of medium to long period performance trends Trend analisys

Precompile + Explain + Compare

Collect short to long period SQL Perf Data


BMC comprehensive set of products

SQL Explorer for DB2

Apptune for DB2

Workload Compare Advisor

Workload Index Advisor

Exception Advisor

Reorg Advisor

BMC Change Manager for DB2

BMC SQL Performance for DB2 Solution


BMC SQL Performance unique functionalities

Efficiency filtering - Monitor as needed based on

recent performance history Prize for ‘good’ statements

Resource Saving Options - Bypass FETCH timings

Exceptions triggered only on first fetch

- Ignore dynamic SQL text literals All dynamic SQL statements that are

otherwise the same are considered as one SQL statement

- Sample data with extrapolation Monitor a constant proportion of

everything

zIIP offload - All the I/O to read/write

performance data is offloaded


BMC Change Manager unique functionalities

Baseline/Versioning Management - Catalog or DDL as input

Management of different environment standards - Migrate Profiles - DML for massive changes

Structured and controlled process to manage changes - SCOPE -> ANALYZE -> EXECUTE - Import / Export functionalities (Baseline <-> DDL, CDL <-> Workid)

Robust compare engine with multiple sources - CDL, DDL, Catalog, Baseline

Batch Automation - CM/Pilot for the full execution in batch (Compare – Import – Analyze – Execution)


What’s next ?

GUI Interface will complement standard 3270 mode

Task based approach initially to: - Catalog Management - SQL Performance

Open to integration with external products

Functionality included in the base component:

-Catalog Navigation -Explain Compare -What-If Statement -Mini Executor -File Manager

-Workspace Manager -Visual Explain -Bind/Rebind/Free -JES Spool Browser

-DB2 Commands -DDL -DCL -Drop


Benefits of an integrated Change Management Process

Mitigate risk - Proactive control of Application Packages before production - Enforce standards - Meet issues/inconsistencies in the early stages

Performance - Optimize SQL statements in the early stages

Productivity - Reduce or eliminate manual errors, especially for the schema changes - Learn from the tools - Less time spent for troubleshooting


Learn more at www.bmc.com

change management: il ciclo di vita di un pacchetto...

Documents