http://goo.gl/EmKxy0

Home > ASE, Troubleshooting > Cross Database intgerity constraints and dbid mismatch

Cross Database intgerity constraints and dbid mismatch

December 16th, 2009 Leave a comment Go to comments

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

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE, Troubleshooting Tags: , ,
  1. February 16th, 2010 at 22:06 | #1

    I have posted same issue on news groups and got following responses:

    Looking 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

  1. No trackbacks yet.