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
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 .