CODEWITHSUNDEEP
informixlocked
Matias Barrios
Matias Barrios

Reputation: 5056

Informix - Locked DB due to lock created by cancelled session?

SI attempted to run a script to generate a table in my Informix database, but the script was missing a newline at EOF, so I think Informix had problems to read it and hence the script got blocked doing nothing. I had to kill the script and add the new line to the file so now the script works fine, except it does not create the table due to a lockecreated when I killed the script abruptly.

I am new to this, so sorry for the dumb question. IBM page does not have a clear and simple explanation of how to clean this now.

So, my question is: How do I unlock the locks so I can continue working in my script?

admin_proyecto@li1106-217 # onstat -k

IBM Informix Dynamic Server Version 12.10.FC9DE -- On-Line (CKPT REQ) -- Up 9 ds
Blocked:CKPT

Locks
address          wtlist           owner            lklist           type     tbz
44199028         0                44ca6830           0                HDR+S
44199138         0                44cac0a0           0                HDR+S
441991c0         0                44cac0a0           4419b6f0         HDR+IX
44199358         0                44ca44d0           0                    S
441993e0         0                44ca44d0           44199358         HDR+S
4419ac50         0                44cac0a0           441991c0         HDR+X
4419aef8         0                44ca44d0           441993e0         HDR+IX
4419b2b0         0                44ca79e0           0                    S
4419b3c0         0                44ca82b8           0                    S
4419b6f0         0                44cac0a0           44199138         HDR+X
4419b998         0                44ca8b90           0                    S
4419bdd8         0                44ca44d0           4419aef8         HDR+X
 12 active, 20000 total, 16384 hash buckets, 0 lock table overflows

Upvotes: 1

Views: 2279

Answers (1)

Luís Marques
Luís Marques

Reputation: 1451

On my "toy" systems i usually point LTAPEDEV to a directory:

LTAPEDEV /usr/informix/dumps/motor_003/backups

Then, when Informix blocks due to having all of it's logical logs full, i manually do an ontape -a to backup to files the used logical logs and free them to be reused.

For example, here I have an Informix instance blocked due to no more logical logs available:

$ onstat -l

IBM Informix Dynamic Server Version 12.10.FC8DE -- On-Line (CKPT REQ) -- Up 00:18:58 -- 213588 Kbytes
Blocked:CKPT

Physical Logging
Buffer bufused  bufsize  numpages   numwrits   pages/io
  P-1  0        64       1043       21         49.67
      phybegin         physize    phypos     phyused    %used
      2:53             51147      28085      240        0.47

Logical Logging
Buffer bufused  bufsize  numrecs    numpages   numwrits   recs/pages pages/io
  L-1  13       64       191473     12472      6933       15.4       1.8
        Subsystem    numrecs    Log Space used
        OLDRSAM      191470     15247376
        HA           3          132

Buffer Waiting
Buffer  ioproc   flags
  L-1   0        0x21     0

address          number   flags    uniqid   begin                size     used    %used
44d75f88         1        U------  47       3:15053              5000        5     0.10
44b6df68         2        U---C-L  48       3:20053              5000     4986    99.72
44c28f38         3        U------  41       3:25053              5000     5000   100.00
44c28fa0         4        U------  42       3:53                 5000     2843    56.86
44d59850         5        U------  43       3:5053               5000        5     0.10
44d598b8         6        U------  44       3:10053              5000        5     0.10
44d59920         7        U------  45       3:30053              5000        5     0.10
44d59988         8        U------  46       3:35053              5000        5     0.10
 8 active, 8 total

On the online log I have:

$ onstat -m

04/23/18 18:20:42  Logical Log Files are Full -- Backup is Needed

So I manually issue the command:

$ ontape -a

Performing automatic backup of logical logs.

File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000041
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000042
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000043
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000044
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000045
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000046
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000047
File created: /usr/informix/dumps/motor_003/backups/informix003.ifx.marqueslocal_3_Log0000000048
Do you want to back up the current logical log? (y/n) n

Program over.

If I check again the status of the logical logs:

$ onstat -l

IBM Informix Dynamic Server Version 12.10.FC8DE -- On-Line -- Up 00:23:42 -- 213588 Kbytes

Physical Logging
Buffer bufused  bufsize  numpages   numwrits   pages/io
  P-2  33       64       1090       24         45.42
      phybegin         physize    phypos     phyused    %used
      2:53             51147      28091      36         0.07

Logical Logging
Buffer bufused  bufsize  numrecs    numpages   numwrits   recs/pages pages/io
  L-1  0        64       291335     15878      7023       18.3       2.3
        Subsystem    numrecs    Log Space used
        OLDRSAM      291331     22046456
        HA           4          176

address          number   flags    uniqid   begin                size     used    %used
44d75f88         1        U-B----  47       3:15053              5000        5     0.10
44b6df68         2        U-B----  48       3:20053              5000     5000   100.00
44c28f38         3        U---C-L  49       3:25053              5000     3392    67.84
44c28fa0         4        U-B----  42       3:53                 5000     2843    56.86
44d59850         5        U-B----  43       3:5053               5000        5     0.10
44d598b8         6        U-B----  44       3:10053              5000        5     0.10
44d59920         7        U-B----  45       3:30053              5000        5     0.10
44d59988         8        U-B----  46       3:35053              5000        5     0.10
 8 active, 8 total

The logical logs are now marked as "Backed Up" and can be reused and the Informix instance is no longer blocked on Blocked:CKPT .

Upvotes: 3

Related Questions