Informix

 View Only
Expand all | Collapse all

IDS 12 Bug

  • 1.  IDS 12 Bug

    IBM Champion
    Posted Sun April 19, 2020 11:50 PM

    Discovered a bug in IDS 12.10 this week that may also exist in 14.10. If you have any tables that start with "aqt," they will be ignored by dbaccess (when getting a table list), dbschema and dbexport. There are special "aqt" views that are apparently used by the warehouse accelerator, but Informix doesn't check for those specific names or whether IWA is installed or whether they're views.

    Do a strings on $INFORMIXDIR/bin/dbaccess (or dbexport or dbschema) and you'll see the exact query it uses.

    Already logged as a bug with IBM: idsdb00105671 ( IT32556)



    ------------------------------
    TJG
    ------------------------------

    #Informix


  • 2.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 08:38 AM
    FWIW myschema will print the schema for those aqt* tables.

    Art



    Original Message


  • 3.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 12:51 PM

    Art:

    I thought about that, but didn't already have myschema built, and remembered having a bit of trouble figuring out how to set up all the options to emulate dbexport behavior. So my when-all-you-have-is-a-hammer workaround was to rename the offending tables, do the export, and then rename them back.



    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 4.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 01:29 PM
    That's what myexport is for. B^)

    But, it would be myschema -d database -l

    Art



    Original Message


  • 5.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 03:36 PM
    My export is a shell wrapper around myschema, no?

    Also, is that lower-ell or upper-eye?

    This is an ancient SAP database that needs DELIMIDENT set to work correctly, uses reserved words as column names and is generally a giant pain in my tuchus.

    Probably worth downloading and building myschema just to get the automatic extent size calculation, I suppose.

    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 6.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 03:43 PM
    Lower ell is the flag that includes dbexport headers in the output. 

    Yes, myexport is at its core a wrapper around myschema, dbaccess, sqlcmd, or myonpload (to perform the exports), and lots of shell and awk scripting for the extra features and to insure that the export is compatible with dbimport.

    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.






    Original Message


  • 7.  RE: IDS 12 Bug

    Posted Mon April 20, 2020 09:09 AM
    Hi Tom,
    In 2013, my customer who used Informix version 11.70 had the same issue and opened PMR.
    This issue still occurs on 14.10.xC3 and 12.10.xc14. To avoid the problem, set the AQT environment variable as in the example below.
    /work2/INFORMIX/1210FC14]dbaccess stores_demo -

Database selected.

> create table aqtlh1310 ( a int) ;

Table created.

/work2/INFORMIX/1210FC14]dbschema -d stores_demo -t aqtlh1310

DBSCHEMA Schema Utility       INFORMIX-SQL Version 12.10.FC14








No table or view aqtlh1310.


/work2/INFORMIX/1210FC14]export AQT=1
/work2/INFORMIX/1210FC14]dbschema -d stores_demo -t aqtlh1310

DBSCHEMA Schema Utility       INFORMIX-SQL Version 12.10.FC14







{ TABLE "informix".aqtlh1310 row size = 4 number of columns = 1 index size = 0 }

create table "informix".aqtlh1310
  (
    a integer
  );

revoke all on "informix".aqtlh1310 from "public" as "informix";​


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

    Original Message


  • 8.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 09:51 AM
    Thanks, SangGyu! I can confirm this works.

    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 9.  RE: IDS 12 Bug

    Posted Mon April 20, 2020 09:56 AM
    After checking, I noticed that AQT is not defined as an environment variable anywhere, so there is a doc fix now requested.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The Informix Roadshow page is here:

    https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

    Shortcut to the Informix Roadshow Page:

    http://bit.ly/ifmx_roadshow

    All presentations and the agenda used by the Roadshow can be found there.
     
    The current ZACS Informix Page can be found here:
     




    Original Message


  • 10.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 12:52 PM

    Scott:

    If I understand how IWA works, the aqt things are all views; if so, the elimination query should be updated to only exclude views that start with aqt* rather than all objects.



    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 11.  RE: IDS 12 Bug

    Posted Mon April 20, 2020 03:05 PM
    I've never seen an AQT that did not start with aqt as the first three letters for the object name or as reported by onstat -g aqt. Of course, I can be wrong.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The Informix Roadshow page is here:

    https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

    Shortcut to the Informix Roadshow Page:

    http://bit.ly/ifmx_roadshow

    All presentations and the agenda used by the Roadshow can be found there.
     
    The current ZACS Informix Page can be found here:
     
     
     



    Original Message


  • 12.  RE: IDS 12 Bug

    IBM Champion
    Posted Mon April 20, 2020 06:01 PM

    Scott:

    I think you misunderstand what I'm saying. The exclusion query (for dbaccess) looks like this:

    select tabname , tabid , owner from informix . systables where tabname != 'ANSI' and tabtype != 'P' and tabname not matches 'aqt*' order by tabname


    I'm saying it should look like this:

    select tabname , tabid , owner from informix . systables where tabname != 'ANSI' and tabtype != 'P' and NOT (tabname MATCHES 'aqt*' AND tabtype = 'V') order by tabname


    ------------------------------
    TOM GIRSCH
    ------------------------------

    Original Message


  • 13.  RE: IDS 12 Bug

    Posted Tue April 21, 2020 09:04 AM
    Corrected as written makes sense on the surface of it. Bug fix perhaps? Of course, if they've done something differently since I last looked at this ..... which was a while ago.
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The Informix Roadshow page is here:

    https://www.ibm.com/developerworks/mydeveloperworks/wikis/home?lang=en_US#/wiki/Informix%20Roadshow%20-%20Informix%20is%20Everywhere

    Shortcut to the Informix Roadshow Page:

    http://bit.ly/ifmx_roadshow

    All presentations and the agenda used by the Roadshow can be found there.
     
    The current ZACS Informix Page can be found here:
     
     
     



    Original Message