Shyam Varan NathPresident, Oracle BIWA SIG

Session #464

April 17, 2008 11 AM-12 noon

Database Development Best Practices: Supporting Agile/Extreme

Programming

About Me!

• Oracle DBA/Developer since Oracle V6 (1994).• OCP in 4 database tracks since 1998• Spoken in Oracle Openworld since 2003,

IOUG/Collaborate since 2005• Founder and President of Oracle BIWA SIG• Awarded IOUG – Oracle Contribution Award 2007 for

success of BIWA SIG• Currently Senior Oracle Developer at Citco• Experience of development in Finance/Banking,

Telecomm, Healthcare etc

Introduction

• Agile Development and its impacts to Database Developers/DBA’s

• Understand the buzz words like Agile Development, Xtreme Programming, Scrum

• Adapt ourselves to support such an environment• Develop a free and easy to implement Oracle

database centric framework for handling rapid database changes

What this Session is Not About?

• Database normalization or how to develop ER models

• Designing database for performance optimization or tuning

• Coding tips or writing efficient PL/SQL scripts

What is Scrum?

Software Development Methodology

• Waterfall Methodology• Users Requirements Document (URD)

Database Development Tasks

• Different tasks for a DB developers / DBA• Database Schema Design (ER Diagram)• Schema changes• Data changes, initial data loads• Stored business logic in procedures, functions and

triggers

Challenges for a Database Developer / DBA • A.  Go Crazy with all these releases in-house • OR

B. Use the DB schema numbering methodology developed earlier to keep it under control.

And the final answer is

Development Lifecycle

1. 1. Development phases of new application (it could be deployment of new packaged application with customization instead)

2. 2. Rollout of the application 3. 3. Periodic new features, enhancements

released in phased manner4. 4. Hot fixes / defect fixes for purely

production support.

Product Conformance Matrix

• Product Conformance Matrix to track the difference components of the product including the DB related components

Organized FileSystem

• Name and store the database scripts in organized folders

Patch Framework

• Table for Patch Frameworkcreate table V_PATCH_STAT( stat VARCHAR2(2) not null, stat_txt VARCHAR2(80) not null, crt_usr VARCHAR2(30) default 'user' null , crt_dttm DATE default sysdate null , mdfy_usr VARCHAR2(30) null , mdfy_dttm DATE null ); create table V_PATCH_HIST( patch VARCHAR2(25) not null, patch_level VARCHAR2(10) not null, patch_date DATE default SYSDATE null , version VARCHAR2(10) null , patch_stat VARCHAR2(2) null );  

Patch Execution

• -- !!! Change Previously required patch !!!• Exec

BP_INSERT_PATCH_HIST('&patch_name','&patch_number', '&patch_version', 'DBPatch','236', '1');

• ----------------------------------------------------------------------------• -- [S1] Adding column pay_recv to

PAYMENT_BATCHES• -- Payment Batch Performance Inprovements• -- CODE: TKYTE• ----------------------------------------------------------------------------• create or replace procedure sp_patch_237_S1 as …• create or replace procedure sp_patch_237_S2 as …

etc

Execution of the Framework

CREATE OR REPLACE PROCEDURE sp_patch_schema ISBEGINDBMS_OUTPUT.put_line ('EXECUTE sp_patch_237_S1');sp_patch_237_S1;DBMS_OUTPUT.put_line ('EXECUTE sp_patch_schema

done');--Execute this if there are no schema changes to update

status patch--DBMS_OUTPUT.put_line ('No schema changes for this

patch');END sp_patch_schema;/Execute BP_SCHEMA_CHANGES ('&patch_name',

'&patch_number', '&patch_version'); 

•Code Snippets and Demo

Summary and Recap

• Recap– Agile, Xtreme Development– Impact on Database developers and DBA’s– Adapting to Agile methodology, develop DB

centric framework– Pieces of the framework

Questions?

Thank You

• Please complete the evaluation form– Speaker: Shyam Varan Nath, – Session name: Database Development

Best Practices: Supporting Agile/Extreme Programming

– Session#464– Contact information

• Shyam Varan Nath• www.OracleBIWA.org• 954 609 2402• ShyamVaran@Gmail.com