How to export synonyms?

[thomasschulz2]

Hello forum,

for creating a test instance we want to export catalog data from selected users/schemas including their synonyms. With loadercli the commands "export user catalog ..." or "export schema <dbuser> ..." don't extract the synoyms. And "export table synonyms ..." doesn't works either.

How can we export synonyms without exporting the data? Our version is 7.6.06.03 on linux (64bit).

Regards,

Thomas

[steffen_schildberg · Accepted solution]

Hi Thomas,

you can't export synonyms at the moment. This is most likely a bug and I have to check this. I'll get back to you as soon as I found the reason. Unfortunately I even do not have a workaround for you.

Sorry for the inconvenience.

Regards,

Steffen

[thomasschulz2 · Accepted solution]

Hello Steffen,

thank you for your investigations. Because we supposed something like this, our developers have created some functions for exporting the needed data out from the table domain.synonms.

Regards,

Thomas

[lbreddemann · Accepted solution]

Hello Thomas,

in this case it would be nice if you would just share this procedure.

Although it shouldn't be too difficult to query the dictionary for this, I would guess that many other users might struggle with that.

It would be cool to have this in the SDN MaxDB WIKI HowTo section.

thanks and regards,

Lars

[steffen_schildberg · Accepted solution]

Hi Thomas,

I dived a bit into it and found that synonyms do not get exported if they are declared public. Is this the case for your synonyms? If they are not public they can on the other hand only be exported when exporting a single table. This is of course rather complicated and I think it is clearly a bug. We will change this behavior but I cannot say for sure when this will be ready for the public.

Good to hear that you found a workaround and sorry for any inconvenience.

Regards,

Steffen

[thomasschulz2 · Accepted solution]

Hi Steffen,

our synonyms are not public.

Edit: Maybe an important info - our synonyms references to tables of other db users (create synonym user1.xyz for user2.xyz).

Regards,

Thomas

Edited by: Thomas Schulz on Apr 18, 2011 3:02 PM

[thomasschulz2 · Accepted solution]

Hi Lars,

I think, that our solution is a little bit too simple for a HowTo, but here it is:

1. Get values:

select synonymname, tableschemaname, tablename from synonyms where owner = '<dbuser>'

2. Run loop with sql commands generated from extracted values:

create synonym <synonymname> for <tableschemaname>.<tablename>

An shorter alternative will be:

select 'create synonym ' & synonymname & ' for ' & tableschemaname & '.' & tablename from synonyms where owner = '<dbuser>'

That's all.

Regards,

Thomas