Tag Archives: informix

What are various Informix operating modes?

The IBM Informix database server has 4 principal modes of operation:

  1. Online
  2. Single User/Administrative
  3. Quiescent/Maintenance
  4. Offline

OFFLINE  <==>  Quiescent  <==>  Single User <==> ONLINE

1. Online: In this mode, users can connect with the database server and perform all database activities. This is normal operating mode of the database server

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — On-Line — Up 36 days 04:41:25 — 164468 Kbytes

2. Offline: When the database server is not running. No shared memory is allocated

[informix@test1 ~]$ onstat –
shared memory not initialized for INFORMIXSERVER ‘ids13’

3. Single User/Administrative: This is the single user mode. This mode allows Informix user to perform all functions, including the issuance of SQL and DDL commands.

The -j -U option enables DBSA to designate specific users (in addition to the informix user) to access the database server.

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Single-User — Up 00:00:24 — 148084 Kbytes

4. Quiescent/Maintenance: Only administrators can access the database server to perform maintenance functions that do not involve the execution of SQL and DDL statements

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Quiescent — Up 00:01:37 — 148084 Kbytes

Advertisements

Informix Dynamic Server (IDS) Logging

IDS knows below lock types:

  • Shared lock
  • Update lock
  • Exclusive lock
  • Intent lock

Locking granularity in IDS:

Granularity: It is the extent to which a system is broken down into small parts, either the system itself or its description or observation. It is the “extent to which a larger entity is subdivided. For example, a yard broken into inches has finer granularity than a yard broken into feet”

IDS allows to place locks on below objects:

  • Database locks
  • Table Locks
  • Page locks
  • Row locks
  • Index key locks

 ***

Share Lock:

Share locks could be placed on rows that don’t have an exclusive lock on it. Other users might place additional shared locks or update locks on the same row, but no other exclusive locks are allowed.

Update Lock:

An update lock is a special kind of lock generated by a cursor that has been declared with the for update clause. Update locks could only be placed on a row that currently has no update or exclusive lock on it.

Once an update lock is placed on a row, it is promoted to an exclusive lock as soon as the row is updated.

Exclusive Lock:

An exclusive lock can only be placed on rows that do not have any other kind of lock on it. Once an exclusive lock is placed on a row, no other locks can be placed on the same row. It is exclusively reserved for this database session.

Example: Open two sessions with userid: informix

From session1: open a database, ex:phase1, in exclusive mode as below

Database phase1 exclusive;

From session2: Try to access the phase1 database as “dbaccess phase1”, then you will get an error.

Remember that, even though you are Informix user in both session, you are still not able to access from another session.

Intent Lock:

Intent locks are special kinds of locks. If, for example, a row is update, an exclusive lock is placed on the row and intent exclusive lock is placed on the table. The intent exclusive table lock ensures that no other session could place a share or exclusive lock on the table as long as individual rows in the table have been exclusively locked.

Database Locks:

Databases could be locked explicitly in exclusive or shared mode.

Database share lock:

A share lock is automatically placed on the database as soon as you open the database. This ensures that no other session could place an exclusive lock on the database or drop the database.

Example: Type command “dbaccess phase1” from session1.

From session2, type “onstat –k”. This will display the following output:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+S    100002   205         0

HDR+S: Share lock

Rowid: is the hexadecimal row ID of the row describing this database in table sysdatabases in the sysmaster database.

Databases and their hexadecimal row IDs could be selected with the following SQL query:

Database sysmaster;

Select name, hex(rowid) FROM sysdatabases;

Database exclusive lock:

An exclusive lock can be explicitly set on a database. Utilities like dbexport places exclusive lock on the database to be exported.

Example: database phase1 exclusive

Now, output from onstat –k:

address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7210c 0        HDR+X    100002   205         0

Here, you can see an exclusive lock (HDR + X) on the database with the hexadecimal rowid=205

Table Locks:

Like databases, tables could either be locked in exclusive or share mode.

Locking a table exclusive mode prevents anybody else from reading or changing data in the locked table. An exception to this are sessions running with an isolation level of dirty read (i.e, read uncommited). These sessions are still able to read (possible inconsistent) data from the exclusively locked table.

Locking a table in share mode allows others to select data from this table but prevents data modifications.

To place a shared lock, you need to be in transaction as below:

begin; lock table orders in share mode

Output from onstat –k:

——————————

address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+S    10010d   0           0

You see a share lock (HDR+S) and a row ID of zero (rowid=0). A zero row ID represents a table lock. The tablespace number (tblsnum=10010d) conforms to the hexadecimal partition number of this table.

The affected table could be identified in two ways:

  • Oncheck command

Oncheck –pt 0x0010010d | more

  • Data dictionary query command

select tabname, dbinfo(“dbspace”, partnum), hex(partnum) from systables where hex(partnum) = “0x0010010D”

Notice that you need to put a 0x00 in front of the hexadecimal number gathered from the onstat –k output, and you need to convert the letters to uppercase (10010d à 0x0010010D) in order to find the table through the dictionary query.

If it is a fragmented table you have to query, the sysfragments dictionary table:

select st.tabname, dbinfo(“dbspace”, sf.partn, hex(sf.partn) from systables st, sysfragments sf where st.tabid = sf.tabid and sf.fragtype = “T” and hex(partnum) = “0x0010010D”

Table exclusive lock:

An exclusive lock can be explicitly set on a table or might be implicitly set by statements like alter table.

Example: begin; lock table orders in exclusive mode

Output from onstat –k:

——————————

address  wtlist   owner    lklist            type     tblsnum  rowid    key#/bsiz

8e73d978 0        92c7b46c 8e741078 HDR+X    10010d   0           0

Page or row locks:

The lock granularity (page or row) could be specified during the creation of a table  or later can be changedwith the alter table statement

Example1: create table t1 (c1 int) lock mode (row)

Example2: alter table t1 modify lock mode (page)

You can determine lock mode of a table using,

  • Oncheck utility

Oncheck –pt  database_name:owner_name.table_name

  • Dictionary query

SELECT tabname, locklevel FROM systables WHERE tabname=”table_name”

The following abbreviations for the locklevel are shown by IDS:

  • B – Views (Check the locklevel of underlying table)
  • P – Page locking
  • R – Row Locking

Page Locks:

Page locks are placed on a database page during the processing of a row if the table has been configured for page level locking.

Page locks are represented with a double zero (00) at the end of a row ID.

Row Lock:

Row locks are placed on a database page during the processing of a row if the table has been configured for row-level locking.

Identifying a locked row:

——————————–

Database phase1;

Set isolation to dirty read;

SELECT * FROM customer WHERE hex(rowid)=”0x0000001A”;

Index Key Locks

IDS also places locks on the index keys like data rows in order to protect those index keys. Key value locking guarantees that unique keys remain unique because it doesn’t allow a second transaction to insert a key with the same value until the transaction that deleted the unique key is committed.

Index key locks are identified by a K in the key#/bsiz column of the onstat –k output.

IBM Informix pops up big in China!

IBM Informix becomes THE RDBMS for the government of the Popular Republic of China! See the details of this game changer IBM deal in IIUG website:

http://www.iiug.org/Insider/insider_oct14.php#H%2E1

 

Thanks,
Ravi