Schem SPI Configuration and Maintenance Guide.pdf

SmartPlant Instrumentation Configuration and Maintenance Guide

Version 2013 March 2013 DSPI2-PE-200013B

Copyright ®

Copyright © 1995-2013 Intergraph Corporation. All Rights Reserved. Intergraph is part of Hexagon. Hexagon. Including software, file formats, and audiovisual displays; may be used pursuant to applicable software license agreement; contains confidential and proprietary information of Intergraph and/or third parties which is protected by copyright law, trade secret law, and international treaty, and may not be provided or otherwise made available without proper authorization from Intergraph Corporation.

U.S. Government Restricted Rights Legend Use, duplication, or disclosure by the government is subject to restrictions as set forth below. For civilian agencies: This was developed at private expense and is "restricted computer software" submitted with restricted rights in accordance with subparagraphs (a) through (d) of the Commercial Computer Software - Restricted Rights clause at 52.227-19 of the Federal Acquisition Regulations Regulations ("FAR") and its successors, successors, and is is unpublished and and all rights are reserved under the copyright laws laws of the United States. For units of the Department of Defense ("DoD"): This is "commercial computer software" software" as defined at DFARS 252.227-7014 and the rights of the Government are as specified at DFARS 227.7202-3. Unpublished - rights reserved under the copyright laws of the United States. Intergraph Corporation P.O. Box 240000 Huntsville, AL 35813

Terms of Use Use of this software product is subject to the End User License Agreement ("EULA") delivered with this software product unless the licensee has a valid signed license for this software product with Intergraph Corporation. If the licensee has a valid signed license for this software product with Intergraph Corporation, the valid signed license shall take precedence and govern the use of this software product. Subject to the terms contained within the applicable license agreement, Intergraph Corporation gives licensee permission to print a reasonable number of copies of the documentation as defined in the applicable license agreement and delivered with the software product for licensee's internal, non-commercial use. The documentation may not be printed for resale or redistribution.

Warranties and Liabilities All warranties warranties given by by Intergraph Intergraph Corporation Corporation about equipment equipment or software are set forth in the EULA provided with with the software software or applicable license for the software product signed by Intergraph Corporation, and nothing stated in, or implied by, this document or its contents shall be considered or deemed a modification or amendment of such warranties. Intergraph believes the information in this publication is accurate as of i ts publication date. The information and the software discussed in this document are subject to change without notice and are subject to applicable technical product descriptions. Intergraph Intergraph Corporation is not responsible for any error that may appear in this document. The software discussed in this document is furnished under a license and may be used or copied only in accordance with the terms of this license. No responsibility is assumed by Intergraph for the use or reliability of software on equipment that is not s upplied by Intergraph or its affiliated companies. THE USER OF THE SOFTWARE IS EXPECTED TO MAKE THE FINAL EVALUATION AS TO THE USEFULNESS OF THE SOFTWARE IN HIS OWN ENVIRONMENT. ENVIRONMENT. Intergraph is not responsible for the accuracy of delivered data including, but not limited to, ca talog, reference and symbol data. Users should verify for themselves that the data is accurate and suitable for their project work.

Trademarks Intergraph, the Intergraph logo, PDS, SmartPlant, SmartMarine, FrameWorks, I-Convert, I-Export, I-Sketch, IntelliShip, INtools, ISOGEN, MARIAN, SmartSketch, SmartSketch, SPOOLGEN, SupportManager, and SupportModeler are trademarks or registered trademarks of Intergraph Corporation or its subsidiaries in the United States and other countries. Microsoft and Windows are registered trademarks of Microsoft Corporation. MicroStation is a registered trademark of Bentley Systems, Inc. Other brands and product names are trademarks of their respective owners.

Copyright ®

Copyright © 1995-2013 Intergraph Corporation. All Rights Reserved. Intergraph is part of Hexagon. Hexagon. Including software, file formats, and audiovisual displays; may be used pursuant to applicable software license agreement; contains confidential and proprietary information of Intergraph and/or third parties which is protected by copyright law, trade secret law, and international treaty, and may not be provided or otherwise made available without proper authorization from Intergraph Corporation.

U.S. Government Restricted Rights Legend Use, duplication, or disclosure by the government is subject to restrictions as set forth below. For civilian agencies: This was developed at private expense and is "restricted computer software" submitted with restricted rights in accordance with subparagraphs (a) through (d) of the Commercial Computer Software - Restricted Rights clause at 52.227-19 of the Federal Acquisition Regulations Regulations ("FAR") and its successors, successors, and is is unpublished and and all rights are reserved under the copyright laws laws of the United States. For units of the Department of Defense ("DoD"): This is "commercial computer software" software" as defined at DFARS 252.227-7014 and the rights of the Government are as specified at DFARS 227.7202-3. Unpublished - rights reserved under the copyright laws of the United States. Intergraph Corporation P.O. Box 240000 Huntsville, AL 35813

Terms of Use Use of this software product is subject to the End User License Agreement ("EULA") delivered with this software product unless the licensee has a valid signed license for this software product with Intergraph Corporation. If the licensee has a valid signed license for this software product with Intergraph Corporation, the valid signed license shall take precedence and govern the use of this software product. Subject to the terms contained within the applicable license agreement, Intergraph Corporation gives licensee permission to print a reasonable number of copies of the documentation as defined in the applicable license agreement and delivered with the software product for licensee's internal, non-commercial use. The documentation may not be printed for resale or redistribution.

Warranties and Liabilities All warranties warranties given by by Intergraph Intergraph Corporation Corporation about equipment equipment or software are set forth in the EULA provided with with the software software or applicable license for the software product signed by Intergraph Corporation, and nothing stated in, or implied by, this document or its contents shall be considered or deemed a modification or amendment of such warranties. Intergraph believes the information in this publication is accurate as of i ts publication date. The information and the software discussed in this document are subject to change without notice and are subject to applicable technical product descriptions. Intergraph Intergraph Corporation is not responsible for any error that may appear in this document. The software discussed in this document is furnished under a license and may be used or copied only in accordance with the terms of this license. No responsibility is assumed by Intergraph for the use or reliability of software on equipment that is not s upplied by Intergraph or its affiliated companies. THE USER OF THE SOFTWARE IS EXPECTED TO MAKE THE FINAL EVALUATION AS TO THE USEFULNESS OF THE SOFTWARE IN HIS OWN ENVIRONMENT. ENVIRONMENT. Intergraph is not responsible for the accuracy of delivered data including, but not limited to, ca talog, reference and symbol data. Users should verify for themselves that the data is accurate and suitable for their project work.

Trademarks Intergraph, the Intergraph logo, PDS, SmartPlant, SmartMarine, FrameWorks, I-Convert, I-Export, I-Sketch, IntelliShip, INtools, ISOGEN, MARIAN, SmartSketch, SmartSketch, SPOOLGEN, SupportManager, and SupportModeler are trademarks or registered trademarks of Intergraph Corporation or its subsidiaries in the United States and other countries. Microsoft and Windows are registered trademarks of Microsoft Corporation. MicroStation is a registered trademark of Bentley Systems, Inc. Other brands and product names are trademarks of their respective owners.

Contents Preface .......................................................................................................................................................... 5 SmartPlant Instrumentation Configuration and Maintenance ................................................................ 7 Database Backup and Restore ................................................................................................................... 9 Backing up a SmartPlant Instrumentation Database on SQL Server ................................................... 10 Create a Dump Device on the Source Server ................................................................................ 10 Backup the SmartPlant Instrumentation Database to the New Dump Device on the Source Server................ ................. .................. ................. .................. .................. .................. ....... 10 Restoring a Backed-up SmartPlant Instrumentatio n Database on SQL Server Ser ver ................ ................. .. 10 Preliminary Restore Tasks ............................................................................................................. 11 Restore the SmartPlant Ins trumentation Database on the T arget Server .................. ................. .. 13 Post-Restore Tasks on SQL Server 2005 ...................................................................................... 13 Backing up a SmartPlant Instrum entation Database on Oracle ...................... ................. .................. .. 15 Backup the SmartPlant Instrumentation Database ........................................................................ 16 Restoring a Backed-up SmartPlant Inst rumentation Database on Oracle ............ .................. ............. 16 Preliminary Restore Tasks ............................................................................................................. 16 Restore the SmartPlant Ins trumentation Database on the Target Serv er .................. ................. .. 22 Post-Restore Tasks ........................................................................................................................ 23 SmartPlant Instrumentation Database Technical Review ..................................................................... 31 Multi-User Strategy ............................................................................................................................... 31 Locking Mechanism .................. .................. .................. ................. .................. ................. .............. 31 SmartPlant Instrumentation Users ................................................................................................. 31 Windows Authentication Logon Method ......................................................................................... 32 SmartPlant Instrumentation User Access Rights ........................................................................... 32 Logon Data and Database Connection Security ................. ................. .................. ................. .............. 32 Other Database Objects ....................................................................................................................... 33 Grants ............................................................................................................................................. 33 Synonym ......................................................................................................................................... 33 Sequence ....................................................................................................................................... 33 Internal Setup Utility .................................................................................................................................. 35 Internal Setup Commands .................................................................................................................... 35 SmartPlant Instrumentation Database Structure and Schemas ........................................................... 36 Referential Integrity Constraints ..................................................................................................... 37 Database Relationship Example .................................................................................................... 38 SmartPlant Instrumentation Database Schemas ........................................................................... 38 Admin Schema ............................. ................. .................. ................. .................. .................. .......... 39 Domain Schema ............................................................................................................................. 40 INTOOLS_LOGIN Schema ............................................................................................................ 40 SPI_login Schema .......................................................................................................................... 40 SPIsuper Schema........................................................................................................................... Schema........................................................................................................................... 41 View-Only Domain Schema ........................................................................................................... 41 Owner Operator Domain Schemas ................................................................................................ 41 Connecting to the SmartPlant Instrumentation Database .................................................................... 42 Testing the Database Con nection Common Tasks ................. .................. .................. ................. .. 43


3

Contents Connect to the Admin Schema ................................. .................. .................. .................. ................ 44 Connect to the Domain Schema on Sybase Adaptive S erver Anywhere ............. .................. ....... 44 Connect to the Domain Schema on Oracle .................................................................................... 45 Connect to the Domain Schema on SQL Server .................... .................. .................. ................. .. 46 Connect to the As-Built Schema .................................................................................................... 47 Connect to a Project Schema ......................................................................................................... 48 Connect to a Combined Project Schema ....................................................................................... 48 Checking Database Version Compatibility ..................................................................................... 49 Commands and Controls ................................................................................................................ 49 SQL Queries ......................................................................................................................................... 53 Executing SQL Commands ............................................................................................................ 53 Execute a SQL Command .............................................................................................................. 53 Examples of SQL Commands ........................................................................................................ 54 Save SQL Results in a Specified File Format ................................................................................ 54 Commands and Controls ................................................................................................................ 55 ODBC Profiles ................................. .................. ................. .................. .................. .................. ............. 55 Create an ODBC Profile for Sybase Adaptive Server Anywhere ................................................... 56 Service Operations ............................................................................................................................... 57 Rebuilding Stored Procedures and Triggers .................................................................................. 57 Testing the Printer .......................................................................................................................... 59 Commands and Controls ................................................................................................................ 59 Index ........................................................................................................................................................... 61

4


Preface This document is the user's guide for the configuration and maintenance of the SmartPlant® Instrumentation database. Intergraph® gives you permission to print as many copies of this document as you need for non-commercial use at your company. You cannot print this document for resale or redistribution outside your company.


5

SECTION 1

SmartPlant Instrumentation Configuration and Maintenance This set of documents describes the procedure for configuring and maintaining the SmartPlant Instrumentation Version 2013 database to ensure optimal performance. 



In this guide, whenever a string appears in brackets, type the desired value substituting the entire string, including the brackets. For example: instead of , type c:\orawin. If you encounter problems during any configuration or maintenance process, contact your local SmartPlant Instrumentation provider or Intergraph Support. To locate your Intergraph Support service provider, use a World Wide Web browser to connect to http://support.intergraph.com (http://support.intergraph.com/ ).


7

SECTION 2

Database Backup and Restore The backup and restore procedures are used in conjunction with one another; you need to make a backup of your SmartPlant Instrum entation database if you want to be able to rest ore a corrupted or damaged database from a previously backed up database. You back up a SmartPlant Instrumentation database using your database platform options. 



During the lifetime of your instrumentation data, you store in your SmartPlant Instrumentation database important data and spend a lot of time creating it. Therefore, it is imperative that you maintain backup procedures, and back up the appropriate SmartPlant Instrumentation data regularly. If you have saved external files, for example, trimmed audit trail data files, spec files saved in .psr or .isf format, keep the original external file paths the same on all client machines when restoring your SmartPlant Instrumentation database.

SmartPlant Instrumentation does not have its own backup utility that allows you to back up the entire Oracle or SQL Server database and relies on you to correctly use the backup utility of the selected database. If you want to back up a specific domain in your database, or back up a database implemented in Sybase Adaptive Server Anywhere, use the Administration module options of SmartPlant Instrumentation. Shut down all engines that use your database (for example, InfoMaker) before backing up or restoring your Oracle or SQL Server database. This document includes information about using the backup and restore procedures in SQL Server and Oracle. The information provided here is not intended to substitute the information given in the relevant database platform manual but to supplement it with requirements specific to SmartPlant Instrumentation. Therefore, we strongly recommend that you familiarize yourself with the backup and restore procedures described in your Oracle and SQL Server user's guides before you read this document. In addition to regular backups, it is highl y recommended to backup the SmartPlant Instrum entation database in the following special cases:    

Before upgrading the version of the software Before and after a massive import At any milestone in your set of tasks (for example, when you finish creating your domain) It is also recommended to test the backup of your data, and restore it once prior to the first major modifications to your data using the following sequence of operations: 1.

Make a database backup.

2.

Make some changes to your database.

3.

Restore the database.

4.

Make sure you have your previous version of data.


9

Database Backup and Restore

Backing up a SmartPlant Instrumentation Database on SQL Server The instructions in this procedure apply to SQL Server 2005. The following procedure describes how to back up your SmartPlant Instrumentation database on another server. You start the procedure by running the MS SQL Server Query Analyzer or MS SQL Server Management Studio on the source server where you create a new dump device that will be used for your backup. Then, you back up your database to the new dump device. This allows you to copy the dump device data file to another server, which completes the backup procedure. You cannot include the existing user-defined database views in your database backup. To be able to use these views, you must make a separate backup of all your user-defined database views before backing up your database. Then, after restoring the database, you need to re-create the user-defined views using InfoMaker or the database utilities of your platform.

Create a Dump Device on the Source Server At this stage, you only create the logical name of the dump device, not the dump file itself. 1.

Depending on your SQL Server version, run either the MS SQL Server Query Analyzer or MS SQL Server Management Studio. Make sure that you log in as the System Administrator.

2.

In the Query window, enter the following stored procedure: SP_ADDUMPDEVICE with the following parameters:

3.



First parameter — ''



Second parameter — ''



Third parameter — ''



Fourth parameter — 2 (without quotation marks)

Click Execute Query.

Backup the SmartPlant Instrumentation Database to the New Dump Device on the Source Server 1.

Depending on your SQL Server version, run either the MS SQL Server Query Analyzer or MS SQL Server Management Studio. Make sure that you log on as the System Administrator.

2.

In the Query window, type: BACKUP DATABASE to WITH BLOCKSIZE = 2048, INIT, STATS=5 GO

3.


You can now copy the dump device data file to the target server.

Restoring a Backed-up SmartPlant Instrumentation Database on SQL Server The instructions in this procedure apply to SQL Server 2005.

10


Database Backup and Restore The following section explains how to restore a backed up SmartPlant Instrumentation database on the target server. The restore procedure is divided into preliminary tasks, restore tasks, and post-restore tasks.

Preliminary Restore Tasks 1.

Depending on your SQL Server version, run either the MS SQL Server Query Analyzer or MS SQL Server Management Studio on the source server and log on as the System Administrator.

2.

In the Query window, type the following: EXEC XP_MSVER EXEC SP_HELPSORT EXEC SP_HELPDB EXEC SP_HELPDB EXEC SP_HELPLOGINS EXEC SP_HELPSERVER GO USE GO EXEC SP_HELPUSER EXEC SP_HELPFILEGROUP EXEC SP_HELPFILE EXEC SP_HELPGROUP EXEC SP_HELPROLE EXEC SP_HELPROLEMEMBER GO

3.


4.

Save the results in a text file so that you can use them to plan your restore and post-restore tasks correctly.

5.

Depending on your SQL Server version, run either the MS SQL Server Query Analyzer or MS SQL Server Management Studio on the source server and log on as the System Administrator. 



6.

7.

You must have SQL Server installed on your target server with exactly the same character set and sort order as on the source server. Make sure that you have enough disk space for each data file. To improve the restore performance, create the Indexes, Data, and Log files on different physical disks if possible.

Create an initial SmartPlant Instrumentation database that contains the following file groups: 

Admin data



Admin indexes



Log file

To create these file groups, in the Query window, type the following: CREATE DATABASE ON PRIMARY (NAME = , FILENAME = '', SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%),


11

Database Backup and Restore FILEGROUP (NAME = , FILENAME = '',>', SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%), LOG ON (NAME = , FILENAME = '', SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) GO

12

8.


9.

Add to the initial SmartPlant Instrumentation database the required file groups for all existing domains in the source database. For each additional domain, run the following queries: ALTER DATABASE ADD FILEGROUP GO ALTER DATABASE ADD FILE (name = , FILENAME = '', SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP GO ALTER DATABASE ADD FILEGROUP GO ALTER DATABASE ADD FILE (NAME = , FILENAME = '', SIZE = MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) TO FILEGROUP GO



Restore the SmartPlant Instrumentation Database on the Target Server 1.

In SQL Plus utility, connect to the account SYSTEM.

2.

Copy the source dump device data file from the source server if you have not done so yet

3.

On the target server, create a dump device for the source dump device as follows: a. In the Query window of the MS SQL Server Query Analyzer or MS SQL Server Management Studio, enter the following stored procedure: SP_ADDUMPDEVICE with the following parameters: 

First parameter — ''



Second parameter — ''





Third parameter — '' Fourth parameter — 2 (without quotation marks)

b. Click Execute Query. 4.

Restore the SmartPlant Instrumentation database by running the following operands in the Query window: RESTORE DATABASE FROM WITH MOVE '' TO '', MOVE '' TO '', MOVE '' TO '', MOVE '' TO '', MOVE '' TO '', REPLACE,STATS=5 GO For each additional domain, before the MOVE operand for the log file, enter two MOVE operands: one for the data file and one for the index file.

5.


Post-Restore Tasks on SQL Server 2005 After your SmartPlant Instr umentation database has been restored, you have to create all the required schema logins, define their default database, and grant appropriate access rights. You perform all the post-restore tasks in the MS SQL Server Management Studio. 1.

Run the MS SQL Server Management Studio on the target server and log on as the System Administrator.


13

Database Backup and Restore 2.

Do the following to create the Admin and SPI_LOGIN schema logins and two logins for each domain: a. In the Query window, type: CREATE LOGIN WITH PASSWORD = '', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN SPIsuper WITH PASSWORD = 'spisuper_pwd', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN SPI_LOGIN WITH PASSWORD = 'spi_login_pwd', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN WITH PASSWORD = '', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN WITH PASSWORD = '', DEFAULT_DATABASE = , CHECK_POLICY = OFF b. Repeat the last two CREATE LOGIN commands for each existing domain in your database. c.

3.


When the domain type is Owner operator, create the As-Built schema login for each domain. Also, for each project, create Project and Combined Project schema logins as follo ws: a. In the Query window, type: CREATE LOGIN WITH PASSWORD = '_A_1', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN _E_, WITH PAS SWORD = '_E_', DEFAULT_DATABASE = , CHECK_POLICY = OFF CREATE LOGIN _C_, WITH PASSWORD = '_C_', DEFAULT_DATABASE = , CHECK_POLICY = OFF b. Repeat the last two CREATE LOGIN commands for each project defined in each domain. c.

4.


Define a default database for the SPIsuper login as follows: a. In the Query window, type: EXEC SP_ADDSRVROLEMEMBER 'SPIsuper', 'sysadmin' b. Click Execute Query.

5.

Check the relationship between the SQL Server logins and the SQL Server database users in the restored database. Run the following SQL to generate the login-user relationship report:

EXEC SP_CHANGE_USERS_LOGIN 'REPORT' 6.

14

Fix the database user logins according to the generated report if required as follows:


Database Backup and Restore a. In the Query window, type: USE ' EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '' EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', name>', ''

name> '', 'SPI_LOGIN', 'SPI_LOGIN' '', '
b. Repeat the last two EXEC commands for each existing domain in your database. c. 7.


When the domain type is Owner operator, for each As-Built schema of each domain and also for each Project and Combined Project schemas, do the following to fix the database user logins according to the generated report, if required as follows: a. In the Query window, type: USE GO EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '_A_1','_A_1', EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '_E_', '_E_' EXEC SP_CHANGE_USERS_LOGIN 'UPDATE_ONE', '_C_', '_C_' b. Repeat the last two EXEC commands for each project defined for each domain. c.


Backing up a SmartPlant Instrumentation Database on Oracle The following procedure describes how to back up your SmartPlant Instrumentation database on the same or another server. All the tasks are performed using the Oracle Export utility that writes data from a source database into an operating system file in binary format. This file is stored outside the database, and it can be read into another Oracle database using the Oracle Import utility. You cannot include the existing user-defined database views in your SmartPlant Instrumentation database backup. To be able to use these views, you must make a separate backup of all your user-defined database views before backing up your SmartPlant Instrumentation database. Then, after restoring the SmartPlant Instrumentation database, you need to re-create the user-defined views using InfoMaker or the database utilit ies of your platform.


15


Backup the SmartPlant Instrumentation Database 1.

Invoke the operating system Command Prompt on the source file server.

2.

At the prompt, type: EXP System/Manager@ FULL=Y FILE= BUFFER=5000000 LOG=

3.

Press Enter .

4.

Once export is complete, you can check the log file for details.

This completes the export task. You can now copy the new dump file to another computer if needed.

Restoring a Backed-up SmartPlant Instrumentation Database on Oracle The following procedure describes how to restore your SmartPlant Instrumentation database on another Oracle server. You use the Oracle Import utility to restore the required database. The Oracle Import utility reads the binary dump file created by the Oracle Export utility back into an Oracle database.

Preliminary Restore Tasks It is imperative that you perform these steps in the order presented. 1.

Check the macro design of the source database by doing the following: a. Run the SQL Plus utility. b. In the Log On dialog box, enter the required values to log on as the System Manager. c. In the Oracle SQL Plus window, type and then press Enter after each command: SPOOL Press Enter . Type the following: SELECT TABLESPACE_NAME,FILE_NAME,BYTES FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME,FILE_NAME; Press Enter . Type the following: SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERS ORDER BY USERNAME; Press Enter . Type the following: SELECT * FROM DBA_SYS_PRIVS ORDER BY GRANTEE,PRIVILEGE; Press Enter . Type the following: SELECT * FROM V$NLS_PARAMETERS; Press Enter . Type the following: SPOOL OFF

16


Database Backup and Restore Press Enter . 2.

On the target server, create a new Oracle instance with a new database. Follow the Installation Guide instructions to create the new instance compatible with SmartPlant Instrumentation. Note that the new database must have the same character set as the source database.

3.

To improve the restore performance, create the Indexes, Data, and Temporary files on different physical disks if possible.

4.

Create table spaces for Admin data, Admin indexes, Admin temporary data, Domain data, Domain indexes, and temporary data for each domain. a. In the SQL Plus window, type and then press Enter as follows: CREATE TABLESPACE DATAFILE '' SIZE M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 3000M DEFAULT STORAGE (INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT; Press Enter . Type the following: CREATE TABLESPACE DATAFILE '' SIZE M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 3000M DEFAULT STORAGE (INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT; Press Enter . Type the following: CREATE TEMPORARY TABLESPACE TEMPFILE '' SIZE M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 3000M; Press Enter . Type the following: CREATE TABLESPACE DATAFILE '< full path and name of the Domain data file>' SIZE M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 3000M DEFAULT STORAGE (INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT; Press Enter . Type the following: CREATE TABLESPACE DATAFILE '' SIZE M REUSE AUTOEXTEND ON NEXT 5M MAXSIZE 3000M DEFAULT STORAGE (INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS 249 PCTINCREASE 50) ONLINE PERMANENT; Press Enter .


17

Database Backup and Restore Type the following: CREATE TEMPORARY TABLESPACE TEMPFILE '' SIZE M REUSE AUTOEXTEND ON NEXT 2M MAXSIZE 3000M; Press Enter . b. Repeat the last three CREATE commands for each required domain (as in the source database). c. If the source database domains have only two table spaces, do not repeat the third CREATE command. 5.

Create database users for the Admin, Encryption, Domain, and As-Built schemas. Also, create the INtools_Engineer Database role. In the SQL Plus window, type and press Enter as follows: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter . Type the following: CREATE USER INTOOLS_LOGIN IDENTIFIED BY INTOOLS_LOGIN DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter . Type the following: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter . Type the following: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter .

6.

If the source database contains As-Built schemas, enter the following CREATE commands for each project in the domain: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter . Type the following: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ;

18


Database Backup and Restore Press Enter . Type the following: CREATE USER IDENTIFIED BY DEFAULT TABLESPACE TEMPORARY TABLESPACE ; Press Enter .

7.



A Combined Project schema is the schema of the As-Built and project.



All As-Built schema logon names contain the _A_ string.



All Project schema logon names contain the _E_ string.



All Combined Project schema logon names contain the _C_ string.

Run the following commands to create the INTOOLS_ENGINEER role: CREATE ROLE INTOOLS_ENGINEER; Press Enter .

8.

Grant system privileges to the following SmartPlant Instrumentation database schemas and roles: 

Admin schema



INTOOLS_LOGIN schema



Domain schemas



As-Built schemas



INtools_Engineer database role

a. In the SQL Plus window, type and then press Enter as follows: GRANT UNLIMITED TABLESPACE TO WITH ADMIN OPTION; Press Enter . GRANT CREATE SESSION TO INTOOLS_LOGIN; Press Enter . GRANT CREATE TABLE TO INTOOLS_LOGIN; Press Enter . GRANT CREATE SESSION TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE TABLE TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE SYNONYM TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE VIEW TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE SEQUENCE TO INTOOLS_ENGINEER; Press Enter . GRANT AUDIT ANY TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE PROCEDURE TO INTOOLS_ENGINEER; Press Enter .


19

Database Backup and Restore GRANT CREATE TRIGGER TO INTOOLS_ENGINEER; Press Enter . GRANT CREATE TRIGGER TO ; Press Enter . GRANT EXECUTE ANY PROCEDURE TO ; Press Enter . GRANT UNLIMITED TABLESPACE TO ; Press Enter . GRANT CREATE SESSION TO ; Press Enter . GRANT CREATE SYNONYM TO ; Press Enter . b. Repeat the last five GRANT commands for each required domain. c. If the source database contains As-Built schemas, enter the following GRANT commands for each project in the domain: GRANT UNLIMITED TABLESPACE TO ; Press Enter . GRANT EXECUTE ANY PROCEDURE TO ; Press Enter . GRANT UNLIMITED TABLESPACE TO ; Press Enter . GRANT EXECUTE ANY PROCEDURE TO ; Press Enter . GRANT UNLIMITED TABLESPACE TO ; Press Enter . GRANT EXECUTE ANY PROCEDURE TO ; Press Enter . d. Repeat the last two GRANT commands for each required combination of Engineering and Combined project in the source domain. e. Continue setting the GRANT options: GRANT DBA TO WITH ADMIN OPTION; Press Enter . f.

Continue setting the GRANT options: GRANT CONNECT TO ; Press Enter . GRANT RESOURCE TO ; Press Enter . GRANT INTOOLS_ENGINEER TO ; Press Enter .

g. Repeat the last three GRANT commands for each required domain (as in the source database). h. Continue setting the GRANT options: GRANT CONNECT TO ;

20


Database Backup and Restore Press Enter . GRANT RESOURCE TO ; Press Enter . GRANT INTOOLS_ENGINEER TO ; Press Enter . GRANT CONNECT TO ; Press Enter . GRANT RESOURCE TO ; Press Enter . GRANT INTOOLS_ENGINEER TO ; Press Enter . GRANT CONNECT TO ; Press Enter . GRANT RESOURCE TO ; Press Enter . GRANT INTOOLS_ENGINEER TO ; Press Enter . i.

Repeat the last six GRANT commands for each required combination of Project and Combined Project in the source domain.

j.

Continue setting the ALTER options: ALTER USER INTOOLS_LOGIN DEFAULT ROLE ALL; Press Enter . ALTER USER DEFAULT ROLE ALL; Press Enter . ALTER USER DEFAULT ROLE ALL; Press Enter . ALTER USER DEFAULT ROLE ALL; Press Enter .

k. Repeat the last two ALTER commands for each required domain. ALTER USER DEFAULT ROLE ALL; Press Enter . ALTER USER DEFAULT ROLE ALL; Press Enter . ALTER USER DEFAULT ROLE ALL; Press Enter . l.

Repeat the last two ALTER commands for each required combination of Project and Combined Project in the source domain.

m. Continue setting the ALTER options: ALTER USER QUOTA UNLIMITED ON ; Press Enter . ALTER USER INTOOLS_LOGIN QUOTA UNLIMITED ON ;


21

Database Backup and Restore Press Enter . ALTER USER QU OTA UNLIMITED ON

QUOTA UNLIMITED ON ; Press Enter . ALTER USER

QUOTA UNLIMITED ON ; Press Enter . n. Repeat the last two ALTER commands for each required domain. o. Continue setting the ALTER options: ALTER USER QUOTA UNLIMITED ON ; Press Enter . ALTER USER QU OTA UNLIMITED ON ; Press Enter . ALTER USER QUOTA UNLIMITED ON ; Press Enter . p. Repeat the last two ALTER commands for each required combination of Engineering and Combined project in the source domain. 9.

Restart the target database.

Restore the SmartPlant Instrumentation Database on the Target Server After you have completed the prelim inary restore tasks, you can start restoring your backed-up database by reading the new dump file back into your SmartPlant Instrumentation database. 1.

Copy the new dump file from the source database to the target server.

2.

Import the Admin schema from the dump file. At the operating system command prompt, type: IMP SYSTEM/MANAGER@ FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

3.

If the INTOOLS_LOGIN schema of the source database contains the INTOOLS_LOGIN table, import the source INTOOLS_LOGIN schema from the dump file. At the operating system command prompt, type: IMP SYSTEM/MANAGER@ FROMUSER=INTOOLS_LOGIN TOUSER=INTOOLS_LOGIN FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

22


Database Backup and Restore 4.

Import the source Domain schemas from the dump file. For each required Domain schema, type the following at the operating system command prompt: IMP SYSTEM/MANAGER FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

5.

Import the required source View-Only Domain schemas from the dump file. For each required View-Only Domain schema, type the following at the operating system command prompt: IMP SYSTEM/MANAGER FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

6.

If the source database contains an As-Built schema, import the source As-Built schemas from the dump file. At the operating system command prompt, type: IMP SYSTEM/MANAGER@ FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

7.

To import the Project schemas from the dump file, for each required Project schema, at the operating system command prompt, type: IMP SYSTEM/MANAGER@ FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

8.

To import the Combined Project schemas from the dump file, for each required Combined Project schema, at the operating system command prompt, type: IMP SYSTEM/MANAGER@ FROMUSER= TOUSER= FILE= IGNORE=Y BUFFER=5000000 LOG= Press Enter .

Post-Restore Tasks Post restore tasks include procedures that verify the validity of the imported objects. You can also run SQL queries to find and correct problems. Steps 1 and 2 in the following procedure are only relevant if you are working with old title blocks created before Version 7.0 of SmartPlant Instrumentation. 1.

Create Power Builder System tables for the SmartPlant Instrumentation database. Run the SQL Plus utility and log on as the system manager. In the Oracle SQL Plus window, type: CREATE TABLE PBCATCOL ( pbc_tnam varchar2(30), pbc_tid float, pbc_ownr varchar2(30),


23

Database Backup and Restore pbc_cnam varchar2(30), pbc_cid float, pbc_labl varchar2(254), pbc_lpos float, pbc_hdr varchar2(254), pbc_hpos float, pbc_jtfy float, pbc_mask varchar2(31), pbc_case float, pbc_hght float, pbc_wdth float, pbc_ptrn varchar2(31), pbc_bmap varchar2(1), pbc_init varchar2(254), pbc_cmnt varchar2(254), pbc_edit varchar2(31), pbc_tag varchar2(254)); Press Enter . ALTER TABLE PBCATCOL ADD CONSTRAINT PBSYSCATCOLDICT_IDX PRIMARY KEY (pbc_tnam,pbc_ownr,pbc_cnam); Press Enter . GRANT ALL ON PBCATCOL TO PUBLIC; Press Enter . CREATE TABLE PBCATEDT ( pbe_name varchar2(30), pbe_edit varchar2(254), pbe_type float, pbe_cntr float, pbe_seqn float, pbe_flag float, pbe_work varchar2(32)); Press Enter . ALTER TABLE PBCATEDT ADD CONSTRAINT PBSYSPBE_IDX PRIMARY KEY (pbe_name,pbe_seqn); Press Enter . GRANT ALL ON PBCATEDT TO PUBLIC; Press Enter . CREATE TABLE PBCATFMT ( pbf_name varchar2(30), pbf_frmt varchar2(254), pbf_type float, pbf_cntr float); Press Enter . ALTER TABLE PBCATFMT ADD CONSTRAINT PBSYSCATFRMTS_IDX PRIMARY KEY (pbf_name); Press Enter . GRANT ALL ON PBCATFMT TO PUBLIC; Press Enter .

24


Database Backup and Restore CREATE TABLE PBCATTBL ( pbt_tnam varchar2(30), pbt_tid float, pbt_ownr varchar2(30), pbd_fhgt float, pbd_fwgt float, pbd_fitl varchar2(1), pbd_funl varchar2(1), pbd_fchr float, pbd_fptc float, pbd_ffce varchar2(18), pbh_fhgt float, pbh_fwgt float, pbh_fitl varchar2(1), pbh_funl varchar2(1), pbh_fchr float, pbh_fptc float, pbh_ffce varchar2(18), pbl_fhgt float, pbl_fwgt float, pbl_fitl varchar2(1), pbl_funl varchar2(1), pbl_fchr float, pbl_fptc float, pbl_ffce varchar2(18), pbt_cmnt varchar2(254)); Press Enter . ALTER TABLE PBCATTBL ADD CONSTRAINT PBSYSCATPBT_IDX PRIMARY KEY (pbt_tnam,pbt_ownr); Press Enter . GRANT ALL ON PBCATTBL TO PUBLIC; Press Enter . CREATE TABLE PBCATVLD ( pbv_name varchar2(30), pbv_vald varchar2(254), pbv_type float, pbv_cntr float, pbv_msg varchar2(254)); Press Enter . ALTER TABLE PBCATVLD add constraint PBSYSCATVLDS_IDX PRIMARY KEY (pbv_name); Press Enter . GRANT ALL ON PBCATVLD TO PUBLIC; Press Enter . 2.

Create the Power Builder System PL/SQL package for the SmartPlant Instrumentation database. In the Oracle SQL Plus window, type: CREATE OR REPLACE PACKAGE PBDBMS AS procedure disable; procedure put(a varchar2); procedure put(a number); procedure put(a date);


25

Database Backup and Restore procedure put_line(a varchar2); procedure put_line(a number); procedure put_line(a date); procedure new_line; procedure get_line(line out varchar2, status out integer); type chararr is table of varchar2(255) index by binary_integer; procedure get_lines(lines out chararr, numlines in out integer); end; / Press Enter . CREATE OR REPLACE PACKAGE BODY PBDBMS AS enabled boolean := TRUE; buf_size binary_integer; tmpbuf varchar2(500) := ''; putidx binary_integer := 1; amtleft binary_integer := 0; getidx binary_integer := 2; getpos binary_integer := 1; get_in_progress boolean := TRUE; type char_arr is table of varchar2(512) index by binary_integer; buf char_arr; idxlimit binary_integer; procedure enable (buffer_size in integer default 20000) is lstatus integer; lockid integer; begin enabled := TRUE; if buffer_size < 2000 then buf_size := 2000; elsif buffer_size > 1000000 then buf_size := 1000000; else buf_size := buffer_size; end if; idxlimit := trunc((buf_size+499) / 500); end; procedure disable is begin enabled := FALSE; end; procedure put(a varchar2) is begin if enabled then tmpbuf := tmpbuf || a; end if; end; procedure put(a number) is begin

26


Database Backup and Restore if enabled then tmpbuf := tmpbuf || to_char(a); end if; end; procedure put(a date) is begin if enabled then tmpbuf := tmpbuf || to_char(a); end if; end; procedure put_line(a varchar2) is begin if enabled then tmpbuf := tmpbuf || a; new_line; end if; end; procedure put_line(a number) is begin if enabled then tmpbuf := tmpbuf || to_char(a); new_line; end if; end; procedure put_line(a date) is begin if enabled then tmpbuf := tmpbuf || to_char(a); new_line; end if; end; procedure new_line is strlen binary_integer; begin if enabled then if get_in_progress then get_in_progress := FALSE; putidx := 1; amtleft := 500; buf(putidx) := ''; end if; strlen := lengthb(tmpbuf); if strlen > 255 then tmpbuf := ''; raise_application_error(-20000, 'ORU-10028: line length overflow, ' || 'limit of 255 bytes per line'); end if; if strlen > amtleft then if putidx >= idxlimit then tmpbuf := ''; raise_application_error(-20000, 'ORU-10027: buffer overflow, ' || 'limit of ' || to_char(buf_size) || ' bytes');


27

Database Backup and Restore end if; buf(putidx) := buf(putidx) || ' -1'; putidx := putidx + 1; amtleft := 500; buf(putidx) := ''; end if; buf(putidx) := buf(putidx) || to_char(strlen,'999') || tmpbuf; amtleft := amtleft - strlen - 4; tmpbuf := ''; end if; end; procedure get_line(line out varchar2, status out integer) is strlen binary_integer; begin if not enabled then status := 1; return; end if; if not get_in_progress then buf(putidx) := buf(putidx) || ' -1'; putidx := putidx + 1; get_in_progress := TRUE; getidx := 1; getpos := 1; tmpbuf := ''; end if; while getidx < putidx loop strlen := to_number(substrb(buf(getidx),getpos,4)); if strlen >= 0 then line := substrb(buf(getidx), getpos+4, strlen); getpos := getpos + strlen + 4; status := 0; return; else getidx := getidx + 1; getpos := 1; end if; end loop; status := 1; return; end; procedure get_lines(lines out chararr, numlines in out integer) is linecnt integer := 1; s integer; begin if not enabled then numlines := 0; return; end if; while linecnt <= numlines loop get_line(lines(linecnt), s);

28


Database Backup and Restore if s = 1 then numlines := linecnt - 1; return; end if; linecnt := linecnt + 1; end loop; numlines := linecnt - 1; return; end; end; / Press Enter . DROP PUBLIC SYNONYM PBDBMS ; Press Enter . CREATE PUBLIC SYNONYM PBDBMS FOR PBDBMS ; Press Enter . GRANT EXECUTE ON PBDBMS TO PUBLIC ; Press Enter . 3.

Check if the imported database contains any invalid objects. In the Oracle SQL Plus window, type: SELECT OWNER,OBJECT_TYPE,OBJECT_NAME

FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY 1,2,3; Press Enter . 4.

To fix the invalid objects, in the Oracle SQL Plus window, type: BEGIN FOR REC IN (SELECT OWNER,OBJECT_TYPE,OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY 1,2,3) LOOP DBMS_DDL.ALTER_COMPILE(REC.OBJECT_TYPE,REC.OWNER, REC.OBJECT_NAME); END LOOP; END; / Press Enter .

5.

To ensure that the imported database is free of invalid objects, run the check again as explained in step 3 above.

6.

If the imported database still contains an invalid object, do the following: 

If the number of invalid objects after running the check the next time is a smaller than the number of invalid objects received the previous time (but larger than zero), run the PL/SQL block again as in step 4.

If the number of invalid objects after running the check the next time is the same as the number of invalid objects received the previous time, connect to the schema of each invalid object and find out the cause of the invalid object. Correct each cause separately. For example, to correct an invalid stored procedure, type: ALTER PROCEDURE COMPILE; 

Press Enter . SHOW ERRORS Press Enter .


29

Database Backup and Restore For example, if there is an error showing a missing sequence, to correct the problem, type: CREATE SEQUENCE ; Press Enter . ALTER PROCEDURE COMPILE; Press Enter . If the result of the previous command is "Statement processed", the problem is solved. If you get another message, run the SHOW ERRORS command again. 7.

If, after running the SELECT query in step 3, there are SmartPlant Instrumentation audit triggers (triggers starting with TR_CL_) that are invalid objects, but the P_Audit_Triggers stored procedure is valid, connect to the appropriate Domain schema and run the following PL/SQL block: DECLARE ISTAT INTEGER; BEGIN P_AUDIT_TRIGGERS(ISTAT); EXCEPTION WHEN OTHERS THEN RAISE; END; / Press Enter .

8.

If you want to import only some of the existing source domains, do the following in the target database: a. Run the SQL Plus utility. b. Connect to the Admin schema. c. Run the following SELECT query: SELECT PROJ_ID,PROJ_NAME,PROJ_LOG_NAME,PROJ_LOG_PASS,PROJ_DEL FROM PROJECT; d. Run the DELETE query where you specify the project IDs of the domains that you want to keep. The domains whose project IDs are not specified will be deleted. In the SQL Plus window, type: DELETE FROM ACTIVITY_TRACKING WHERE PROJ_ID IN (SELECT PROJ_ID FROM PROJECT WHERE PROJ_DEL <> 'H' AND PROJ_ID NOT IN (,,)); Press Enter . DELETE FROM MODULE_LICENSE_PROJECT WHERE PROJ_ID IN (SELECT PROJ_ID FROM PROJECT WHERE PROJ_DEL <> 'H' AND PROJ_ID NOT IN (,),); Press Enter . DELETE FROM PROJECT WHERE PROJ_ID NOT IN (,,) AND PROJ_DEL <> 'H'; Press Enter . COMMIT; Press Enter .

30


SECTION 3

SmartPlant Instrumentation Database Technical Review The technical review describes general database features, such as referential integrit y constraints and database concepts. Also, it explains multi-user strategy, lists database schemas, describes connection security issues, and database version control. The Data Dictionary, which describes the tables, their columns, their attributes, and relations between the tables, is available upon request.

Multi-User Strategy Locking Mechanism The internal locking mechanism of the RDBMS prevents two or more users from writing to the same data simultaneously. However, SmartPlant Instrumentation requires an additional locking mechanism to enforce some engineering rules. This is done to prevent simultaneous access by more than one user to the same item or related data. For example, when User A is updating a spec sheet, the system prevents User B from updating the same spec sheet or from modifying the same instrument tag number from the Instrument Index module. Another example is when User A is making changes to a cable. The system prevents User B fr om modifying the cable, its sets, and its wires. Furthermore, if the wires are connected to a terminal, User B cannot access the panels, strips and terminals to which these wires are connected. In this case, neither can User B access panels, strips and terminals associated with these wires by propagation from a device panel. We implemented this locking in the multi-user RDBMS using stored procedures. On Oracle, the locking is done using the statements select ... from ... where ... for update nowait. On SQL Server, a locking table exists on which each locked item has a record. This record will have been created when the transaction is completed.

SmartPlant Instrumentation Users Every user logs on to SmartPlant Instrumentation using a name and a password. The software records SmartPlant Instrumentation user logon name and password (whether encrypted or not) in the INTOOLS_USER table. Two types of SmartPlant Instrumentation users are registered in the database server: 

System Administrator (SA) — This user has privileges of the database System Administrator. These privileges allow the System Administrator to create database schemas, tables, and other database objects. The System Administrator is responsible for setting up the general security definitions, such as password encryption, password expiration, whether user names are required to be unique, and how SmartPlant Instrum entation responds to users who log on with incorrect passwords.


31

SmartPlant Instrumentation Database Technical Review 

SmartPlant Instrumentation user — This user has privileges limited to mainly connected schemas including some minimal rights in the Admin schema. SmartPlant Instrumentation Domain Administrator is a SmartPlant Instrum entation user who has privileges to grant access rights for user groups and perform various administration activities.

Windows Authentication Logon Method The System Administrator can enable the user to log on to SmartPlant Instrumentation using the Windows authentication logon method. Windows authentication logon method allows the software to create SmartPlant Instrumentation users automatically and assign them to existing SmartPlant Instrumentation groups as soon as these users start SmartPlant Instrumentation. For more information, see the Windows Authentication topic in SmartPlant Instrumentation Online Help. The following is an example of a Windows authentication logon format:

Windows domain\Windows user name In this example, the Windows user name corresponds to the name of user's computer.

SmartPlant Instrumentation User Access Rights SmartPlant Instrumentation Domain Administrator assigns users to groups and grants access rights for each group. When assigning a user to a group, the software associates this user automatically with the appropriate domain. A group can contain one or more users and a particular user can belong to more than one group. User definitions and user access rights only apply to SmartPlant Instrumentation application activities and access to SmartPlant Instrumentation items. User definitions and user access rights do not apply to database access or database schema logon name and password.

Logon Data and Database Connection Security This section provides detailed informat ion about database schemas and their functions and about types of SmartPlant Instrumentation users. Also, the section describes how the software retrieves the database schema logon data to connect to the SmartPlant Instrumentation database. Using this information, you can learn how to enhance your logon data and database connection security if needed. SmartPlant Instrumentation is a Client/Server product. Therefore, two layers of security are maintained. On the user side, the application handles access rights to prevent users from accessing different items in the system, and can give different levels of access (for example, view only, modify, add) to various application sections. For details, see Administration Module User's Guide, Domain Administration > Access Rights > Grant Access Rights. On the database side, it is important to prevent users who do not have the appropriate access rights from accessing data using external tools. It is also important to eliminate the possibility of SmartPlant Instrumentation users without appropriate access rights from accessing other data that may reside on the same database server.

32


SmartPlant Instrumentation Database Technical Review

Other Database Objects Grants Grants are given via the INTOOLS_ENGINEER role permissions.  



The Administration (Admin) schema gets a DBA grant. Each domain receives its grant to view all tables in the Administration schema and update permission for REV_SOFTWARE in the domain table. Domains receive grants to update columns in the DOMAIN and LICENSE tables of the Admin schema.

Synonym On Oracle, the synonym is given to Admin tables for each domain so it can be read from each domain.

Sequence The sequence is used to create unique numbers for the table's Primary Keys.  





SEQ_NEXT_ID - to give the next sequence number for the row ID. All new records use the same sequence except the cascade tables: cable-set-wire; and panel-strip-terminal. For these tables, the top-level item type is given a sequence number like all the other tables, then the system just selects the lowest number available and grows sequentially (that is, when creating a panel, the panel_id will be taken from the sequence, the first strip ID will be 1, the first terminal in this strip will be 1, and the rest of the terminals will go from there: 2,3,4,...). On Oracle, the sequence cache is 100. Oracle needs to complete reading a set of 100 sequence numbers before accessing the disk to read the next set of sequence numbers. On Sybase Adaptive Server Anywhere and SQL Server, tables with the same names (SEQ_NEXT_ID and so forth) replace the functionality of the sequence.


33

SECTION 4

Internal Setup Utility The Internal Setup Utility is for the use of System Administrators to troubleshoot and fix database problems in SmartPlant Instrumentation without the need to log on to SmartPlant Instrumentation. The Internal Setup Utility is installed when you perform your network installation. With the Internal Setup Utility, you can execute SQL commands for updating the database or for ODBC configuration. You can also compare and match a version of SmartPlant Instrumentation with the number available in the version information file. Incorrect use of this utility can damage the SmartPlant Instrumentation database. If you are unsure of any of the procedures, back-up your database and contact Intergraph Support before starting to work with the Internal Setup Utility.

Internal Setup Commands File Open — Allows you to open an external SQL file in the Query pane of the Database Type Test and Query window. The encoding of the source SQL file must be Unicode LE. You can then execute the retrieved SQL statements as needed. Available when the Database Type Test and Query window is open and you are connected to the database. Close — Closes the Database Type Test and Query window. Available when the Database Type Test and Query window is open. Save As — Saves the results of an SQL Select command in a specified format. Active only after you connect to a database. Print — Prints the results of an SQL Select command. Active only after you connect to a database. Database Type Test and Query — Opens the Database Type Test and Query window to test your database connection and version compatibility. You can also query your database from this window by means of SQL commands. Printer Diagnostics — Opens the Printer Diagnostics dialog box, which displays details of your current default printer settings and allows you to specify the printing orientation. From this dialog box, you can also launch the Properties dialog box if you need to change the printer settings. Versions — Opens the Versions dialog box, where you compare SmartPlant Instrumentation and template database versions with the version information file.

Action The Action menu is available only when the Database Type Test and Query window is open. Note that you can access most of these commands by clicking the icons that become active when you open this window. (Database connection actions)


35

Internal Setup Utility Use INI file — Copies the database settings from the Intools.ini file to the fields under the Connection setting group box. Connect

— Connects to the database indicated by the parameters.

(Database query actions)

— Active only after you connect to a database.

Execute — Executes any SQL commands that you typed in the Query pane.

Settings Available only from the Internal Setup window. Create ODBC Profile — Creates an ODBC profile to configure SmartPlant Instrumentation database files. Rebuild Procedures and Triggers — Identifies abnormal database behavior and corrects it by rebuilding the stored procedures and triggers in the database.

SmartPlant Instrumentation Database Structure and Schemas The SmartPlant Instrumentation database consists of a hierarchy, where each level of the hierarchy contains tables that are specific to that level. The highest level of the hierarchy is the Admin schema. On the next level of the hierarchy, ther e is a Domain schema for each domain you initialize in the database. For an engineering company domain, the hierarchy can be represented as follows:

Within each domain, there are plant hierarchy levels. By default, each domain has three levels as follows: , , and . When you perform an upgrade, the Admin schema is updated first, and then the software prompts you to upgrade domains (one domain at a time). The following table shows most common types of data you can define at each level of the hierarchy:

36

Level

Table Data Included

Admin schema

Users, user groups, naming conventions, access rights

Note


Internal Setup Utility Level

Table Data Included

Note

Domain schema

instrument types and other supporting tables, spec forms, reference wiring data

Wiring data, custom fields

None

Used as a container only

Loops, instruments

The software checks uniqueness at the level

There is a connection between the data settings at each level of the plant hierarchy, for example, an instrument tag includes data from various tables at the following levels:   



COMPONENT table (contains instruments) at the level Supporting tables at the domain level PD_GENERAL table (contains process functions, such as pressure, flow, temperature, and so forth) at the level SPEC_SHEET_DATA table (contains specification data) at the level

See Also

Database Relationship Example (on page 38) SmartPlant Instrumentation Database Schemas (on page 38)

Referential Integrity Constraints There are foreign keys between the parent and child tables. In some cases the referential constraints cannot be kept via foreign keys due to technical limitations, so the stored procedures and triggers perform the constraints. Due to a mutating problem, some referential integrity in Oracle and SQL Server is kept only in stored procedures and triggers or in the application (referred to in the table as o(p)). Some parent tables in SmartPlant Instrumentation, mostly supporting tables (also known as validation tables) have initial rows for cases where t he child table is not required to have a defined value for the parent table. The ID number of these rows is usually 0. In Sybase Adaptive Server Anywhere, these table columns are defined with a default of 0 and the foreign key is set to default on deletion of the corresponding records. With other platforms, this emulation is done by the application or the database code and a restricted foreign key exists.


37

Internal Setup Utility

Database Relationship Example The following example shows how data in the LOOP and COMPONENT tables is related. The LOOP table includes the following columns: Column

Example Value

Column Status

LOOP_ID

1000

Primary key

LOOP_NAME

F-100

LOOP_SERV

Feed to B-101 Pass A

The COMPONENT table includes the following columns: Column

Example Value

Column Status

CMPNT_ID

999

Primary key

CMPNT_NAME

FT-100

LOOP_ID

1000

CMPNT_SERV

Heat exchanger inlet

Foreign key

A primary key is the column or a com bination of columns where the values determ ine the uniqueness of each record. Note that both tables include the column LOOP_ID. This column is the primary key in the LOOP table. In the COMPONENT table, the LOOP_ID column uses the value of LOOP_ID in the LOOP table to associate the tag to the loop, and is therefore the foreign key in the COMPONENT table. In the LOOP table, there is a zero row, which is used as a reference row if you create a tag that is not associated with any loop. The following SQL Select command retrieves loop numbers and instrument tag numbers with the associations between them: Select component.cmpnt_name, loop.loop_name from component, loop where component.loop_id = loop.loop_id; See Also

SmartPlant Instrumentation Database Structure (see "SmartPlant Instrumentation Database Structure and Schemas" on page 36)

SmartPlant Instrumentation Database Schemas SmartPlant Instrumentation database schemas are internal objects that hold SmartPlant Instrumentation data and enable the software to connect to the database. Each database schema has a logon name and password, which the software uses to connect to the database. The following schemas exist in SmartPlant Instrumentation:      

38

Admin Schema (on page 39) Domain Schema (on page 40) INTOOLS_LOGIN Schema (on page 40) SPI_login Schema (on page 40) SPIsuper Schema (on page 41) View-Only Domain Schema (on page 41)


Internal Setup Utility 

Owner Operator Domain Schemas (on Schemas (on page 41) page 41)

See Also

Connecting to the SmartPlant Instrumentation Database (on Database (on page 42) page 42) SmartPlant Instrumentation Database Structure (see Structure (see "SmartPlant Instrumentation Database Structure and Schemas" Schemas" on page 36) page 36)

Admin Schema The Admin schema holds SmartPlant Instrumentation administration data managed by the System Administrator. The logo name and and password of the Admin schema depend on your database platform.

Admin Schema in a Sybase Adaptive Server Anywhere Database Every shipped Sybase Adaptive Server Anywhere database already contains the Admin schema with the IN_DBAMN logon IN_DBAMN logon name and IN_DBAMN logon password. password. In a Sybase Adaptive Server Anywhere database, it is not possible to modify or encrypt the Admin schema. When connecting to the Admin schema, the software retrieves the Admin schema logon name and password from the Intools.ini file.

Admin Schema in a SQL Server Database The software creates the Admin schema in your SQL Server instance on the database server when you run the DB Setup Utility. The default Admin schema logon name is SPI_DBAMN. SPI_DBAMN . The default Admin schema logon password is SPI_DBA. SPI_DBA. SmartPlant Instrumentation System Administrator can redefine the def ault name and password when running the DB S etup Utility, provided that the schema password is different from the schema name.

Admin Schema in an Oracle Database The software creates the Adm in schema in your Oracle instance on the database server when you run the DB Setup Utility. The default Admin schema logon name name and password is IN_DBAMN. IN_DBAMN. SmartPlant Instrumentation System System Administrator can redefine the default name and password when running the DB Setup Utility. The software adds the Oracle role DBA with maximum privileges to the Admin schema. The software uses the DBA role when running the DB Setup Utility, initializing a new domain, or upgrading the software. Furthermore, the following Administration module activities cannot be performed without the DBA role:            

Assigning Administrator rights Deleting a domain Deleting an invalid domain Accessing the database Encrypting passwords of SmartPlant Instrumentation users Managing security Resizing tablespaces Working with the tablespace list Rebuilding catalog tables Rebuilding stored procedures and triggers Rebuilding domain views Clearing locking

SmartPlant Instrumentation Instrumentatio n Configuration and Maintenance Guide

39

Internal Setup Utility By default, regardless of the database platform, the Admin schema logon password is not encrypted. On Oracle or SQL Server, if needed, the System Administrator can encrypt the Admin schema logon password. For more information, see User's Guide, Administration Module > System Administration > Database Security > Encrypt the Admin Schema Logon Password. When encrypting the password, the software removes the Admin schema logon name name and password from the Intools.ini file and stores this data in the INTOOLS_LOGIN table of the INTOOLS_LOGIN schema. See Also

Connecting to the SmartPlant Instrumentation Database (on Database (on page 42) page 42)

Domain Schema The Domain schema holds SmartPlant Instrumentation domain data, managed by the Domain Administrator and engineers. The software creates the Domain schema when t he System Administrator initiali zes a domain. The System Administrator is prom pted to define the Domain schema name and password and the defined Domain schema name becomes the domain logon name in the database. In Sybase Adaptive Adaptive Server Anywhere, the Domain schema password is not encrypted and cannot be encrypted; after initializing a domain, the Domain schema logon name and password password become set permanently in the software. In Oracle or SQL Server, the System Administrator can encrypt all the Domain schema logon passwords in the database if needed. When performing a database connection, the software retrieves the logon name and password from the PROJECT table of the Admin schema. See Also


INTOOLS_LOGIN Schema INTOOLS_LOGIN is INTOOLS_LOGIN is a SmartPlant Instrumentation database schema created only in Oracle during the database setup. This schema stores the encrypted Admin Admin schema password in the database. The schema logon name and password are both intools_login. intools_login . The logon name is set permanently in the software. The System Administrator can change and encrypt the intools_login password intools_login password if needed. See Also

(on page 42) page 42) Connecting to the SmartPlant Instrumentation Database Database (on

SPI_login Schema SPI_login is SPI_login is a SmartPlant Instrumentation database schema (database user) created only in SQL Server during the database setup. Each SPI_login schema schema stores the encrypted Admin schema password in the database. This schema login is shared for all databases you create in a particular SQL Server instance. For example, if you you create SPI1 and SPI2 databases in the same instance, each database contains the SPI_login schema but, at the instance level, both schemas share the same login, whose name is SPI_login and SPI_login and password is spi_login_pwd (lower spi_login_pwd (lower case). The login name is is set permanently in the software. The System Administrator can change and encrypt the spi_login_pwd password spi_login_pwd password if needed. See Also


40



SPIsuper Schema SPIsuper is is a SmartPlant Instrumentation database user created only in SQL Server during the database setup. This schema is used when you initialize and define a domain, and also when when you back up, up, upgrade, or delete a domain. Furthermore, this schema is used when you run any DBA menu DBA menu option in the System Administration window. The schema login is shared for all databases you create in a particular SQL Server instance. For example, if you create SPI1 and SPI1 and SPI2 databases SPI2 databases in the same instance, each database contains the SPIsuper schema schema but, at the instance level, both schemas share the same login. The login name is SPIsuper and the password is spisuper_pwd (lower case). Both the login name and password are set permanently in the software. See Also


View-Only Domain Schema The View-Only Domain schema holds database views of all tables in a domain. The software creates this schema when the System Administrator initializes a domain. This schema enables enables viewing data for users of report generators, generators, such as Microsoft Access and InfoMaker. The View-Only Domain schema logon name and password that the software creates is _VIEW, NAME>_VIEW, where the DOMAIN NAME segment corresponds to the Domain schema logon name in the database. The logon name is set permanently in the software. The default logon password is _VIEW. NAME>_VIEW. In the database, the password is not encrypted. The System Administrator can change change the password password only once, when when initializing a new domain. On the Initialize dialog Initialize dialog box, the default password a nd the characters that you type when changing t he password appear masked. See Also


Owner Operator Domain Schemas An owner operator domain is a dom ain that supports an As- Built functionality with multiple projects. There are are three owner operator domain schemas. As-Built Schema — Holds As-Built database database views. The software creates the logon name name and password automatically using the following naming convention: _a_1. name>_a_1. The segment corresponds to the Domain schema logon name in the database. The As- Built schema logon name and password are id entical to each other. When perform ing a database connection, the software retrieves the logon name and password from the ENGINEERING_PROJECT table of the Domain schema. schema. The password password _a_1 is not encrypted. The logon name name and password are set permanently in the software. Project Schema — Holds project database views. The software creates the logon name and and password automatically using the following naming convention: _e_. number>. The segment name> segment corresponds to the Domain schema logon name in the database. The Project schema logon name and password are identical to each other. When performing a database connection, the software retrieves the logon name and password from the ENGINEERING_PROJECT table of the Domain schema. The password _e_ is not encrypted. The logon name and password are set permanently in the software.


41

Internal Setup Utility Combined Project Schema — Holds database views of a project used in a combination with As-Built. In SmartPlant Instr umentation, such a combination enables the user t o open a project with As-Built items displayed. The software creates the logon name and password automatically using the following naming convention: _c_. The segment corresponds to the Domain schema logon name in the database. The Combined Project schema logon name and password are identical to each other. When performing a database connection, the software retrieves the logon name and password from the ENGINEERING_PROJECT table of the Domain schema. The password _c_ is not encrypted. The logon name and password are set permanently in the software. See Also

Connecting to the SmartPlant Instrumentation Database (on page 42)

Connecting to the SmartPlant Instrumentation Database The Internal Setup Utility allows you to connect to the Admin schema or Domain schema and then run various SQL commands to view or modify data in the tables. On Oracle or SQL Server, if the Admin schema is not encrypted, any user can connect t o the Admin schema, view user logon information, or update data in the Admin schema tables, or retrieve Domain schema logon information and then connect to the appropriate Domain schema. If the System Administrator encrypted the Admin schema, you can connect to the Admin schema only if you know the Admin schema logon password. It is not possible to encrypt the Admin schema on Sybase Adaptive Server Anywhere. For details about the Admin schema encryption, see User's Guide, Administration Module > System Adm inistration > Database Securi ty > Encrypt the Admin Schema Logon Password . When you connect to the Admin schema, depending on whether the Admin schema password is encrypted or not, the software retrieves the Admin schema logon name and password in one of the following ways: 



If the Admin schema is not encrypted, the software connects to the Admin schema using the Admin schema logon name and password from the Intools.ini file. On an Oracle platform, the default logon name and password are IN_DBAMN. On a SQL Server platform, the default logon name is SPI_DBAMN and the password is SPI_DBA. It is possible to specify different settings during SmartPlant Instrumentation database setup. In SQL Server, the name must be different from the password. If the Admin schema is encrypted, the software connects to the INTOOLS_LOGIN schema and then retrieves the Admin schema logon name and password from the INTOOLS_LOGIN table.

When you connect to a Domain schema, the software performs the following actions: a. Connects to the Admin schema. b. After retrieving the Admin schema logon name and password, the software connects to the Domain schema and retrieves the Domain schema logon name and password from the PROJECT table of the Admin schema. The following connection actions only apply when the domain type is Owner operator : 

42

After connecting to the Domain schema, the software connects to a project schema (As-Built or project) and retrieves the logon name and password from the ENGINEERING_PROJECT table of the Domain schema.



When a SmartPlant Instrumentation user opens a project with As-Built items displayed, the software connects to the Combined Project schema and retrieves the logon name and password from the ENGINEERING_PROJECT table of the Domain schema.

See Also

SmartPlant Instrumentation Database Schemas (on page 38) ODBC Profiles (on page 55) Checking Database Version Compatibility (on page 49)

Testing the Database Connection Common Tasks These tasks deal with testing the connection to the Admin schema or Domain schema of the SmartPlant Instrumentation database. Click the appropriate topic on the Contents tab and then click the topic that you want.

Connect to the Admin Schema Use this procedure to test the connection to the Admin schema in your database. You can either connect to the database whose connection settings are specified in the Intools.ini file or connect to a different database. For details, see Connect to the Admin Schema (on page 44).

Connect to the Domain Schema on Sybase Adaptive Server Anywhere Use this procedure to test the connection to a specific Domain schema residing in Sybase Adaptive Server Anywhere. You can either connect to the Domain schema whose connection settings are specified in the Intools.ini file or connect to a different Sybase Adaptive Server Anywhere database, provided that you previousl y created the appropriate ODBC profil e. For details, see Connect to the Domain Schema on Sybase Adaptive Ser ver Anywhere (on page 44).

Connect to the Domain Schema on Oracle Use this procedure to test the connection to a specific Domain schema residing in Oracle. You can either connect to the database whose connection settings are specified in the Intools.ini file or connect to a different database, provided that you know the Oracle instance alias for the target Oracle database. For details, see Connect to the Domain Schema on Oracle (on page 45).

Connect to the Domain Schema on SQL Server Use this procedure to test the connection to a specific Domain schema residing in SQL Server. You can either connect to the database whose connection settings are specified in the Intools.ini file or connect to a different SQL Server database, provided that you previously created the appropriate ODBC profile. For details, see Connect to the Domain Schema on SQL Server (on page 46).

Connect to the As-Built Schema Use this procedure to connect to the As-Built schema in an owner operator domain. For details, see Connect to the As- Built Schema (see "Connect to the As-Built Schema" on page 47).

Connect to a Project Schema Use this procedure to connect to a specific Project schema in an owner operator domain. For details, see Connect to a Project Schema (on page 48).


43

Internal Setup Utility Connect to a Combined Project Schema Use this procedure to connect to a specific Combined Project schema in an owner operator domain. For details, see Connect to a Combined Project Schema (on page 48). See Also

Checking Database Version Compatibility (on page 49) SmartPlant Instrumentation Database Schemas (on page 38)

Connect to the Admin Schema 1.

On the menu bar, click File > Database Type Test and Query.

2.

Do one of the following:





3.

Click Actions > Use INI File. On the toolbar, click Admin schema.

to retrieve the parameters from the Intools.ini file for the current

Under, Database type group box, select the type of database that you are working in, and then, under Connection settings, type the appropriate database parameters in the boxes to retrieve the parameters from a different Admin schema. On Oracle or SQL Server, the System Administrator can prevent unauthorized connections to the Admin schema by encrypting the Admin schema password using the Administration module option. When encrypting the password, the software removes the Admin schema logon name and password from the Intools.ini file and stores th is data in the database. Therefore, if the password is encrypted, you cannot retrieve it automatically from the Intools.ini file but must enter it manually. For more information, see the Administration Module User's Guide > System Administration > Database Security > Encrypt the Admin Schema Logon Password .

4.

Do one of the following: 

On the menu bar, click Actions > Connect.



On the toolbar, click

.

The Results pane displays the result of the connection. A zero (0) indicates a successful operation. Codes that indicate a connection failure include a message explaining the reason for the failure. See Also

SmartPlant Instrumentation Database Schemas (on page 38) Checking Database Version Compatibility (on page 49)

Connect to the Domain Schema on Sybase Adaptive Server Anywhere 1.


2.


44

To connect to the Admin schema whose parameters are defined in the Intools.ini file on the current client machine, click Actions > Use INI File. The software automatically retrieves the connection parameters from the Intools.ini file for the Admin schema.







3.



The ODBC driver box must display ODBC. In the Schema logon name box, type the target Domain schema logon name specified by the System Administrator when initializing the domain. In the Schema logon password box, type the target Domain schema logon password specified by the System Administrator when initializing the domain.

If the DBParm box is empty, enter the following string: DBParm=ConnectString='DSN=;UID=;PWD=.' 



5.

When connecting to a Domain schema in a database that is located on another machine, make sure that the appropriate ODBC profile exists on the current machine. For details, see Create an ODBC Profile for Sybase Adaptive Server Anywhere (on page 56).

Under Connection settings, do the following: 

4.

To connect directly to the target Domain schema without specifying the Admin schema parameters first, under Database type, select Sybase Adaptive Server Anywhere.

If the Database name box does not display any setting, you can leave it blank. If you connected to the Admin schema using the Intools.ini file parameters, the DBParm string is retrieved automatically from the Intools.ini file. When you change the login name and password, the software automatically updates the DBParm string. For example, if the DBParm string for connecting to the Admin schema is ConnectString='DSN=INTOOLS;UID=IN_DBAMN;PWD=IN_DBAMN' and your target Domain schema name is DEMO and password is also DEMO, the DBParm string changes to ConnectString='DSN=INTOOLS;UID=DEMO;PWD=DEMO'.


Click Actions > Connect.



Click

.



Connect to the Domain Schema on Oracle 1.


2.

Under Database type, do the following:





3.

Select Oracle. In the ODBC driver box, enter the DBMS parameter of your Oracle version. For example, if you use Oracle 9i, enter O90.

Under Connection settings, do the following: 



In the Schema logon name box, enter the target Domain schema logon name specified by the System Administrator when initializing the domain. In the Schema logon password box, enter the target Domain schema logon password specified by the System Administrator when initializing the domain.


45

Internal Setup Utility 4.

In the Server name and Database name boxes, enter the Oracle instance ali as name, which is the same for the Oracle server name and Oracle database name.

5.





Click

.



Connect to the Domain Schema on SQL Server 1.


2.








To connect to the Admin schema whose parameters are defined in the Intools.ini file on the current client machine, click Actions > Use INI File. The software automatically retrieves the connection parameters from the Intools.ini file for the Admin schema. To connect directly to the target Domain schema without specifying the Admin schema parameters first, under Database type, select SQL Server . When connecting to a Domain schema in a database that is located on another machine, make sure that the appropriate ODBC profile exists on the current machine. For details, see ODBC Profiles (on page 55). The ODBC driver box must display MSS. This value is retrieved from the Intools.ini file, where it appears in the [Database] section as the DBMS parameter value. Make sure that in the MSS value of the DBMS parameter appears in upper case. Otherwise, it is not possible to work with specifications in SmartPlant Instrumentation.

3.

Under Connection settings, do the following:

4.

In the Schema logon name box, type the target Domain schema logon name specified by the System Administrator when initializing the domain.

5.

In the Schema logon password box, type the target Domain schema logon password specified by the System Administrator when initializing the domain.

6.

Enter or modify the DBParm string using the following values: DBParm=ConnectString='DSN=;UID=;PWD=', DisableBind=1 If you connected to the Admin schema using the Intools.ini file parameters, the DBParm string is retrieved automatically from the Intools.ini file. When you change the logon name and password, the software does not update the DBParm string. You must manually change the UID and PWD values to match the Domain schema logon name and password respectively. 

46

For SQL Server 2008, the default ODBC profile name for SQL Server is MSS2008. For SQL Server 2012, the default ODBC profile name for SQL Server is MSS2012.



7.

If the Server name and Database name boxes do not display an y settings, you can leave them blank.





Click

.



Connect to the As-Built Schema 1.


2.

Connect to the Domain schema.

3.

In the Query pane, type the following command: select * from ENGINEERING_PROJECT;

4.

Click Execute.

5.

On the SQL Result window, scroll right to display the Eng Log Name and Eng Log Pass columns.





The Eng Log Name and Eng Log Pass columns contain As-Built and Project schema logon information. In the ENGINEERING_PROJECT table, the As-Built row is the one whose Eng Proj ID column value is 1.

6.

Type the As-Built schema logon name, which appears in the Eng Log Name column, in the Schema logon name box in the Database Type Test and Query window.

7.

Type the As-Built schema logon password, which appears in the Eng Log Pass column, in the Schema logon password box in the Database Type Test and Query window.

8.





Click

.


SmartPlant Instrumentation Database Schemas (on page 38)


47


Connect to a Project Schema 1.


2.


3.

In the Query pane, type following command: select * from ENGINEERING_PROJECT;

4.

Click Execute.

5.

On the SQL Result window, scroll right to display the Eng Log Name and Eng Log Pass columns. The Eng Log Name and Eng Log Pass columns contain Project and As- Built schema logon information. The Engineer Name column displays names of existing projects.

6.

Type the target Project schema logon name, which appears in the Eng Log Name column, in the Schema logon name box in the Database Type Test and Query window.

7.

Type the target Project schema logon password, which appears in the Eng Log Pass column, in the Schema logon password box in the Database Type Test and Query window.

8.





Click

.



Connect to a Combined Project Schema 1.


2.


3.

In the Query pane, type following command: select * from ENGINEERING_PROJECT;

4.

Click Execute.

5.

On the SQL Result window, scroll right to display the Eng Log Name and Eng Log Pass columns. The As-Built log Name and As-built Log Pass columns contain Combined Project schema logon information. The Engineer Name column displays names of existing projects.

6.

Type the target Combined Project schema logon name, which appears in the As-built log Name column, in the Schema logon name box in the Database Type Test and Query window.

7.

Type the target Combined Project schema logon password, which appears in the As-built Log Pass column, in the Schema logon password box in the Database Type Test and Query window.

8.


48






Click

.


Internal Setup Utility The Results pane displays the result of the connection. A zero (0) indicates a successful operation. Codes that indicate a connection failure include a message explaining the reason for the failure. See Also


Checking Database Version Compatibility When you run SmartPlant Instrumentation after installation or update, if a message appears that there is an inconsistency between the database and the application, this procedure can help you to start troubleshooting the source of the problem. You can then notify Intergraph Support.

Check the Database and Application Versions 1.

On the menu bar, click File > Versions.

2.

Click Read. By default, the Internal Setup Utility reads data from the SmartPlant Instrumentation database and the version information file.

3.

Beside Domain, select the domain in which you want to check the version numbers. 



If the version information file number is higher than the corresponding domain database number, the most likely reason for the incompatibility is that the database needs updating. In this case, you should open the Administration module and run the upgrade option on the domain. If the version information file number is lower than the corresponding domain database number, the most likely reason is that an older SmartPlant Instrumentation version was installed over an existing database. In this case, you should contact Intergraph Support for further assistance. The template database does not have a version number.

Commands and Controls Database Type Test and Query Window You use this window to connect to a database and to run SQL commands. On the menu bar, click File > Database Type Test and Query to open this window.

Database type In this group box, you select the platform on which your database runs. The following options are available: Sybase Adaptive Server Anywhere Oracle SQL Server

  

If you click Actions > Use INI File, the software selects the database type automatically.


49

Internal Setup Utility ODBC driver — Accept the default value for the database platform, or type a different value. If your database is Oracle, you must enter the DBMS parameter of your Oracle version. For example, for Oracle 11g, enter ORA. If you click Actions > Use INI File, the software retrieves the DBMS parameter value from the [Database] section of Intools.ini f ile and displays this value in the ODBC driver box automatically. When the database platform is SQL Server, make sure that the ODBC driver box displays MSS in upper case. In the Intools.ini file, the string DBMS=MSS must display MSS in upper case; otherwise, it is not possible to work with specifications in SmartPlant In strumentation.

Connection settings Displays the connection settings retrieved from the Intools.ini file or allows you to enter the connection settings manually if you need to connect to another database. Schema logon name — Allows you to type the logon name of the Admin or Domain schema. Schema logon password — Allows you to type the logon password of the Admin or Domain schema. The password characters appear masked. Server name connect.

— Allows you to type the Oracle instance alias for the server to which you want to

Only required when the database type is Oracle. Database name — Allows you to type the Oracle instance alias for the database to which you want to connect. The alias name is the same for both boxes. Only required when the database type is Oracle. DBParm — Displays the connection string as defined for the DBParm parameter under the [Database] section of the Intools.ini file. Only required when the database type is SQL Server or Sybase Adaptive Server Anywhere. On SQL Server, if you first connected to the Admin schema by using the Intools.ini file parameters, when connecting to a Dom ain schema on SQL Server, you must manually change the UID and PWD values to match the displayed target Domain schema logon name and password respectively. On Sybase Adaptive Server Anywhere, the DBParm string is updated automatically as you change the values in the Schema logon name or Schema logon password boxes. Auto commit — Allows you to execute a batch of SQL commands. If you clear the check box, when executing SQL commands, the software prompts you to execute each SQL command manually. Query — Allow you to enter the SQL command that you want to execute for the database to which you are connected. Results — Displays the result of making a database connection or executing SQL commands. On connecting to the database, a zero (0) parameter indicates a successful connection.

Database Type Test and Query Toolbar Use .INI — Allows you to copy the database settings from the Intools.ini file to the boxes in the Connection setting dialog box. Connect — Connects to the database specified in the Database type and Connection setting parameters. The following icons are active only after you connect to a database. Execute

50

— Executes the SQL commands that you typed in the Query pane.


Internal Setup Utility Open

— Opens an .sql file.

Save as Print

— Save the results of an SQL Select command in a specified format. — Print the results of an SQL Select command.

Versions Use this dialog box together with Intergraph Support to compare SmartPlant Instrumentation and template database versions with the version information file, c_ver.pbd.

Read from From this group box, select one or more of the following: User database — Select to read version data from the database used for creating and managing SmartPlant Instrumentation data. Template database — Select to read version data from the SmartPlant Instrumentation template database file, in_templ.db. This file is used for database upgrade. Version file — Select to read version data from the version information file, c_ver.pbd. Domain — After clicking Read to display the data, select the domain to which you want to compare the version information. Admin — Displays the Admin schema version ID in each category. Domain — Displays the Domain schema version ID in each category.


51

SECTION 5

SQL Queries This section contains topics about using SQL Queries in the Internal Setup Utility. In This Section

Executing SQL Commands ........................................................... 53 Execute a SQL Command ............................................................. 53 Examples of SQL Commands ....................................................... 54 Save SQL Results in a Specified File Format ............................... 54 Commands and Controls ............................................................... 55

Executing SQL Commands You can execute SQL commands to retrieve data from the database, or to update values in the database. After retrieving data, you can save it in several formats such as Excel spreadsheets, PowerSoft reports, SQL, and so forth. You execute SQL commands in the Database Type Test and Query window, in which you can either type an SQL command in the Query pane or load SQL commands from an external SQL file, provided that the file encoding is Unicode LE. In the Internal Setup Utility, it is possible to run certain SQL commands generated by the DBChecker Utility. However, when using the Internal Setup Utility on the SQL Server platform, commands that contain 'go' do not run. To be able to run these commands in the Internal Setup Utility, change all the occurrences of 'go' with ';'.

Execute a SQL Command 1.


2.


Define connection parameters for the Admin schema by clicking Actions > Use INI File on the menu bar or by clicking



3.

4.

on the toolbar.

Define connection parameters for the appropriate Domain schema. For details, see Testing the Database Connection Common Tasks (on page 43).


On the menu bar, click Actions > Connect.



On the toolbar, click

.






Under Query, type the SQL command that you want to execute. End each command with a ; (semicolon) and do not type more than one SQL Select command. On the menu bar, click File > Open, and then, select an external SQL file with the .sql extension.

The encoding of the source SQL file must be Unicode LE. Any other SQL file encoding is not supported. 5.


On the menu bar, click Actions > Execute.


53


Examples of SQL Commands The following SQL Select command retrieves the current software version and domain name from the Admin schema tables: Select rev_software, proj_name from project; The following SQL Select command retrieves loop numbers and their associated instrument tag numbers from the Domain schema tables: Select component.cmpnt_name, loop.loop_name from component, loop where component.loop_id = loop.loop_id; or:

Select c.cmpnt_name, l.loop_name from component c, loop l where c.loop_id = l.loop_id;  

End each command with a ; (semicolon). In this example, the where argument ensures that the tag numbers appear with the correct relationship to the loops to which they belong.

Save SQL Results in a Specified File Format 1.

Execute an SQL Select command.

2.

With the SQL Result dialog box open, click File > Save As.

3.

On the Save As dialog box, select one of the following data formats: 



Data Only: Saves only the data that is visible in the preview. This option is recommended for easier viewing of the data, and also enables you to manipulate the headers and select the columns you want to display.

4.

Click OK and navigate to the location where you want to save the file.

5.

Select the required file format and type the name of the file, then click Save.





54

Original: Saves the data in all the fields, including the ones that are not visible in the preview, for example, internal database ID numbers. This option is recommended if you want to save the file in .psr or .sql format, or if you want to re- import the data into SmartPlant Instrumentation at a later stage.

You can save a result in the original format as an SQL file under the name of the table that you specified in the Select command. When you run the SQL file, it creates the table. You can delete all the data except for the set of values under the first insert into section and run the file as a script to insert the zero (default) line if it is missing. This is a requirement for making correct validations in Sybase Adaptive Server Anywhere. If you save SQL results in SQL format and you intend to open the SQL file in the Internal Setup Utility later, make sure you select the correct encoding, which is Unicode LE.



Commands and Controls Save As Dialog Box This dialog box enables you to save your query results as an external file. Among the formats supported are Microsoft Excel, PowerSoft, and SQL.

Original Saves the data in all the fields, including internal database ID numbers and so forth. This option is recommended if you want to save the file in . psr, .xls, or .sql format, or if you want to re-import the data into SmartPlant Instrumentation at a later stage.

Data only Saves only the report data that is visible in the preview. This option is recommended for easier viewing of the data, and enables you to manipulate the headers and select the columns you want to display. Include headers — Allows you to include the column headers in the new file you are saving. This option is available only if you selected Data only. Edit headers — Opens the Edit Column Headers dialog box where you can edit the column headers SmartPlant Instrumentation saves in the new external file. This option is available only if you selected Data only and Include headers.

ODBC Profiles SmartPlant Instrumentation uses an ODBC profile when connecting to a SmartPlant Instrumentation database on SQL Server or Sybase Adaptive Server Anywhere. When you install SmartPlant Instrumentation on SQL Server or Sybase Adaptive Server Anywhere, the software automatically creates an ODBC profile on your client machine. The created profile type is System DSN, which allows every user work ing on this machine to connect to the database using this profile. You do not need to configure this profile to connect to SmartPlant Instrumentation.

SQL Server ODBC Profiles When you install SmartPlant Instrumentation on SQL Server, the software creates an ODBC profile on the Windows registr y level and also adds database profile connection parameters in the [Database] section of the Intools.ini file. For SQL Server 2008 (or SQL Server 2012) database server, on every client machine where you install SmartPlant Instrumentation, the software creates the MSS2008 (or MSS2012) profile. It is not possible to create SQL Server ODBC profiles using the Internal Setup Utility. However, you can use the Internal Setup Utility to test the connection to your database using any other ODBC profile for SQL Server that exists in your Windows registry. 



To ensure that you do not receive errors when opening a domain from your database, in the [Database] section of the Intools.ini file, the ServerName parameter must have the same value as in the ODBC connection string. You must use your tools to create an ODBC profile if you want to connect from your client machine to another SmartPlant instrumentation database residing on a different machine.


55

Internal Setup Utility Sybase Adaptive Server Anywhere ODBC Profiles When installing SmartPlant Instrumentation, the software creates an ODBC profile for every shipped .db file on the Windows registry level and also adds database profile connection parameters in the [Database] section of the Intools.ini file. For example, if you chose to create an empty database rather than a demo database, in the Intools.ini file, the software automatically sets the connection parameters to enable the use of the ODBC profile created for the Intools.db database. Connection parameters for other ODBC profiles are commented out. When you install SmartPlant Instrumentation Stand-Alone for Sybase, for every shipped Sybase Adaptive Server Anywhere database, the software adds the following ODBC connection string: DBParm=ConnectString='DSN=;UID=;PWD=' You must create an additional ODBC profile in the following cases: 



You need to connect from your client machine to another SmartPlant instrumentation database residing on a different machine. You need to connect to another Sybase Adaptive Server Anywhere database on your client machine. For example, you received a .db file you want to use as a source for domain initialization, or you renamed an existing .db file or moved it to another folder.

The Internal Setup Utility provides a convenient method of creating ODBC profiles for Sybase Adaptive Server Anywhere, although, you can define ODBC profiles using the W indows Control Panel, Data Sources option, on the System DSN tab or User DSN tab. When creating an ODBC profile using the Internal Setup Utility, you have an option to define the profile creation method. The following methods are available: 





System DSN — Allows you to create a database prof ile on the registry entry level of the local machine so that every user working on this machine can connect to the database using this profile. The profile appears in the Windows registry in the following path: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. User DSN — Allows you to create a database profile on the registry entry level of the current user so that only the current user working on this machine can connect to the database using this profile. The profile appears in the Windows registry in the following path: HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. User DSN and System DSN — Allows you to create a profile on both registry entry levels.

See Also


Create an ODBC Profile for Sybase Adaptive Server Anywhere

56

1.

On the menu bar, click Settings > Create ODBC Profiles to open the ODBC Profile Wizard.

2.

Click Next, and under Profile name, type a name for the profile, and then click Next.

3.

Under Database path and filename, type the database path and filename, or click Browse to navigate to the target database file if the target database resides in S ybase Adaptive Server Anywhere (for example: Intools.db ), and then click Next.

4.

Under Schema name, type the schema name for this profile, or accept the default, and then click Next.

5.

Under Schema password, type the password, or accept the default, and then click Next.

6.

Under Database driver path, do one of the following: 

Accept the displayed path and database driver.



Type the path and database driver.





Click Browse to navigate to the database driver.

7.

Click Next, and under Database start command, accept the displayed value, or type the name and path of the database engine, and then click Next.

8.

On the Update INTOOLS.INI file page, do one of the following: 



9.

To update the Windows registry and the [Database] section of the Intools.ini file, select Modify INTOOLS.INI and Windows registry. To update the registry, but not the Intools.ini file, select Modify Windows registry only.

From the Create Windows registry entry list, select one of the following options: 





SYSTEM DSN — to create a database profile on the registry entry level of the local machine so that every user working on this machine can connect to the database using this profile. The profile appears in the Windows registry in the following path: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI. USER DSN — to create a database profile on the r egistry entry level of the current user so that only the current user working on this machine can connect to the database using this profile. The profile appears in the Windows registry in the following path: HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI. USER DSN and SYSTEM DSN — to create a profile both registry entry levels.

10. Click Next, and then click Finish. 11. Check the connection to the database. For details, see Testing the Database Connection Common Tasks (on page 43). 





You can also configure the profile using the Windows Control Panel, Data Sources option, on the System DSN tab or User DSN tab. If a database profile row already exists in the intools.ini file, the Internal Setup Utility adds the profile as a new DBParm row, for example, for a Sybase Adaptive Server Anywhere ODBC profile, the following row is added: DBParm=ConnectString='DSN=TEST;UID=IN_DBAMN;PWD=IN_DBAMN' If there are more than three rows representing database profiles, the Internal Setup Utility deletes the first row from the intools.ini file when adding the new row.

Service Operations This section contains topics about various service operations performed in the Internal Setup Utility.

Rebuilding Stored Procedures and Triggers This procedure allows you to identify abnormal database behavior and correct it by rebuilding the stored procedures and triggers in the database. Stored procedures and triggers are portions of code that run automatically when you perform certain actions that affect the database. For example, when you delete a loop, there is a stored procedure that runs to delete an associated loop drawing. You can re-create stored procedures either for the Admin schema or for the Domain schema. The System Administrator should perform this procedure if inappropriate SQL error messages are displayed in SmartPlant Instrumentation. Normally, you should run this option from the Adm inistration module; however, you can run it from the Internal Setup Utility if for som e reason you cannot log on to the Administration module.


57


Rebuild Stored Procedures and Triggers 



This procedure should only be carried out when you are specifically instructed to do so by Intergraph Support. Make sure that all users have logged out of the SmartPlant Instrumentation database before starting this procedure. When you start rebuilding stored procedures and triggers, no users should attempt to log on to SmartPlant Instrumentation.

Create a Batch Rebuild File As in batch upgrade, you can create a .bat f ile that rebuilds stored procedures and triggers for multiple domains simultaneously. The following procedure describes how to create a batch rebuild file. 1.

In Notepad, create a batch file (for example, BatchRebuild.bat).

2.

Enter a line for each domain you are asked to rebuild stored procedures and triggers for, as shown in the example below: Upgrade -9999 //rebuild stored procedures and triggers for admin schema Upgrade - //rebuild stored procedures and triggers for a specific domain

3.

Double-click the batch file to run it and rebuild stored procedures and triggers for the defined domains. You can schedule the batch file to run at a convenient time using W indows scheduler (for example, during the night or over the weekend).

Rebuild Stores Procedures and Triggers Manually 1.

With the System Administration window open, click DBA >Rebuild Stored Procedures and Triggers.

2.


Click Admin schema to rebuild stored procedures and triggers of the Admin schema.

Click Domain schema and then, under Domain, select a domain for which you can rebuild stored procedures and triggers.





3.

Click OK. 



When rebuilding stored procedures and triggers of the Admin schema, the Domain list is redundant. If the process stops for any reason, you can restart the process and the process continues from where it stopped. If you get an error that cannot be corrected, contact Intergraph Support with the error description. It is recommended that you have your log file available when contacting Intergraph Support.

4.

On the Rebuild Stored Procedures dialog box you can click Browse to specify the log file path and filename, if you do not want to use the default Log.txt file. This file contains information of the current session and any errors that may have occurred in previous sessions. The log file is concatenated, if you use the same log file name.

5.

Click OK to start rebuilding the stored procedures and triggers of the selected schema.

At the end of the process, an appropri ate message is displayed and an additional log file Logerror.txt is generated in the SmartPlant Instrumentation home folder. The data in the

58


Internal Setup Utility Logerror.txt file is incremented between sessions. Send the Logerror.txt file to Intergraph Support after you complete the process. See Also

SmartPlant Instrumentation Database Schemas (on page 38) Connecting to the SmartPlant Instrumentation Database (on page 42)

Testing the Printer This option allows you to troubleshoot printing problems by testing the default printer and displaying current settings.

Commands and Controls Printer Diagnostics Dialog Box You use this dialog box to display the settings of the current default printer. The dialog box also allows you to access a dialog box to change the printer settings. To access this dialog box, click File > Printer Diagnostics.

Printer settings Displays the current printer settings after you click Test. Printer name — Displays the name and model of the printer. Horizontal resolution — Displays the horizontal resolution of your printer in dpi (dots per inch). Vertical resolution — Displays the vertical resolution of your printer in dpi. Port — Displays the port connecting your computer to the printer. Paper width — Displays the width of the paper you selected in setup. Paper height — Displays the height of the paper you selected in setup. Paper size — Displays the name of the paper-size you selected, for example: A4, Letter, wit h the appropriate dimensions. Print driver — Displays the name of the driver file you set for this printer on your computer. Printing orientation — Displays Portrait or Landscape selected, according to the print setting.

Rebuild Procedures and Triggers Dialog Box This option allows you to identify abnormal database behavior and correct it by rebuilding the stored procedures and triggers in the database. Normally, the System Administrator accesses this feature from the Administration module; however, you can run it from the Internal Setup Utility if for some reason you cannot log on to the Administration module. To open this dialog box from the Internal Setup Utility, click Settings > Rebuild Procedures and Triggers. Select a schema you want to rebuild:  

Admin schema — Allows you to rebuild the Admin schema. Domain schema — Allows you to rebuild the schema of the domain you select from the Domain list.

Domain — Allows you to select the target domain if you choose to rebuild a Domain schema. OK — Opens the Rebuilding Stored Procedures and Triggers dialog box where you can accept or specify a log file path and start the process. For further details see Rebuilding Stored Procedures and Triggers Dialog Box.


59

Internal Setup Utility Make sure that all users have logged out of the database before rebuilding stored procedures and triggers. When you start rebuilding stored procedures and triggers, no users should attempt to log on to SmartPlant Instrumentation.

60


Schem SPI Configuration and Maintenance Guide.pdf

Recommend Documents