Columns in ixbar file

As threatened er, promised in another thread, I am reviving a question posed in 2013 or so on comp.databases.informix: Does anyone know a query on sysutils that would recreate the ixbar file ? Sadly, only 2 people responded and neither had an answer. I did find a page that purports to describe the columns of ixbar . I don't know how old that page is but it only describes 10 columns and the ixbar files in my system (IDS 12.10.FC9W1X4) shows 20 columns. (21 if I forget to combine the date/time into one field.) I'd like to write a script that puts this data into human-comprehensible form but I have no useful starting point with no up-to-date information on those columns. Say, IS there a query I can run to obtain the identical information, perhaps how onbar [re]generates it after every backup or restore? Anyone out there with the information necessary to put this idea into action? Interestingly enough, chapter 9 in the 12 Backup & Restore Guide describes a bunch of tables in the sysutils database, including bar_ixbar with 22 columns and and nearly identical table named bar_syncdeltab. But I can't find either table in dbaccess sysutils . In fact, a query on sysmaster:systabnames gives me these 10 alleged tables: dbsname tabname sysutils bar_act_idx X sysutils bar_action sysutils bar_inst_idx X sysutils bar_inst_idx2 X sysutils bar_instance X sysutils bar_obj_idx sysutils bar_object sysutils bar_orderseq sysutils bar_ordertab sysutils bar_server I marked the X on the table names that don't show up in dbaccess. And, of course, bar_ixbar really ain't there. Thanks much for way to get this data! Now that the documentation seems to have misled me, I'm on a vendetta! OH! Who will join my quest for the one table to rule them all? (I need a Gandalf emoji.) ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Hello Jacob, I had a script that generate ixbar file from sysutils (in case of onsmsync will not do that). Works for me with Version 14 / should also work with Version 12. #!/bin/sh DBACCESS_COLUMNS=186; export DBACCESS_COLUMNS dbaccess sysutils - 2>/dev/null <<EOF | awk '{if($0!~/^$/){printf("%-168s\\n",$0)}}' OUTPUT TO PIPE "/bin/cat" WITHOUT HEADINGS SELECT o.obj_srv_name[1,18]::CHAR(19) || -- INFORIMXSERVER o.obj_name[1,18]::CHAR(19) || -- logical log number or dbspace name o.obj_type::CHAR(3) || -- R == rootdbs, L = logical log, CD == critical dbspace -- ND == nonciritical dbspace or sbspace, B == blobspace CASE WHEN a.act_type = 1 THEN 0 -- 1 == backup WHEN a.act_type = 5 THEN 1 -- 5 == whole-system backup END || " " || a.act_aid::CHAR(6) || i.ins_level::CHAR(2) || -- level of backup / 0 == full backup, 1/2 diff to prev level i.ins_copyid_hi::CHAR(11) || i.ins_copyid_lo::CHAR(11) || a.act_start::CHAR(20) || 1::CHAR(6) || i.ins_first_log::CHAR(6) || i.rsam_time::CHAR(12) || i.ins_req_aid::CHAR(6) || i.ins_logstream::CHAR(6) || "- " || "- " || i.ins_chpt_log::CHAR(6) || i.seal_time::CHAR(12) || i.prev_seal_time::CHAR(12) || i.ins_backup_order FROM bar_object o, bar_action a, bar_instance i WHERE o.obj_oid = a.act_oid AND a.act_aid = i.ins_aid AND o.obj_oid = i.ins_oid AND a.act_type IN (1,5) ORDER BY a.act_aid, i.ins_backup_order; EOF HTH Cheers, Markus ------------------------------ Markus Holzbauer ------------------------------

Hi Jacob, The documented way to do this is onsmsync -b See https://www.ibm.com/docs/en/informix-servers/14.10?topic=catalogs-onsmsync-utility I think you need a zero byte ixbar file to start with and it will then work. At least it did for me just now with 14.10.xC10. Ben. ------------------------------ Benjamin Thompson ------------------------------

Thanks for the idea. However, Ben, according to what I have read in the PDFs, onsmsync does not merely present usable ixbar-type data; it actually updates the current ixbar file. I'm interested in peeking, not poking. (Remember the old BASIC commands from the pre-IBM personal computers?) Admittedly, I may have missed an option to output to another file or STDOUT. +----- Jacob Salomon --------------------------------------------------+ | Praise without end for the go-ahead zeal of whoever it was that | | invented the wheel; | | But never a word for the poor soul's sake that thought ahead, | | and invented the brake. | +-------------- Howard Nemerov, on the US Bicentennial ----------------+

This post was removed

Markus, BTW, is DBACCESS_COLUMNS documented anywhere? ------------------------------ Sincerely, Dennis ------------------------------

Hello Dennis, I think it came in with the later 12.10 versions (xc10 or 11) and all of the 14.10 versions. But still not documented here: https://www.ibm.com/docs/en/informix-servers/14.10?topic=variables-environment-in-informix-products Cheers, Markus ------------------------------ Markus Holzbauer ------------------------------

Hi Markus. I finally got around to trying your SQL What's with all the concatenation operations? And the :: Type-casting? All for nice columns? Leave that to the spreadsheet (or my beautify-unl.pl script.) Whatever the real problem is, I keep getting this error: 1207: Converted value does not fit into the allotted spaceA noncharacter value, after conversion to a character string, is too long to fit in the buffer that was provided. Suppose I try it without concatenating columns: I still got the same error. Then I took out the width type-casting operators like :: CHAR(6) . The the query ran. Now I need to ask: What's with the constant "1" and the two dash columns? That's actually a question for Informix, not for you; those columns appear as such in the ixbar file, except that there are log backups running every couple minutes so the query will never quite match the ixbar file regenerated days or weeks ago. Here's my updated, working version of that query, formatted according to MY tastes :-): -- In database sysutils -- SELECT o.obj_srv_name[1,18], -- INFORIMXSERVER o.obj_name[1,18], -- logical log number or dbspace name o.obj_type, -- R = rootdbs, L = logical log, -- CD = critical dbspace, ND = nonciritical -- dbspace or sbspace, B == blobspace CASE WHEN a.act_type = 1 THEN 0 -- 1 = backup WHEN a.act_type = 5 THEN 1 -- 5 = whole-system backup END as action_type, a.act_aid, i.ins_level, -- level of backup: 0, 1, or 2 i.ins_copyid_hi, i.ins_copyid_lo, a.act_start, 1 as one, i.ins_first_log, i.rsam_time, i.ins_req_aid, i.ins_logstream, "- " as dash1, "- " as dash2, i.ins_chpt_log, i.seal_time, i.prev_seal_time, i.ins_backup_order FROM bar_object o, bar_action a, bar_instance i WHERE o.obj_oid = a.act_oid AND a.act_aid = i.ins_aid AND o.obj_oid = i.ins_oid AND a.act_type IN (1,5) ORDER BY a.act_aid, i.ins_backup_order; Hopefully this will stay nicely formatted when it displays for you. You bet I'm saving this in my directories! THANKS SO MUCH! ------------------------------ Jacob Salomon --- Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra ------------------------------

Hi, The ixbar file does not have to be empty. Be aware though if onsmsync cannot access any of the storage manager entries it finds (Not sure if that is in the sql tables or the ixbar file) e.g. if security on the storage manager is misconfigured and blocks access then it will empty both the ixbar file and the sql tables leaving Informix knowing nothing!!! A more interesting tool would be one that would a) scan the storage manager catalogs for likely entries b) generate an ixbar file based on timestamps in the storage manager catalogs c) generate load files for the sql tables !! Regards, David. ------------------------------ David Williams Senior Database Platform Engineer Flutter London ------------------------------