Hi Folks,
I came across the interesting issue on ASE Server.(Still not sure, need to check with sybase tech suport)
Suppose in our prod env, we have cross database referential integrity constraints, these referential integrity constraints stores in sysreferences table by relating the dbids of the two dbs(primary db and referenced db).
When we perform the refresh from one server to another (Suppose PROD - > UAT).These constraints come as it is in refreshed database. In refreshed dataserver, all dbs may not be created in same order as source dataserver, which results in dbid mismatch from source server.
Still our constraints are same as source db with source dbids.
When we run the dbcc checks on refreshed db, it starts flaging for the wrong dbid in sysreferences table.
Interesting ! Isn't it?
In sybase manual and sybooks, I didn't get any article related to db id mismatch, for cross db integrity constraints.
Will update u soon, if any.
Anva
I have posted same issue on news groups and got following responses:
ReplyDeleteLooking like, Sybase don't have any permanent solution for the issue. Thanks.
By Mark A. Parsons
Yes, this is a known issue.
Any time you're dumping/loading databases between different dataservers, and you have inter-database (or
inter-dataserver) references, you have the potential to run into this issue. Some areas where this issue arises:
- logins (master database) vs users/aliases (in user database)
- cross-database referential integrity constraints
- encrypted columns vs encryption key in different database
- proxy tables (sysattributes) vs sysservers (master database); eg, production sysservers entries typically differ from
development syssyservers entries, so proxies pointing at remote dataservers will (typically) be 'wrong' after dumping
from prod and loading into dev
-------------------
In most cases it's possible to update system tables to fix the issues, though a) this is usually not supported by Sybase
TechSupport and b) requires more advanced knowledge of the relationship between the various system tables. [NOTE: In
some cases it's also best to bounce the dataserver after such modifications in order to insure ASE in-memory data is
properly updated.]
For most folks the typical solution is to drop/recreate the offending items.
-------------------
Regardless of the method used to 'fix' the issue, the solution is always custom designed based on local requirements
(ie, there is no auto-sync/auto-fix tool from Sybase).
By Carl Kayser
Known issues? Yep. Some particulars (and some with ISUG Enhancement
Request IDs).
(1) ER 3103. Provide support for disabling/enabling referential
constraints. The "enablement" might check and correct the ids by assuming
the names to be correct. No action by Sybase on this yet.
(2) ER 3351. Allow for "sp_addlogin .... suid=N". This would help in
maintaining a consistent suid for each login across ASE servers. No action
by Sybase on this yet.
(3) ER 3352. Allow for "create role ... with srid=N". The role analog of
3351. No action by Sybase on this yet.
(4) ER 3475. With 15.0.2 or later one can "create database ... with dbid=N
..." Definitely helpful.
(5) ER 3586. Provide an SP to synch syslogins and sysusers. No action by
Sybase on this yet. Problem with this is what exactly is meant by synching
the system tables. I (and some other admins) write our own SPs for doing
this. No action by Sybase on this yet.
By Cory Sane [TeamSybase]
Many of us have solved this problem by maintaining the same dbids for every database across servers.
On some of my older version servers it is not uncommon to see a dbname of placeholder17 because we wanted to skip that dbid on
that server.
--
Cory Sane