Transactions in database are defined as the group database operations which are combined under a logical unit. Transactions can be used to maintain consistency, integrity and control over a database. The data integrity is maintained despite the errors thrown by the system. Database transactions use several statements like SELECT, UPDATE, DELETE and INSERT. All modification made to the database will saved on permanent basis if no error is occured during the transaction process.

Single phase transactions which are directly handled by the database are called as local transactions. Co-ordinated transaction processes which use fail-safe mechanism by transaction resolution are called as distributed transactions.

• Check: All interview questions and answers

Transactions which are performed on the server are highly efficient. Transactions made in DB2 database can be controlled using MsDb2Connection. BeginTransaction object can be used to initiate objects in local transaction. Commands in local transaction can be enlisted using the transaction property of the command MsDb2Command. Commit or Rollback statements are used according to the requirement of the database user.

BEGIN, ROLLBACK and COMMIT are the three basic DB2 database command used for database transactions. All database transaction procedures are only attempted after the use of BEGIN statement. BEGIN statements are considered to be the part of database transactions which is usually completed by COMMIT or ROLLBACK statements.

• Read: What is meant by inner join and outer?

Steps to Perform a Transaction in DB2

• MsDb2Connection.BeginTransaction statement is used to mark the start of the transaction.
• The transaction which needs to executed is assigned after the statement MsDb2Connection.BeginTransaction. If transaction is started without the begin statement then MsDb2Exception will be thrown as a result.
• Execute the required transaction stateemnts.
• If the transaction is success then the command MsDb2Commit is used to save the transaction.
• MsDb2Rollback is used to cancel the failed transaction.

If the transaction is used without Commit or Rollback statements then the transaction is automatically rolledback by the database.

• Learn: Aptitude questions and answers

The code given below will demonstrate the transaction used in the DB2 database.

static void Transac1Connection()
{
MsDb2Conn my1Connection new MsDb2Conn(@”fileName=HOST.udl “);
my1Connection.Open1();
// Just start the domestic transaction.
MsDb2Transac my1Trans = my1Connection.Begin1Trans();
// This command enlists the current process of transaction.
MsDb2Comm my1Command = my1Connection.Create1Comm();
my1Comm.Trans= my1Trans;
try
{
my1Comm.Comm1Text = “Insert into the Region(RegID, RegDesc) VALUES (110, ‘Descrip’)”;
my1Comm.ExecuteNonQuery();
my1Comm.Comm1Text = “Insert into the region(RegID, RegDesc) VALUES (111, ‘Descrip’)”;
my1Comm.ExecuteNonQuery();
my1Trans.Commit();
Console.WriteLine(“Both of the records are added to the DB”);
}
catch(Exception e1)
{
try
{
my1Trans.Rollback();
}
catch(MsDb2Excep ex1)
{
if(my1Trans.Conn1!= null)
{
Console.WriteLine(“Exception Type= ” + ex.GetType() +
” It has encountered during the roll back attempt”);
}
}

Console.WriteLine(“Exception Type=” + e.GetType() +
“was encountered while inserting the data.”);
Console.WriteLine(“The neither record has added to the DB file”);
}
finally
{
Console.ReadLine();
my1Conn.Close();
}
}
// End Transaction1Connection

How indoubt transaction issue is fixed in DB2 database?

Indoubt transaction issues occur while using a two phase commit during a transaction. Indoubt transaction issue can be fixed by following the steps given below

• Connect to database using “db2 connect to”
• Indoubt transactions in DB2 database can be cross checked using the command “db2 list indoubt transactions”.
• Indoubt transactions listed are either committed or rolled back by using the statement “db2 list indoubt transactions with prompting”.

The following are the interactive command line used in indoubt transaction.

• c1 - Commits the first indoubt transaction.
• r3 - Roadblocks second indoubt transaction.
• f3 - Forgets third indoubt transaction.

The step3 is repeated until all the indoubt transactions are resolved.