Database Update Wizard

Database Update Wizard: Running a Script

The Database Update Wizard can work with Data Transfer and Update Schema statements in script files. When working with the Database Update Wizard, users choose the Run Script command and the wizard will execute the script. The script files must be located on the system on which Tomcat is deployed.

Script files can be chained together such that one script file can incorporate the commands from other script files. For example, you could have four individual script files for updating four types of data (say Space, Building Operations, SDS, and Lease data) and then have another script file that calls the first four, one after the other. This enables you to break updates into smaller components, but still execute them together if desired.

This command is handy for users who are applying Archibus add-on products and feature changes. You can separately develop sets of updates, but execute them together.

This topic has the following sections:

• Starting the Run Script option
• Example script
• Load the file
• Message log
• SQL log

Also see these topics:

• DUW Scripts: Supported Operations
• DUW Scripts: Best Practices
• DUW Scripts: Update Process

• Database Update Wizard Overview

Note: When running a script, you may find that the Database Update Wizard times out on slow servers after 30 minutes. To ensure that the script runs fully, you can change the session timeout from the default 30 minutes to 60 minutes and then restart the application server. In the /webapp/WEB-INF/web.xml file edit <session-config><session-timeout>30</session-timeout> to <session-config><session-timeout>60</session-timeout>.

Starting the Run Script option

1. Add the script files to the same server on which Tomcat is deployed.
2. Start the wizard: System / Add-In Manager / Run Database Update Wizard
3. Select “Run Script” option and click Next.

Example script

--INSERT INTO bl(bl_id,name) values('mytest','test'); DELETE FROM bl WHERE bl_id='mytest'; INSERT INTO bl(bl_id,name) values('mybldg','test'); --COMMENT ${import.file('D:/test.csv')}; ${refresh.data.dictionary}; ${alter.table('zone',true)}; ${recreate.table('ac',false)}; ${refresh.data.dictionary}; ${reload.workflow.rules};

Scripts can call other scripts using a command, such as:

${run.script('D:/abcd/my-script-01.duw'} ... (other commands such as ${import.file('D:/test.csv')}; ) ${run.script('D:/abcd/my-script-02.duw'} ... (other commands such as ${import.file('D:/test.csv')}; ) ... etc.

Load the file

The supported file extensions are: duw, sql, txt.

There are three ways for the files to be executed:

Server file

This loads the supported files from \projects\users\public\dt\database-update\personalized-database

• “Refresh” button reloads the files from location
• “Select File” loads the contents into “Script File” text area

Local file

You can choose the file using the Browse button. The file must be on the same server on which Tomcat is deployed.

Input text area (Script File)

Type the commands into text area.

• You can highlight a portion of the script and click “Run Script”. Then only the selected highlighted  code will be executed.
• The “Script file” input box is editable.

Message log

For a command that is executed in the message log, you will see a message. There are two situations:

The command executes successfully and you see a message depending on the command type:

1. Skip comment (applies to commented lines)
2. Sql command executed successfully (applies to SQL commands)
3. X record(s) inserted, Y record(s) updated, Z record(s) with error (applies to data transfer files imports)
4. Table(s) {} were updated. Please check the log for details (applies to update schema statements)
5. Archibus workflow rules reloaded successfully (applies to ${reload.workflow.rules})
6. Archibus Data Dictionary reloaded successfully (applies to ${refresh.data.dictionary})

An error message displays. This happens when:

• there is an SQL error. The message log displays the database engine error.
• the command is not supported. The system displays a message.
• a data transfer file is not found. The system displays an explicit message.

SQL log

SQL executed against the database for commands that are executed successfully in the Database Update Wizard are logged into a file called projects/users/public/dt/database-update/personalized-database/runDuw.sql on the server. This includes comments in the script and all SQL executed including for imported data files.

The logged SQL is not equivalent to the script. It represents the SQL executed for the given run of the script against a given database. It is DBMS-specific and database-specific. SQL executed for CSV imports are data-dependent.

The log is reset every time a script is run and may be reset by data transfers, the Schema Change Wizard, or user-defined field configuration.