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

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,

BTW, is DBACCESS_COLUMNS documented anywhere?

#!/bin/shDBACCESS_COLUMNS=186; export DBACCESS_COLUMNSdbaccess 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

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

#!/bin/shDBACCESS_COLUMNS=186; export DBACCESS_COLUMNSdbaccess 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

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 space

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

#!/bin/shDBACCESS_COLUMNS=186; export DBACCESS_COLUMNSdbaccess 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

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.

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.