TMACUL

CA SDM carregando contatos ca_contact/usp_contact usando pdm_load

Blog Post created by TMACUL Champion on Sep 3, 2015

English version: CA SDM Loading contacts pdm_load

 

É de extrema importância ter ciência que utilizar os comandos pdm_load e pdm_userload podem destruir sua base de dados, para mitigar este risco, faça sempre um backup anter de qualquer alteração, utilize o pdm_extract..

 

 

Tabela do banco de dados

select * from ca_contact

 

1. BACKUP

 

Comando de BACKUP
pdm_extract ca_contact > ca_contact.txt

 

 

2. Extrutura da 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)

 

 

Estrutura de carga: 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. Exemplo

 

loading_cnt.txt - conteúdo do arquivo

TABLE ca_contact

userid  last_name email_address alt_phone_number

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

 

PassosDescrição
1. Crie o arquivo  loading_cnt.txt file

TABLE ca_contact

userid  last_name email_address alt_phone_number

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

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. faça a carga do arquivo
Utilizando o contato criado, para carregar a tabela secundária usp_contact utilize o comandopdm_deref

TABLE usp_contact

userid access notify_method1 notify_method2 notify_method3 notify_method4

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

4. Crie seu arquivo de entrada load_uspcnt.txt

Deref

{

input = userid

output = id

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

}

05.Crie seu arquivo spec cnt_spec.txt
CASDM_cntload_04.jpg06.Rode o comando Dref utilizando a sintaxe

 

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. Crie seu arquivo spec acctyp_spec.txt, Para saber mais sobre o objeto Accces  clique aqui
CASDM_cntload_05.jpg

06. Rode o comando Dref

 

sintaxe:

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. Crie seu arquivo spec notify_spec.txt, Quer saber mais sobre o objeto noturg Clique aqui
CASDM_cntload_06.jpg

09. Rode o comando Dref

 

sintaxe:

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. Este mesmo processo funciona para todos os campos

12. Para carregar rode o comando

 

pdm_load -v -a -f load_uspcnt_exit_C.txt

 

 

 

 

 

 

 

Referências:

 

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

Outcomes