Documentum : DBLink, View, Materialized View, Register Table

Hello,

A post concerning the user of DbLink, View, and Register Table in Documentum.

Presentation of DBLink
In a database management system (DBMS), such as Oracle Database, a DBLink, or database link, is a database object that allows queries to be executed on another database, is physically on the same machine or is remote.
A DBLink is a schema object that causes Oracle to connect to a remote database to access it. This is a local pointer that allows the user to access objects in a remote database.
There are three types of DBLinks:

• Private DBLinks : It is the type of DBLink that is created by default when the user does not specify the type, especially when it does not specify the PUBLIC keyword when creating it. Only the user who created this link can then use it;
• Public DBLinks : This is the type of DBLink that is created when the user specifies the type by the PUBLIC keyword. In this case, this link will be usable by other users;
• Global DBLinks

The syntax for viewing DBLinks already existing in a database is:

select * from dba_db_links;
select owner, db_link, username from dba_db_links;
There is an internal Oracle view, accessible only to the SYS user, allows you to view the creation date as well as the encrypted password:
select NAME, CTIME, HOST, PASSWORD from sys.link$;
The SQL syntax for creating a DBLink is as follows:
CREATE [PUBLIC] DATABASE LINK <name_of_database_link>
[CONNECT TO <utilisateur oracle> IDENTIFIED BY <password_of_oracle_remote_user>]
USING '<database_connection_string>' ;
whereabouts:

The CONNECT TO option allows access to the remote database with a different user name than the current one in the local database session.
is the name of the database to which the DBLink points, if the GLOBAL_NAMES parameter is ‘true’. Otherwise, any identifier can be used.
is a valid SQL * NET connection string (for example, under Oracle, found in the tnsnames.ora file ; this file on Content Servers is in a folder like ‘D:\oracle\ora19c\win64_19_client\network\admin\tnsnames.ora’).

To then use a database link, simply specify the link used, preceded by the symbol ‘@’, before the identifier of the remote object.

For example, to obtain the list of client codes (CLIENT_CODE of MYSCHEMA schema) of the CLIENTS table located on another instance (accessible by the link named MY_DB_LINK):
select CLIENT_CODE from MYSCHEMA.CLIENTS@MY_DB_LINK;
Presentation of View

A view in a database is a synthesis of a request to query the database. It can be seen as a virtual table, defined by a query.

Views are not necessarily purely virtual. Some DBMSs like Oracle Database can store the view on disk, it becomes a real cache system.
Views are created with the CREATE VIEW command. An example, where a company’s employees are in a table, the departments of the company in another and where one has to make a join to display the name of the department with the employee:
CREATE TABLE Employes (id SERIAL, nom TEXT, department INTEGER);
CREATE TABLE Departments (id SERIAL, nom TEXT);
SELECT e.nom as Employe, d.nom as Department FROM Employes e,Departments d WHERE e.department = d.id;
If, a view ViewEveryBody is created:
CREATE VIEW ViewEveryBody AS
         SELECT e.nom as Employe, d.nom as Department
                FROM Employes e, Departments d
                WHERE e.department = d.id;
The above query SELECT can be used much more simply, the join will be no longer visible:
SELECT * FROM ViewEveryBody;
The views are used almost as tables (they can be in a FROM clause of a SELECT, UPDATE, etc) with some restrictions, which depend on the DBMS.
In relational database systems, a view is a virtual table representing the result of a query on the database. As the name suggests and unlike a standard view, in a materialized view the data is duplicated. It is used primarily for optimization and performance purposes in the case where the associated query is particularly complex or cumbersome, or to make table replications.

The freshness of the data in the materialized view depends on the options chosen when it is created. The offset between the data in the master table and the materialized view can be zero (synchronous refresh) or a scheduled duration: time, day, and so on. Depending on the context, there are different types of materialized view: on primary key, rowid, and more or less complex: with aggregation functions, subqueries, joins, etc.
SQL Queries

Minimal syntax for creating a materialized view under Oracle:
CREATE MATERIALIZED VIEW MV1 AS SELECT * FROM scott.emp

Query to create a materialized view with accurate refresh rate under Oracle:
CREATE MATERIALIZED VIEW MV_UneVueMaterialisee
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM mySchema.MyTable;

To find the select of a materialized view under Oracle:
SELECT QUERY FROM ALL_MVIEWS WHERE MVIEW_NAME='MV1'

Use of view via DBLink:
SELECT * FROM ViewEveryBody@MY_DB_LINK; 


SELECT * FROM MV1@MY_DB_LINK;



Register Table – Example 1

How to register a table in database which is outside the documentum server?

Create database link
create public database link
test
connect to
testdb
identified by
testpass
using 'orcl';

Create views for tables (you should select from database link to a view)
CREATE OR REPLACE VIEW banks AS
	SELECT ID, CODE, TYPEID, SHORTNAMESTRID, NAMESTRID, ADDRESSSTRID, PHONE, FAX, EMAIL, TELEX, SWIFTCODE
FROM banks@test;

Create registered tables for views created
register table REPOSITORY.BANKS (
"ID" INTEGER,
"CODE" CHAR(12),
"TYPEID" INTEGER,
"SHORTNAMESTRID" INTEGER,
"NAMESTRID" INTEGER,
"ADDRESSSTRID" INTEGER,
"PHONE" CHAR(40),
"FAX" CHAR(40),
"EMAIL" CHAR(40),
"TELEX" CHAR(40),
"SWIFTCODE" CHAR(11)
)KEY ("ID");


It is possible to select data of registered table from documentum via dql it will pull data from the view and the view will pull data from the other database using the database link.
Register Table – Example 2

REGISTER TABLE on a external table to the DCTM database accessed via VIEW and DBLINK:

SQL to create the dblink and an ORACLE view:
CREATE DATABASE LINK my dblinklocalname CONNECT TO userofdblink IDENTIFIED BY passwordofdblink  USING 'database_connection_string';
create or replace view v_my_view as SELECT
  p.PRS_IDT_PERS as racine, NVL(s.MY_FIELD_1, ' ') as groupe
FROM
  MY_TABLE_0@dblinklocalname p, MY_TABLE_1@dblinklocalname s
WHERE
s.AGT_USER_ID = NVL(p.MY_FIELD_0, ' ') and NVL(s.MY_FIELD_1, ' ') <> ' ' ;

DQL to create a register table in DCTM with the same name as the view:
REGISTER TABLE dm_dbo.v_my_view  (racine char(8), groupe CHAR(32)) ;

DQL to use the register table:
SELECT code, description FROM dm_dbo.v_my_view;


Register Table – Example 3

REGISTER TABLE on a local table to the DCTM database:

SQL to create the table in the database DCTM ORACLE
CREATE TABLE DBO.sg_services
(
	code			INT NOT NULL,
	description		VARCHAR(32),
	groupe			VARCHAR(32)
);


Insert into sg_services (code,description,groupe) values (1,'INT','grp-int');
Insert into sg_services (code,description,groupe) values (2,'AFR','grp-afr');
Insert into sg_services (code,description,groupe) values (3,'SUD','grp-sud');

DQL to register table in DCTM
register table dm_dbo.sg_services (dummy int);
GO

API or DQL to allow everyone to see the register table
retrieve,c,dm_registered where object_name = 'sg_services'
grant,c,l,dm_world,3
save,c,l
...
update dm_registered object set world_table_permit = 15, set group_table_permit = 15, set owner_table_permit = 15 where object_name = 'sg_services';
GO

DQL use of the register table
SELECT code, description FROM dm_dbo.sg_services ;

DQL for unregister a table
unregister dm_dbo.sg_services

API for describe a table
API> describe,c,table,dm_dbo.sg_services
...
Table Name:	sg_services


Columns:	1

dummy                             INTEGER



That’s all!!!
Huseyin OZVEREN
Share this:
Click to share on Twitter (Opens in new window)
Click to share on Facebook (Opens in new window)


	
Related