Antoine_Sauteron_1266

database internal structure

Discussion created by Antoine_Sauteron_1266 on Oct 18, 2016
According to what I understood from DBA ‘s explanations, Oracle uses by default 2 tablespaces : one for the DATA part, one for the IDX part.
Even if this is not an oracle default, it seems that both tablespaces UC4_DATA and UC4_INDEX are used when creating tables as part of the ucybdbld process. 
Actually it does not only have to do with Oracle, it's also true for MS SQL or DB2.

Please confirm that the uc_dll_sp0.sql is the sql file used to initialize the database schema.
When loading the initial data, there are several scripts that are subsequently run, like drop table, check privileges, etc. So uc_ddl.sql is not the only one involved in that proces.

Customer has changed and imposed new standard, which consist in using one single tablespace per application (in our case UC4 or Automic is an application).
According to this new constraint, will it be possible to install the database after having changed all references to « TABLESPACE UC4_INDEX » and « TABLESPACE UC4_DATA » to « AUTOMIC_TABLESPACE » (for example) in the uc_dll_sp0.sql file.
Yes it is technically possible after modifying the .sql scripts. But we do not recommend to do so because it will imply that all scripts will always need to be changed when performing an update of the AE version.

In case yes, please details any other location where we may have to perform such a mass update.
You will need to search for every occurrence of 'UC4_INDEX' and 'UC4_DATA' in the folder "db" of the Automation Engine image : db/general and db/oracle/<Oracle_version>.

We are aware  that, along the upgrade process from V6 to V11, we will probably need to perform such a mass update on all the uc_dll_sp0.sql files of the intermediate version installed (V6) or used as upgrade target (V8 then V11)

Migrating from V8 directly to v11.2 is definitely not recommended because of the numerous DB structure changes. Prefered way would be to update from v8 to v9, then V10 and finally v11.2.

Best regards,
Antoine

Outcomes