how to clean database 1

[babeo]

Hi

Fisrst question: I have a question about database growwing. Orginal we always have small databases, but then they grow. I supposed each year they grow about 20GB and it means we lost 20G, and eventually we will run out of space for database, as the result, I need to clean up the old data in the database.

Example, I have a database named MyDB, and there are 5 tables A (fields a,b,c), B(d,e,f), C(g,h,l), D(k,n,m) in that databases, this database keeps data from Jan, 2001 - now (april 2002), now I need to clean up the old database from jan, 2001 - May 2001. How can I do it?

Second question: What is "bcp" is doing for, can I use for backup and restore database? what is the syntax. Thanks.

Last question: What is the best book of Sybase to read for a very biginner to intermediate person ?

Thanks.

 

[qyllr]

hello,

first question:

i am assuming these 5 tables are related to each other with at least one table containing a date column. it would be ideal if all the tables contain a date column.

there are several options you can do but this is how i would approach it to keep it simple:

1. dump the database to your backup device.

2. write a delete SQL with the date range you want specified in your where clause. this is the 'tricky' part. if you have date columns on all the tables, you can write a delete SQL for each table. if not, you would need to join the related tables to the one containing the date column and delete the data from that table last...

3. if you have tables that are not related to any, i am guessing that they contain static data. most likely you would need to retain them...

the other option is to bcp out the data and use a shell script or perl to tweak the data and then bcp in... this requires development... may take some time...

other DBAs in this forum may have other ideas...

second question:

bcp - bulk copy program - this a tool used to transfer data between a table and an OS file...

you can find the syntax and explanation by going to the following url:

http://manuals.sybase.com/onlineboo...@Generic__BookView/1004;cs=default;ts=default

last question:

the following are excellent books:

Sybase SQLServer 11 Unleashed by SAMS
Transact-SQL by O'Reilly

For the latest in ASE you can use Sybase's on-line books:

http://sybooks.sybase.com/as.html

hth,
q.

 

[babeo]

hi qyllr

Thank you very much, pretty much help for me, however could you answer more detail a bit for the following one please:
Also to let you know that each table have a date/time column

- I can dump the whole database into the tape device, but what I would like to dump individual table onto the LOCAL harddrive server, then "tar" to tape from that directory. How can I do that, need a formal command, or do I have to use the same "dump" command to dump individual table?

Thanks

 

[qyllr]

hello,

the dump command will backup the database specified...

to backup individual tables you would need to run bcp out for each table then tar it up.

here is the command:

bcp out ---

$SYB_BIN/bcp databaseName..tableName out $PATH/tableName.dat -UuserName -Ppassword -SserverName -c -t "|" -r "\n"

where
$SYB_BIN = Sybase bin directory
$PATH = Path where you want to put your files


hth,
q.

 

[babeo]

Thanks again

 

[Guest_imported]

I have same database in two different server. DATABSE A in servers A and datbase A in server B. Now how do i keep them in sysnc in real time. In another word, if user update database A in server A, i need them to update in realtime in database A in server B.

RIght now I do dump A daatabse in Server A and load it into server B but it needs user in server B to logout and takes lot of time.

any suggestions

 

[Guest_imported]

I have same database in two different server. DATABSE A in servers A and datbase A in server B. Now how do i keep them in sysnc in real time. In another word, if user update database A in server A, i need them to update in realtime in database A in server B.

RIght now I do dump A daatabse in Server A and load it into server B but it needs user in server B to logout and takes lot of time.

any suggestions

 

[qyllr]

hello,

i would try replication...

q.

 

[sunpatil]

how can we do the replication can u give the procedure please.

 

[qyllr]

hello,

please install and read the technical library provided by the replication server software package...
it includes topics in design, installation, configuration and administration...

this will help you a lot...

good luck,
q.