DB2 Mainframe

ISOLATION LEVEL:- Degree to which db2 data which is being accessed with cobol-db2 program is isolated with another parallely executing cobol-db2 program.
Cursor stability (CS) - The cursor stability isolation level locks only the current row which the program is accessing. As soon as the program shifts to the next row, the lock in the previous row gets released. The cursor stability fetches only committed rows for the program to access. This is a default isolation level.
Read stability (RS) - This isolation level places a lock on all the rows which qualifies the SQL statement’s predicate (eg: WHERE clause). The lock is retained until the entire processing is completed.
Uncommitted read (UR) - The uncommitted read isolation level is used in the SQL statements meant for read-only purpose. There is no lock placed on a row/record and it fetches the committed as well as uncommitted rows (from the other programs/transactions).
Repeatable read (RR) - This isolation level is used when we need to retain the locks until a commit.

DB2 Error Codes:
92P or -927 :- THE LANGUAGE INTERFACE (LI) WAS CALLED WHEN THE CONNECTING ENVIRONMENT WAS NOT ESTABLISHED. THE PROGRAM SHOULD BE INVOKED UNDER THE DSN COMMAND.

80N or -805 :- Bind issue.

18} or -180 :- Incorrect data/time representation.

ACID Compliance in Databases

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that ensure reliable transactions in a database system. These properties are critical for databases like DB2, MySQL (InnoDB), PostgreSQL, and Oracle to maintain data integrity.

---

1. Atomicity ⚡

"All or Nothing" Execution
A transaction is either fully completed or fully rolled back if any part fails.

🔹 Example:

• You transfer ₹1000 from Account A to Account B.

• If the debit from A succeeds but credit to B fails, the entire transaction is rolled back.

🔹 COBOL-DB2 Example:

   EXEC SQL
       BEGIN TRANSACTION
   END-EXEC.

   EXEC SQL
       UPDATE ACCOUNTS SET BALANCE = BALANCE - 1000 WHERE ACCOUNT_NO = 'A123'
   END-EXEC.

   EXEC SQL
       UPDATE ACCOUNTS SET BALANCE = BALANCE + 1000 WHERE ACCOUNT_NO = 'B456'
   END-EXEC.

   IF SQLCODE = 0 THEN
       EXEC SQL COMMIT END-EXEC.
   ELSE
       EXEC SQL ROLLBACK END-EXEC.
   END-IF.

✔ Ensures either both updates happen or none.

---

2. Consistency ✅

Database remains in a valid state before and after transactions.
The database follows rules like constraints, foreign keys, and triggers.

🔹 Example:

• If a transaction breaks a foreign key constraint, it fails.

• A balance cannot be negative after a withdrawal.

✔ Prevents corrupt or invalid data from being stored.

---

3. Isolation 🔒

Transactions execute independently without interference.
Multiple transactions run in parallel without affecting each other.

🔹 Example:

• Two users withdrawing from the same account simultaneously should not cause an incorrect balance.

🔹 Isolation Levels in DB2 & MySQL:

• READ UNCOMMITTED – Dirty reads possible (fast but risky).

• READ COMMITTED – No dirty reads, but uncommitted changes aren't seen.

• REPEATABLE READ – Prevents non-repeatable reads.

• SERIALIZABLE – Fully isolated (safe but slow).

🔹 COBOL-DB2 Example (Setting Isolation Level):

   EXEC SQL
       SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
   END-EXEC.

✔ Prevents race conditions and dirty reads.

---

4. Durability 💾

Once a transaction is committed, it is permanently saved, even after a crash.

🔹 Example:

• If a bank confirms a ₹5000 deposit, it must be stored, even if the system crashes afterward.

🔹 COBOL-DB2 Example (Ensuring Durability):

   EXEC SQL COMMIT END-EXEC.

✔ Ensures no committed transaction is lost.

---

Summary Table:

---

Why is ACID Important?

✅ Prevents data corruption
✅ Ensures reliable transactions
✅ Makes databases safe for banking, finance, and critical apps

🚀 InnoDB in MySQL, DB2, Oracle, and PostgreSQL are ACID-compliant!