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

Question

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

The clue is in the message ;) (CKPT REQ) .... Blocked:CKPT Your IDS server is bocked waiting for a checkpoint. Check your online log (onstat -m) and have a read at: http://www-01.ibm.com/support/docview.wss?uid=swg21148482 — jsagrera, Apr 23 '18 at 14:11
You seem to be stuck again with logical logs full but not backed up, so they cannot be reused. What is the output of `onstat -l` ? — Luís Marques, Apr 23 '18 at 14:25
I could not solve the problem so I simply ran oninit -i. I know that this will delete everything but at this stage it did not matter as just now I am starting to create the DB. Now I recreated both spaces and it works fine again. Also I corrected the script and now it does not hang the session. — Matias Barrios, Apr 23 '18 at 15:29
You know that you will get your Informix instance stuck again when you run out of usable logical logs? If it is only a test system, set `LTAPEDEV` to `/dev/null/` as written on the IBM article that J_S linked. Or run a ontape -a when it happens. Just never use /dev/null in a production system. — Luís Marques, Apr 23 '18 at 16:25
@LuísMarques Yes, I directed the flow to here : LTAPEDEV /matiasInformixDBSpaces/backuplog.log Would that be enough to avoid this in the future? I chowned that file to informix group and user — Matias Barrios, Apr 23 '18 at 16:32
@JonathanLeffler I have made the changes so now it looks like this : LTAPEDEV /matiasInformixDBSpaces/ Should I restart something? — Matias Barrios, Apr 23 '18 at 18:41
You could restart the server; that would ensure that the change is noticed. If you only edit the onconfig file rather than using `onmode` to make the change, the server won't notice the change in configuration. OTOH, if you use `onmode` to make the change, then the server is aware of it. — Jonathan Leffler, Apr 24 '18 at 02:35
when I try 'onmode' only, Im getting an error. What is the flag to reload the configuration? @JonathanLeffler And once again, thanks for all your help buddy — Matias Barrios, Apr 24 '18 at 16:12
You'd be running as `informix` and you'd use the `onmode -wf LTAPEDEV=/new/location` to make the change to the onconfig file. Run `onmode` with no arguments as `informix` to see the available options. (Saying "as `informix`" is a shorthand for what POSIX calls "appropriately privileged".) — Jonathan Leffler, Apr 24 '18 at 16:13

score 3 · Accepted Answer · answered Apr 23 '18 at 17:37

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 .

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

1 Answers1