Skip to main content
Version: OpCon 23.0 (On-Prem)

OpCon Database Scripts

SMA Technologies provides several SQL scripts for installing and upgrading the OpCon database and all supporting data. The next sections describe some of these SQL scripts. The scripts are in the <Configuration Directory>\Utilities\Database\ directory.

note

The Configuration Directory location is based on where you installed your programs. For more information, refer to File Locations in the Concepts online help.

All log files generated by these scripts are written to the <Output Directory>\Utilities\Database\Log\ folder.

note

The Output Directory was configured during installation. For more information, refer to File Locations in the Concepts online help.

Database Creation Script​

The database creation script name is DB_Create.sql. When executed, the script performs the following tasks:

  • Creates a database called OPCONXPS in the SQL Server Data directory
  • Allocates an initial 200 Megabytes (MB) for the database
  • Allocates an initial 1 Gigabyte (GB) for the transaction log
  • Sets the database options
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

Logins and Roles Creation Script​

The logins and roles creation script name is CreateDefaultOpConxpsloginsAndRoles.sql. When executed, the script performs the following tasks:

  • Creates the opconsam and opconui login IDs
  • Creates opconxps, opconreader, and opconspuser database roles
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

Schema Installation Script​

The schema installation script name is Opconxps_DBTables.sql. When executed, the script performs the following tasks:

  • Installs or updates the OpCon database schema
  • Grants full control to the "OpConxps" role for all OpCon tables
  • Grants read-only access to the "opconreader" role for all OpCon tables
  • Populates static tables with data
  • Loads initial data to selected administration tables
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

Database Stored Procedures Script​

The database stored procedures script name is OpConxps_DBProcs.sql. When executed, the script performs the following tasks:

  • Creates all stored procedures that support OpCon features and processing
  • Grants EXEC permissions on all stored procedures to the opconxps database role
  • Grants EXEC permission for supplemental stored procedures to the "opconspuser" role
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

Database Functions Script​

The Database Functions script name is OpConxps_DBFunctions.sql. When executed, the script performs the following tasks:

  • Creates all database functions that support OpCon features and processing
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

PDSA Framework Script​

The PDSA framework script name is PDSAFramework.sql. When executed, the script performs the following tasks:

  • Creates the tables required for the PDSA
  • Grants full control to the "OpConxps" role for all PDSA tables
  • Grants read-only access to the "opconreader" role for all PDSA tables
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

PDSA Framework Data Script​

The PDSA framework data script name is PDSAFrameworkData.sql. When executed, the script performs the following tasks:

  • Generates the output to the Database Name>_PDSAData.log file

Audit Triggers Script​

The audit triggers script name is OpConxps_DBTriggers.sql. When executed, the script performs the following tasks:

  • Creates a set of INSERT, DELETE, and UPDATE triggers on a subset of the OpCon database tables that will record data for auditing purposes
  • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update

Pre Update Checks Script​

The pre update checks script name is PreUpdateChecks.sql. When executed, the script performs the following tasks:

  • Checks to make sure the SQL Compatibility level is set to the current version of SQL. If the OpCon database compatibility level is too old, the script updates the value to ensure correct operation.
  • Checks the History Archive table to make sure there are no duplicate records. If any are found, the script deletes the duplicates to ensure a successful upgrade.
  • Creates a list of machines and machine groups that have any jobs, other than OpenVMS, OS2200, MPE/ix or BIS, where the platform specific job data is still stored in legacy data format.
    • Produces a copy of this list in the log file so that the user can take appropriate action if necessary
    • Generates output to the DB_Setup.log during database creation or the DB_Update.log during database update