2

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?

azaveri7
  • 793
  • 3
  • 18
  • 48
  • First thing to realize is that Sybase was a company that produced multiple database products, so it would be helpful to know what database product (ASE, IQ, SQLAnywhere, etc) you are using, and what version. Without knowing any details, my instinct would be to look at partitioning the data by date. You can then archive partitions and restore them when necessary. – Mike Gardner Aug 26 '14 at 14:20
  • I dont think so,we can do archiving at database level.It can only done at table level when you have date column defined in a table. – SSE Aug 26 '14 at 19:28
  • The Sybase produce used is Sybase ASE 15. – azaveri7 Aug 27 '14 at 06:47
  • Please advise me on how to achieve archiving of data on Sybase ASE 15.0 – azaveri7 Aug 27 '14 at 06:54

2 Answers2

0

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.

dom
  • 652
  • 1
  • 16
  • 35
  • Hi Dom, Thanks for the reply. I am asked to store the archived data into flat files and store these files on SFTP server, so database size can be reduced. Please let me know if you have any idea on this. – azaveri7 Sep 09 '14 at 05:08
  • @azaveri7 thats another possible way of doing it. just make sure you have the table schema as it was at the time you did the export. once the table changes over the years you may are no longer able to load the data directly into the original table and you will have to create a new temporary table to load the data first. – dom Sep 10 '14 at 15:57
0

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
dom
  • 652
  • 1
  • 16
  • 35