Hi

Is it possible to create a SYNONYM for a SEQUENCE in DB2?

I am using UDB 8.1.5 on both NT and Solaris. I am quite new to DB2 (I'm an Oracle head! Sorry!).

I have been able to create:
- Grants for Sequences
- Grants for Tables
- Grants for Views
- Synonyms for Tables
- Synonyms for Views

e.g. CREATE SYNONYM FOAPPUSER.TLOCK FOR FOAPP.TLOCK
... where FOAPP owns Table TLOCK, and FOAPPUSER wants to refer to this Table without prefixing the Table Owner.

This works fine for Tables and Views, but not for SEQUENCES.

This is a serious spanner in the works for me!!

Any ideas / workarounds? Creating the Sequences in FOAPPUSER is currently not an option.

Many many thanks in advance.

Brian.

What exactly is the 'spanner in the works'?

You could probably get round it by wrapping the call to your sequence in a stored procedure.

We have a mature product that has traditionally been sold on SQL Server and Oracle, where such concepts as creating synonyms for sequences was a valid concept.

We have now ported it to DB2, and all works fine when the Application and Serucity Schemas that are the objects owners are also the Schemas that connect from the Application. However, we need to add an extra layer of security, namely dummy schemas that do not own any objects, but merely have S/I/U/D grants and synonyms to the object owner schemas.

This system has approx 300 sequences and the code is set in stone.
How do you suggest a stored procedure would help?
e.g. INSERT INTO TLOCK (C,I,blah,blah2) VALUES(106,SLock.Nextval,'blah','blah2');
would be an example of some of our code....

Changing the code is NOT an option.

Cheers,

Brian.

Any takers?
Any ideas?

If you've just ported this, how come changing the code is not an option? The migration must have involved some changes?

The idea of using an SP would be to create procedure referencing whichever objects you require. the SP would bind to those objects (i.e. your sequence) and you could grant execute on the SP's package to a user that otherwise wouldn't have access to those objects.

Maybe this won't fit your scenario but it is a workaround for this kind of problem.

Damian