Use of pdm_key_refresh

[Anon Anon]

I have a couple of scripts which update the usp_lrel_asset_chgnr table in order to automatically attach CIs to Change Orders.  Due to the amount of our CIs per CO, it saves about 3 hours of manual work to run these two scripts.  The problem is I have to increment the id field in that table within my script, leaving SDM in a state where the cache needs to be updated with the pdm_key_refresh command.

According to 

pdm_key_refresh--Refresh Cached Key Information - CA Service Management - 14.1 - CA Technologies Documentation 

this can cause data corruption.  I've not had any issues in test, and have needed to run it once in production.

 

Can anyone speak to the actual dangers of running this command twice on a weekly basis?

 

 

Thanks in advance,

Izz

[Kyle_R]

Hello Izz,

 

In CA Support we typically use the pdm_key_refresh command to fix something that has gone wrong with the Key Control Table.  It is (a) typically a "safe" script to run and (b) only used when the data sequence is corrupt, that is, not much else to lose.

 

We would typically avoid touching the Key Control Table at all except at system creation time. All subsequent data loads that then come in through standard integration points (via pdm_loads, Web Services, emails, AHD.dll  etc etc) which will maintain the integrity of the Key Control Table.

 

My general experience is that those documentation warnings are not put in lightly - if it is there, then someone has corrupted their data. Although note that the warning is about changing the Key Control Table, not about running the command. 

 

Having said that, we're not CA Services, so we don't see the side where this may be a 'common practice' to keep sites running with current data.

 

I'll refer you to this post (wait a second after clicking it for the jump within the page to occur) which speaks about the pdm_key_reference command and which links off to a useful TEC document.

 

 

I see you have an issue open with CA Support. If you'd like the CA Engineering perspective on "the interaction between the pdm_key_refresh command and manual changes to the id field of the usp_lrel_asset_chgnr table" then I suggest we let that run. Our Support understanding is that the pdm_key_refresh does a scan of data tables, and then updates the Key Control Table, but you don't know until you ask what else is going on. 

 

You may want to give some more explanation of how this works at the moment - do you leave the system OPEN for access for three hours AND have id updates running at the same time THEN do a pdm_key_refresh at the end of that?  That seems a recipe for clashes to tables occurring. Also update the case with the database type and version, and anything else site specific to that location.

 

Hello Everyone Else,

 

Any sites that have similar experience on the pdm_key_refresh for regular use?

 

Thanks, Kyle_R.

[Anon Anon]

Hi Kyle_R,

I opened the support case first and wanted perspectives from both CA and "from the field."  I understand the command itself is not harmful, but it does say it drops the data in the Key Control Table, which could be the issue.  When I run the command, it is within microseconds of an update to the database, scriptomatically.  The system is technically open for access, but the command is scheduled to be run when no one is in the office and there shouldn't be changes or updates to SDM.

I'll post to the case as well, but wanted to put this here.

DB is MS SQL 2012 (11.0.6567.0 to be exact, which is the latest version of SQL 2012).

[Lindsay_Estabrooks]

This is the danger of updating the database behind the application. A better approach would be to have your script make web service calls to Service Desk to attach the CIs.

[Legacy User]

That gets my vote - should be a much safer approach.

[Anon Anon]

I like this option as well.  Being that much of our feeds are SQL query based, and that I had gone through the pain of trying to learn the db schema, it was natural for me to write a python script with sql inserts.  I was unaware at that time that the PKs didn't auto-increment and that you would need to actually insert the value for this field.  I still don't understand the design decision behind this part that has caused this headache.

 

Thanks for the suggestion of using web calls.  I'll look at refactoring probably today for such, but still want to know more on this approach, although I am well aware DB updates are not supported.

 

Cheers!

-Izz

[Anon Anon]

Thanks everyone who responded...although not a direct answer of pdm_key_refresh, the suggestion of using web services pointed me in the right direction and I now have scripts working correctly utilizing the createLrelRelationships service.  Thanks a ton!