|
Home > Archive > ASE Database forum > May 2005 > "Select into" from one Sybase ASE server to another?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
"Select into" from one Sybase ASE server to another?
|
|
| Robin Parsons 2005-04-26, 8:24 pm |
| I am successfully using a stored procedure to drop tables,
drop indexes, and then "select into" to load tables in one
database from another database on the same server. We would
now like to load the first database from our Production
database that is located on a different physical machine.
Both servers are running the same version of Sybase ASE.
How could we accomplish this?
Thanks in advance,
Robin
| |
| Mark A. Parsons 2005-04-26, 8:24 pm |
| A quick-n-dirty answer:
- setup a proxy table (see 'create existing table'/'create proxy_table') in
a local database to reference a table in a remote database
Then it's as 'easy' as:
select <select_list>
into <new_local_table>
from <proxy_table>
The proxy could be setup in any of your local databases.
Obviously (?) you need to insure your servers are setup to support CIS
connections (used by proxy tables).
Robin, Parsons wrote:
> I am successfully using a stored procedure to drop tables,
> drop indexes, and then "select into" to load tables in one
> database from another database on the same server. We would
> now like to load the first database from our Production
> database that is located on a different physical machine.
> Both servers are running the same version of Sybase ASE.
>
> How could we accomplish this?
>
> Thanks in advance,
> Robin
| |
| wkraatz 2005-04-27, 1:24 pm |
| My suggestion is to define the target database equivalent to
the production database. (Same sequence and size of data
and log allocations). Then dump the production database use
the dump file and load the target database. You would need
to adjust the users to match the target systems
sysloginstable. This could be accomplished by dropping all
the users and aliases and readding them.
> I am successfully using a stored procedure to drop tables,
> drop indexes, and then "select into" to load tables in one
> database from another database on the same server. We
> would now like to load the first database from our
> Production database that is located on a different
> physical machine. Both servers are running the same
> version of Sybase ASE.
>
> How could we accomplish this?
>
> Thanks in advance,
> Robin
| |
| Robin Parsons 2005-04-27, 1:24 pm |
| The database we are populating is a subset of data from the
Production database (it is data that the public may retrieve
interactively on our web site). We need to make this a
batch job that runs nightly. We currently have a
PowerBuilder application that uses the timer event to fire
pipelines to do the load from one server to the other, then
we populate the backup on the same server with a stored
procedure. We have recently moved the database from SQL
Server to another Sybase database and are getting rid of SQL
Server. Now the load, which used to run in 40 minutes takes
2 1/2 hours, from Sybase to Sybase. (Sorry, but it's true.)
When we run the load with the stored procedure doing
"select into" that populates the backup database, it runs
very fast, about 12-14 minutes to load the same data.
Ideally, we would like to handle the first load (from
Production database) the same way, but have not figured out
how to reference "select into" from one server to another as
we were able to reference from one database to another on
the same server.
I'll try to learn about proxies and see if that suggestion
will work.
Thanks all,
Robin[color=darkred]
> My suggestion is to define the target database equivalent
> to the production database. (Same sequence and size of
> data and log allocations). Then dump the production
> database use the dump file and load the target database.
> You would need to adjust the users to match the target
> systems sysloginstable. This could be accomplished by
> dropping all the users and aliases and readding them.
>
| |
| Mark A. Parsons 2005-04-27, 8:25 pm |
| A couple other options (which you may have already thought about?) ...
1 - dump and load the production database into a similarly sized 'staging'
database on the dataserver that services your web schtuff; then run a
'local' select/into from the 'staging' database into the 'web' database;
granted, you'll need to make sure your web app doesn't have access to the
'staging' database (may be a security issue here?) [basically a
combination of wkraatz's rec and the current method?]
2 - if you have access to RepServer you could have changes in production
automatically (in asynchronous mode) copied over to the 'web' database;
granted, if you don't have RepServer in house you'll have to address the
normal issues (licensing, disk space/cpu cycles, training, installation,
tuning, etc.) of getting RepServer up and running
3 - consider setting up a batch process that uses 'isql' and 'bcp' to move
records between databases? You can probably search around (web,
codexchange.sybase.com, etc.) looking for a bcp|pipe tool for data
migration that could handle the same thing. This really depends on where
the current degradation in peformance is located (ie, is it a PB issue? a
source or target database issue? network issue? ???).
Robin, Parsons wrote:
[color=darkred]
> The database we are populating is a subset of data from the
> Production database (it is data that the public may retrieve
> interactively on our web site). We need to make this a
> batch job that runs nightly. We currently have a
> PowerBuilder application that uses the timer event to fire
> pipelines to do the load from one server to the other, then
> we populate the backup on the same server with a stored
> procedure. We have recently moved the database from SQL
> Server to another Sybase database and are getting rid of SQL
> Server. Now the load, which used to run in 40 minutes takes
> 2 1/2 hours, from Sybase to Sybase. (Sorry, but it's true.)
> When we run the load with the stored procedure doing
> "select into" that populates the backup database, it runs
> very fast, about 12-14 minutes to load the same data.
> Ideally, we would like to handle the first load (from
> Production database) the same way, but have not figured out
> how to reference "select into" from one server to another as
> we were able to reference from one database to another on
> the same server.
>
> I'll try to learn about proxies and see if that suggestion
> will work.
>
> Thanks all,
> Robin
| |
| Bret Halford 2005-05-02, 9:23 am |
| If data is being taken from a number of tables, creating a proxy database
would be even easier.
-bret
"Mark A. Parsons" wrote:
[color=darkred]
> A quick-n-dirty answer:
>
> - setup a proxy table (see 'create existing table'/'create proxy_table') in
> a local database to reference a table in a remote database
>
> Then it's as 'easy' as:
>
> select <select_list>
> into <new_local_table>
> from <proxy_table>
>
> The proxy could be setup in any of your local databases.
>
> Obviously (?) you need to insure your servers are setup to support CIS
> connections (used by proxy tables).
>
> Robin, Parsons wrote:
>
|
|
|
|
|