Is it possible to create an alias or synonym for an existing table that would point to some other existing table ?
Situation: I have a tool based on Java with Hibernate, and I worked on a task to migrate some area from an old DB table (SchemaOLD.TableOLD) to a new table (SchemaNEW.TableNEW) (all this happening in the same DB server). Things are close do DONE, but I discovered some legacy 3rd party components that still point to the original location (SchemaOLD.TableOLD). Due to several business constraints, I'm forced to look for faster alternative solutions (instead of having those other components also migrated...).
I need to somehow make the DB Server to "resolve" the SchemaOLD.TableOLD String to the new one SchemaNEW.TableNEW, so that those legacy components would run undisturbed while actually retrieving data from the new location. I would need this to take place while both the old and new tables still exist in the DB .
Is this somehow possible ? What would be the best solution, aliases or synonyms ? The difference between these two is a little unclear for me...
Thank you.
If you're wanting to use either ALIAS or SYNONYM, then you should use ALIAS. Here is the note from the SYNONYM Knowledge Center page:
Important: Use aliases instead of synonyms. Synonyms are similar to aliases, but are supported only for compatibility with previous releases. Synonyms might not be available in future releases of DB2®. Synonyms behave differently with DB2 for z/OS® than with the other DB2 family products. Do not use synonyms when writing new SQL statements or when creating portable applications.
Another option could be a VIEW:
CREATE VIEW SchemaOLD.TableOLD AS
SELECT * FROM SchemaNEW.TableNEW
If you're also needing to prevent the old utilities from updating the "new" table, you could potentially look into using a trigger on that view.