Managing Your Database › Data Dereferencing › How to Use pdm_deref Example

How to Use pdm_deref Example

The following example shows how to use the pdm_deref utility in a CA SDM ticket tracking system.

Assume that an existing ticket tracking system that you implemented on a spreadsheet has columns labeled Trouble Description, Technician First and Last Name, and Entry Date. These columns correspond to the description, assignee, and open_date fields in the CA SDM Change_Request table. The Trouble Description field contains the same data type as the description field. But the assignee field is a numeric field, and the Technician field on the spreadsheet is in a “last name, first name” format.

To use pdm_deref

1. Load the technicians’ names into the Contact table.
2. Prepare a pdm_deref input file with the existing information.
3. Build a specifications file to map the new contact names to assignee values.
4. Prepare a pdm_userload input file to be used to update the Change_Request table.

This process is described in more detail in the following steps:

1. Prepare a pdm_userload input file, location.dat, for the Location table as follows:

   TABLE ca.location
   location_name address_2 address_2
   {"Boulder NCC ‑ NQ", "716 Main 
       Street","Boulder, CO 84302"}
   {"Colorado Springs NCC", "2765 Spring Street", 
       "Colorado Springs, CO 84303"}
   {"Denver NCC", "3765 Stoneridge Way", "Denver, 
       CO 80254"}
   

2. Load the data as follows:

   pdm_load ‑f location.dat
   

3. Prepare an input file, contact.dat, with the original information as follows:

   TABLE ca.contact
   last_name first_name middle_name location pri_phone_number
   {"Harrison", "Frank," "Harold", "NCC ‑ HQ", "303‑555‑2333"}
   {"Hertzog", "William", "I.", "Colorado Springs NCC", "303‑966‑1987"}
   {"Lyman", "Jeanie", "L.", "Denver NCC", "303‑966‑5301"}
   

4. Prepare a dereferencing tool specifications file, contact.spec as follows:

   Deref
   {
   input = c_location
   output = location_uuid
   rule = "SELECT id FROM ca.location WHERE location_name=?"
   }
   

   Important! Do not place a blank space in front of the SELECT keyword. Deref uses the new contacts’ first and last names to obtain the appropriate numeric ID fields for loading the Change_Request table. In addition, the “hooks” represented by question marks (?), correspond to the specified input fields. You must have the same number of hooks as input fields, and they must be in the same order.

5. Run pdm_deref as follows:

   pdm_deref ‑s contact.spec < contact.dat > contact.out
   

   The output file, contact.out, looks like the following:

   TABLE ca.contact
   last_name first_name middle_name location.uuid pri_phone_number
   {"Harrison", "Frank", "Harold", "69499D5A2424884887E62EC9823F5E47", "303‑555‑2333"}
   {"Hertzog", "William", "I.", "86873FA40BA4234A8CF7A418D7C8B2DB", "303‑966‑1987"}
   {"Lyman", "Jeanie", "L.", "58AA42789957734E8BEE146D07F7AD49", "303‑966‑5301"}
   

6. Load the contact.out file into the CA SDM database as follows:

   pdm_load ‑i ‑f contact.out
   

   Note: You must use the pdm_load command to use the –i option.

7. (UNIX only, optional) Write a script, Convert_Ticket, as shown in the following:

   #!/bin/sh
   pdm_load ‑i $1
   cat $2 | pdm_deref ‑s $3 | pdm_load ‑i
   

   You can run this script, as shown by the following:

   Convert Ticket location.dat contact.dat contact.spec
   

In this example, pdm_load with the ‑i flag is used to speed the process. If you are making these updates on a regular basis, you can drop the ‑i flag so that pdm_load checks for duplicate records.

The following are additional examples of dereferencing tool specification files:

Deref
{

input = first_name, last_name, middle_name
output = assignee
rule = " SELECT id from ca.contact \

       WHERE first_name=? \
       AND last_name=? \
       AND middle_name=? "

}

This rule converts three fields labeled first_name, last_name, and middle_name to the appropriate contact UUID. If all three input fields are not present, the rule is not applied. No match produces an error message and processing continues. For multiple matches, the first value is used; an error message is produced, and processing continues.