Database

2. Database

Introduction

To meet specific needs, several customizations are applied on the AHD database.

Customization of the AHD Database is done by creating and/or modifying files in the USVD directory structure. The $NX_ROOT\bopcfg directory and its subdirectories contain the files that define the AHD database and its behavior. Changes can be applied directly to these files but they can be overwritten by applying a patch or by upgrading the program. Therefore most of the customization files are placed in $NX_ROOT\site\mods and its subdirectories. These files overrides and/or appends the ones located in $NX_ROOT\bopcfg and will never be overwritten during patch-application, upgrade or reinstall of USVD.

Different types of changes can be applied using different file types.

Schema files (*.sch)

Schema files are used to make modifications in the database schema ( the layout). These files describe which fields are added or modified in a table, or what new tables are added.

The schema files are located in the $NX_ROOT\site\mods directory.

Schema files are only necessary on the Service Desk server.

Majic files (*.maj)

Majic files are used to make attribute modifications in the object schema. These files describe for which new fields in an object attributes are added or modified.

The majic files are located in the $NX_ROOT\site\mods\majic directory.

Majic files are only necessary on the Service Desk server.

Modification files (*.mod)

Modification files are used to make attribute modifications in the database schema. These files describe for which default fields in a table attributes are added or modified.

The modification files are located in the $NX_ROOT\site\mods\majic directory.

Modification files are only necessary on the Service Desk server.

Wand files (*wnd)

Wand files are used to make behavior modifications to the GUI. These fields describe the behavior of a GUI- screen, e.g. when the contents of a field is modified, a trigger can be launched to check if the new value is a correct value.

The wand fields are located in the $NX_ROOT\site\mods\wand directory.

Wand files are necessary on both Service Desk server and his clients.

Spell files (*.spl)

Spell files are used to make behavior modifications to objects. These files describe the behavior of an object, e.g. when the contents of a fields is modified, a trigger can be launched to check if the new value is a correct value.

The spell files are located in the $NX_ROOT\site\mods\spl directory.

Spell files are only necessary on the Service Desk server.

Realization

New tables

zResolving_Ver – Resolving version

SQL table name: zres_ver

This table is requested for being able to retrieve incidents, problems and change orders, which should be solved with a certain release version.

zLanguage – User language

SQL table name: zlang

This table is requested for being able to set the language of the customer in his/her details

New fields

ext_ref – External reference

Added to tables Call_Req (cr), and Change_Order (chg) as a text field.

For being able, to add an external reference, (of third party), in to the system database.

zRes_Ver – Resolution Version

Added to tables Call_Req (cr), and Change_Order (chg), as a text field.

For indicating by which version the item should be solved.

Is a reference field pointing to zResolving_Ver.

zpb_flag – Candidate for problem

Added to table Call_Req (cr), as a numeric field.

This field is used in incidents for pointing to the fact that the concerned incident is a possible future problem.

zcategory – Final category

Added to tables Call_Req (cr), and Change_Order (chg) as a numeric field.

For indicating the final problem category when the incident, problem or change order is closed.

Modified fields

category – table Call_Req Üset required

customer – table Call_Req Üset required

group – table Call_Req Üset required

priority – table Call_Req Üset required

impact – table Call_Req Üset required

description – table Call_Req Üset required

Files and locations

Added files:

company.sch located in $NX_ROOT\site\mods

company.mod located in $NX_ROOT\site\mods\majic

Changed files:

None

Customization

Schema files (Modif.sch)

The database schema is modified as described in the modif.sch file. This file is located in the directory $NX_ROOT\site\mods on the Service Desk server.

// Customisation
//
// Modif.SCH - version 2.1
//
// Created by Daniel Paessens (HP)
//

#include "../schema.mac"

// External reference for Incidents/Problem/Request
TABLE Call_Req       {ext_ref STRING 40;}

// Flag for possible problem inside Incident/Problem/Request
TABLE Call_Req       {zpb_flag INTEGER REF bool;}

// External reference for Changes
TABLE Change_Request  {ext_ref STRING 40;}

// Resolving Version for Incidents/Problem/Request
TABLE Call_Req       {zRes_Ver STRING 30 REF zResolving_Ver;}

// Second problem category
TABLE Call_Req       {zcategory LONG_SYM REF pcat;}

// Resolving Version for Changes
TABLE Change_Request  {zRes_Ver STRING 30 REF zResolving_Ver;}

// User language for Contacts
Table Contact      {zLang STRING 30 REF zLanguage;}

// Table for Resolving Version of Software
TABLE zResolving_Ver {
     code STRING 30 uniq; // internal key
     last_mod_dt LOCAL_TIME; // date changes were made
     id INTEGER uniq KEY; // key id
     del INTEGER nn; // 0=present,1=gone
     sym HIER_SYM uniq S_KEY; // type symbol
     description ENT_DESC; // desc of reason codes
     }

// Table for User language
TABLE zLanguage {
     code STRING 30 uniq; // internal key
     last_mod_dt LOCAL_TIME; // date changes were made
     id INTEGER uniq KEY; // key id
     del INTEGER nn; // 0=present,1=gone
     sym HIER_SYM uniq S_KEY; // type symbol
     description ENT_DESC; // desc of reason codes
     }

p1 zResolving_Ver -> CURR_PROV zres_ver;
p1 zLanguage -> CURR_PROV zlang;

Modif.mod

They changes are in ‘modif.mod’ are supported by additional files, which give the relationships between fields of different tables. As well, the behaving of the tables in for example listings. These files are located on the following directory:

$NXROOT\site\mod\majic\ on the Service Desk server.

// Customisation
// Created by Daniel Paessens (HP)
// Email: daniel.paessens@hp.com
//

// External reference for Incidents/Problems
OBJECT cr {ATTRIBUTES Call_Req {ext_ref STRING;};};

// Required fields for Incidents/Problems
MODIFY cr category REQUIRED;
MODIFY cr customer REQUIRED;
MODIFY cr group REQUIRED;
MODIFY cr priority REQUIRED;
MODIFY cr impact REQUIRED;
MODIFY cr description REQUIRED;

// External reference for Changes
OBJECT chg {ATTRIBUTES Change_Request{ext_ref STRING;};};

// Candidate problem from inicident
OBJECT cr {ATTRIBUTES Call_Req{zpb_flag SREL bool {ON_NEW DEFAULT 0;};};};

// Resolving Version for Incidents/Problems
OBJECT cr {ATTRIBUTES Call_Req { zRes_Ver SREL zres_ver;};};

// Resolving Version for Changes
OBJECT chg {ATTRIBUTES Change_Request { zRes_Ver SREL zres_ver;};};

// Second problem category in Requests/Incidents/Problems
OBJECT cr {ATTRIBUTES Call_Req {zcategory SREL pcat;};};

// User language for Contact
OBJECT cnt {ATTRIBUTES Contact { zLang SREL zlang;};};

// Language codes
OBJECT zlang {
     ATTRIBUTES zLanguage {
          sym STRING REQUIRED;
          delete_flag del SREL actbool REQUIRED {ON_NEW DEFAULT 0;};
          last_mod_dt DATE {ON_CI SET NOW;};
          description STRING ;
          code STRING WRITE_NEW REQUIRED;
     };
     FACTORY {
          STANDARD_LISTS {
               SORT_BY "sym,code";
               WHERE "delete_flag = 0";
               };
          REL_ATTR code;
          COMMON_NAME sym;
          FUNCTION_GROUP "reference";
     };
};

//reason codes
OBJECT zres_ver {
     ATTRIBUTES zResolving_Ver {
          sym STRING REQUIRED;
          delete_flag del SREL actbool REQUIRED {ON_NEW DEFAULT 0;};
          last_mod_dt DATE {ON_CI SET NOW;};
          description STRING ;
          code STRING WRITE_NEW REQUIRED;
          };
     FACTORY {
          STANDARD_LISTS {
               SORT_BY "sym,code";
               WHERE "delete_flag = 0";
               };
          REL_ATTR code;
          COMMON_NAME sym;
          FUNCTION_GROUP "reference";
     };
};

Rebuilding the database

Just modifying the schema is not enough. Before USVD can use the new or modified tables, the database dictionary must be updated and the database tables rebuilt. The following steps describe the process of rebuilding the AHD database.

Step 1:

Does you database already contain data that you want to preserve? If NOT, skip to step 3

Step 2:

Rebuilding an existing table removes all data in that table. Make a backup of all tables that are going to be rebuilt using ‘pdm_extract’.

Synstax: pdm_extract AHDTablename > outputfile

Tables to backup are: Contact, Call_Req, Change_Orders.

Examples:

pdm_extract Contact > cnt.txt

pdm_extract Call_Req > cr.txt

…

Step 3:

Run ‘pdm_configure’.

Enter the correct Server Node name: DSRV443
You must enter the passwords for the Service Desk and the Restricted User accounts
Enter the correct Mail Profile
Enter the correct Database name: AHD
Enter the correct Database node: DSRV443
Enter the correct Database password: PASSWORD
Important! ONLY if you skipped step 2, select the “Reinitialize Database checkbox”. This option completely creates a new data dictionary and SQL database, removing ALL existing data.
Select the “ITIL form” checkbox

Step 4:

This step depends on whether you reinitialized the database or not.

If you did reinitialize then the configure program already created all SQL tables for you so you can skip the rest of this procedure.

If you didn’t then the new tables and fields should also be created in SQL. To do this, run the sqlbuild command for each added or modified table:

Syntax: sqlbuild –p tablename database ddlictlocation

Tables to build are: Contact, Call_Req, Change_Orders, zLanguage, ZRes_Ver.

Examples:

sqlbuild –p Contact AHD $NX_ROOT\site\ddict.sch

sqlbuild –p Call_Req AHD $NX_ROOT\site\ddict.sch

…

Step 5:

After running sqlbuild you can then import the extracted data again using ‘pdm_userload’.

Syntax: pdm_userload –f filename

Examples:

pdm_userload –f cnt.txt

pdm_userload –f cr.txt

…

Top

	Home \| Table of Contents \| Overview Map \| Icon Legend
	Implementation of Unicenter Service Desk ServicePlus 6.0 SP1
	Daniel Paessens ega@paessens.org

Implementation of Unicenter Service Desk ServciePlus 6.0 SP1	Powered ByEga's Inc
	Last updated: 2009-11-19