how to get the tablename from the onlog output

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

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

Thank you, Sir. Thanks & Regards, Neville Monteiro. Sent with ProtonMail Secure Email.

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

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

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

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

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

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

You have to convert hex to dec: 0x401f6d = ‭4198764‬ echo "select tabname from systabnames where partnum= 4198764" | dbaccess sysmaster HTH Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

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

You have tabname: tabname 977_6280 I.e. it is index name. Hrvoje ------------------------------ Hrvoje Zokovic ------------------------------

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

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 "0x 401f6d". ------------------------------ Neville Monteiro ------------------------------

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

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

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

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

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

Then we can assume that the table was dropped and recreated. if you have auditing turned on for table creation/destruction, you can verify that in the audit logs. ------------------------------ Luis Marques ------------------------------

OK, I got the answer. The following was also executed yesterday :- alter fragment on table pmtk_present init in datadbs1The above mentioned table was recreated in the same dbspace and therefore it resulted in a different partnum. Thanks a lot everyone. ------------------------------ Neville Monteiro ------------------------------

It has been moved or deleted since the partnum was displayed/used Cheers Paul ------------------------------ Paul Watson Oninit LLC Cell +1 9133877529 ------------------------------

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 www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ 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.

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

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

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

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