Troubleshooting of agent database classification errors in Sybase Central

---------------------------------------------------------------------------

---- This is andkylee's personal originality. Please repost it with respect to the author's Labor achievements;

---- The original source must be specified for reprinting.
:
Http://blog.csdn.net/andkylee

---



2010-07-02
15:18:00

---- Keyword: Sybase Central proxy database proxy table proxy Database

----------------------------------------------------------------------------

Problem description:

When you view the database in Sybase Central, no user database (Name: andkylee) is found in the database directory. However, execute sp_helpdb on the database using iSQL to check whether andkylee exists. After searching for a while in Sybase Central, I found the database andkylee in the proxy database directory. It is very strange how to get into the proxy database. Database andkylee is a common user database.

Continue, expand the directory of the andkylee database in the proxy database in sequence, but no user table can be found. The proxy table directory is empty and does not use the table directory (the real proxy database does not use the table ). Wondering, where are all the user tables in the andkylee library?

However, other database client tools can be used to query the user table data in the andkylee database. For example, use iSQL to connect to the database and access the andkylee database. Sp_help can view all object names. If all user tables are found, you can run select to view the table data. For example, PowerBuilder and dbartisan can find all the tables in the andkylee library under the tables directory. It seems that there are not many exceptions in the user database andkylee. It is a common database rather than a proxy database.

 

 

Cause analysis:

At first, I thought it was because the users in the andkylee library were not associated with the login account. This situation is quite common.

Run the following command in the master database: Select SUID, name from syslogins where name = 'escourt4'

1> select SUID, name from syslogins where name = 'escourt4 '<br/> 2> go <br/> SUID name <br/> ----------- ---------------------------- <br/> 5 escourt4 <br/> (1 row affected)

Log on to escourt4 with the following SUID: 5.

Go to the user database andkylee.

1> Use andkylee <br/> 2> go <br/> 1> select SUID, uid, name from sysusers where name = 'escourt4 '<br/> 2> go <br/> SUID uid name <br/> ----------- ---------------------------- <br/> 5 3 escourt4 <br/> (1 row affected)

It can be seen that the UID of escourt4 in the user's warehouse picking andkylee is: 3, and its SUID is: 5, which is the SUID of the corresponding escourt4 logon. This is normal!

 

It seems that it has nothing to do with the user database andkylee. Why should I find it in the master database!

First, what information about each database is stored in the system table sysdatabases of the master?

The information of each field in sysdatabases is as follows:

Name Data type description <br/> name sysname database name <br/> dbid smallint Database ID <br/> SUID int database owner's server user id <br/> Status smallint Control bit; table 1-6 lists the control spaces that users can set with sp_dboption <br/> Version smallint is not used <br/> logptr int pointer to transaction log <br/> crdate datetime creation date <br/> dumptrdate datetime date when dump transaction was last executed <br/> status2 smallint null additional control bit (see Table 1-7 on page 27th) <br/> Audit settings of the audflags int null database <br/> deftabaud int null indicates the Bit Blocking of the default audit settings of the table definition <br/> defvwaud int null indicates the default audit settings of the view definition. <br/> defpraud int null is the bit blocking that defines the default audit settings for Stored Procedures <br/> def_remote_type smallint null when the storage location is not provided through the Stored Procedure sp_addobjectdef, specify the default object type to be used for remote tables <br/> def_remote_loc varchar (349) null when the storage location is not provided through the storage procedure sp_addobjectdef, specify the default storage location for the remote table <br/> status3 int null additional control bit <br/> status4 int null additional control bit <br/> audflags2 varbinary (16) null for future use <br/>

 

Def_remote_loc stores the default storage location of the remote table.

 

Use interactive SQL to view sysdatabases data in the system table (the reason for not using PowerBuilder is that null and empty strings cannot be distinguished in the query results ).

Carefully compare the information of each database in sysdatabases. It is found that the ref_remote_loc value corresponding to andkylee is not null, while the ref_remote_loc value corresponding to other libraries is null.

Is the reason here?

 

Solution:

Change the ref_remote_loc value of database andkylee in sysdatabases table to null.

1> use master <br/> 2> go <br/> 1> Update sysdatabases <br/> 2> set def_remote_loc = NULL <br/> 3> where dbid = db_id ('andkylil ') <br/> 4> go <br/> (1 row affected) <br/> 1>

Use Sybase Central to reconnect to the database. The user database andkylee is no longer in the proxy database. Problem solved!

 

 

This problem is related to the proxy database in Sybase.

Let's test the agent database in ase!

 

Objective: To create a proxy database proxydb and reference all objects under user escourt4 of another user database andkylee on the same ase.

 

1> disk init <br/> 2> name = 'proxydb _ dat ', <br/> 3> physname = 'd:/syb_data/proxydb_dat.dat ', <br/> 4> size = '20m' <br/> 5> go <br/> 1> disk init <br/> 2> name = 'proxydb _ log ', <br/> 3> physname = 'd:/syb_data/proxydb_log.dat ', <br/> 4> size = '10m' <br/> 5> go <br/> 1> Create Database proxydb <br/> 2> On proxydb_dat = '20m' log on proxydb_log = '10m' <br/> 3> with default_location "local. andkylee. escourt4. "<br/> 4> for proxy_update <br/> 5> go <br/> Create Database: allocating 5120 logical pages (20.0 megabytes) on disk <br/> 'proxydb _ dd '. <br/> Create Database: allocating 2560 logical pages (10.0 megabytes) on disk <br/> 'proxydb _ log '. <br/> database 'proxydb' is now online. <br/> New User Added. <br/> (1 row affected)

Initialize the proxydb_dat and proxydb_log devices and create a proxy database proxydb. Create a proxy table for all objects pointing to local. andkylee. escourt4. in proxydb.

 

 

View the data in the proxy table in the proxy database proxydb:

 

1> Use proxydb <br/> 2> go <br/> 1> select top 10 ID, name, user_name (UID) as user_name from proxydb .. sysobjects <br/> 2> where type = 'U' <br/> 3> order by name <br/> 4> go <br/> ID name <br/> ----------- latency <br/> 800002850 aix_pagnos <br/> 832002964 aix_pageno_range <br/> 864003078 aix_sys_syscolumns <br/> 896003192 aix_sys_sysindexes <br/> 928003306 latency <br/> 960003420 ajdacg <br/> 992003534 ajdajy <br/> 1024003648 ajgdb <br/> 1104003933 ajgdb1 <br/> 1168004161 ajgdb_bakup <br/> (10 rows affected) <br/> 1> select count (*) from escourt4.ajgdb1 <br/> 2> go <br/> ----------- <br/> 123611 <br/> (1 row affected)

The proxy database is created successfully!