IDS 12 Bug

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

FWIW myschema will print the schema for those aqt* tables. Art ------Original Message------ 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

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

Thanks, SangGyu! I can confirm this works. ------------------------------ TOM GIRSCH ------------------------------

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: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/ ------Original Message------ Thanks, SangGyu! I can confirm this works. ------------------------------ TOM GIRSCH ------------------------------ #Informix

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

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

That's what myexport is for. B^) But, it would be myschema -d database -l Art ------Original Message------ 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 ------------------------------ #Informix

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: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/ ------Original Message------ 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 ------------------------------ #Informix

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

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 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. ------Original Message------ 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 ------------------------------ #Informix

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

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: https://w3-connections.ibm.com/wikis/home?lang=en#!/wiki/Wf58c4c538dbf_45b4_b7a7_5003d0ceb79b/page/Informix The older Internal IMAZ CTP Informix Main Page can be found here: https://w3-connections.ibm.com/wikis/home?lang=en-us#!/wiki/Info%20Mgmt%20Client%20Technical%20Professional%20Resources%20Wiki/page/Informix Website for Internet Of Things https://www.ibm.com/internet-of-things/ Website for Informix https://www.ibm.com/analytics/us/en/technology/informix/ ------Original Message------ 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 tabnameI'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 ------------------------------ #Informix