# Day 22: Informix(3)
## Concept
- StackOverflow-**Dirty Read**
[Transaction isolation levels relation with locks on table](https://stackoverflow.com/questions/16162357/transaction-isolation-levels-relation-with-locks-on-table?rq=1)

- [RDBMS - 錯誤讀取**現象**及交易隔離層級原理介紹與舉例](https://blog.kennycoder.io/2020/02/09/RDBMS-%E9%8C%AF%E8%AA%A4%E8%AE%80%E5%8F%96%E7%8F%BE%E8%B1%A1%E5%8F%8A%E4%BA%A4%E6%98%93%E9%9A%94%E9%9B%A2%E5%B1%A4%E7%B4%9A%E5%8E%9F%E7%90%86%E4%BB%8B%E7%B4%B9%E8%88%87%E8%88%89%E4%BE%8B/)
> 當有兩個交易 (TX) 進行的時候，其中一個 TX 可以讀取另一個還未 committ 的 TX 在這段時期做的資料改動。

- Use case
[IBM-Using the Dirty Read Isolation Level](https://www.ibm.com/docs/en/informix-servers/12.10?topic=levels-using-dirty-read-isolation-level)
> When you use High Availability Data Replication,
the database server effectively uses Dirty Read isolation on the HDR Secondary Server,
regardless of the specified SET ISOLATION or SET TRANSACTION isolation level,
unless the UPDATABLE_SECONDARY configuration parameter is enabled.
For more information about this topic,
see Isolation Levels for Secondary Data Replication Servers.

- **Performance** Guide
[Dirty Read isolation](https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_425.htm)
- **Summary**
- Dirty Read 現象:

Source: [Dirty Read isolation](https://informix.hcldoc.com/14.10/help/index.jsp?topic=%2Fcom.ibm.perf.doc%2Fids_prf_425.htm)
- 既然會造成問題，那為什還要使用?
- 如果每次讀last commit，會造成負擔(效能不佳)
- 建表: create 不需所有資料即可進行
## 資料庫異動
新增/刪除(Create/Drop)
Note 刪除 年月

Example:
- 新增:
- 使用 **dbschema** 查看之前建的Table定義

        dbschema -d [dbname] -t [tablename]

- [Syntax of the dbschema command](https://www.ibm.com/docs/en/informix-servers/12.10?topic=utility-syntax-dbschema-command)
- Java - SQLgen

        java SQLgen yyyyMM

PS: generate SQL 的程式是由謝師傅所開發，超讚!

- 刪除:
- Notice
- Drop 的 Table 通常不是最近的月份(避免誤刪!)
- SQL

        database dbName@serverName;
        -- database [db name]@[instance];
        drop table tableName;

##### 師承謝門