dba’s, oracle designer and the development life cycle by peter wilkinson, mercury international...

DBA’s, Oracle Designer and the Development Life CycleBy 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 maintenanceHow 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 objectUp to 20 extra properties per objectBasic 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.htmOr 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_ 10 VARCHAR2(240)


…….

User Extensions - Code Generation

Using SQL, log into Designer and access your workarea

Work out which tables/views hold your dataMust be familiar with Designer metamodelUse the CI_ views

Create a SQL script to generate an implementation script

PROMPT Connect to Designer databaseCONNECT PWILKINSON@DESSELECT 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 (1)

Connect to the Designer database and workarea

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 '


Select the application system and database


REM Store the id of the database to save a joinVARIABLE c_orad_id NUMBERBEGIN 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;/

Store to ID of the database for later use

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 datuWHERE 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_referenceORDER BY datu.name,dboi.global_synonym_name;


PROMPT Set Buffer Pool for BMS_ANALYSIS_JOBS ALTER TABLE BMS.BMS_ANALYSIS_JOBS STORAGE (BUFFER_POOL RECYCLE);

Using User Extensions (5) The result is a simple, syntactically correct, statement

for every table you want to implement

Impress Me Again!

Advanced 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

Go On….

Write scripts toCreate database links and initialize the propagator Install common proceduresCreate notification (conflict resolution) packagesCreate replication groups and priority groupsAssign database objects (tables/index/packages) to

replication groupsAssign default column groups to tables

Getting the Picture… Write SQL scripts to

Add conflict resolution to tablesGenerate replication supportAdd table triggersAdd 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

ExamplesProject plans Implementation procedures/notes Init.oraListener.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.