Transcript DBA`s Oracle Designer and the Development Life Cycle

DBA’s, Oracle Designer and
the Development Life Cycle
By Peter Wilkinson, Mercury International Ltd.
Leslie Tierstein, SCI Consulting, Inc
Why Designer Is Useless for a
DBA
 Always
need to reengineer data model
 Not up to date
 No links to other monitoring tools
 Cannot record new database properties
 Cannot refine indexes for use
 A lot to learn just for a pretty ERD
 Expensive
What Can Designer Do for Me?
 Can
it:
 Monitor
the database?
 Automatically inform me of changes?
 Analyze indexes?
 Suggest new ones?
 Reorganize/partition tables?
 Quickly add users?
 Integrate with other tools I (may/will) have.
What Can Designer Do for Me
 Designer
shows what SHOULD be in the database
– not what currently is in it
 Record and document changes made
Starting with the Server…
Give Me a Good Example
 User
maintenance
 How
is user information currently recorded?
 Who should have a valid login?
 What privileges should they have?
 When were changes made?
 When did users leave?
User Maintenance
What About Buffer Pools! Got You!
 A new
property in 8i
 Expanded in 9i
 Not found in Designer
 Very useful to a DBA for tuning table access
User Extensions (1)
 Add
extra properties to an existing Designer object
 Up
to 20 extra properties per object
 Basic editing properties (numeric/character/date,
length)
 Define
additional objects
 Define associations between objects
User Extensions (2)
 Where
are they stored?
 Refer
to the document
.\CDOC72\model\el_defs\outer_frame2_de.htm
 Or use the F5 key in the Design Editor
 How
can they be used?
Defining a User Extension
SQL> desc ci_table_implementations
Name
Null? Type
CACHED
VARCHAR2(1)
CHANGED_BY
VARCHAR2(30)
COMPLETE_FLAG
NOT NULL VARCHAR2(1)
CREATED_BY
NOT NULL VARCHAR2(30)
DATABASE_USER_REFERENCE
NOT NULL NUMBER(38)
…….
STORAGE_DEFINITION_REFERENCE
NUMBER(38)
TABLE_DEFINITION_REFERENCE
NUMBER(38)
TABLESPACE_REFERENCE
NUMBER(38)
TYPES
NOT NULL NUMBER(38)
USER_DEFINED_PROPERTY_0
VARCHAR2(240)
USER_DEFINED_PROPERTY_1
VARCHAR2(240)
USER_DEFINED_PROPERTY_ 10
VARCHAR2(240)
USER_DEFINED_PROPERTY_11
VARCHAR2(240)
…….
User Extensions - Code
Generation
 Using
SQL, log into Designer and access your
workarea
 Work out which tables/views hold your data
 Must
be familiar with Designer metamodel
 Use the CI_ views
 Create
script
a SQL script to generate an implementation
Using User Extensions (1)
 Connect
to the Designer database and workarea
PROMPT Connect to Designer database
CONNECT PWILKINSON@DES
SELECT name FROM sdd_workareas ORDER BY 1;
ACCEPT p_wa PROMPT 'Select Workarea from above '
EXEC jr_context.set_workarea('&p_wa')
Using User Extensions (2)
 Select
the application system and database
SELECT name FROM ci_application_systems ORDER BY 1;
ACCEPT p_app PROMPT 'Select Application System from above '
SELECT orad.name
FROM
ci_app_sys_databases asda
,ci_oracle_databases orad
,ci_application_systems apps
WHERE orad.id = asda.database_reference
AND apps.id = asda.application_system_reference
AND apps.name = '&p_app'
ORDER BY 1;
ACCEPT p_db PROMPT 'Select Master Definition Database from above '
Using User Extensions (3)

Store to ID of the database for later use
REM Store the id of the database to save a join
VARIABLE c_orad_id NUMBER
BEGIN
SELECT orad.id INTO :c_orad_id
FROM
ci_application_systems apps
,ci_app_sys_databases asda
,ci_oracle_databases orad
WHERE asda.application_system_reference=apps.id
AND asda.database_reference=orad.id
AND apps.name = '&p_app'
AND orad.name = '&p_db';
END;
/
Using User Extensions (4)
SPOOL s:\replication\bms_1\buffer_pools1.sql
SELECT 'PROMPT Set buffer pool for ' || dboi.global_synonym_name || chr(10) ||
'ALTER TABLE ' || datu.name || '.' || dboi.global_synonym_name || chr(10) ||
‘
STORAGE (BUFFER_POOL ' || nvl(dboi.user_defined_property_0,'default') || ');'
FROM ci_oracle_databases orad
,ci_replication_groups repg
,ci_db_object_implementations dboi
,ci_database_users datu
WHERE orad.id = :c_orad_id
AND repg.parent_ivid = orad.ivid
AND dboi.replication_group_reference = repg.id
AND dboi.complete_flag = 'Y'
AND dboi.object_implementation_type = 'TBI'
AND dboi.global_synonym_name IS NOT NULL
AND datu.id = dboi.database_user_reference
ORDER BY datu.name,dboi.global_synonym_name;
Using User Extensions (5)

The result is a simple, syntactically correct,
statement for every table you want to implement
PROMPT Set Buffer Pool for BMS_ANALYSIS_JOBS
ALTER TABLE BMS.BMS_ANALYSIS_JOBS
STORAGE (BUFFER_POOL RECYCLE);
Impress Me Again!
replication – real generation scripts
 Create new properties for priority groups and
values
 Create new properties for conflict resolution
columns
 Define databases and replication groups
 Define common procedures for handling conflicts
 Advanced
Go On….
 Write
scripts to
 Create
database links and initialize the propagator
 Install common procedures
 Create notification (conflict resolution) packages
 Create replication groups and priority groups
 Assign database objects (tables/index/packages) to
replication groups
 Assign default column groups to tables
Getting the Picture…
 Write
SQL scripts to
 Add
conflict resolution to tables
 Generate replication support
 Add table triggers
 Add new master sites
 Use
DBA studio to monitor scripts and tidy up
(push/purge jobs)
 Finally upload the scripts into your workarea
How is this Done?
 Either
 Designers
will have completed applications in Designer
to be implemented in the database
 Reverse engineer an existing application
 Have
your own login and versioning branch
 Create your own DBA workarea and application
 Grant relevant access to applications you have to
manage
 Must have VERSIONING
… And Versioning Means What?
 Keeps
a records of the database before major (or
minor) improvements are done
 Preserves the techniques used to improve indexes
etc. so they can be shown to other DBAs or as a
reminder for future work
 Useful if new changes do not work out
 Designers can learn from your work
 Scripts can be stored and versioned
Storing Documents
 Your
can store many types of documents (ASCII,
Word etc.) into Designer where they are safe and
versioned
 Examples
 Project
plans
 Implementation procedures/notes
 Init.ora
 Listener.ora
Uploading Files
Merging
 Merging
versioned objects
 Changes from bug fixes, amendments, DBA
analysis
 Automatic merging (for the brave)
 Manual merging (for everyone else)
 Shown in Version History Viewer
Merge Window
DBA Benefits
 Self
documenting
 Central control point
 Share information with other DBA’s
 Feedback to developers
 Scripts and other documents are safely stored
Developer Benefits
 Can
see how their application actually gets
implemented
 See how their original index/table specifications
have been improved
 Improve their own design based on how the
application is currently implemented
 Improve communication between design and DBA
groups
Management Benefits
 Increase
team cooptation and communications
 Leverage cost benefits
 DBA knowledge is now stored and shareable
 Better management/auditing reports
 Increase efficiency
 Company procedures (defined)/followed
Questions and Answers
 Now
is the time to ask questions
 This is a real project with real data
 Thank you all for listening and
participating
The End
About the Authors
 Leslie
Tierstein is a Senior Technical Manager at
SCI Consulting, Inc. She can be reached at
[email protected].
 Peter Wilkinson is a DBA at Mercury International
LTD.