Proxy tables
Rob
understand if I can use Proxy tables to this scenario.
"Part of data (old data) in a table has been moved to a
different database (same table name)"
e.g. MEMBER_TABLE in memberdb
MEMBER_TABLE in memberarcdb
I have an application (have only binary, no sourse code)
which reads from the memberdb.MEMBER_TABLE and diaplays in a
User Interface.
Since I can't see the underlying select statement which
reads from the MEMBER_TABLE, I was wondering if I can create
a proxy table to fetch data from both DBs ?
Thank you
Rob
tartampion
create a view in memberdb called MEMBER_TABLE pointing to
MEMBER_TABLE in memberarcdb. Retreiving data from the view
is going to be much faster than fetching them using a proxy
table.
Proxy tables; in my experience; are only good in the case of
small tables. A proxy table on an archive table(Normally
huge) is going to associate itself with performance issues.
tartampion
Mark A. Parsons
----------------
Regarding the SQL text being submitted by the application ...
If you're using ASE 12.5.0.3+ you may be able to use MDA tables to capture the SQL text.
Alternatively, configure the dataserver to use auditing, then enable the auditing of cmdtext for the desired login. The
SQL text should show up in one of the sysaudits tables in the sybsecurity database.
If you're not sure what I'm referring to (MDA or auditing) just post back to the newsgroup and we can go into more
details on the options for capturing SQL text.
----------------
Regarding the access of data from 2 different tables ...
Does the application pull data from both copies of the table? or just from the memberdb copy? Does the application
need to pull data from both tables?
Do both databases reside in the same dataserver?
There are typically some performance issues with using proxy tables so before we start looking at proxy table solutions
we need to see if there are other options.
Depending on your ASE version (@@version), and assuming both databases reside in the same dataserver, it may be possible
to access both tables via a single view.
An alternative method could include defining a stored proc to access the 2 tables, then placing a proxy table on the
front of the stored proc to make the application think it's accessing a single table.
If the 2 databases reside in different dataservers then at some point a proxy table would be needed to access the data
in the remote dataserver.
Assuming the application's query cannot be modified, each option I can think of requires renaming memberdb..MEMBER_TABLE
and creating a new memberdb..MEMBER_TABLE; the new object would be a view or a proxy table ... at which point we need to
take into consideration all other processes which also access the MEMBER_TABLE table(s), ie, what kind of changes would
need to be made for INSERT/UPDATE/DELETE/SELECT statements for all other applications?
'course, if the application is executing a stored proc (as opposed to SELECTing from MEMBER_TABLE) then the solution
becomes much easier to implement, ie, make a modification to the stored proc so that it pulls data from both tables.
Sooooo ... there are a few options available to you ... and a few new issues you'll need to address based on the
solution you choose to go with.
'course, if the application could be modified then the solution (again) becomes much easier to implement, ie, modify the
application code. ("Duh, Mark!" ?)
Sherlock, Kevin [TeamSybase]
memberarcdb is on the same ASE server as memberdb then just simply create a
view to solve your problem.
However, since you say that you can't "see" the underlying SQL, I'm assuming
that you can't change it either. So, your problem is that the namespace
"memberdb..MEMBER_TABLE" is already being used for the base table. If you
change that name to point to a view, you must rename the base table to
another name, and that might break other SQL statements that refer to that
name. You could get fancy and create a view named "memberdb..MEMBER_TABLE"
which gets it's data from both tables, and then write some "instead_of"
triggers on the view to handle inserts/updates/deletes that were intended
for the base table. Not sure that would be the best solution though.
Best solution would be to create a view named something else like
"MEMBER_TABLE_ALL" for example, and then change the SQL which references the
old table name.
<Rob> wrote in message news:49c3a273.6de...@sybase.com...
Mark A. Parsons
In order to implement a solution which allows the application to access both copies of the MEMBER_TABLE table, you
*HAVE* to know what query (stored proc?) the application is submitting to the dataserver, hence the need to capture the
SQL text being submitted by the application.
--------------
Additional ideas about capturing SQL text ...
The ASE dataserver software comes with a program called RIBO (typically an additional option chosen when installing the
dataserver software) which basically allows you to sniff/capture all traffic (including SQL text) between a client
application and the dataserver. [There are some 3rd party products which can provide the same benefit, but in this case
it's probably a lot cheaper to use the 'free' RIBO ... if you go this route.]
If the application's access of the MEMBER_TABLE table takes awhile to run you may be able to capture the SQL text with
the following:
=================
-- issued from a different spid/connection:
dbcc traceon(3604)
go
dbcc sqltext(<spid_of_application>)
go
=================
However, don't hold your breath (ie, dbcc sqltext is not very reliable and often provides just a portion of the SQL text).
NOTE: You could apply an exclusive table lock (from another spid) to the MEMBER_TABLE, then while the application's
query is blocked you could run dbcc sqltext (thus eliminating the issue of an application query that normally runs too
quickly to capture).
Rob
I have learnt something new here.. Will read and use the
monitoring tables to capture the SQL...
Thank you
Rob