Category: Database

A unique database that fits all the needs is required by every organization for which data management is of prime importance. A single application that performs all the tasks including handling transactions, operations, warehousing, reporting and analytics is a dream of all organizations.

But this is not possible in the real world as we need to use different applications for different tasks. When it comes to database space then Oracle and DB2 are the well known transaction leaders. Later it was known that these databases were unable to provide reporting. So, they came with such databases that actually performed this task.Further these databases were unable to put the required effort for the required performance and there arose a need to add summary tables, temporary data stores and other things. Some different attributes are to be created. These different attempts to transform the technology and make it suitable for the needs have given rise to various factors such as:

• Current databases are typed to hardware platforms- a very useful solution for hardware vendors.
• Memory Solutions- If we go on increasing the disk space then how can in memory help to solve the problems as it costs more but is unable to attend more volume.
• Reporting tools- Is another memory solution but is transferred to the application side server that has lead to restrictions on various sensitive points.

We can say different data is created at different times and there are times when most of the times the data is unequal or inconsistent. Further, different departments may also provide different data and managing these various data becomes a challenging task for organizations at times. Data is transferred through a complete cycle and it possesses a great value to the enterprise and selecting the best tool that maintains its value is of prime importance.So, now we can say that horizontal or row based databases can be used as transaction stores for operations, unorganized or half organized data which needs search style access, vertical stores for data analysis or a separate hard disk or device with more space that is used to drive business intelligence. And some of the highly abridged data storage devices are using for storing big data logs, front end that offers the reporting capabilities.

So, here we can see different data types need different databases. Some may be compressed, some may be row based, column based etc as per the need. This aptly states that every organization should have perfect tool for the perfect database to maintain the data perfectly and as per the need. Hence, what are you waiting for? Reading this article, you might be sure which database is required for your business and which one suits your need the best. Then go ahead and select the one available from the market so that you can easily organize the huge databases as well as convert unstructured data into the structured ones thus making it easier for you to get it as and when required.

Database is known as data stored in computer systems that can be accessed and altered. But, the process to keep the information handy when needed is unknown. Without organizing the information, it would be difficult to retrieve it again. Here are some of the utilities of dbms. 

Database Management Systems:

DataBase Management Systems (DBMS) is a method that provides various ways to manage the data so that we can easily retrieve it when needed. This entire process of data storage, organization and retrieval consists of:

1. Database Language: With this database language, it becomes easy to decode the database management structure whether it is relational, object based or hierarchical depending on the needs. The Data related to web application is mostly stored in relational Database management system.

2. Database transformer: This is used to manage the entire information in terms of fields, records, objects or files as per the need. The data is easily optimized and thus we can get a balanced well organized that can be restored quickly when required. This also helps to manage the space so that more information can be stored.

3. Query Language: This is used to write programmes that can obtain the data from the database and reveal it to the user. Changes can be done and saved for later use. One such database language is SQL.

4. Performance Technique: Many times similar data is entered by different users which may overwrite the existing one leading to some issues. In order to avoid such errors, database management system has a unique performance technique that validates the data before it is saved or entered in the database. If similar type of data is entered then the system doesn’t allow adding it thus reducing errors in any database file.

Some of the utilities of Database management system:

Database management system allows to a developer to create an index and add the necessary information that can be easily altered without worrying about its storage and recovery. Some of the important characteristics of this system are mentioned below:

1. Multiple User Access & Security Management: Many users can access the database at same time without affecting the most sensitive data. A user can only access the data he/she needs; no additional data can be visible as it provides separate credentials to every user.

2. Backup: This enables to store the data so that it can be available when needed in case the database is to be updated.

3. Logs Access: Using the logs, a database administrator can easily see how the database is being used.

4. Strict Rules: Check that certain fields contain the specified data as mentioned. For instance, a field is set to accept only dates. So, these rules ensure whether that particular field has data related to dates and no other information is stored.

5. Statistical data: DBMS has various formulas that make the calculation very easy and quick. This makes quick representation of the data.

Above we explained some of the utilities of dbms to inform you all about its effectiveness. A database management system should be used, so that all the necessary sensitive information is maintained easily. Many IT firms provide database management solutions that can be beneficial to your business. You can get in touch with us to discuss your needs and get the best system suitable for your business.

Introduction to Data Archiving:

Keeping your records archived is very important for business owner of all type of company. Archiving is the process of transferring data they no longer actively to separate data storage location that can be located and accessed when necessary. Data archiving is one of the best solution for the problems that are related to ever-increasing volumes of data. The mean of archiving is not creating backups for inactive data, because achieve retrievals can be selective, while backups are not. On the other hand, backups are oriented to the data store, and archives are application-oriented, so it can be a part of a backup and recovery scheme.

By archiving old data, you can manage uncontrollable data growth and keep your databases smaller and more manageable. It will help you to identify which files are no longer active in your organizations. With smaller tables, you will control index rebuilds times and backup/restore times. You will see performance gains on particular queries that either scan a huge part of the table or clustered index.

As per your business need, one should select either delete the unwanted data, or save the data to a history table and delete that from production database.

Implementing archival process is not tough task…you just need to follow three easy steps that are entailed in this process:

• Identify data that you want to archive
• Copying identify data in to history table
• Clear your production database by deleting the identified data

One don’t need to archive single table every day, but they need to understand different tables in the database are connected to each other through foreign key relationships and then sequence your delete/archive process.

Furthermore, Data backups are used to restore data in case it is corrupted or destroyed, on the other hand data archives protect in active older information that occasionally need to be accessed. These topics give the details about the concept of archiving, identify high-level terminology connected with archiving, as well as define various other concepts associated with setting up and implementing a database data archiving strategy.

The Need for Archiving:

Not one or two there are several factors that require data archiving. First, the volume of data in database always increases. Second, most of this data is not active but must be maintained for business reasons and third, it is also important to preserve all inactive data as inexpensively and effectively as possible.

Databases are increasing at an unexpected rate that’s why data centers are more concerned about the volumes of data. Some inactive data exist in all over due to past dealings, the use of data warehouses, and several other reasons. Inactive data is not more useable but it’s less likely to be accessed.

Several reasons why you should maintain inactive data like:

• You are emulating government necessities
• You expect the requirement for creating a future trend analysis
• You need to keep up an entire history of your clients

When the amounts of database increase, the percentage of inactive data automatically increase, this can create issues like:

• Performance problems from extra input/output  (I/O) and widespread processing that effects clients
• Manageability issues because huge objects become difficult identify and size limit are reached.
• Extensive hardware and storage overheads

Benefits of Archiving

• There are several benefits associated with archiving – reclaim disk space, reduce storage expense, lower data maintenance and enhance the performance of active data.
• Interrelated is one of the main benefits of archiving. By recovering disk space that was used by inactive data, you can effectively cut down your storage expenses as well as reduce object maintenance.
• And another benefit of archiving is that the search query result performance will increase automatically after by removing the inactive data from the database.
• Lowering costs:
  • Lowering expenses is the most significant benefits of archiving your inactive data. You can store DB2 table archives on less expensive storage media than that holding your active data.
  • If your business active data is stored in high-performance, expensive data storage medium, then this is the perfect solution to establish less expensive storage medium on which to place your archive tables and files. Magnetic tape is best example of this type of medium.
  • Moreover, it improve performance of applications, use less CPU, Input/output, and memory resources, which helps to saving extra cost.
• Less object maintenance:
  • Decreasing object maintenance is the key feature of inactive data archiving.
  • Archiving DB2 data decrease the overall number of DB2 objects that require maintenance. For instance, when less data exists, utility processing requires less time, cutting down batch processing times.

Case Study for Data Archiving in Symphony – A Hotel Management System:

Now starting from the data archiving operation, we provide data archive facility to only Admin and it may be possible by including only in server installation setup. Not at the client end.

Now Admin can initiate an operation of archiving the old data of reservation. As per the process flow he just set the filtering criteria for which data we have to cover in archiving operation. Means set the date. Data may be older than 2-3 years.

After Defining the Filter Criteria, our actual Archive process is start. This is complex process, so I try to divide whole process in 3 sub process.

Set the Basic Information Related to Data Archive

In that we will take the information related to the following details:

• What the Filtered Criteria?
• When we take the Data Archive?
• At the time of Archiving what will be the size of archived data?
• Where you store this archived data? (Physical Location of Archived Data File)
• Information Related To Media Serial Number.
• Who perform this archive operation on which date?

We will store this whole information in one table in the Database. And from that database we will retrieve back one archive ID which is unique for every archive operation.

Generate the result set of the affected Data in Archive Operation

After successful completion of Process 1, we will go for process

In this process we will get the list of filtered reservations, through that we will get the list of dependent data from different tables.

After getting all this data we build up the DataSet (Collection of DataTable) and write that whole data in particular binary file. So your data is very secure and user can’t do any update operation on that, because binary format is not readable like an XML document. One another advantage is that it is very less in size. So it is very cost effective with respect to XML data file.

After getting all this data we build up the DataSet (Collection of DataTable) and write that whole data in particular binary file. So your data is very secure and user can’t do any update operation on that, because binary format is not readable like an XML document. One another advantage is that it is very less in size. So it is very cost effective with respect to XML data file.

Binary file Generation Coding is defined below.

public void DeserializeDataSet(DataSet ds)
{
ds.RemotingFormat = SerializationFormat.Binary;
FileStream fs = new FileStream(“MyData.bin”, FileMode.OpenOrCreate);
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(fs, ds);
fs.Close();
}

Here in above code we pass the DataSet in one function through that dataset we generate our data file with name of “MyData.bin”. bin is extension for binary file. This file is very less than the XML file. It is I think 5 times less than the size of XML datafile with same data.

After creating that file we will write that file in Magnetic media or any media.

Update our DB and do actual delete old or in active data from the Database.

After successful completion of process 2, we will go for process 3.

In this process, we will use the result set of already get dependent data from the Database. It is sub process of Process No 2.

From that first of all Update the all filtered reservation records, make IsArchive field = true and put the ArchiveID = new archive Id which is coming from process 1 execution. After this successful update operation on database we will delete all the dependent records from the child tables.

For successful completion of whole DataArchive operation, the Process 1, 2, and 3 are the main or major requirements of the system. If any process or sub process may be failure, the whole DataArchive operation fails and you have to start from the Process-1.

Database Level Implementation


CREATE PROC dbo.ArchiveData
(
@CutOffDate datetime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @CutOffDate IS NULL
BEGIN
SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
END
ELSE
BEGIN
IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
BEGIN
RAISERROR (‘Cannot delete transaction from last three months’, 16, 1)
RETURN -1
END
END
BEGIN TRAN
INSERT INTO Archive.dbo.tra_Transaction_Master
SELECT * FROM dbo.tra_Transaction_Master
WHERE Tran_Date_Time < @CutOffDate
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while copying data to Archive.dbo.tra_Transaction_Master’, 16, 1)
RETURN -1
END
INSERT INTO Archive.dbo.tra_Folio_Transactions
SELECT * FROM dbo.tra_Folio_Transactions
WHERE Transaction_ID IN
(
SELECT Transaction_ID FROM dbo.tra_Transaction_Master
WHERE Tran_Date_Time < @CutOffDate
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while copying data to Archive.dbo.tra_Folio_Transactions’, 16, 1)
RETURN -1
END
DELETE dbo.tra_Folio_Transactions
WHERE Transaction_ID IN
(
SELECT Transaction_ID FROM dbo.tra_Transaction_Master
WHERE Tran_Date_Time < @CutOffDate
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while deleting data from dbo.tra_Folio_Transactions’, 16, 1)
RETURN -1
END
DELETE dbo.tra_Transaction_Master
WHERE Tran_Date_Time < @CutOffDate
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR (‘Error occured while deleting data from dbo.tra_Transaction_Master’, 16, 1)
RETURN -1
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END