Problems with Package.UTF8.DB.Migration v1.0.2

[Michael A. Lowry]

Automic Automation v24.0.0 was released a bit over a week ago. Version 24 adds support for UTF-8, and Broadcom helpfully provided a migration tool, Package.UTF8.DB.Migration, to help migrate AE databases that use other character sets to the UTF-8 character set.

I'd like to advise anyone contemplating such a migration to wait until an update for the tool is released. The tool (v1.0.2) currently has bugs that can prevent a successful DB migration.

During the weekend, we tried to use Package.UTF8.DB.Migration v1.0.2 to complete migration of a database to UTF-8. The online portion of the migration completed successfully, but the offline portion of that finalizes the migration did not go so well. Among other problems, we found that all of the ALTER TABLE … ADD CONSTRAINT statements in the 1st generated SQL script were invalid and could not be executed without modification/correction.

We opened a support ticket with Broadcom.

[Donato Faggella]

Hi Michael. Thanks for the information. Bye. Donato

------------------------------
Donato Faggella
DevOps Engineer III
Swisscom (Suisse) SA
------------------------------

[Peter Grundler]

Hi Michael,

which database are you using? I'm currently struggling with MSSQL because of a prerequisite (a required command does not run on MSSQL 2022).

regards,
Peter

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Michael A. Lowry]

We use Oracle.  We ran into different problems than the one you describe.

[Markus Embacher]

Hi @Peter Grundler, 

what is the command you are struggling with?

Regards, Markus

[Peter Grundler]

Servus Markus,

It's GRANT CREATE PUBLIC DATABASE LINK TO <destination DB user>

Best regards,

Peter 

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Markus Embacher]

Servus Peter :-)

What exactly is the issue with that command? Any error messages?

thank you!

Regards, Markus

[Peter Grundler]

Hi Markus,

the error message is

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'PUBLIC'.

 

I have checked the Microsoft documentation (https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver16) and in fact, this command is not listed/supported.

best regards,
Peter

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Markus Embacher]

Hi Peter,

this is the command for Oracle. Let me find out the correct commands for MS-SQL and get back to you.

Regards, Markus

[Peter Grundler]

Thank you Markus!

It would be helpful - if possible - to discuss a correct and working procedure regarding MSSQL. Following the steps described in the documentation is currently not working.

best regards,
Peter

------------------------------
Automic Certified Professional/Expert & Broadcom Knight

For AUTOMIC trainings please check https://www.qskills.de/qs/workshops/automic/
------------------------------

[Markus Embacher]

Hi Peter,

here is an example for MS-SQL:

USE [master]
EXEC sp_addlinkedserver 
    @server=N'UC4_SOURCE', 
    @srvproduct=N'', 
    @provider=N'MSOLEDBSQL', 
    @datasrc=N'<datasource>',
    @catalog='ae';
GO

Pls have a look at sp_addlinkedserver (Transact-SQL) - SQL Server for the details. The above is just an example.

After defining the link please test the connection before you try to run the migration:
select count(*) from from UC4_SOURCE.ae.dbo.OH

I'll address your feedback internally.

Regards, Markus

[Michael A. Lowry]

We initially ran into a similar problem. The schema user had neither the right to create a DB link nor the right to grant this authorization. We had to ask our DBA to create the DB link for us.

[Michael A. Lowry]

Broadcom opened bug ID PCK_UTF8_DB-1 for the problems we reported. An update will be released soon. See AE v24 problems.

[Bartosz Suchecki]

Hello,
for 1.0.3v of utf8 migration package, I successfully performed the migration of our systems. So for anyone reading, 1.0.3 is working at least for me.

Unfortunately I still had to slightly modify sql queries, but for any questions you can message me.

[Michael A. Lowry]

Broadcom published a KB article based on our support ticket:
KB367040 Problems raised up with FINALIZE SQL in Package.UTF8.DB.Migration v1.0.2

On Monday, Broadcom released v1.0.4 of the UTF-8 DB Migration Action Pack.

I have asked Broadcom to confirm whether the bugs we reported are fixed in v1.0.4.

[Michael A. Lowry]

The release notes for v1.0.4 mention only one of the problems we reported - the problem mentioned in the KB article under the heading D. Error running 2nd SQL script. Broadcom proposes to fix the problem as follows:

Fixed final step to clarify vendors. (DE124860)

We added in email that we tested our generated SQL only with database vendors tool.

"These SQL scripts have been verified using official database tools. If you're using third-party tools, please adjust the SQL queries as needed. We recommend the following tools, which we used for testing:

Oracle: SQL Developer

DB2: IBM Data Studio

MS SQL Server: SQL Server Management Studio (SSMS)"

This disclaimer added to the email states that the migration tool has been tested only in the DB clients provided by the respective DB vendors. It implies but does not clearly state that the tool may not work as intended in other DB clients.

For background, when we tried to run the 2nd SQL script in DBeaver, we encountered the errors mentioned in the KB article. We found however that we could run the individual statements one at a time.

We have asked Broadcom for an update on the remaining three problems described in the KB article:

1. Operation interrupted during insert into RT in 1st generated SQL
2. Invalid 'ADD CONSTRAINT' statements in 1st generated SQL
3. Problem setting the statements terminator in the 2nd SQL script