Oracle

Oracle#

You can create a project for an Oracle database by connecting directly to the Oracle database or by loading in PL/SQL files. You can also create a project which combines the two ways, by connecting directly to the database and then loading in PL/SQL files. Oracle implementations can consist of a large number of schemas, tables, views, stored procedures, sequences, triggers and other elements which depend on each on each other. Lattix can extract dependencies for all of these elements.

When you create a project for an Oracle database by connecting directly to the database, Lattix obtains its dependency information by querying the database catalog. When Lattix loads PL/SQL files, it parses the source code to extract the dependency information. The Oracle database can also maintain the source code internally. The Options dialog provides an option for you to indicate that Lattix should parse the source code that this loaded in the database. The database catalog does not keep dependency information for stored procedures within packages; instead, it aggregates the dependencies of all the stored procedures within a package to the dependencies of the package. However, when Lattix parses source code, it will extract dependencies for stored procedures also.

Using this approach you can:

  • Obtain a precise big picture view of how your database is organized. Unlike traditional ER diagrams, Lattix allows you to see the interdependencies between all database elements.

  • Understand the impact of change and what needs to be retested whenever database elements are altered.

  • Formalize and maintain the architecture and prevent architectural erosion.

  • Identify undesirable dependencies and missing elements.

  • If necessary, create a migration strategy.

Creating a Model from a Database Connection#

Privileges Required#

Lattix LDM is concerned only with the design of your Oracle database. Therefore, it does not need any access to the actual data in the database. It also does not make any changes to your database. It only needs access to certain tables and views in the catalog.

In particular, please ensure that the user that will be used to connect to the database has SELECT access to the following tables/views:

  • DBA_OBJECTS

  • DBA_DEPENDENCIES

  • DBA_DB_LINKS

  • DBA_INDEXES

  • DBA_CONSTRAINTS

  • DBA_USERS

  • DBA_TRIGGERS

  • DBA_SYNONYMS

Note that a user with SELECT access to the catalog will already have access to these tables and views.

Input to Create a New Project#

Create a new project as follows:

  1. Start LDM.

  2. Select File->New Project.

  3. Select Enterprise .NET or All from the Profiles options.

  4. Select Oracle from the Module Type drop-down.

  5. Choose Oracle Database under Datasources.

  6. Click on Add… in the Datasources panel. You will see the following dialog box show up:

    oracle-connect-65

  7. Please enter the service id, the hostname, the port number, user name and password. Click on OK.

  8. You can specify the schemas to include or exclude by clicking on the Options … button in the Input Sources tab.

  9. Click on Create Project.

You will see an initial DSM appear after the input has been processed.

Filtering Schemas to be Processed#

If you have a large database, you might want to limit the set of schemas included in the model. To do this, you need to change the options for the Input Source for your project.

If you are creating a new project:

  • Select the database connection for the project on the Input Sources tab

  • Press the Options… button near the bottom of the Input Source tab

If you want to edit the Input Source options for an existing project:

  • Select Project Properties from the Project menu

  • Expand the Oracle folder in the left panel of the Project Properties dialog and select Input Sources

  • Select the database connection in the right panel and press the Options… button near the bottom of the dialog

The Oracle Options dialog box will appear:
image0
By default, the SYS, OUTLN, SYSTEM and ORDSYS schemas are filtered. You can use the Oracle Options dialog to either specify the set of schemas to be included in the model or the set of schemas to be filtered from the model.

Creating a Model from SQL Files#

The Oracle module can also parse SQL source stored in files.

image1

Selecting an input source other than Oracle Database using the Input Sources radio buttons on the left hand side of the Create New Project dialog allows you to add files that contain SQL source (DDL and DML) to your project.

Projects can be comprised of both source files and database connections.

Options#

Create Atoms for Synonyms#

If this option is not selected all dependencies between database elements which go through synonyms will become direct. If this option is selected then partition names for synonyms will be created.

Move Public Synonyms#

If this option is not selected, the public synonyms will appear in a partition PUBLIC_SYN under the database. If this option is selected then public synonyms will be moved to the schema where the object the synonym references resides in.

Process Tags Inside Comments#

If this option is selected, comments will be read in to process for tags. The comment should be in the form:

@tag: name

where name will show up as the value of the ‘tag’ property.

Show External References#

If this option is clear, references to undefined symbols are excluded from the model.

Filter Known Externals#

When set, references to known externals (i.e. built-in functions and packages) are excluded from the model.

Use Disk Caching to Minimize Memory Usage#

When parsing SQL source, the memory required to resolve dependencies for large databases can be quite large. If your project does not load due to memory issues, enable this option can often allow Lattix to parse your database.

Parse Oracle Source Code in Database#

Enabling this option causes Lattix to parse the source code for the packages, stored procedures and triggers in your database. This increases processing time, but also captures many dependencies not available from the Oracle data dictionary.

Store SQL Source Code In Project (to Enable View Source)#

Enabling this option causes Lattix to store the source code it parses in the .LDZ file. This allows you to examine the source code that generated a dependency or element in the DSM.

Atom Types and Dependency Kinds#

The Oracle module generates a number of atom types and dependency kinds. This enables filtering, rule specification and usage display based on the value of atom types and dependency kinds.

  • Sequence

  • Field

  • Attribute

  • Column

  • Index

  • Table

  • View

  • Dictionary

  • View Column

  • Package Variable

  • Procedure

  • Type Procedure

  • Trigger

  • Schema

  • Java Class

  • Sql Source File

  • Type

  • Package

  • Synonym

The Lattix LDM Oracle Module defines the following dependency kinds:

  • Data

    • Modify

    • Read

  • Type

  • Code

    • Method Call

  • DML

    • Query Column

    • Query Table

    • Insert Table

    • Update Table

    • Delete Table

  • DDL

    • Column

    • Synonym Definition

    • Drop

    • Foreign Key

    • Use Synonym

  • DBA_DEPENDENCIES

    • Reference

  • Element Reference

  • Script File

    • Definition

    • Script Include (@/@@)

The Initial DSM#

The initial DSM view of the database contains partitions that group various database objects by category.

image2

The toplevel partition contains the name of the service (ORCL in this case), which contains a SCHEMA partition which in turn contains partitions for various schemas in the database. In this picture, the OLAPSYS schema has been expanded revealing that it has PACKAGES, TABLES and VIEWS and that there are quite a few dependencies between them.

Parsing SQL Code from the Database#

By default, the Oracle module does not parse the SQL code in your database. All of the relationships displayed in the initial DSM are generated by querying the Oracle data dictionary. Parsing the SQL code in the database reveals finer granularity of relationships between your packages, procedures and triggers in your database. However, parsing the SQL code in the database requires more resources than traversing the data dictionary. The data dictionary view gives a good overview of the database structure.

If the input for creating your project is comprised of files containing SQL code, then the code is always parsed.

  • Select Project Properties from the Project Menu

  • Select Options in the Oracle folder

  • Check Parse SQL Source Code in Database

  • (optionally) Check Use disk caching to minimize memory use

  • (optionally) Check Store Source Code in Project

  • Press OK

Lattix will then ask you if you want to update the project. Update the project.

Using the OCI Driver#

To get access to this capability, you must install a version of Lattix that has the libraries needed for OCI.
Please contact Lattix to download the plugin.

To create a datasource that uses OCI to connect to the database:

  • Start Lattix and select New Project.. from the File menu

  • Select the All Profile and press Next

  • Select the Oracle Module

    orcl_oci_1

  • Press Add…

  • Enable OCI Driver

    orcl_oci_2

Using the OCI driver allows you to connect to your database using two different naming schemes:

  • TNSName - If you specify only an SID and without a Host or Port, the Oracle module connects to the target database using the SID as the TNSName. To use TNSNames, tnsnames.ora must be in a directory listed in the PATH environment variable or indicated by the system TNS_ADMIN environment variable.

  • Fully Qualified - If you specify an SID, Host and Port, the Oracle module connects to the target database specified by the Host, Port and SID

The example above is connecting using a TNSName.

  • Press OK to confirm the Oracle Database Connection Properties dialog

  • Press Create Project to confirm the Create New Project dialog box and begin processing the database