how to manually migrate rhythmyx repo to ms sql server

Rhythmyx Technical Discussion
1

  1. if anyone knows an easier way (i couldn’t get MS SSMA to work) then please let me know !

  2. create a clean base rhythmyx repository install to ms sql server

  3. dump DDL for any custom tables

  4. globally replace datatypes
    number(0,0) -> int
    char2 -> nchar
    varchar2 -> nvarchar
    date -> datetime
    blob -> binary
    clob -> ntext

  5. run DDL against ms sql to add custom tables to clean base build

  6. use ms sql server management studio to import all tables to a import db schema

  7. insert into final.tables select * from import.tables

declare @src varchar(20)
declare @dst varchar(20)

set @src = ‘import-schema’
set @dst = ‘final-schema’

select
’ truncate table '+ @dst + ‘.’ + so.name +
char(13) + char(10) +
’ insert into ’ + @dst + ‘.’ + so.name + ’ select * from ’ + @src + ‘.’ + so.name

from
sys.objects so

join
sys.schemas ss on so.schema_id=ss.schema_id

where
ss.name = @src and so.type=‘U’

order by
so.name

2

revised data type mappings (because of future depreciated types in mssql)

number(10,0) -> int
varchar2( -> nvarchar(
char2( -> nchar(
date -> datetime
blob -> varbinary(max)
clob -> nvarchar(max)

3

watch out for PSLOGDAT different column size LOG_DATA

legacy
CREATE TABLE [RHYTHMYX].[PSLOGDAT](
[LOG_ID_HIGH] [decimal](10, 0) NOT NULL,
[LOG_ID_LOW] [decimal](10, 0) NOT NULL,
[LOG_SEQ] [decimal](10, 0) NOT NULL,
[LOG_SUBT] [decimal](10, 0) NOT NULL,
[LOG_SUBSEQ] [decimal](10, 0) NOT NULL,
[LOG_DATA] nvarchar NULL
) ON [PRIMARY]

mssql
CREATE TABLE [rhythbase].[pslogdat](
[log_id_high] [int] NOT NULL,
[log_id_low] [int] NOT NULL,
[log_seq] [int] NOT NULL,
[log_subt] [int] NOT NULL,
[log_subseq] [int] NOT NULL,
[log_data] nvarchar NULL
) ON [PRIMARY]

4

revised table copy sql :

declare @src varchar(20)
declare @dst varchar(20)

set @src = ‘src-schema’
set @dst = ‘dst-schema’

select
‘print ’ + ‘’’’ + @dst + ‘.’ + so.name + ‘’’’
+
char(13) + char(10)
+
'truncate table '+ @dst + ‘.’ + so.name
+
char(13) + char(10)
+
'insert into ’ + @dst + ‘.’ + so.name + ’ select * from ’ + @src + ‘.’ + so.name
+
char(13) + char(10)
+
char(13) + char(10)

from
sys.objects so

join
sys.schemas ss on so.schema_id=ss.schema_id

where
ss.name = @src and so.type=‘U’
and
so.name not like ‘FIXMASTER%’
and
so.name not like ‘%_BAK’
and
so.name not like ‘%_BAKUP’
and
so.name not like ‘%_UPG’

order by
so.name

5

latest problems

dbo.PSX_OBJECTS
.Net SqlClient Data Provider: Msg 4712, Level 16, State 1, Line 96
Cannot truncate table ‘dbo.PSX_OBJECTS’ because it is being referenced by a FOREIGN KEY constraint.

dbo.PSX_PERSISTEDPROPERTYVALUES
.Net SqlClient Data Provider: Msg 2627, Level 14, State 1, Line 101
Violation of PRIMARY KEY constraint ‘PK__PSX_PERSISTEDPRO__76969D2E’. Cannot insert duplicate key in object ‘dbo.PSX_PERSISTEDPROPERTYVALUES’.
The statement has been terminated.

dbo.PSX_RELATIONSHIPPROPERTIES
.Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 109
The INSERT statement conflicted with the FOREIGN KEY constraint “FK__PSX_RELATIO__RID__7D439ABD”. The conflict occurred in database “rhythmyx”, table “dbo.PSX_RELATIONSHIPS”, column ‘RID’.
The statement has been terminated.

dbo.PSX_RELATIONSHIPS
.Net SqlClient Data Provider: Msg 4712, Level 16, State 1, Line 112
Cannot truncate table ‘dbo.PSX_RELATIONSHIPS’ because it is being referenced by a FOREIGN KEY constraint.

6

psx_object*, psx_relationship*, transition* fixed by using delete from and reordering sql statements

print ‘delete’
delete from dbo.psx_objectproperties
delete from dbo.psx_objects

– FAILS even if empty !
– truncate table dbo.psx_objectproperties
– truncate table dbo.psx_objects
– FAILS even if empty !

print ‘insert’
insert into dbo.psx_objects select * from rhythmyx.psx_objects
insert into dbo.psx_objectproperties select * from rhythmyx.psx_objectproperties

7

i’ve now automated this process by using

drop function truins
go

create function truins
(
@fun varchar(10) = ‘’,
@src varchar(100) = ‘’,
@dst varchar(100) = ‘’,
@tbl varchar(100) = ‘’
)
returns varchar(500)
begin
declare @srctbl varchar(200)
declare @dsttbl varchar(200)
set @srctbl = @src + ‘.’ + @tbl
set @dsttbl = @dst + ‘.’ + @tbl
return
CASE @fun
WHEN ‘TRU’ THEN
‘truncate table ’ + @dsttbl
WHEN ‘DEL’ THEN
‘delete from ’ + @dsttbl
WHEN ‘DRP’ THEN
’ if NOT (OBJECT_ID(N’’’ + @dsttbl + ‘’’)) IS NULL drop table ’ + @dsttbl
WHEN ‘SEL’ THEN
’ select * into ’ + @dsttbl + ’ from ’ + @srctbl
WHEN ‘INS’ THEN
'insert into ’ + @dsttbl + ’ select * from ’ + @srctbl
END
end
go

select dbo.truins(‘TRU’,‘b’,‘c’,‘d’)

declare @src varchar(20)
declare @dst varchar(20)

set @src = ‘src’
set @dst = ‘dst’

select
dbo.truins(‘TRU’, @src, @dst, so.name) + char(13) + char(10)
from
sys.objects so
join
sys.schemas ss on so.schema_id=ss.schema_id
left join
sys.foreign_keys fk on so.object_id = fk.parent_object_id
where
so.type=‘U’ and ss.name = @src and
fk.parent_object_id is null

select
dbo.truins(‘DEL’, @src, @dst, so.name) + char(13) + char(10)
from
sys.objects so
join
sys.schemas ss on so.schema_id=ss.schema_id
left join
sys.foreign_keys fk on so.object_id = fk.parent_object_id
where
so.type=‘U’ and ss.name = @src and
fk.parent_object_id is not null

select
dbo.truins(‘INS’, @src, @dst, so.name)
from
sys.objects so
join
sys.schemas ss on so.schema_id=ss.schema_id
left join
sys.foreign_keys fk on so.object_id = fk.parent_object_id
where
so.type=‘U’ and ss.name = @src and
fk.parent_object_id is null

select
dbo.truins(‘INS’, @src, @dst, so.name)
from
sys.objects so
join
sys.schemas ss on so.schema_id=ss.schema_id
left join
sys.foreign_keys fk on so.object_id = fk.parent_object_id
where
so.type=‘U’ and ss.name = @src and
fk.parent_object_id is not null

8

revised sql generation