Using Synonyms

Synonyms is a type of SQL object that was added in SQL 2005. A synonym permits you to access an object with a different name. Synonyms can be handy when you need to do cross-database or cross-server access. The problem with this type of access is that you can't tell beforehand which name the other database or server will have, and the name could change when a new test environment is set up. By defining synonyms for the objects in the other database/server, you don't need to litter your code with dynamic SQL to handle the database/server name. All you need to do is to change the definition of the synonyms. AbaPerls supplies a stored procedure for this task.

Contents:
   Setting Up a Synonym File
   Retargeting Synonyms
   Reloading Synonym Files
   Various Observations
   Permissions

Setting Up a Synonym File

Let's say that you work with a suite of stored procedures in the SUBSIE subsystem that need to access tables and stored procedure in a different database, maybe on a different server. I will refer to that database as the target database. Once you have identified which objects you need to access in the target database, you create a file with the extension .syno in the MESSAGE directory of the SUBSIE subsystem (or some inner subsystem) to hold the synonym definitions. Let's assume that the file is called mysynonyms.syno. Here is an example how the file could look like:

CREATE SYNONYM otherdb_orders         FOR orders
CREATE SYNONYM otherdb_order_details  FOR order_details
CREATE SYNONYM otherdb_products       FOR products
CREATE SYNONYM otherdb_order_get_sp   FOR order_get_sp

When you load a synonym file, you must specify the subsystem with the ‑subsystem option, like you need to do with many other file types as well.

As you can see, the synonym definition does not include any database name, and in fact AbaPerls will give you an error if you do. When you create the file initially, AbaPerls will assume tempdb as the remote database.

Retargeting Synonyms

Next, you use the stored procedure ap_sob_retarget_synonyms_sp to change the definitions of the synonyms to point to the actual target database you will work with. You could put the call to this procedure in a .postsql file, but in most cases you don't want to do that, because typically the name of the target database will be different in different environments. Thus, you run this procedure when you need to target a new database.

ap_sob_retarget_synonyms_sp takes four parameters:

@subsystem	The subsystem for the synonym file.
@filename	The name of the synonym file, in this example mysynonyms.syno.
@dbname	The database in which the target objects reside. You can pass this parameter as NULL or the empty string to denote that the objects reside in the local database. You cannot leave this parameter NULL/empty if you specify a server.
@server	The remote server for the target objects, if any. There is a default of NULL for the local server.

In your case the target objects are in the database thatdb, so you run:

EXEC ap_sob_retarget_synonyms_sp 'SUBSIE', 'mysynonyms.sym', 'thatdb'

As a receipt of the operation, ap_sob_retarget_synonyms_sp lists the synonyms in the file, and their new definition:

otherdb_orders          [thatdb].[dbo].[orders]
otherdb_order_details   [thatdb].[dbo].[order_details]
otherdb_products        [thatdb].[dbo].[products]
otherdb_order_get_sp    [thatdb].[dbo].[order_get_sp]

Reloading Synonym Files

After you have been working for some time, you realise that your original pick of synonyms was not the best, so you edit mysynonyms.syno to read:

CREATE SYNONYM otherdb_orders         FOR orders
CREATE SYNONYM otherdb_order_details  FOR order_details
CREATE SYNONYM otherdb_order_get_sp   FOR order_get_for_remote_sp
CREATE SYNONYM otherdb_order_put_sp   FOR order_put_sp

That is, you deleted one definition, changed one and you added a new one.

When AbaPerls loads the file, it will find that the file is already stored in the database, and will therefore retrieve the current server/database part of the synonyms. AbaPerls then drops all synonyms associated with the file, and then loads the synonyms as now listed in the file. But this time AbaPerls does not tack on tempdb in the definition, but instead restores the server/database part that already was there. Thus, you don't need to run ap_sob_retarget_synonyms_sp just because you reloaded the synonym file. Note that AbaPerls assumes that all synonyms associated with the file have the same server/database part. If that is not the case (because someone has tampered with the synonyms outside AbaPerls), the result is undefined.

Various Observations

Since AbaPerls handles all synonyms in a file as a unit, it follows that if you target objects could be spread out over several databases, you should put them in different files.

If the synonym definition starts with a number character (#), AbaPerls does not add any server/database part to the definition.

As you have seen, AbaPerls handles synonyms somewhat different from other type of objects. Here are the key differences:

The same file can define several synonyms.
There is no check that the object name agrees with the file name.
A synonym file is treated as a unit by AbaPerls, but in difference to an INSERT-file, a synonym file is not an object in itself in AbaPerls.
AbaPerls does not perform any object checking of synonym files. That is, you will not be alerted of misspellings in the target names.
.syno files are only supported if the ABAPERLS subsystem is present in the database. If you need to work with synonyms without the AbaPerls System Tables, you will need to put your synonym definitions in .sql files and roll your own, using IF EXISTS etc.

When you build an empty database DBBUILD, synonym files are created very early, after the .sql files, and likewise they appear early in the update scripts generated by DBUPDGEN.

Permissions

If a synonym could be accessed directly by a user, the user will need to have permission on the synonym, as well as the underlying object. In this case, you will need to add entries to grant.template to define the permissions for the synonyms. Since a synonym can refer to different sort of objects, different synonyms may seem to need different permissions. But if you as a matter of routine want to grant SELECT on all tables and EXEC on all stored procedures, you can do this in grant.template:

GRANT EXEC, SELECT ON .* TO dvp -- SYNONYM

When you run ap_sob_retarget_synonyms_sp, it makes sure that the permissions are retained when it drops and recreates the synonyms.