Transcript Presentation Guidelines

Database Development Best Practices:
Supporting Agile/Extreme Programming
Shyam Varan Nath
President, Oracle BIWA SIG
Session #464
April 17, 2008 11 AM-12 noon
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.
2.
3.
4.
1. Development phases of new application (it could be
deployment of new packaged application with
customization instead)
2. Rollout of the application
3. Periodic new features, enhancements released in phased
manner
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 Framework
create 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 IS
BEGIN
DBMS_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
[email protected]