4.10. Using the SQL Script Manager
The SQL Script Manager covers three main areas:
- Scripts that check the consistency of the SICS database, with the option to take corrective action.
- Scripts to update the Business Objects reporting tables (BOAC tables).
- Functionality to check for, and optionally correct, Note field references.
The setup and use of Business Objects reporting table scripts are described in the SICS documentation included with the Business Objects setup. Please see ‘SICS BO Quick Guide.doc FIX_ME’ and ‘SICS Reporting Guide.doc FIX_ME’.
Consistency checking scripts can be used to check for inconsistent or missing data, and can also contain SQL for correcting detected problems.
Using the SQL Script Manager the SICS system administrator can import new scripts, browse scripts, run loaded scripts, and correct detected problems.
DXC provides the Business Objects reporting table scripts. DXC does not provide any consistency checking scripts, but the client can create and manage their own scripts.
Starting the Tool #
Open the SICS System Administration Utility. You find the SQL Script Manager in the Database Setup folder.
When you click the SQL Script Manager icon, you see the SQL Script Manager window, which initially is empty.
Organising Scripts #
The SQL Script Manager is dependent on scripts loaded from XML files. Before using the tool, it is necessary to tell it where to look for these scripts, and when to load them.
Storing Scripts #
The scripts to be loaded must be stored in a folder which contains nothing but these scripts.
The scripts have the file extension .xml.
Setting up where to Look for Scripts #
In the SQL Script Manager window, click the Importing tab and then click Find.
Browse the folder hierarchy and select the desired folder. If you want to include all sub folders from you import folder, select the Include subfolders check box.
After you have selected your import folder, you may want to set some options to your existing scripts, or your new script.
Loading New Scripts #
After you have selected the folder that you want to import. Click the Import Files button. When the import process is complete, the script list on the Script tab is refreshed automatically.
Executing Scripts #
Typically, the first step after you have imported scripts is to see if any problems were detected. The status field in the main window displays Failed if a problem was detected, or Successful if everything is ok. A hyphen ("-") indicates the script has not yet been executed.
Detecting Problems in the Database #
- Select one or more scripts by selecting them in the list. Use the <ctrl>-key to select groups of scripts. You can select all the scripts by clicking Select All.
- Click the Detect Selected button or select Detect from the pop-up menu.
- When the system has checked all files, the statuses of the scripts in the list are updated automatically.
Inspecting Data #
Some scripts also show detailed information about corrupted data.
You can check if a script has corrupted data by selecting Inspect from the pop-up menu in the display list.
Correcting Problems in the Database #
Warning! Correcting problems will modify the contents of the database. Should only be performed when necessary.
Any attempts to correct non-correctable problems will be ignored. The procedure is similar to detecting problems:
- Select one or more scripts by selecting them in the list.
- Click the Correct Selected button or select Correct from the pop-up menu.
- Click OK in the confirmation form.
After detecting or correcting problems, the Status, Last Time Executed Detected and Corrected fields in display list on the Script tab are updated automatically.
Script Information #
To display detailed information about a script:
- Select a script.
- Select Properties from the pop-up menu or double-click on the script.
General: This tab displays general information about the scripts, such as a short and a long description, approximate execution duration, which database type and database build and SICS system this script applies to, script category, and a unique identifier.
Status: Information about the previous execution is displayed here. This includes the result (e.g. success, failure) and execution time.
Detect: The actual SQL statements used for detecting the selected problem are displayed here.
Correct: If this tab is present, the selected problem is correctable. This is where the SQL statement for fixing the problem is displayed along with the number of corrected rows.
Inspect: The SQL statements used for showing detailed information about data in error are showed here.
Deleting Scripts #
- Select one or more scripts in the display list
- Select Delete from the pop-up menu.
- Click OK in the confirmation form.
XML Syntax Guide #
General #
- All XML tags must be lowercase
- Only alphanumeric characters are allowed in tags.
- All tags must be closed: <tag>…</tag>
- The parser is not fool proof! Incorrect tags will just be ignored!
Tag Definitions #
Note: XML tags in bold!
| Type | Tag | Must Contain |
|---|---|---|
| Top Level1 | check | identifier + shortdescripton + longdescription + dbtype + dbbuild + execduration + category + intendedapp + inspect + detect |
| - | checkandfix | (Same as check) + correct |
| Complex Tags2 | detect3 | sql+ operator + (`detect |
| - | inspect3 | sql A sql statement needed to inspect data in error. |
| - | correct3 | sql+ sql + ….. any number of sql statements needed to correct the problem. |
| - | operator | Any Smalltalk message that can be used to compare two Integers and return a Boolean. =, <, > …. |
| - | sql | String containing SQL statement. All table names must be prefixed by %1! (This is replaced by schema owner at run time. Scripts should be tested by a user needing to prefix schemas.) The SQL must be 100% sure not to fail on any database! This will crash the system! All SQL statements used inside a detect tag must return a COUNT. All SQL statements used inside a correct tag must solve the problem.5 |
| Primitive Tags4 | dbtype | One of the supported databases of SICS. All databases will accept scripts made for any |
| - | dbbuild | The database build which this script is certified under.6 |
| - | identifier | Short String identifying this script. This value should be unique. No more than 10 characters. |
| - | shortdescription | Short String describing what this script performs. No more than 30 characters. |
| - | longdescription | String giving a more detailed description of this script. Should provide information about: The severity of this problem. Probable errors causing this problem. Tables and columns involved. How often this test should be run. |
| - | category | String - Any kind of categorisation. |
| - | execduration | String - An estimate for how long the script will take to run. |
| - | constant | Integer |
| - | intendedapp | The application this script is intended for.7 |
| - | excludedapp | The application this script is intended for.7 1. Complex tags that do not have to be contained within other tags. Use check-and-fix for scripts containing a solution (correct) and check for scripts without. 2. Complex tags contain other tags within themselves. 3. detect = a SQL script which returns a true/false result inspect = a SQL script which returns a set of data correct = a SQL script which updates the database 4. Tags that only contain one value: <tag>myString</tag>. 5. The status of the script is set to “Successful” after performing a correct. 6. Currently scripts are limited to support only one specific database build. 7. The effect of setting ‘intendedapp’and ’excludedapp’ - Vertical axis: Possible database types Horizontal axis: intendedapp (ex=excludedapp) |
| CORE | LIFE | P&C |
|---|---|---|
| Pure LIFE | x | x |
| Combined LIFE+P&C | x | x |
| P&C | x | - |
Note References Repair Function #
This functionality is used for detecting and correcting problems caused by shared or dangling references to Notes in SICS. Such problems could cause unwanted note updates or system crashes due to missing objects in the database.
The Note References tab shows a list of all database tables and columns having references to Notes, as shown below.
It is possible to select one or more columns and start the analysis of the data.If no problem is detected, the process terminates with a confirmation message. If the system finds any problem, it will be reported to the user, as shown below.
If the wants to correct the detected problems, it will is enough to select “OK”. Clicking on Cancel the process terminates without performing any change to the data stored in the database.
This functionality will perform two different actions, depending on the type of problem found:
- For Dangling References, the system will remove the reference by setting it to NULL.
- For Shared References, the system will create copies of the involved notes and connect each of them to one and only one object.
If the user decides to apply the correction, a log file containing all the performed operations will be created and, after the process terminates, a full refresh of the system is performed.