Unchained Migration to Designer 9i: step by step, repeatedly and bidirectional Lucas Jellema, AMIS S

1
Unchained Migration to Designer 9i step by step,
repeatedly and bi-directional Lucas Jellema,
AMIS Services BVOTDUG 2003
2
Agenda

• Designer 9i Migration (from Designer 6.0)
• Process and limitations
• Internals
• Step 1 Migration into an existing Workarea
• Step 2 Migrate in stages
• Step 3 Repeated Migration (refresh)
• Step 4 Downward migration
• Discussion and Conclusions

3
Designer 9i Migration from Designer 6.0

• Migration at Repository (database server) level
• Typically followed by Application Migration
  (Forms and Reports)
• re-design, re-generation or conversion design
  capture
• Possibly merging several 6.0 Repositories into a
  single 9i Repository
• Or migrating from a single 6.0 Repository to
  multiple 9i Repositories
• Migration from Designer 1.3.2 supported through
  an intermediate step to Designer 6.0 (server
  side)
• Fully automated process
• Typically though not necessarily across
  databases

4
Designer 9i Designer 6i
Designer 9i
Designer 6i

The only difference is in the Client (Forms
Generator) Note Oracle SCM is the same as
Oracle 6i Repository Note Designer 2.1.2
Designer 6.0 (Server Side)
5
Major Differences between Designer 6.0 and
Designer 9i (Server Side)

• From 4 tables to 150 tables

sdd_elements
sdd_structure_elements
i_sdd_object_versions
i_sdd_ent
i_sdd_funent
i_sdd_folder_members
i_sdd_att
rm_text_lines
ck_application_logic
rm_text_lines
ck_application_logic
API
API
ci_entities
cioentity
ci_entities
cioentity
ci_function_entity_usages
ci_function_entity_usages
6
Major Differences between Designer 6.0 and
Designer 9i (Server Side)

• New concepts
• Versioning
• Files in the Repository
• Workareas and Configurations
• Repository Schema Registration
• No more sharing (nor skeleton application
  systems)
• LOV Components
• Some new properties as well as several deprecated
  (obsoleted) properties

7
Preparations for Migration Process

• Preparations
• Install Designer 9i Client Repository
• Migrate User Extensions
• Extract (Unload) in Designer 6.0 RAU gtLoad in
  Designer 9i Repository Administration Utility
• Migrate Subordinate Users
• Using CKGENUSR.SQL and CKGENPRV.SQL
• Manually by creating database users with the same
  name as the users in Designer 6.0 and identifying
  them as Repository Users in the Designer 9i RAU
• Restrict usage of source and target repository
  during migration
• Select is not a problem at all
• DML should not take place on the source
  application systems
• Performance will be impacted during actual
  migration

8
Migration Process

• From the RAU as Repository Owner start the
  migration process
• Provide the name of the workarea to hold the
  migration result (for versioned repositories)
• this must be a new workarea
• Provide connect details for the source (Designer
  6.0) Repository for the Repository Owner
• Select Application Systems to migrate
• Application systems related through shares are
  included automatically
• Confirm the selection and start the actual
  migration
• Wait between 15 minutes and many hours
• Done!

9
Migration internals

• Directory of interest ORACLE_HOME\repadm61
• All conversion/migration scripts are in \cnv
• Except scripts to migrate users, these are in
  \utl
• Log-files are written to \logs
• Actions performed as
• Repository Owner on Designer 9i Repository
• Reading from Repository Owner on Designer 6.0
  Repository
• Through a database link (Designer 6.0 Database
  must be known in the tnsnames.ora on the database
  server for the 9i Repository)

10
Migration internals (2)

• A number of intermediate (staging) tables is
  created at the start of the migration
• For new or transformed elements
• For bookkeeping
• The source 6.0 Repository is cleansed
• ckazanal.sql for repairing 6.0 corruptions

11
Migration internals (3)

• Populate several upgrade tables to support the
  creation of new 9i elements
• that do not directly correspond to 6.0 Repository
  Objects, such as List of Values and Navigation
  Items

12
Migration internals (4)

• Populate the JR_ID_MAPS table with one record for
  every 6.0 Repository Object to be migrated
• as well as every row in the upgrade tables
• In every record in JR_ID_MAPS, include the old
  Designer 6.0 ID and the new 9i IRID en IVID.

13
Migration internals (5)

• Load data into the 9i Repository tables from both
  the 6.0 Repository tables (SDD_ELEMENTS,
  SDD_STRUCTURE_ELEMENTS), the upgrade tables and
  the JR_ID_MAPS table.
• The latter table is not only used for values in
  IRID and IVID columns of the 9i Repository table
  but also the (IRID) value in all referencing
  columns.

14
Migration internals (6)

• Example of usage of JR_ID_MAPS
• insert into isdd_ent(irid, ivid, name, plural,
  short_name,...)select m.irid, m.ivid, e.name,
  e.plural, e.short_namefrom jr_id_maps m,
  ci_entities_at_dblink_to_des60 ewhere e.id m.id

15
Limitations in the migration process

• Migration always ends up in a new workarea
• Migration result cannot reference pre-existing
  (pre-migrated) elements
• Migration cannot be repeated
• To add changes made in Designer 6.0 to 9i
  Repository
• Migration of multiple versions of application
  systems in 6.0 does not result in multiple
  versions in Designer 9i
• Migration cannot be made downwards
• Elements in Designer 9i cannot be migrated (back)
  to a Designer 6.0 Repository

16
Limitations in the migration process

• Not relevant if you have a single 6.0 Repository,
  do a big-bang migration and switch off the 6.0

Designer 6.0
B
Migration 6.0 gt 9i
A
C
17
Version Control for modified Designer scripts

• Changing base scripts is unsupported and
  basically tricky
• It is important to be always able to
• Fall back to the original scripts
• Go along with product upgrades
• The approach here is to use Oracle SCM for
  managing the scripts and the changes to them

18
Tackling the first limitationMigrate to a
Workarea of your choice

• The RAU checks ina case-sensitive way
  whether the workareaname provided
  alreadyoccurs if so the entryis rejected
• The migration scriptscan handle migrationto an
  existing workareafine
• We can trick the RAU by typing in an existing
  workarea name with different casing (mywork
  instead of MyWork)
• For this to work properly, we also need to change
  ckwkarea.sql

19
Tackling the first limitationMigrate to a
Workarea of your choice (2)

• ckwkarea.sql ...
• select irid
• into wkarea_irid
• from isdd_workareas
• where lower(name) wkarea_name ...
  jr_workarea.rename_workarea(workarea_id gt
  wkarea_irid, new_name gt wkarea_name)
• With this change, you can type a lower case
  workarea name in the RAU and if a workarea with
  the same name (but different casing) exists, it
  will be the target workarea for the migration

20
Tackling the second limitationMigrate in stages

• The default functionality allows you to migrate
  sets of applications systems at different times
• But it will not recognize or use previously
  migrated application systems on subsequent runs

Oracle Designer 9i Repository
Oracle Designer 6.0 Repository
CUS
FIN
CORE
21
Tackling the second limitationMigrate in stages

• What the migration tool should do
• Set the referencing columns in FIN to reference
  the CORE elements previously migrated
• The values of referencing columns are derived
  from JR_ID_MAPS
• We can manipulate JR_ID_MAPS after it has been
  populated and before it is usedto derive
  referencing column values

Oracle Designer 9i Repository
22
Tackling the second limitationMigrate in stages

• JR_ID_MAPS can be manipulated from an archive of
  records of previously migrated elements

database link
Designer 6.0
Designer 9i
B
Migration 6.0 gt 9i
C
A
C
JR_ID_MAPS
id irid ivid
A
ID_MAP_ARCH
C
id irid ivid
update
C
23
Tackling the second limitationMigrate in stages

• Back up the contents of the JR_ID_MAPS table
  after each migration for future reference
• In the JR_ID_MAPS_ARCH and MIGRATED_APPS tables
• At the start of a migration, indicate which
  previously migrated application systems should be
  reused or can be hooked into
• Modify the migration scripts to not migrate
  elements in the REUSED application systems
• Modify the migration scripts to update JR_ID_MAPS
  for all reused elements with the IRID assigned in
  previous migration runs before starting the
  actual migration
• So that new elements will have referencing
  columns with their value derived from JR_ID_MAPS
  based on JR_ID_MAPS_ARCH

24
Tackling the second limitationMigrate in stages

• Two Warnings
• If you have added elements to the previously
  migrated application system and these elements
  are referenced, ensure that these elements are
  migrated
• For example by creating a temporary application
  system in 6.0, transfering these elements to it,
  include that application system in the migration
  set (and moving back these elements after
  migration)
• When a migration fails or is aborted, Designer
  will perform a purge before a subsequent
  migration is started.
• Before this Purge can be done, ensure that you
  delete from JR_ID_MAPS all records with an IRID
  value that is derived from existing Repository
  Elements

25
Tackling the third limitationRepeat the migration

• Migration should be repeatable
• To add changes made in Designer 6.0 to 9i
  Repository
• Migration of multiple versions of application
  systems in 6.0 does not result in multiple
  versions in Designer 9i
• Essentials
• All migrated instances of the same object become
  part of the same version tree
• There is a branch for all migrated objects
  (versions)
• Changes from Designer 6.0 end up on this branch
  and are merged to the Designer 9i Main Branch

26
Tackling the third limitationRepeat the migration

• After the migration, the result is in the
  Repository with the same IRID as the version tree
  but not yet on any branch we will now
• Link the migration result to its predecessor on
  the migration branch
• Check in the migration result
• Or undo checkout if we somehow determine that it
  was not changed since the previous migration
• Merge the changes brought in by the migration to
  the latest development version in Designer 9i
• In this way we can also build a version tree in
  Designer 9i from elements in different
  application system versions in Designer 6.0

27
Tackling the fourth limitationDowngrate
Migrate Downwards

• If work continues in Designer 6.0, it may be
  desired to capture the changes made in Designer
  9i back into Designer 6.0
• This operation is not at all supported by the
  core Designer 9i product

28
Tackling the fourth limitationDowngrate
Migrate Downwards

• Steps
• In Designer 9i determine which elements have been
  changed (new version checked in)
• Using ID_MAP_ARCH, determine the Designer 6.0
  element to which each changed object is mapped
• Update the Designer 6.0 element using a PL/SQL
  program reading from the 9i API and writing to
  the 6.0 API

29
Tackling the fourth limitationDowngrate An
Example

• Function to get Designer 6.0 Id from Designer 9i
  Irid
• Create function get_id_for_irid( p_irid in
  number) return number is
• l_id number(38)
• cursor c_idm( b_irid in number) is
• select idm.id from id_map_arch idm where
  idm.irid b_irid
• begin
• open c_idm( b_irid gt p_irid)
• fetch c_idm into l_id
• close c_idm
• return l_id
• end
• Loop over all changed objects in Designer 9i
• For each object, make the required API calls for
  reading the changed element and updating the
  source element in Designer 6.0

30
Tackling the fourth limitationDowngrate An
Example

• procedure downgrate_entity( p_ivid in number) is
• cursor c_ent(b_ivid) is select from
  ci_entities_at_r9i where ivid b_ivid
•  r_ent c_entrowtype
• l_ent cioentity.data
•  begin
• l_ent.v.id get_id_for_irid( r_ent.irid)
• l_ent.v.name r_ent.name
• l_ent.v.supertype_reference
  get_id_for_irid(l_ent.supertype_reference)
• l_ent.v.plural r_ent.plural
• .. process other properties and set indicators
• if l_ent.v.id is null
• then
• cioentity.ins( null, l_ent)
• -- update ID_MAP_ARCH with this new mapping
  between IRID, IVID and ID
• insert into ID_MAP_ARCH (id, irid, ivid)
  values (l_ent.v.id, r_ent.irid, r_ent.ivid)
• else
• cioentity.upd( l_ent.v.id, l_ent)
• end if
• for r_att in c_att( b_ent_irid gt p_irid) loop

31
Discussion and Conclusions

• Migration can be performed in a much simpler and
  more flexible way through relatively small
  changes to standard scripts
• Though not supported and requiring extensive
  testing
• Really only relevant if for a substantial period
  of time work is done in parallel in Designer 6.0
  en 9i
• Ideally you can migrate everything in one big
  step and forget about Designer 6.0 altogether
• The Designer/Repository product team could have
  been more responsive to migration needs
• Even though the migration software for Designer
  6i/9i is very good already
• Especially since the changes for extended
  functionality are so simple and small

32
The End

• For questions send an email to jellema_at_amis.nl
• For more information, scripts and examples go to
  www.amis.nl/technology