Database Schema Manager

More options are available in Database Schema Manager if it's opened outside of the host installer or host application.

  1. From the download file that contains the installer, navigate to host-setup > tools > DatabaseSchemaManager and double-click CwDbSchemaManager.exe.
  1. Select either SQL Server or Oracle.
  2. Enter the Azteca user's password in the first text box.
  3. Select the server instance and Cityworks database from the drop-down lists.
  4. Click Login.

Database Schema Manager shows the progress while it checks the CWVERSION and migration history tables. Click Log to open or close the log. When the log is open, there are the following options: Clear the contents of the log, Copy the log’s contents to clipboard, Copy SQL to only copy the SQL scripts, or Save the log’s contents. The Log also states any data migrations that need to be completed. See Data Migration Manager Guide for more information.

Click the arrow next to Advanced Options for the following options:

  • Verify Database: This option verifies the structure of the Cityworks database and identifies any errors. The errors are displayed in the log, followed by a list of SQL scripts that can be run to correct the errors.
  • Check Database Version: This option checks the Cityworks database version.
  • Configure CWDBAs: This option allows for the configuration of Cityworks Database Administrator (CWDBA) users. A CWDBA is created during the installation of the Cityworks database. These users have access to the Admins and Domains options in Designer via the home page or General tab.
  • Generate SQL: This option generates SQL syntax for the update. The syntax could be copied and ran directly against the Cityworks database outside of Database Manager.
  • Disconnect: This option disconnects from the Cityworks database.

NOTE: If you are using PLL, before running Database Schema Manager, please check the DEFAULT_DURATION_TIME value in the TASK table. If the value is not NULL, 0, or an increment of 15, update this in the Details panel in Admin.

NOTE: When new tables are added to the database, they are not added to the PWSYSID table until they are populated through the Cityworks UI. This means that data imported to other tables is not added to the PWSYSID table until something else is added to that table via the UI.

The Update Default Data check box is selected by default. If left selected, Database Manager checks the default data in each of the tables listed below and updates the data if it does not match the default values. Check the default behavior for these tables. If you have modified any of the default data in these tables and feel like those changes could be altered based on this information, you should clear the Update Default Data check box before proceeding. Ask our support department for more information.

  • Data in these tables always inserts new or missing, and updates existing records:
  • azteca.CODEDESCFIELD
  • azteca.DESCSCOREFIELD
  • azteca.DEPOSIT_TYPE
  • azteca.DISTRESSCODE
  • azteca.EMPRELTABLEFIELD
  • azteca.FEATURE_CONSOLE
  • azteca.FEE_TYPE
  • azteca.FLAG_SEVERITY
  • azteca.FUNCTIONS_LEVELS
  • azteca.INSPECTIONTYPE
  • azteca.REPORTCATEGORY
  • azteca.SOURCETABLEFIELD
  • azteca.SYSTEM_STATUS
  • azteca.SYSTEM_TABLES
  • azteca.TASK_TYPE
  • azteca.ALERT_SEVERITY
  • Specific codes involved with our Paver integration are also updated like this.
  • Data in these tables only inserts new or missing records:
  • azteca.AUDIT_TRACE
  • azteca.FUNCTIONS
  • azteca.PWCODE
  • azteca.PWCODETYPE
  • azteca.QUERYFIELDCONFIGURATION
  • azteca.SYSTEM_TABLES_COLUMNS
  • Data in these tables is only inserted if the table is empty:
  • azteca.CCTVSCORE
  • azteca.PWCONDSCORE
  • azteca.PWUNIT
  • azteca.TABLEFIELDMAP