Table Of Contents

Repository Views

Creating Repository Views

Creating Views Sybase Repository

New and Upgrade Installation

Creating Views in Oracle Repository

New and Upgrade Installation

Using Views in Prime Fulfillment

Summary View

Site View

Customer View

Region View

Repository Views

---

A view is a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables (base tables) in a relational database, a view does not form part of the physical schema; it is a dynamic and virtual table computed or collated from data in the database. Changing the data in a table alters the data shown in subsequent invocations of the view.

The advantages of repository views are as follows:

•Data security: Provides an additional level of table security by restricting access to a pre-determined set of rows and/or columns of a table.

•Provides an easy way to query data from different data sources like a single table.

•Useful when developing complex reports based on multiple tables.

This chapter contains the following sections:

•Creating Repository Views

•Using Views in Prime Fulfillment

Creating Repository Views

This section describes how to create views in Sybase repository and Oracle repository.

•Creating Views Sybase Repository

•Creating Views in Oracle Repository

Creating Views Sybase Repository

New and Upgrade Installation

All the views available in Cisco Prime Fulfillment (see the Using Views in Prime Fulfillment) are created as part of the new and upgrade installation of Prime Fulfillment 6.1.

Creating Views in Oracle Repository

New and Upgrade Installation

To create repository views (see the Using Views in Prime Fulfillment) in new and upgrade installation of Cisco Prime Fulfillment 6.1, follow these steps:

---

Step 1 Copy the schema.tar file to the Oracle server and then extract all files into a directory.

---

Note The schema information is held in the schema.tar file in the software package. Obtain the correct package (schemas can change between packages) and extract the schema.tar file from the package.

---

Step 2 Navigate to the directory containing the expanded schema, then go to the ddl/6.0 sub-directory.

Step 3 Run the command sqlplus.

Step 4 Log in as sysdba and provide the DBA privileges to the Prime Fulfillment user using the command:
GRANT DBA, CONNECT, RESOURCE TO <isc_user>;

Step 5 Log in with the username and password previously created.

Step 6 Enter the SQL command start DBViews.sql;

This will create all the views in Oracle repository.

---

Using Views in Prime Fulfillment

The different views available in Prime Fulfillment are as follows:

•Summary View

•Site View

•Customer View

•Region View

Summary View

You can query using the column name for summary veiw. Table F-1 describes the column name and its type name.

Table F-1 Summary view column names 

Column Name	Type Name
SR_Number	Integer
SR_STATE	Integer
SR_Last_Modified_Time	Varchar
PE_Name	Varchar
PE_Interface	Varchar
PE_Interface_IPAddress	Varchar
CE_Name	Varchar
CE_Interface	Varchar
CE_Interface_IPAddress	Varchar
CE_Type	Integer
CE_Site_ID	Integer
CE_Site_Name	Varchar
VPN_Name	Varchar
VRF_Name	Varchar
Customer_ID	Integer
Customer_Name	Varchar
JOB_DESCRIPTION	Varchar

The description of the column name is as follows:

•SR_Number—Service Request Number, represents the service request JOB ID that is available on the Service Request page in the Prime Fulfillment GUI

•SR_STATE—State of the Service Request and the following table maps the value in the database and its associated state:

Database Value	Associated State
-1	UNKNOWN
0	All States
1	Requested
2	Pending
3	Failed Deploy
4	InValid
5	Deployed
6	Broken
7	Functional
8	Lost
9	Closed
10	Failed Audit
11	Wait Deploy
12	In Progress

•SR_Last_Modified_Time—last modified time of SR based on the current state of the SR

•PE_Name—PE Host Name

•PE_Interface—PE Interface Name associated with SR.

•PE_Interface_IPAddress—IP address of the PE interface

•CE_Name—CE Host Name

•CE_Interface—CE interface name associated with SR

•CE_Interface_IPAddress—IP address of the CE interface

•CE_Type—Management type of the CE Device, the following table maps the value in the database and the CE Management Type:

Database Value	CE Management Type
-1	UNKNOWN
0	Managed
1	UnManaged
2	Managed - Management LAN
3	UnManaged - Management LAN
4	Directly Connected
5	Directly Connected Management Host
6	Multi-VRF
7	Un Managed Multi-VRF

•CE_Site_ID—Site ID of the CE

•CE_Site_Name—Site name of the CE

•VPN_Name—VPN name associated with SR

•VRF_Name—VRF name associated with SR

•Customer_ID—Customer ID

•Customer_Name—Customer Name

•JOB_DESCRIPTION—Job description of MPLS SR

An example for the summary view query is as follows:

select SR_Number, PE_Name, CE_Name, VPN_Name from Summary_View;

Site View

You can query using the column name for site veiw. Table F-2 describes the column name and its type name.

Table F-2 Site view column names

Column Name	Type Name
SITE_ID	Integer
SITE_NAME	Varchar
CPE_Name	Varchar
LINK_ID	Integer

The description of the column name is as follows:

•SITE_ID—Site ID

•SITE_NAME—Site Name

•CPE_Name—CPE name associated with the site

•LINK_ID—Link ID of the CPE associated to a SR

An example for the site view query is as follows:

select Site_Id, Site_Name, CPE_Name, Link_ID from Site_View;

Customer View

You can query using the column name for customer view. Table F-3 describes the column name and its type name.

Table F-3 Customer view column names

Column Name	Type Name
CUSTOMER_ID	Integer
CUSTOMER_CONTACT	Varchar

The description of the column name is as follows:

•CUSTOMER_ID—Customer ID

•CUSTOMER_CONTACT—Information about the customer

An example for the customer view query is as follows:

select * from Customer_View;

Region View

You can query using the column name that is available for region view. Table F-4 describes the column name and its type name.

Table F-4 Region view column name

Column Name	Type Name
PROVIDER_ID	Integer
REGION_ID	Integer
PE_NAME	Varchar

The description of the column name is as follows:

•PROVIDER_ID—Provider ID

•REGION_ID—Region ID of the provider

•PE_NAME—PE Host Name associated to this Region

An example for the region view query is as follows:

select Region_Id, PE_Name from Region_View;