Automic Workload Automation

We want to copy the PROD environment to DEV without losing trials/tests we have on the DEV. We want PROD union DEV.

My questions are:

1-     What is the best way to do it?
2-     Is there a document/article/white paper about the Unload/Load options in details and when/how to use each option?
3 - After that we will test the migration to v12 on our DEV environment. Can we use the same method?

1 - You may copy the objects from one environment to the other with Client Copy, the transport case. Each have their pros and cons.

Client Copy
> Pros : allows you to make a copy of the entire client's content in a few clicks. Only objects that do not exist will be added, existing objects will not be overwritten.
> Cons : you may copy data you don't need in the process.

Transport case
> Pros : the exported objects can be altered with DB Change utility, in order to adjust their attributes.
> Cons : you will need to select the objects and add them to the Transport Case, which can take long specially if they are contained in many different folders.

Unload Utility
You can use Unload utility to either export all objects (unload all objects).

Of course there are a lot more considerations, and I'm certain that other users of the forum could tell you about them, based on their practical experience.


2 - The information regarding the Unload / Load process can be found in the documentation :

> DB Unload : graphical mode / command line
> DB Load : graphical mode /  command line


3 - Both methods are still supported in v12. However you need to ensure that both source and target client are on the same version. If not you may still use the Load / Unload.

just wanted to add, if you use Transport Case or Export (as XML) function, no Statistics and Reports will be transferred.

For a classical deployment of new Jobs I use the XML Export function - cause the Statistics of DEV System are not very interesting in PROD ENV :-)

The goal is to get all dependent objects, that were changed during development - possibly export with references could help.

From Version 11 its now possible to import and export folders.  If the Export/Import (as xml)function  is used its important to activate the option Keep existing folder links otherwise all links of imported Objects will be removed. 

Antoine_Sauteron Thanks for the great effort.
I really wonder, what is the use of "Export all Tables" option in the DB Unload?
I tried to Unload (Export all tables)/Load the exported file to empty database (i.e. only structure is created but without initial and default data) but it gave me ORA-01465: invalid hex number.

For the client copy, you also can copy the "report" and "statistic", and transport only have object definition no historical records

See also my list of pros and cons of transport case and XML.

I think the conversation in that case goes a bit aside of the topic.

Antoine_Sauteron 's intention was to copy the content of a whole client to another, and which way to use. in that case, only the Client Copy or the Transport Case (or Unload all Objects within Unload-Utility) would be an option. XML Export might be also an option in a very very small client. I dont want to list the Pros and Cons Antoine and Michael listed already, but following came to my mind:

Client Copy Pro: Easy to use if configured correctly, either within one system over between two systems if they are on the same version (servicepack), data and folder structures are there completely, and yes FrankMuffke , the statistics and reports are still there, but you can reorganize them out directly after.

Client Copy Cons: if you use it between two systems, both of that systems need to be on the same version, means, if your PROD System is on a higher Version than your TEST System, you cant use it. If you want to do mass changes within the objects, it is not processed. If you have on your Targetsystems the agents named differently than in the PROD System, you have to change the Parameters within the target client. E.G. you plan to copy a client containing 10 JOBS from a test environment to a prod environment, and the test systems Agent is named "WIN01TEST" you have no chance during the Client Copy process to change it to "WIN01PROD" because the Agent it is supposed to run on within PROD environment is named that way.

Load/Unload Pro: You can change the Objects using change utility if you unload via Transport Case
You can load data out of a older Version System and load it into a system with a more recent Version. The Transport Case can also be saved somewhere as a backup of the objects (or client) maybe for revision reasons.

Alternatively you can also use the function "transport all objects" from unload utility, this saves you from need to put all objects to transport case, but please dont forget to add the parameter "-D" to your command if you do it over batchmode. Otherwise all the objects will remain in transportcase.

Load/Unload Cons: Not that easy to use because you have to run 2 utilities instead of one and this on 2 different systems, if you use transport case instead of "unload all objects" you have to add the objects to transport case before.

So the main Questions you have to ask yourself what fits the best:

.) Are both of the systems on the same Version? (maybe client copy is sufficient then)
.) do I need to attune the objects because there are different preconditions in the target system? (transport case > change utility?)
.) do i really need to export all objects, or do i just need a few for a new process? (xml export?)
.) do i want a transport case for security or revision reasons to have a backup? (transport case if its a lot of objects, xml export if a few)

Hesham El-Sheika

Export all tables you can use if you want to set up a completely new Automation Engine system and load all the data of an existing system to it. Therefore the installation process is a bit different, instead of loading initialdata you just create the tables using the UC_ddl.sql and then load the data structure you unloaded of the existing system to it. It then should contain 1:1 all the data of the existing system.

Please note, that this is only sufficient if you have a data structure available, not if you have a Transport Case or just a plain new system.

Please find further information in documentation about it:

https://docs.automic.com/documentation/webhelp/english/AWA/11.2/AE/11.2/All%20Guides/help.htm#ucaakb.htm?Highlight=uc_ddl.sql

Harald_Heidinger_152This is exactly what I did:
1- Exported data (Export All) from PROD db
2- Ran (drop_all.sql) on DEV db to drop all tables
3- Ran (UC_ddl_sp0.sql ) on DEV db to  re-construct the tables (only the structure).
4- Loaded the file exported in step 1
But I got the error U0003590 UCUDB - DB error: 'OCIStmtExecute', 'ERROR   ', '', 'ORA-01465: invalid hex number'. I confirmed that both databases are using codepage WE8MSWIN1252. I attach the log and trace files of the DB Load.

I can't tell what is the problem. Could you please help? Thanks

Hesham, i dont have a documentation present now, but are you sure that WE8MSWIN1252 is a supported Codepage? (i know i could embarass myself with that Statement now) but please re-check documentation on this.


If this is not the issue, please accept the fact that analysing traces and logfiles would extend the possibility and also the Topic of this post. Please be invited to open an incident with Support for this if necessary.

We might to set this off Topic as soon as the incident is created, just to avoid confusion of other Readers within that post which are more interested in the General Topic.

Thank you!

Hello,

Harald_Heidinger_152
Yes this codepage is supported - can be seen here in the documentation :

You can choose from either of the following three code pages, whichever best fits your needs:
WE8ISO8859P1, WE8ISO8859P15 and WE8MSWIN1252.

Hesham El-Sheika

ORA-01465: invalid hex number

This type of error is most likely Oracle-related. Could be an issue with a missing Oracle package, or with the Oracle client.
Should you need further assistance on this, please open an incident ticket with a copy of the logs produced by DB Load attached.

Best regards,
Antoine

Antoine_Sauteron
Harald_Heidinger_152
It is working now. The cause is very strange but as soon as I fixed it, it is working.

Here it is:

In the logs of DB Load, I noticed that there is an "ORA-00942: table or view does not exist" before the "invalid hex number error"

I checked the tables owned by UC4 created by uc_ddl_sp0.sql on dev with the tables owned by UC4 on prod. I found that the UC_VERSI table was missing on dev! I believe this table is used mainly (and only?!) by db tools to check the version of the database and its status. That is why it didn't created without notice. By why it wasn't created despite I used the supplied script uc_ddl_sp0.sql?

Simple: there is an empty line in the middle of the table creation statement:

CREATE TABLE UC_VERSI  (
        VERSI_Major NUMBER(38,0) NOT NULL,
        VERSI_Minor NUMBER(38,0) NOT NULL,
        VERSI_Patch NUMBER(38,0) NOT NULL,
        VERSI_DateRel DATE NOT NULL,
        VERSI_DateImpl DATE NOT NULL,
        VERSI_DBVersion VARCHAR2 (255 CHAR) NULL,
        VERSI_V10_0 NUMBER(38,0) NULL,

        CONSTRAINT PK_VERSI PRIMARY KEY
        (
                VERSI_Major,
                VERSI_Minor,
                VERSI_Patch
        ) USING INDEX  TABLESPACE UC4_INDEX
)  TABLESPACE UC4_DATA;

in the middle of a huge number of DDL statements, no one will notice the error. I used the above statement (without the empty line) to create the table and Voila! It is working.

Great news Hesham ! It's strange that the table was missing in the first place, but good to see you resolved it.

Harald_Heidinger_152 Antoine_Sauteron Thanks for the great support and valuable information :smile:

Another thing to note as a difference from Client Copy and a Transport Case/Export is that passwords in LOGIN objects and USER objects get reset in a transport or export. Depending on how many you have and how many of them you know this could be a problem and I haven't found a way that is 100% accurate to automatically repopulate them if this is something that was going to happen on a regular basis.