Informix

 View Only
Expand all | Collapse all

how to get the tablename from the onlog output

  • 1.  how to get the tablename from the onlog output

    Posted Tue March 03, 2020 09:59 PM
    Hello All,

    How do I get the actual tablename from the "onlog -l -n <logical log number>" output ?

    The INFORMIXSERVER went into a long transaction state and was hung. When we restarted it, again it went into a long transaction state and had to be restarted. We have found out that there is a lot of "ADDITEM" mentioned in the output of all the logical log records and the transaction id is 248. "ADDITEM" adds index entries and therefore we suspect that someone had made a heavy insert into a particular table due to which the index records were also generated. Therefore we wanted to get the tablename from the "onlog output. Following is the first few page output of "onlog -l -n 424".

    > cat /tmp/424.log | more

    IBM Informix Dynamic Server  Logical Log display
    Copyright IBM Corporation 1996, 2004 All rights reserved.
    (c) Copyright HCL Technologies Ltd.  2018. All rights reserved.

    log uniqid: 424.

    addr     len  type     xid      id link   
    18       72   ADDITEM  248      1  31ff75c  401f6e   401f6d   72e11e   29772 1     12  
             48000000 01001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 5cf71f03 ........ ....\...
             d24dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             1ee17200 4c740000 01000c00 56454e20 ..r.Lt.. ....VEN 
             800012fa 8000a9f8                   ........
    60       64   ADDITEM  248      0  18       50003d   401f6d   72e11e   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 18000000 ........ ........
             d34dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             1ee17200 18120000 01000400 8000a9f8 ..r..... ........
    a0       68   ADDITEM  248      0  60       50003e   401f6d   72e11e   24570 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 60000000 ........ ....`...
             d44dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             1ee17200 fa5f0000 01000800 800012fa ..r.._.. ........
             8000a9f8                            ....
    e4       72   ADDITEM  248      0  a0       50003f   401f6d   72e11e   28372 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 a0000000 ........ ........
             d54dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             1ee17200 d46e0000 01000a00 800012fa ..r..n.. ........
             8000a9f8 5020a9f8                   ....P ..

    addr     len  type     xid      id link   
    12c      64   ADDITEM  248      0  e4       500040   401f6d   72e11e   2163  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 e4000000 ........ ........
             d64dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             1ee17200 73080000 01000400 800012fa ..r.s... ........
    16c      64   ADDITEM  248      0  12c      500041   401f6d   72e11e   4532  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 2c010000 ........ ....,...
             d74dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             1ee17200 b4110000 01000200 502012fa ..r..... ....P ..
    1ac      96   HINSERT  248      0  16c      401f6d   72e11f   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 6c010000 ........ ....l...
             d94dc32c 6d1f4000 6d1f4000 1fe17200 .M.,m.@. m.@...r.
             1d000004 00000000 00000000 502012fa ........ ....P ..
             534c2020 534c2020 000012fe 0000a9f8 SL  SL   ........
             50200000 0000c714 13021509 1f00aac9 P ...... ........
    20c      72   ADDITEM  248      0  1ac      401f6e   401f6d   72e11f   31393 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 ac010000 ........ ........
             d94dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             1fe17200 a17a0000 01000c00 534c2020 ..r..z.. ....SL  
             800012fe 8000a9f8                   ........

    addr     len  type     xid      id link   
    254      64   ADDITEM  248      0  20c      50003d   401f6d   72e11f   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 0c020000 ........ ........
             da4dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             1fe17200 18120000 01000400 8000a9f8 ..r..... ........
    294      68   ADDITEM  248      0  254      50003e   401f6d   72e11f   25749 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 54020000 ........ ....T...
             db4dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             1fe17200 95640000 01000800 800012fe ..r..d.. ........
             8000a9f8                            ....
    2d8      72   ADDITEM  248      0  294      50003f   401f6d   72e11f   25805 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 94020000 ........ ........
             dc4dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             1fe17200 cd640000 01000a00 800012fe ..r..d.. ........
             8000a9f8 5020a9f8                   ....P ..
    320      64   ADDITEM  248      0  2d8      500040   401f6d   72e11f   2073  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 d8020000 ........ ........
             dd4dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             1fe17200 19080000 01000400 800012fe ..r..... ........

    addr     len  type     xid      id link   
    360      64   ADDITEM  248      0  320      500041   401f6d   72e11f   4532  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 20030000 ........ .... ...
             de4dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             1fe17200 b4110000 01000200 502012fe ..r..... ....P ..
    3a0      96   HINSERT  248      0  360      401f6d   72e120   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 60030000 ........ ....`...
             e04dc32c 6d1f4000 6d1f4000 20e17200 .M.,m.@. m.@. .r.
             1d000004 00000000 00000000 502012fe ........ ....P ..
             4d414f20 4d414f20 00001300 0000a9f8 MAO MAO  ........
             54520000 0000c714 13021509 1f00aac9 TR...... ........
    400      72   ADDITEM  248      0  3a0      401f6e   401f6d   72e120   33255 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 a0030000 ........ ........
             e04dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             20e17200 e7810000 01000c00 4d414f20  .r..... ....MAO 
             80001300 8000a9f8                   ........

    addr     len  type     xid      id link   
    448      64   ADDITEM  248      0  400      50003d   401f6d   72e120   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 00040000 ........ ........
             e14dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             20e17200 18120000 01000400 8000a9f8  .r..... ........
    488      68   ADDITEM  248      0  448      50003e   401f6d   72e120   24461 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 48040000 ........ ....H...
             e24dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             20e17200 8d5f0000 01000800 80001300  .r.._.. ........
             8000a9f8                            ....
    4cc      72   ADDITEM  248      0  488      50003f   401f6d   72e120   29124 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 88040000 ........ ........
             e34dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             20e17200 c4710000 01000a00 80001300  .r..q.. ........
             8000a9f8 5452a9f8                   ....TR..
    514      64   ADDITEM  248      0  4cc      500040   401f6d   72e120   346   1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 cc040000 ........ ........
             e44dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             20e17200 5a010000 01000400 80001300  .r.Z... ........

    addr     len  type     xid      id link   
    554      64   ADDITEM  248      0  514      500041   401f6d   72e120   4473  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 14050000 ........ ........
             e54dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             20e17200 79110000 01000200 54521300  .r.y... ....TR..
    594      96   HINSERT  248      0  554      401f6d   72e121   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 54050000 ........ ....T...
             e74dc32c 6d1f4000 6d1f4000 21e17200 .M.,m.@. m.@.!.r.
             1d000004 00000000 00000000 54521300 ........ ....TR..
             41445649 41445649 00001302 0000a9f8 ADVIADVI ........
             4e450000 0000c714 13021509 1f00aac9 NE...... ........
    5f4      72   ADDITEM  248      0  594      401f6e   401f6d   72e121   33754 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 94050000 ........ ........
             e74dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             21e17200 da830000 01000c00 41445649 !.r..... ....ADVI
             80001302 8000a9f8                   ........

    addr     len  type     xid      id link   
    63c      64   ADDITEM  248      0  5f4      50003d   401f6d   72e121   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 f4050000 ........ ........
             e84dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             21e17200 18120000 01000400 8000a9f8 !.r..... ........
    67c      68   ADDITEM  248      0  63c      50003e   401f6d   72e121   21603 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 3c060000 ........ ....<...
             e94dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             21e17200 63540000 01000800 80001302 !.r.cT.. ........
             8000a9f8                            ....
    6c0      72   ADDITEM  248      0  67c      50003f   401f6d   72e121   28398 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 7c060000 ........ ....|...
             ea4dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             21e17200 ee6e0000 01000a00 80001302 !.r..n.. ........
             8000a9f8 4e45a9f8                   ....NE..
    708      64   ADDITEM  248      0  6c0      500040   401f6d   72e121   2164  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 c0060000 ........ ........
             eb4dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             21e17200 74080000 01000400 80001302 !.r.t... ........

    addr     len  type     xid      id link   
    748      64   ADDITEM  248      0  708      500041   401f6d   72e121   4519  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 08070000 ........ ........
             ec4dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             21e17200 a7110000 01000200 4e451302 !.r..... ....NE..
    788      96   HINSERT  248      0  748      401f6d   72e122   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 48070000 ........ ....H...
             ee4dc32c 6d1f4000 6d1f4000 22e17200 .M.,m.@. m.@.".r.
             1d000004 00000000 00000000 4e451302 ........ ....NE..
             4b484544 4b484544 0000130a 0000a9f8 KHEDKHED ........
             50200000 0000c714 13021509 1f00aac9 P ...... ........
    7e8      72   ADDITEM  248      0  788      401f6e   401f6d   72e122   29288 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 88070000 ........ ........
             ee4dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             22e17200 68720000 01000c00 4b484544 ".r.hr.. ....KHED
             8000130a 8000a9f8                   ........

    addr     len  type     xid      id link   
    104c     64   ADDITEM  248      0  7e8      50003d   401f6d   72e122   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 e8070000 ........ ........
             f04dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             22e17200 18120000 01000400 8000a9f8 ".r..... ........
    108c     68   ADDITEM  248      0  104c     50003e   401f6d   72e122   22263 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 4c100000 ........ ....L...
             f14dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             22e17200 f7560000 01000800 8000130a ".r..V.. ........
             8000a9f8                            ....
    10d0     72   ADDITEM  248      0  108c     50003f   401f6d   72e122   26615 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 8c100000 ........ ........
             f24dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             22e17200 f7670000 01000a00 8000130a ".r..g.. ........
             8000a9f8 5020a9f8                   ....P ..
    1118     64   ADDITEM  248      0  10d0     500040   401f6d   72e122   1539  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 d0100000 ........ ........
             f34dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             22e17200 03060000 01000400 8000130a ".r..... ........

    addr     len  type     xid      id link   
    1158     64   ADDITEM  248      0  1118     500041   401f6d   72e122   4532  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 18110000 ........ ........
             f44dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             22e17200 b4110000 01000200 5020130a ".r..... ....P ..
    1198     96   HINSERT  248      0  1158     401f6d   72e123   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 58110000 ........ ....X...
             f64dc32c 6d1f4000 6d1f4000 23e17200 .M.,m.@. m.@.#.r.
             1d000004 00000000 00000000 5020130a ........ ....P ..
             56425720 56425720 0000130c 0000a9f8 VBW VBW  ........
             50200000 011ec714 1303160e 1700aac9 P ...... ........
    11f8     72   ADDITEM  248      0  1198     401f6e   401f6d   72e123   32187 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 98110000 ........ ........
             f64dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             23e17200 bb7d0000 01000c00 56425720 #.r..}.. ....VBW 
             8000130c 8000a9f8                   ........

    addr     len  type     xid      id link   
    1240     64   ADDITEM  248      0  11f8     50003d   401f6d   72e123   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 f8110000 ........ ........
             f74dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             23e17200 18120000 01000400 8000a9f8 #.r..... ........
    1280     68   ADDITEM  248      0  1240     50003e   401f6d   72e123   25119 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 40120000 ........ ....@...
             f84dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             23e17200 1f620000 01000800 8000130c #.r..b.. ........
             8000a9f8                            ....
    12c4     72   ADDITEM  248      0  1280     50003f   401f6d   72e123   28871 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 80120000 ........ ........
             f94dc32c 3f005000 3f005000 6d1f4000 .M.,?.P. ?.P.m.@.
             23e17200 c7700000 01000a00 8000130c #.r..p.. ........
             8000a9f8 5020a9f8                   ....P ..
    130c     64   ADDITEM  248      0  12c4     500040   401f6d   72e123   3066  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 c4120000 ........ ........
             fa4dc32c 40005000 40005000 6d1f4000 .M.,@.P. @.P.m.@.
             23e17200 fa0b0000 01000400 8000130c #.r..... ........

    addr     len  type     xid      id link   
    134c     64   ADDITEM  248      0  130c     500041   401f6d   72e123   4532  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 0c130000 ........ ........
             fb4dc32c 41005000 41005000 6d1f4000 .M.,A.P. A.P.m.@.
             23e17200 b4110000 01000200 5020130c #.r..... ....P ..
    138c     96   HINSERT  248      0  134c     401f6d   72e124   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 4c130000 ........ ....L...
             fd4dc32c 6d1f4000 6d1f4000 24e17200 .M.,m.@. m.@.$.r.
             1d000004 00000000 00000000 5020130c ........ ....P ..
             4b542020 4b542020 00001311 0000a9f8 KT  KT   ........
             52200000 0000c714 13021509 1f00aac9 R ...... ........
    13ec     72   ADDITEM  248      0  138c     401f6e   401f6d   72e124   33054 1     12  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 8c130000 ........ ........
             fd4dc32c 6e1f4000 6e1f4000 6d1f4000 .M.,n.@. n.@.m.@.
             24e17200 1e810000 01000c00 4b542020 $.r..... ....KT  
             80001311 8000a9f8                   ........

    addr     len  type     xid      id link   
    1434     64   ADDITEM  248      0  13ec     50003d   401f6d   72e124   4632  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 ec130000 ........ ........
             fe4dc32c 3d005000 3d005000 6d1f4000 .M.,=.P. =.P.m.@.
             24e17200 18120000 01000400 8000a9f8 $.r..... ........
    1474     68   ADDITEM  248      0  1434     50003e   401f6d   72e124   22646 1     8   
             44000000 00001c00 10000000 00000000 D....... ........
             00000000 00000000 f8000000 34140000 ........ ....4...
             ff4dc32c 3e005000 3e005000 6d1f4000 .M.,>.P. >.P.m.@.
             24e17200 76580000 01000800 80001311 $.r.vX.. ........
             8000a9f8                            ....
    14b8     72   ADDITEM  248      0  1474     50003f   401f6d   72e124   26661 1     10  
             48000000 00001c00 10000000 00000000 H....... ........
             00000000 00000000 f8000000 74140000 ........ ....t...
             004ec32c 3f005000 3f005000 6d1f4000 .N.,?.P. ?.P.m.@.
             24e17200 25680000 01000a00 80001311 $.r.%h.. ........
             8000a9f8 5220a9f8                   ....R ..
    1500     64   ADDITEM  248      0  14b8     500040   401f6d   72e124   1665  1     4   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 b8140000 ........ ........
             014ec32c 40005000 40005000 6d1f4000 .N.,@.P. @.P.m.@.
             24e17200 81060000 01000400 80001311 $.r..... ........

    addr     len  type     xid      id link   
    1540     64   ADDITEM  248      0  1500     500041   401f6d   72e124   4531  1     2   
             40000000 00001c00 10000000 00000000 @....... ........
             00000000 00000000 f8000000 00150000 ........ ........
             024ec32c 41005000 41005000 6d1f4000 .N.,A.P. A.P.m.@.
             24e17200 b3110000 01000200 52201311 $.r..... ....R ..
    1580     96   HINSERT  248      0  1540     401f6d   72e125   29
             60000000 00002800 12010000 00000000 `.....(. ........
             00000000 00000000 f8000000 40150000 ........ ....@...

    Now, how to get the tablename from the above output ?

    Thanks & Regards,
    Neville Monteiro.

    Sent with ProtonMail Secure Email.


    #Informix


  • 2.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Tue March 03, 2020 10:32 PM
    Just look up based on the partnum from the ADDITEM line, starting at column 7

    tblspace ID - Hexadecimal
    rowid - Hexadecimal
    logical page - Decimal
    key number - Decimal
    key length - Decimal


    > Hello All,
    >
    > How do I get the actual tablename from the "onlog -l -n
    #Informix


  • 3.  RE: how to get the tablename from the onlog output

    Posted Thu March 05, 2020 04:11 AM
    Thank you, Sir.

    Thanks & Regards,
    Neville Monteiro.

    Sent with ProtonMail Secure Email.






  • 4.  RE: how to get the tablename from the onlog output

    Posted Thu March 05, 2020 11:17 AM
    The easiest way how we could pin-point the tablename was follows :-
    1) Created a output file out of one of the logical log files (onlog -l -n 387 > /tmp/387.log)

    2) Grepped for the "ADDITEM" in the 387.log file since we have seen a log of ADDITEM entries (egrep ADDITEM /tmp/387.log)

    3) The output was as follows :-
    20b467c 68 ADDITEM 248 0 20b463c 50003e 401f6d 123 1 1 8
    20b46c0 72 ADDITEM 248 0 20b467c 50003f 401f6d 123 1 1 10
    20b4708 64 ADDITEM 248 0 20b46c0 500040 401f6d 123 1 1 4
    20b4748 64 ADDITEM 248 0 20b4708 500041 401f6d 123 1 1 2
    20b47e8 72 ADDITEM 248 0 20b4788 401f6e 401f6d 124 1 1 12
    20b504c 64 ADDITEM 248 0 20b47e8 50003d 401f6d 124 1 1 4
    20b508c 68 ADDITEM 248 0 20b504c 50003e 401f6d 124 1 1 8
    20b50d0 72 ADDITEM 248 0 20b508c 50003f 401f6d 124 1 1 10
    20b5118 64 ADDITEM 248 0 20b50d0 500040 401f6d 124 1 1 4
    20b5158 64 ADDITEM 248 0 20b5118 500041 401f6d 124 1 1 2
    20b51f8 72 ADDITEM 248 0 20b5198 401f6e 401f6d 125 1 1 12
    ...... snipped

    4) Since "401f6d" (i.e. column number 8) was repeated on every line, we assumed that to be the table name.

    5) There is a sysadmin job which estimates the compression for the tables which runs periodically on the instance and the output is written in the online.log file. We searched for "401f6d" in the online.log file and found that the partnum "401f6d" is for the table "pmtk_present as follows :-
    01/15/20 17:35:45 admin_fragment_command('fragment estimate_compression ','4202344') succeeded
    01/15/20 17:35:45 SCHAPI Estimate Compression for krcntrldb:"informix".pmwk_6arr_sum started
    01/15/20 17:35:45 SCHAPI Estimate succeeded for table 'krcntrldb:"informix".pmwk_6arr_sum' partnum 401f69.
    01/15/20 17:35:45 admin_fragment_command('fragment estimate_compression ','4202345') succeeded
    01/15/20 17:35:45 SCHAPI Estimate Compression for krcntrldb:"informix".pmtk_present started
    01/15/20 17:35:45 SCHAPI Estimate succeeded for table 'krcntrldb:"informix".pmtk_present' partnum 401f6d.
    01/15/20 17:35:45 admin_fragment_command('fragment estimate_compression ','4202349') succeeded
    01/15/20 17:35:45 SCHAPI Estimate Compression for krcntrldb:"informix". 1781_11671 started
    01/15/20 17:35:45 SCHAPI Estimate succeeded for index 'krcntrldb:"informix". 1781_11671' partnum 401f6e.
    01/15/20 17:35:45 admin_fragment_command('fragment estimate_compression ','4202350') succeeded
    01/15/20 17:35:45 SCHAPI Estimate Compression for krcntrldb:"informix".pmt_att_entry started
    01/15/20 17:35:46 SCHAPI Estimate succeeded for table 'krcntrldb:"informix".pmt_att_entry' partnum 401f6f.
    01/15/20 17:35:46 admin_fragment_command('fragment estimate_compression ','4202351') succeeded

    6) Checked the "Info" of the pmtk_present through dbaccess and following is the output :-
    INFO - pmtk_present: Columns Indexes Privileges References Status cOnstraints triGgers Table Fragments Exit
    Display information about indexes for the columns in a table.
    ----------------------- krcntrldb@olr_bela ----- Press CTRL-W for Help --------
    Index_name Owner Type/Clstr Access_Method Columns
    1781_11671 informix unique/No btree att_stn
                                                                emp_no
                                                                att_date
    attdate informix dupls/No btree att_date
    attdt_emp informix dupls/No btree emp_no
                                                          att_date
    pmtldate_idx informix dupls/No btree emp_no
                                                              att_date
                                                              att_flg
    prempno informix dupls/No btree emp_no
    prflag informix dupls/No btree att_flg

    7) As per output against serial number 3 above, grepped for values mentioned in column number 7 in the output of the estimate compression job in online.log file
    a) Searched for "50003e"
    01/15/20 17:38:35 SCHAPI Estimate succeeded for index 'krcntrldb:"informix".attdt_emp' partnum 50003e.
    01/15/20 17:38:35 admin_fragment_command('fragment estimate_compression ','5242942') succeeded
    #) attdt_emp is one of the indexes for the pmtk_present table.

    b) Searched for "50003f"
    01/15/20 17:38:35 SCHAPI Estimate succeeded for index 'krcntrldb:"informix".pmtldate_idx' partnum 50003f.
    01/15/20 17:38:35 admin_fragment_command('fragment estimate_compression ','5242943') succeeded
    #) pmtldate_idx is one of the indexes for the pmtk_present table.

    c) Searched for "500040"
    01/15/20 17:38:37 SCHAPI Estimate succeeded for index 'krcntrldb:"informix".prempno' partnum 500040.
    01/15/20 17:38:37 admin_fragment_command('fragment estimate_compression ','5242944') succeeded
    #) prempno is one of the indexes for the pmtk_present table.

    I hope the above helps. ​​​

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 5.  RE: how to get the tablename from the onlog output

    Posted Thu March 05, 2020 08:22 PM
    Hi Neville. Thank you for sharing your good tips.
    However, if the mon_compression_estimates job is disabled in the Informix scheduler, the above method cannot be used.
    If you look up a table by partnum, how about using the following command?
    Or maybe there are other better ways.

    /work2/INFORMIX/1210FC13/skjeong]onlog -l -n 1538 > onlog.1538
    /work2/INFORMIX/1210FC13/skjeong]cat onlog.1538 | grep ADDITEM | head
    85160    64   ADDITEM  17       0  85080    1000c8   1000c7   5a0b     6     1     4
    851a0    68   ADDITEM  17       0  85160    1000c9   1000c7   5a0b     4     1     8
    851e4    68   ADDITEM  17       0  851a0    1000ca   1000c7   5a0b     7     1     8
    870ec    64   ADDITEM  17       0  87080    1000c4   1000c3   3528     6     1     4
    8712c    68   ADDITEM  17       0  870ec    1000c5   1000c3   3528     20    1     8
    87170    72   ADDITEM  17       0  8712c    1000c6   1000c3   3528     34    1     12
    a55e0    124  ADDITEM  18       0  a5520    4002ef   4002ee   101      1     1     64
    a56dc    124  ADDITEM  18       0  a565c    4002eb   4002ea   102      1     1     64
    b4c20    72   ADDITEM  18       0  b48e4    4002e6   4002e5   fc04     8     1     10
    b591c    72   ADDITEM  18       0  b4d10    4002e6   4002e5   fd01     8     1     10
    /work2/INFORMIX/1210FC13/skjeong]echo "select tabname from systabnames where partnum='0x1000c3'" | dbaccess sysmaster
    
    Database selected.
    
    
    
    
    tabname  ph_run
    
    1 row(s) retrieved.
    
    
    
    Database closed.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 6.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 12:11 AM
    Hello SangGyu Jeong,

    I am not getting the tablename in our instance when I execute the same query using partnum "401f6d" as follows :-

    informix > echo "select tabname from systabnames where partnum='0x401f6d'" | dbaccess sysmaster
    Database selected.

    No rows found.

    Database closed.
    informix >

    OR

    informix > echo "select tabname from systabnames where hex(partnum)='0x401f6d'" | dbaccess sysmaster
    Database selected.

    No rows found.

    Database closed.
    informix >

    Kindly guide.

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 7.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 12:42 AM
    That's weird. How about compared to hex (partnum) like below?

    /work2/INFORMIX/1210FC13/skjeong]cat onlog.1550 | grep ADDITEM | head -20
    3944     72   ADDITEM  30       0  38f8     4002be   4002be   37d01    822   1     12
    d86c     72   ADDITEM  30       0  d820     4002be   4002be   37e01    822   1     12
    1779c    72   ADDITEM  30       0  17750    4002be   4002be   37f01    822   1     12
    216cc    72   ADDITEM  30       0  21680    4002be   4002be   38001    822   1     12
    2b5fc    72   ADDITEM  30       0  2b5b0    4002be   4002be   38101    822   1     12
    35528    72   ADDITEM  30       0  354dc    4002be   4002be   38201    822   1     12
    3f450    72   ADDITEM  30       0  3f404    4002be   4002be   38301    822   1     12
    493a4    72   ADDITEM  30       0  49358    4002be   4002be   38401    822   1     12
    532cc    72   ADDITEM  30       0  53280    4002be   4002be   38501    822   1     12
    5d1f8    72   ADDITEM  30       0  5d1ac    4002be   4002be   38601    822   1     12
    672b8    72   ADDITEM  30       0  67124    4002be   4002be   38701    904   1     12
    711e4    72   ADDITEM  30       0  71198    4002be   4002be   38901    904   1     12
    7b114    72   ADDITEM  30       0  7b0c8    4002be   4002be   38a01    904   1     12
    85048    72   ADDITEM  30       0  84fe0    4002be   4002be   38b01    904   1     12
    8ef5c    72   ADDITEM  30       0  8ef10    4002be   4002be   38c01    904   1     12
    98e88    72   ADDITEM  30       0  98e3c    4002be   4002be   38d01    904   1     12
    a2db0    72   ADDITEM  30       0  a2d64    4002be   4002be   38e01    904   1     12
    accdc    72   ADDITEM  30       0  acc90    4002be   4002be   38f01    904   1     12
    b6c08    72   ADDITEM  30       0  b6bbc    4002be   4002be   39001    904   1     12
    c0b50    72   ADDITEM  30       0  c0b04    4002be   4002be   39101    904   1     12
    /work2/INFORMIX/1210FC13/skjeong]echo "select tabname from systabnames where partnum='0x4002be'" | dbaccess sysmaster
    
    Database selected.
    
    
    
    
    tabname  raw_container
    
    1 row(s) retrieved.
    
    
    
    Database closed.
    
    /work2/INFORMIX/1210FC13/skjeong]echo "select hex(partnum) from systabnames where tabname='raw_container'" | dbaccess sysmaster
    
    Database selected.
    
    
    
    (expression)
    
    0x004002BE
    
    1 row(s) retrieved.
    
    
    
    Database closed.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 8.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 01:39 AM
    Does not display the table name.

    echo "select tabname from systabnames where partnum='0x401f6d'" | dbaccess sysmaster

    Database selected.

    No rows found.

    Database closed.

    informix >

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 9.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 01:45 AM
    What is the result of this query?

    select dbsname, tabname, partnum, hex (partnum) from systabnames where tabname = 'pmtk_present'

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 10.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 02:00 AM
    informix > echo "select dbsname, tabname, partnum, hex (partnum) from systabnames where tabname = 'pmtk_present';" | dbaccess sysmaster

    Database selected.

    dbsname krdb
    tabname pmtk_present
    partnum 4199886
    (expression) 0x004015CE

    dbsname krcntrldb
    tabname pmtk_present
    partnum 5243334
    (expression) 0x005001C6

    2 row(s) retrieved.

    Database closed.

    The required table "pmtk_present" which I am looking at, is in "krcntrldb" database. But in the hexadecimal output here, it is displayed as "0x005001C6" and not "0x401f6d".

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 11.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 01:46 AM
    Edited by System Fri January 20, 2023 04:21 PM
    You have to convert hex to dec:
    0x401f6d‭4198764‬
    echo "select tabname from systabnames where partnum=4198764" | dbaccess sysmaster

    HTH
    Hrvoje


    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 12.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 01:50 AM
    Still did not get the table name.

    informix > echo "select tabname from systabnames where partnum=4198764" | dbaccess sysmaster

    Database selected.

    tabname 977_6280

    1 row(s) retrieved.

    Database closed.

    informix >

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 13.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 01:53 AM
    You have tabname:
    tabname 977_6280
    I.e. it is index name.
    Hrvoje


    ------------------------------
    Hrvoje Zokovic
    ------------------------------



  • 14.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 02:12 AM
    It is not the index name which we are looking to find out, but the tabname using its hexadecimal partnum which is "401f6d".

    In the online.log file, when the compression estimale job runs from sysadmin, it is registered as "401f6d' is for pmtk_present as follows :-
    01/15/20 17:35:45 SCHAPI Estimate Compression for krcntrldb:"informix".pmtk_present started
    01/15/20 17:35:45 SCHAPI Estimate succeeded for table 'krcntrldb:"informix".pmtk_present' partnum 401f6d.



    ------------------------------
    Neville Monteiro
    ------------------------------



  • 15.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 01:58 AM
    The calculation seems to be wrong.

    401f6d => 4202349

    echo "select tabname from systabnames where partnum = 4202349" | dbaccess sysmaster

    See also in online.log.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 16.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 02:04 AM
    Still no table name.

    informix > echo "select tabname from systabnames where partnum = 4202349" | dbaccess sysmaster

    Database selected.

    No rows found.

    Database closed.

    INformix >

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 17.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 02:43 AM
    Isn't the newly created krcntrldb:pmtk_present table now existing?

    echo "select tabname, hex (partnum), created from systables where tabname = 'pmtk_present'" | dbaccess krcntrldb

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 18.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 02:50 AM
    The pmtk_present table is existing in the krcntrldb database.

    > echo "select tabname, hex (partnum), created from systables where tabname = 'pmtk_present'" | dbaccess krcntrldb

    Database selected.

    tabname pmtk_present
    (expression) 0x005001C6
    created 09/01/2020

    1 row(s) retrieved.

    Database closed.

    > echo "select count(*) from pmtk_present;" | dbaccess krcntrldb

    Database selected.

    (count(*))
    672676

    1 row(s) retrieved.

    Database closed.

    Neville

    ------------------------------
    Neville Monteiro
    ------------------------------



  • 19.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 04:36 AM
    According to the contents of onlog and online.log, the table and index corresponding to each partnum is estimated as below.

    401f6d: pmtk_present
    401f6e: 1781_11671
    50003d: attdate
    50003e: attdt_emp
    50003f: pmtldate_idx
    500040: prempno
    500041: prflag

    I don't know why partnum is now 5001C6. The chunk number has changed!
    Anyway, this is far from the original question ;)

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 20.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 04:45 AM
    Then we can assume that the table was dropped and recreated. @Neville Monteiro if you have auditing turned on for table creation/destruction, you can verify ​that in the audit logs.

    ------------------------------
    Luis Marques
    ------------------------------



  • 21.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 05:12 AM
    OK, I got the answer.

    The following was also executed yesterday :-
    alter fragment on table pmtk_present init in datadbs1

    The above mentioned table was recreated in the same dbspace and therefore it resulted in a different partnum.

    Thanks a lot everyone.


    ------------------------------
    Neville Monteiro
    ------------------------------



  • 22.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 05:32 AM
    It has been moved or deleted since the partnum was displayed/used

    Cheers
    Paul

    ------------------------------
    Paul Watson
    Oninit LLC
    Cell +1 9133877529
    ------------------------------



  • 23.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 06:15 AM
    Neville:

    You have to convert the partnum from HEX to decimal. You can most easily do that using bc (note that bc uses upper case letters while informix reports hex values using lower case, so you have to upshift the partnum):

    bc
    ibase=16
    401F6D
    4202349
    ^D

    So here you want to do:

    dbaccess sysmaster -

    select tabname from systabnames where partnum=4202349;



    Art

    Art S. Kagel, President and Principal Consultant
    ASK Database Management


    Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference.  Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.








  • 24.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 06:46 AM
    Or write a simple function that understand dec and hex

    select tabname from systabnames where partnum=getpart(4202349);
    select tabname from systabnames where partnum=getpart("0x401f6d")
    select tabname from systabnames where partnum=getpart("401f6d")

    and never worry about hex or dec ever again :-)

    Cheers
    Paul

    > Neville:
    >
    > You have to convert the partnum from HEX to decimal. You can most easily
    > do that using bc (note that bc uses upper case letters while informix
    > reports hex values using lower case, so you have to upshift the partnum):
    > bcibase=16401F6D4202349
    > ^D
    > So here you want to do:
    > dbaccess sysmaster -
    > select tabname from systabnames where partnum=4202349;
    >
    >
    >
    > Art
    > Art S. Kagel, President and Principal ConsultantASK Database
    > Managementwww.askdbmgt.com
    #Informix


  • 25.  RE: how to get the tablename from the onlog output

    Posted Fri March 06, 2020 07:28 AM
    Looking for a way to change a bit easier ... In Oracle, the TO_NUMBER function provides the ability to convert from hexadecimal to decimal.
    SQL> select to_number('401f6d', 'xxxxxxx') from dual;
    
    TO_NUMBER('401F6D','XXXXXXX')
    -----------------------------
                          4202349
    ​

    In Informix, using the CAST function works the same.
    select '0x401f6d'::integer from sysdual;
    select ('0x'||'401f6d')::integer from sysdual;
    select cast ('0x401f6d' as integer) from sysdual;
    
    
    > select ('0x'||'401f6d')::integer from sysdual;
    
    
     (constant)
    
        4202349
    
    1 row(s) retrieved.​


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 26.  RE: how to get the tablename from the onlog output

    IBM Champion
    Posted Fri March 06, 2020 07:36 AM
    True, just depends on how much you like to type

    Cheers
    Paul

    > Looking for a way to change a bit easier ... In Oracle, the TO_NUMBER
    > function provides the ability to convert from hexadecimal to decimal.
    >
    > SQL> select to_number('401f6d', 'xxxxxxx') from dual;
    >
    > TO_NUMBER('401F6D','XXXXXXX')
    > -----------------------------
    > 4202349
    > ???
    >
    > In Informix, using the CAST function works the same.
    >
    > select '0x401f6d'::integer from sysdual;
    > select ('0x'||'401f6d')::integer from sysdual;
    > select cast ('0x401f6d' as integer) from sysdual;
    >
    >
    >> select ('0x'||'401f6d')::integer from sysdual;
    >
    >
    > (constant)
    >
    > 4202349
    >
    > 1 row(s) retrieved.???
    >
    > ------------------------------
    > SangGyu Jeong
    > Software Engineer
    > Infrasoft
    > Seoul Korea, Republic of
    > ------------------------------
    > -------------------------------------------
    > Original Message:
    > Sent: Fri March 06, 2020 06:45 AM
    > From: Paul Watson
    > Subject: how to get the tablename from the onlog output
    >
    > Or write a simple function that understand dec and hex
    >
    > select tabname from systabnames where partnum=getpart(4202349);
    > select tabname from systabnames where partnum=getpart("0x401f6d")
    > select tabname from systabnames where partnum=getpart("401f6d")
    >
    > and never worry about hex or dec ever again :-)
    >
    > Cheers
    > Paul
    >
    >> Neville:
    >>
    >> You have to convert the partnum from HEX to decimal. You can most easily
    >> do that using bc (note that bc uses upper case letters while informix
    >> reports hex values using lower case, so you have to upshift the
    >> partnum):
    >> bcibase=16401F6D4202349
    >> ^D
    >> So here you want to do:
    >> dbaccess sysmaster -
    >> select tabname from systabnames where partnum=4202349;
    >>
    >>
    >>
    >> Art
    >> Art S. Kagel, President and Principal ConsultantASK Database
    >> Managementwww.askdbmgt.com
    >
    > Reply to Sender :
    > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&SenderKey=321aa177-684f-4844-a5ad-34ecda64d7a9&MID=46000&MDATE=757545845%253b&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved
    >
    > Reply to Discussion :
    > https://community.ibm.com/eGroups/PostReply/?GroupId=4147&MID=46000&MDATE=757545845%253b&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved
    >
    >
    >
    > You are subscribed to "Informix" as paul@oninit.com. To change your
    > subscriptions, go to
    > http://community.ibm.com/community/user/preferences?section=Subscriptions&MDATE=757545845%253b&UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved.
    > To unsubscribe from this community discussion, go to
    > http://community.ibm.com/HigherLogic/eGroups/Unsubscribe.aspx?UserKey=29a6c229-a98c-46da-9248-4df042a4a263&sKey=KeyRemoved&GroupKey=60eb97a2-57c0-4130-9b6d-57174f97d5a8.


    --
    Paul Watson
    Tel: +1 913-674-0360
    Mob: +1 913-387-7529
    Web: www.oninit.com

    Oninit? is a registered trademark of Oninit LLC

    Failure is not as frightening as regret.
    If you want to improve, be content to be thought foolish and stupid.
    What this country needs are more unemployed politicians




  • 27.  RE: how to get the tablename from the onlog output

    Posted Sat March 07, 2020 12:33 AM
    Thank you all.

    I did learn quite a lot in regards to this topic (i.e. onlog) in the last couple of days.

    Thank you to each and every one of you.

    ------------------------------
    Neville Monteiro
    ------------------------------