Cognos and Informix are not playing nice together

IBM Informix Dynamic Server Version 11.50.FC6 (three 2GB tempdb space configured; DBSPACETEMP temp0:temp1:temp2) HP-UX B.11.31 ia64 (BL870c integrity server) Cognos 10.1.1 Note: Since we get our Informix & Cognos licenses from our ERP provider we do not have direct access to IBM support. And getting our ERP provider to pass a problem to IBM and getting direct help from IBM is almost impossible to do. This problem has made me conclude I no longer know anything about how Informix works. The problem: We have a hand full of Congos reports that take large chucks of resources when running. One report caused the rootdb space to fill up. This one report would lead me to believe the temp/sort tables the sql from the report generated used rootdb instead of the configured temp dbspaces. I don't know Cognos and assumed it would let Informix handle the sort/temp space allocation. Question 1: anyone know if Cognos somehow override what is configured in the onconf file? Or have any theories why my rootdb space would fill even when I have tempdb space configured and it almost empty? This report and the other handful of reports at least from the OS side show the disk i/o goes to high 90s% for Informix. Also response time and all other indications on the server itself show a slowing due to Informix taking all disk i/o. If I go into OAT while the reports are running and look at storage, rootdb space will slowly get measureable "other" chuck of space used (20%) and index/data will fluctuate a little but seem stable. If I look at the three temp spaces it goes from ~100% free to 10-20% used. Everything looks normal, or at least nothing unusual. I can't remember what else I looked at in OAT but nothing stood out, from an OAT perspective Informix was running great. Question 2: Before we test one of these reports again, any suggestions of what I need to verify in my configuration? Or setup of Informix? Question 3: Ok the next time we test one of these reports what OS commands, Informix commands or sql should I be running to find what is truly going on? John Adamski Sr. Network Specialist Graceland University

Kind of a stab in the dark, but if Cognos is building logged temp tables, they could land in your rootdbs if you don't have a logged dbspace in your DBSPACETEMP list. --EEM >-----Original Message----- >From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of >John Adamski >Sent: Thursday, February 09, 2012 3:00 PM >To: ids@iiug.org >Subject: Cognos and Informix are not playing nice together [26216] > >IBM Informix Dynamic Server Version 11.50.FC6 (three 2GB tempdb space >configured; DBSPACETEMP temp0:temp1:temp2) HP-UX B.11.31 ia64 (BL870c >integrity server) Cognos 10.1.1 > >Note: >Since we get our Informix & Cognos licenses from our ERP provider we do >not have direct access to IBM support. And getting our ERP provider to >pass a problem to IBM and getting direct help from IBM is almost >impossible to do. > >This problem has made me conclude I no longer know anything about how >Informix works. > >The problem: > >We have a hand full of Congos reports that take large chucks of >resources when running. One report caused the rootdb space to fill up. >This one report would lead me to believe the temp/sort tables the sql >from the report generated used rootdb instead of the configured temp >dbspaces. I don't know Cognos and assumed it would let Informix handle >the sort/temp space allocation. > >Question 1: anyone know if Cognos somehow override what is configured in >the onconf file? Or have any theories why my rootdb space would fill >even when I have tempdb space configured and it almost empty? > >This report and the other handful of reports at least from the OS side >show the disk i/o goes to high 90s% for Informix. Also response time and >all other indications on the server itself show a slowing due to >Informix taking all disk i/o. > >If I go into OAT while the reports are running and look at storage, >rootdb space will slowly get measureable "other" chuck of space used >(20%) and index/data will fluctuate a little but seem stable. If I look >at the three temp spaces it goes from ~100% free to 10-20% used. >Everything looks normal, or at least nothing unusual. > >I can't remember what else I looked at in OAT but nothing stood out, >from an OAT perspective Informix was running great. > >Question 2: Before we test one of these reports again, any suggestions >of what I need to verify in my configuration? Or setup of Informix? > >Question 3: Ok the next time we test one of these reports what OS >commands, Informix commands or sql should I be running to find what is >truly going on? > >John Adamski >Sr. Network Specialist >Graceland University > > >************************************************************************ >******* > Forum Note: Use "Reply" to post a response in the discussion forum.

Make sure that TEMPTAB_NOLOG is set to '1' in your ONCONFIG file. That will force all temp tables to be unlogged and so, unless the CREATE TEMP TABLE statement has an IN <logged dbspace> clause it will be placed in one of the temp dbspaces. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Thu, Feb 9, 2012 at 4:00 PM, John Adamski <adamski@graceland.edu> wrote: > IBM Informix Dynamic Server Version 11.50.FC6 > (three 2GB tempdb space configured; DBSPACETEMP temp0:temp1:temp2) > HP-UX B.11.31 ia64 (BL870c integrity server) > Cognos 10.1.1 > > Note: > Since we get our Informix & Cognos licenses from our ERP provider we do not > have direct access to IBM support. And getting our ERP provider to pass a > problem to IBM and getting direct help from IBM is almost impossible to do. > > This problem has made me conclude I no longer know anything about how > Informix > works. > > The problem: > > We have a hand full of Congos reports that take large chucks of resources > when > running. One report caused the rootdb space to fill up. This one report > would > lead me to believe the temp/sort tables the sql from the report generated > used > rootdb instead of the configured temp dbspaces. I don't know Cognos and > assumed it would let Informix handle the sort/temp space allocation. > > Question 1: anyone know if Cognos somehow override what is configured in > the > onconf file? Or have any theories why my rootdb space would fill even when > I > have tempdb space configured and it almost empty? > > This report and the other handful of reports at least from the OS side show > the disk i/o goes to high 90s% for Informix. Also response time and all > other > indications on the server itself show a slowing due to Informix taking all > disk i/o. > > If I go into OAT while the reports are running and look at storage, rootdb > space will slowly get measureable "other" chuck of space used (20%) and > index/data will fluctuate a little but seem stable. If I look at the three > temp spaces it goes from ~100% free to 10-20% used. Everything looks > normal, > or at least nothing unusual. > > I can't remember what else I looked at in OAT but nothing stood out, from > an > OAT perspective Informix was running great. > > Question 2: Before we test one of these reports again, any suggestions of > what > I need to verify in my configuration? Or setup of Informix? > > Question 3: Ok the next time we test one of these reports what OS commands, > Informix commands or sql should I be running to find what is truly going > on? > > John Adamski > Sr. Network Specialist > Graceland University > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bafbfd5377704b88ef0c7

Art & Everett I checked and I do have TEMPTAB_NOLOG set to 1. Thanks for the idea. John -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, February 09, 2012 3:46 PM To: ids@iiug.org Subject: Re: Cognos and Informix are not playing nice t.... [26218] Make sure that TEMPTAB_NOLOG is set to '1' in your ONCONFIG file. That will force all temp tables to be unlogged and so, unless the CREATE TEMP TABLE statement has an IN <logged dbspace> clause it will be placed in one of the temp dbspaces. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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. On Thu, Feb 9, 2012 at 4:00 PM, John Adamski <adamski@graceland.edu> wrote: > IBM Informix Dynamic Server Version 11.50.FC6 (three 2GB tempdb space > configured; DBSPACETEMP temp0:temp1:temp2) HP-UX B.11.31 ia64 (BL870c > integrity server) Cognos 10.1.1 > > Note: > Since we get our Informix & Cognos licenses from our ERP provider we > do not have direct access to IBM support. And getting our ERP provider > to pass a problem to IBM and getting direct help from IBM is almost impossible to do. > > This problem has made me conclude I no longer know anything about how > Informix works. > > The problem: > > We have a hand full of Congos reports that take large chucks of > resources when running. One report caused the rootdb space to fill up. > This one report would lead me to believe the temp/sort tables the sql > from the report generated used rootdb instead of the configured temp > dbspaces. I don't know Cognos and assumed it would let Informix handle > the sort/temp space allocation. > > Question 1: anyone know if Cognos somehow override what is configured > in the onconf file? Or have any theories why my rootdb space would > fill even when I have tempdb space configured and it almost empty? > > This report and the other handful of reports at least from the OS side > show the disk i/o goes to high 90s% for Informix. Also response time > and all other indications on the server itself show a slowing due to > Informix taking all disk i/o. > > If I go into OAT while the reports are running and look at storage, > rootdb space will slowly get measureable "other" chuck of space used > (20%) and index/data will fluctuate a little but seem stable. If I > look at the three temp spaces it goes from ~100% free to 10-20% used. > Everything looks normal, or at least nothing unusual. > > I can't remember what else I looked at in OAT but nothing stood out, > from an OAT perspective Informix was running great. > > Question 2: Before we test one of these reports again, any suggestions > of what I need to verify in my configuration? Or setup of Informix? > > Question 3: Ok the next time we test one of these reports what OS > commands, Informix commands or sql should I be running to find what is > truly going on? > > John Adamski > Sr. Network Specialist > Graceland University > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bafbfd5377704b88ef0c7 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.