How to archive old data in Sybase database?

Question

I am working on an application which is built on Struts as server side java technology and Sybase ASE 15 is used as database for storing the data for the application. Now the tables in Sybase have been filled with huge chunk of data, so higher management wants data older by few years (say 3 years) to be archived and deleted from database tables, but that archived data should be able to be restored whenever required.

We should also be able to identify the dependency between tables automatically and then decide the order of tables in which data has to be archived.

We also need to store the table format along with data, so data retrieval process is easy.

I am a JAVA guy and don't know anything about archiving data from database. Also I have worked mostly with Oracle and very new to Sybase.

Please advise me are there any tools/procedures available to archive data from Sybase database. Any help/guidance/pointers on this issue will be very helpful.

I read about achieving this through partitioning of data in database. The current database is already partitioned in round-robin partition method. Is it possible to achieve database partition based on timestamp on already partitioned database. Also all the tables does not contain timestamp columns.

I also read that if partitioning is not possible, data archival can also be achieved through creation of views. I wanted to know whether this option is possible and if yes, how can it be achieved?

Answer 1

an easy an cheap way of doing this is to create a second database on the same server called archive. the database should have the "select into" option enabled. Now, you generate a "select into" query that enters the data from your normal database into the archive db. if that happened you can run a delete query with the same conditions as the "select into" command to get rid of the data in your primary database. if your table does not change over the time you could always use the same table in the archive database. if it does change you probably want to create the table every time again and name it something like tablename-year-month.

Answer 2

if you are asked to have the data in flat files use the bcp utility to extract the data. I usually use this extract utility with the following parameters:

bcp <database>..<tablename> out /path/<tablename>.out -U<username> -P<password> -S<servername> -c 

See some detailed documentation about all paremeters and options in the SybBooks.

Once you have all these flat files containing your data you can truncate all tables and start again with a clean and empty database until you need to archive again. if you don't need to have a whole table extracted to a file i recommend doing the following:

• create a temporary table that has the same schema
• select into that table the data that you want to have archived.
• delete the data with the same where clause on the original table
• use the above described bcp out command to get the data extracted to a flat file