topic Re: How to repoint the Data Workspace post clone? in ArcGIS Workflow Manager Questions

How to repoint the Data Workspace post clone?

AlisonGaiser1 — Tue, 13 Oct 2015 19:33:19 GMT

Every Sunday night we clone our production workflow manager database (EP4) back to our development WMX database (ED4).

Before our upgrade from 10.0 to 10.2.2 we used to run the following SQL post database clone (this information was supplied to us from Esri Technical support):

update wmx.JTX_DATABASES set INSTANCE='sde:oracle10g:ED4' where OBJECTID=65;

update wmx.JTX_DATABASES set db_name='ED4_ind_ora10' where OBJECTID=65;

This used to work beautifully. Unfortunately, post upgrade, it only partially works. At first I thought the problem was the fact that we are no longer using 10g but instead 11g, so I changed the SQL to the following:

update wmx.JTX_DATABASES set INSTANCE='sde:oracle11g:ED4' where OBJECTID=65;

update wmx.JTX_DATABASES set db_name='ED4_ind_ora11' where OBJECTID=65;

This change worked fine on the Workflow Manager side. I can create new jobs and they successfully go into the dev database. The problem shows itself when I run a step like launch ArcMap. Then my map document is not switched properly. The system does not recognize that there is a database change required (it sees the username and version - if required).

When I look at the value of the connection_properties field in the JTX_Databases table, I see that the INSTANCE value is not switched.

<PropertySet xsi:type='typens:PropertySet' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xs='http://www.w3.org/2001/XMLSchema' xmlns:typens='http://www.esri.com/schemas/ArcGIS/10.1'><PropertyArray xsi:type='typens:ArrayOfPropertySetProperty'><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>SERVER</Key><Value xsi:type='xs:string'></Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>INSTANCE</Key><Value xsi:type='xs:string'>sde:oracle10g:ep4</Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>DATABASE</Key><Value xsi:type='xs:string'></Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>VERSION</Key><Value xsi:type='xs:string'>SDE.DEFAULT</Value></PropertySetProperty><PropertySetProperty xsi:type='typens:PropertySetProperty'><Key>AUTHENTICATION_MODE</Key><Value xsi:type='xs:string'>DBMS</Value></PropertySetProperty></PropertyArray></PropertySet>

If I go into the application and manually reconnect to our dev database it appears to update the connection_properties to point to the dev database.

<Key>INSTANCE</Key><Value xsi:type='xs:string'>sde:oracle11g:ed4</Value>

Once this change has been made we have no problems connecting to the correct database.

Can I do a search and replace in the CLOB field? Or am I playing with fire? What do others do with their cloned databases?

Thanks,

Alison

Re: How to repoint the Data Workspace post clone?

AlisonGaiser1 — Tue, 24 May 2016 19:00:48 GMT

We have finally upgraded our development and production environments to 10.3.1 and this problem has become more urgent. Just hoping someone out there might have some suggestions.

At the moment the only workaround is to manually update our development environment workspace properties using WMX Administrator every Monday morning.

Any ideas out there?

Alison

Re: How to repoint the Data Workspace post clone?

DanPatterson_Retired — Tue, 24 May 2016 21:43:40 GMT

Hi Alison... knowing I prefer to work local, I was curious as to what workflow manager was about... I presume you have gone through the help links What's new in ArcGIS Workflow Manager—Help | ArcGIS for Desktop although for 10.4, you can check 10.3 there as well.. such things as Workflow Manager Advanced Settings—Help | ArcGIS for Desktop are buried within. Maybe you will have to call in some of the locals

Re: How to repoint the Data Workspace post clone?

AlisonGaiser1 — Wed, 25 May 2016 16:04:45 GMT

So my suspicions from above were correct. I do need to update that value. It was much easier than I suspected. See below.

UPDATE "WMX"."JTX_DATABASES"

SET CONNECTION_PROPERTIES = '<PropertySet xsi:type=''typens:PropertySet'' xmlns:xsi=''http://www.w3.org/2001/XMLSchema-instance'' xmlns:xs=''http://www.w3.org/2001/XMLSchema'' xmlns:typens=''http://www.esri.com/schemas/ArcGIS/10.3''><PropertyArray xsi:type=''typens:ArrayOfPropertySetProperty''><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DBCLIENT</Key><Value xsi:type=''xs:string''>oracle</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>DB_CONNECTION_PROPERTIES</Key><Value xsi:type=''xs:string''>sde:oracle11g:egised4</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PROJECT_INSTANCE</Key><Value xsi:type=''xs:string''>SDE</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>VERSION</Key><Value xsi:type=''xs:string''>SDE.DEFAULT</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>USER</Key><Value xsi:type=''xs:string''>wmx</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>PASSWORD</Key><Value xsi:type=''xs:string''>biglongstringwhichmeansnothingtoanyone</Value></PropertySetProperty><PropertySetProperty xsi:type=''typens:PropertySetProperty''><Key>AUTHENTICATION_MODE</Key><Value xsi:type=''xs:string''>DBMS</Value></PropertySetProperty></PropertyArray></PropertySet>'

WHERE DB_NAME = 'ED4_ind_ora11';