CA SDM Loading contacts pdm_load

Document created by TMACUL Champion on Aug 31, 2015Last modified by ramanzini on Sep 4, 2015
Version 8Show Document
  • View in full screen mode

The first thing what you need know: VERY Important! Using pdm_load can be destructive so always back up your database before you perform a pdm_load, and use pdm_userload unless instructed to use pdm_load.

 

Important! Make a backup copy of the database before running pdm_load with this option. After old database records are removed, you must restore the CA SDM database with this backup copy if you want to recover any deleted records, use pdm_extract for this procedure.

 

1. BACKUP

 

BACKUP COMMAND
pdm_extract ca_contact > ca_contact.txt



2. Structure table


bop_sinfo -dq cnt

Factory cnt < ca_contact, usp_contact >

Attributes:

  id <contact_uuid>    UUID UNIQUE

  producer_id          LOCAL STRING(20)

  persistent_id        LOCAL STRING(60)

  supervisor_contact_uuid SREL -> cnt.id

  last_mod_dt last_update_date DATE

  creation_date        DATE

  last_name            STRING(100) REQUIRED SKEY

  first_name          STRING(100) SKEY

  middle_name          STRING(100) SKEY

  alias                STRING(30)

  delete_flag inactive SREL -> actbool.enum REQUIRED

  version_number      INTEGER

  last_mod_by last_update_user STRING(64)

  creation_user        STRING(64)

  exclude_registration INTEGER

  delete_time          DATE

  userid              STRING(100)

  phone_number pri_phone_number STRING(40)

  alt_phone alt_phone_number STRING(40)

  mobile_phone mobile_phone_number STRING(40)

  type contact_type    SREL -> ctp.id

  billing_code cost_center SREL -> cost_cntr.id

  location location_uuid SREL -> loc.id

  dept department      SREL -> dept.id

  organization organization_uuid SREL -> org.id

  admin_org admin_organization_uuid SREL -> org.id

  company company_uuid SREL -> ca_cmpny.id

  notes comment<comments> STRING(255)

  fax_phone fax_number STRING(40)

  beeper_phone pager_number STRING(40)

  email_address        STRING(120)

  pemail_address pager_email_address STRING(120)

  room_location        STRING(30)

  contact_num alternate_identifier STRING(30)

  position job_title  SREL -> position.id

  tenant_group        SREL -> tenant_group.id

  cenv                BREL <- lrel_cenv_cntref.cnt (LREL nr) {cnt = ?}

  cntntf              BREL <- lrel_notify_list_cntntf.cnt (LREL cr) {cnt = ?}

  service_locs        BREL <- lrel_svc_locs_svc_groups.cnt (LREL loc) {cnt = ?}

  service_pcat        BREL <- lrel_svc_grps_svc_pcat.cnt (LREL pcat) {cnt = ?}

  service_chgcat      BREL <- lrel_svc_grps_svc_chgcat.cnt (LREL chgcat) {cnt = ?}

  service_isscat      BREL <- lrel_svc_grps_svc_isscat.cnt (LREL isscat) {cnt = ?}

  service_wftpl        BREL <- lrel_svc_grps_svc_wftpl.cnt (LREL wftpl) {cnt = ?}

  att_ntfrlist        BREL <- lrel_ntfr_cntlist_att_ntfrlist.cnt (LREL ntfr) {cnt = ?}

  cntissntf            BREL <- lrel_notify_list_cntissntf.cnt (LREL iss) {cnt = ?}

  cntchgntf            BREL <- lrel_notify_list_cntchgntf.cnt (LREL chg) {cnt = ?}

  macro_ntf            BREL <- lrel_att_cntlist_macro_ntf.cnt (LREL macro) {cnt = ?}

  mgs_ntf              BREL <- lrel_dist_cntlist_mgs_ntf.cnt (LREL mgs) {cnt = ?}

  member_list          BREL <- grpmem.group (LREL member) {group = ?}

  group_list          BREL <- grpmem.member (LREL group) {member = ?}

  cnthandling_list    BREL <- contact_handling.contact (LREL special_handling) {contact = ?}

  web_url              LOCAL STRING(0) DERIVED('http://<servername>:8080/CAisd/pdmweb.exe?OP=SEARCH+FACTORY=cnt+SKIPLIST=1+QBE.EQ.id=' || CAST(id AS NVARCHAR))

  all_creq            BREL <- cr.customer {customer = ?}

  all_chg              BREL <- chg.requestor {requestor = ?}

  all_iss              BREL <- iss.requestor {requestor = ?}

  all_open_creq        QREL <- cr {customer = ? and active = 1}

  combo_name          LOCAL STRING(0) DERIVED(last_name || isnull(', ' || first_name || ' ' || middle_name))

  combo_name2          LOCAL STRING(0) DERIVED(last_name || ',' || isnull(first_name) || ',' || isnull(middle_name))

  log_reader          BREL <- lr.contact {contact = ?}

  roles                BREL <- cnt_role.contact {contact = ?}

  active_special_handling QREL <- contact_handling {contact = ? and special_handling.delete_flag = 0}

  workload            QREL <- cr {assignee = ? and active = 1}

  workload_chg        QREL <- chg {assignee = ? and active = 1}

  workload_iss        QREL <- iss {assignee = ? and active = 1}

  workload_wf          QREL <- wf {assignee = ? and status = 'PEND'}

  workload_isswf      QREL <- iss_wf {assignee = ? and status = 'PEND'}

  cr_nf_list          BREL <- qp_cr_cnt.cnt_srel {cnt_srel = ?}

  chg_nf_list          BREL <- qp_chg_cnt.cnt_srel {cnt_srel = ?}

  iss_nf_list          BREL <- qp_iss_cnt.cnt_srel {cnt_srel = ?}

  change_tasks        QREL <- wf {assignee = ? AND status.allow_task_update = 1}

  issue_tasks          QREL <- iss_wf {assignee = ? AND status.allow_task_update = 1}

  tenant              SREL -> tenant.id REQUIRED

  access_type c_acctyp_id SREL -> acctyp.id

  notify_method1 c_cm_id1 SREL -> cmth.id

  notify_method2 c_cm_id2 SREL -> cmth.id

  notify_method3 c_cm_id3 SREL -> cmth.id

  notify_method4 c_cm_id4 SREL -> cmth.id

  notify_ws1 c_ws_id1  SREL -> wrkshft.persistent_id

  notify_ws2 c_ws_id2  SREL -> wrkshft.persistent_id

  notify_ws3 c_ws_id3  SREL -> wrkshft.persistent_id

  notify_ws4 c_ws_id4  SREL -> wrkshft.persistent_id

  domain c_domain      SREL -> dmn.id

  vendor c_vendor      SREL -> ca_cmpny.id

  service_type c_service_type SREL -> no_contract_sdsc.code

  timezone c_timezone  SREL -> tz.code

  integration_cnt      INTEGER

  confirm_save        INTEGER

  notify_urgency1      LOCAL SREL -> noturg.enum

  notify_urgency2      LOCAL SREL -> noturg.enum

  notify_urgency3      LOCAL SREL -> noturg.enum

  notify_urgency4      LOCAL SREL -> noturg.enum

  schedule c_schedule  SREL -> wrkshft.persistent_id

  available c_available INTEGER

  pgroups              LOCAL STRING(0)

ldap_dn              STRING(512)

  auto_bias_wf        LOCAL INTEGER

  auto_bias_isswf      LOCAL INTEGER

  linked_id_usp_contact id<contact_uuid> UUID UNIQUE

  cost                DOUBLE

  currency_type        SREL -> usp_currency.id

  KD_APPROVAL          BREL <- CI_ACTIONS_ALTERNATE.CONTACT_ID {CONTACT_ID = ?}

  audit_userid        LOCAL SREL -> cnt.id TENANCY_UNRESTRICTED

  sap_personID        STRING(32)

 

 

Structure load: pdm_extract ca_contact

TABLE ca_contact

  admin_organization_uuid alias alt_phone_number alternate_identifier comment company_uuid contact_type cost_center creation_date creation_user delete_time department email_address exclude_registration fax_number first_name floor_location id inactive job_function job_title last_name last_update_date last_update_user location_uuid mail_stop middle_name mobile_phone_number organization_uuid pager_email_address pager_number pri_phone_number room_location supervisor_contact_uuid tenant tenant_group userid version_number

  { "" ,"" ,"" ,"" ,"Usuário para a integração do Asset Management" ,"", "2307" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", "16226C765005B94E957E0F477DEF1B1C" ,"0" ,"" ,"" ,"System_AM_User",

  "06/17/2015 15:52:24" ,"ServiceDesk" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"" ,"", "C6F2D388395F734EBC4C6AEEBCBF68E4" ,"" ,"System_AM_User" ,"1" }

 

 

 

3. Example

 

loading_cnt.txt - Content

TABLE ca_contact

userid  last_name email_address alt_phone_number

{"login.name","user Last Name ","my@emailaddress.com",""}

 

StepsDescription
1. Create loading_cnt.txt file
2. pdm_load -i  -f carga_cnt.txt

command = C:\PROGRA~2/CA/SERVIC~1\bin\dbload -i -v  CARGA_~1.TXT

 

Processing CARGA_~1.TXT

LOADING DATA

        userid: [login.name]

        last_name: [user Last Name]

        email_address: [my@emailaddress.com]

        alt_phone_number: []

        alias: []

        id: [3988E217CE309B42B1250564894A02CD]

        inactive: [0]

        tenant: [00]

ca_contact :

Rows  :1

Inserts:1

Updates:0

Errors : 0

MaxKey : 0

 

 

Ending Totals:

Total tables :1

Total rows  :1

Total inserts:1

Total updates:0

Total errors: 0

Highest Table keys processed    :ca_contact(0)

Files processed: 1

3. Check your load, using -v parameter and also looking for contact on CA Service Desk
Using the contact created, it´s possible load the secondary table usp_contact, for task it´s necessary to use pdm_deref command, following the steps above you´ll do it

TABLE usp_contact

userid access notify_method1 notify_method2 notify_method3 notify_method4

{"name.lastname", "Cliente" , "Normal","Normal","Normal","Normal"}

4. Create your input file load_uspcnt.txt

Deref

{

input = userid

output = id

rule = "select id FROM ca_contact WHERE userid=?"

}

05.Create your spec file cnt_spec.txt
CASDM_cntload_04.jpg06.Run Dref Command use sintax


pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)



pdm_deref -v -s cnt_spec.txt < load_uspcnt.txt > load_uspcnt_exit_A

.txt

TABLE usp_contact

      id access notify_method1 notify_method2 notify_method3 notify_method4

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "Cliente", "Normal", "Normal",

     "Normal", "Normal" }

07. load_uspcnt_exit_A.txt

Deref

{

input = access

output = c_acctyp_id

rule = "select id FROM Access_Type_v2 WHERE sym=?"

}

05. Create your spec file acctyp_spec.txt, to know more about Accces Type object click here
CASDM_cntload_05.jpg

06. Run Dref Command use syntax


pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)



pdm_deref -v -s Access_Type_spec.txt < load_uspcnt_exit_A.txt > load_uspcnt_exit_B.txt




TABLE usp_contact

      id c_acctyp_id notify_method1

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "Normal", }

07. load_uspcnt_exit_B.txt

Deref

{

input = notify_method1

output = notify_urgency1

rule = "select id FROM Notification_Urgency WHERE sym=?"

}

08. Create your spec file notify_spec.txt, do you need know more about noturg object click here
CASDM_cntload_06.jpg

09. Run Dref Command use syntax


pdm_dref -s (spec file name)    <  (input file name)    > (exit file name)



pdm_deref -v -s notify_spec.txt < load_uspcnt_exit_B.txt > load_uspcnt_exit_C.txt


TABLE usp_contact

      id c_acctyp_id notify_urgency1

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "401" }

10. load_uspcnt_exit_C.txt
11. lt´s the same process to do using all field, if ...

12. to load..


pdm_load -v -a -f load_uspcnt_exit_C.txt

 

 

Loading Contact Method (cmth object)

 

StepsDescription

TABLE usp_contact

id c_acctyp_id notify

{"4B066F502B009F48BC53A4CAC6DFA9AA", "10004" , "Email"}

01. Input file load_uspcnt.txt

Deref

{

input = notify

output = c_cm_id1

rule = "select id FROM Contact_Method WHERE sym=?"

}

02. Spec file

03. Run command


pdm_deref -v -s cmth_spec.txt < load_uspcnt.txt > myexitfile.txt


TABLE usp_contact

      id c_acctyp_id c_cm_id1

 

 

     { "4B066F502B009F48BC53A4CAC6DFA9AA", "10004", "1800" }

03. Exit file

to know more about cmth object -CA SDM::. cmth - Contact_Method

 

 

References:


DEREF - pdm_deref - Everthing do you need know

CA SDM Load department using pdm_load - dept - ca_resource_department

CA Service Desk Manager Load Category using pdm_load - pcat - Prob_Category - User view Prob_Ctg Category.xlsx

 

 

Was this information helpful? left a comment.

2 people found this helpful

Attachments

    Outcomes