Informix 11 to 12 Upgrade ... "error"
After upgrading from IDS 11.70.FC8 to 12.10.FC4 on AIX 6, a statement of the form CREATE TEMP TABLE ... WITH NO LOG IN inhousedbs (a regular, non-temp dbspace) began failing with SQL -229 / ISAM -130 'no such DBspace', though it had worked under 11.70. The poster suspected 12.10 now requires temp tables to go into real temp dbspaces. Art Kagel suggested TEMPTAB_NOLOG might be involved and asked for the effective value; Fernando Nunes noted a commented-out parameter takes its default (0) and that sysmaster:syscfgtab shows default/configured/effective values. The poster concluded TEMPTAB_NOLOG was inactive in both versions and so played no part. No definitive confirmation or fix is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
2 Versions:
From: Version 11.70.FC8
To: Version 12.10.FC4
AIX 6
Ok, here goes. Please note that I place "error" in quotes, because I do not
believe this is an error. I just want to confirm my theory.
We did a test upgrade from version 11 to version 12, and the following
statement failed on version 12 (where it was working previously on version 11)
CREATE TEMP TABLE tmp_temptable
(
psh_id INTEGER,
psh_service_code CHAR(4),
psh_msisdn_no CHAR(15),
psh_subscriber_id INTEGER,
psd_param_id INTEGER,
psd_param_value CHAR(20)
) WITH NO LOG IN inhousedbs
SQL statement error number -229.
Could not open or create a temporary file.
SYSTEM error number -130.
ISAM error: no such DBspace
Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but an actual
dbspace. I believe that the previous version of Informix was less strict, and
still allowed this syntax, but that version 12 tightened the rules, and now
forces all TEMP tables to go to actually TEMP dbspaces.
So I do not believe this to be an error, but the way Informix is actually
supposed to work.
Can someone confirm this please ?
Regards
Dirk
________________________________
NOTE: This e-mail message is subject to the MTN Group disclaimer see
https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Dirk:
This is definitely a "it's supposed to work that way." But there may be a
contributing item. Do you have TEMPTAB_NOLOG set in both versions'
ONCONFIG or only in the v12 ONCONFIG?
Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-logging
unless they are placed, without the WITH NO LOG clause, in a logged temp
table. Without TEMPTAB_NOLOG set the engine probably creates the temp
table in the logged dbspace but makes it a logged temp table despite the
WITH NO LOG clause. With that clause set it is rejecting the request.
That might be a change in behavior between 11.70 and 12.10. Dunno.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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.
On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma.... <Dirk.Moolman@mtn.co.za>
wrote:
> 2 Versions:
>
> From: Version 11.70.FC8
> To: Version 12.10.FC4
>
> AIX 6
>
> Ok, here goes. Please note that I place "error" in quotes, because I do not
> believe this is an error. I just want to confirm my theory.
>
> We did a test upgrade from version 11 to version 12, and the following
> statement failed on version 12 (where it was working previously on version
> 11)
>
> CREATE TEMP TABLE tmp_temptable>
> (
>
> psh_id INTEGER,
>
> psh_service_code CHAR(4),
>
> psh_msisdn_no CHAR(15),
>
> psh_subscriber_id INTEGER,
>
> psd_param_id INTEGER,
>
> psd_param_value CHAR(20)
>
> ) WITH NO LOG IN inhousedbs
>
> SQL statement error number -229.
> Could not open or create a temporary file.
> SYSTEM error number -130.
> ISAM error: no such DBspace>
> Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but an
> actual
> dbspace. I believe that the previous version of Informix was less strict,
> and
> still allowed this syntax, but that version 12 tightened the rules, and now
> forces all TEMP tables to go to actually TEMP dbspaces.
>
> So I do not believe this to be an error, but the way Informix is actually
> supposed to work.
>
> Can someone confirm this please ?
>
> Regards
> Dirk
>
> ________________________________
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3bd50dbc0c4050251659a
Ok, interesting.
In version 11 it is in the ONCONFIG:
TEMPTAB_NOLOG 0
But in version 12, the new version, it is hashed out completely
#TEMPTAB_NOLOG 0
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, 05 September 2014 03:25 PM
> To: ids@iiug.org
> Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
>
> Dirk:
>
> This is definitely a "it's supposed to work that way." But there may be
> a contributing item. Do you have TEMPTAB_NOLOG set in both versions'
> ONCONFIG or only in the v12 ONCONFIG?
>
> Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-
> logging unless they are placed, without the WITH NO LOG clause, in a
> logged temp table. Without TEMPTAB_NOLOG set the engine probably
> creates the temp table in the logged dbspace but makes it a logged temp
> table despite the WITH NO LOG clause. With that clause set it is
> rejecting the request.
> That might be a change in behavior between 11.70 and 12.10. Dunno.
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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.
>
> On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> <Dirk.Moolman@mtn.co.za>
> wrote:
>
> > 2 Versions:
> >
> > From: Version 11.70.FC8
> > To: Version 12.10.FC4
> >
> > AIX 6
> >
> > Ok, here goes. Please note that I place "error" in quotes, because I
> > do not believe this is an error. I just want to confirm my theory.
> >
> > We did a test upgrade from version 11 to version 12, and the
> following
> > statement failed on version 12 (where it was working previously on
> > version
> > 11)
> >
> > CREATE TEMP TABLE tmp_temptable> >
> > (
> >
> > psh_id INTEGER,
> >
> > psh_service_code CHAR(4),
> >
> > psh_msisdn_no CHAR(15),
> >
> > psh_subscriber_id INTEGER,
> >
> > psd_param_id INTEGER,
> >
> > psd_param_value CHAR(20)
> >
> > ) WITH NO LOG IN inhousedbs
> >
> > SQL statement error number -229.
> > Could not open or create a temporary file.
> > SYSTEM error number -130.
> > ISAM error: no such DBspace> >
> > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but an
> > actual dbspace. I believe that the previous version of Informix was
> > less strict, and still allowed this syntax, but that version 12
> > tightened the rules, and now forces all TEMP tables to go to actually
> > TEMP dbspaces.
> >
> > So I do not believe this to be an error, but the way Informix is
> > actually supposed to work.
> >
> > Can someone confirm this please ?
> >
> > Regards
> > Dirk
> >
> > ________________________________
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a11c3bd50dbc0c4050251659a
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
________________________________
NOTE: This e-mail message is subject to the MTN Group disclaimer see
https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
I assume that both have the same meaning
0 = not set
Hashed out = not set
?
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Dirk Moolma....
> Sent: Friday, 05 September 2014 03:37 PM
> To: ids@iiug.org
> Subject: RE: Informix 11 to 12 Upgrade ... "error" [33685]
>
> Ok, interesting.
>
> In version 11 it is in the ONCONFIG:
>
> TEMPTAB_NOLOG 0>
> But in version 12, the new version, it is hashed out completely
>
> #TEMPTAB_NOLOG 0
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Friday, 05 September 2014 03:25 PM
> > To: ids@iiug.org
> > Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
> >
> > Dirk:
> >
> > This is definitely a "it's supposed to work that way." But there may
> > be a contributing item. Do you have TEMPTAB_NOLOG set in both
> versions'
> > ONCONFIG or only in the v12 ONCONFIG?
> >
> > Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-
> > logging unless they are placed, without the WITH NO LOG clause, in a
> > logged temp table. Without TEMPTAB_NOLOG set the engine probably
> > creates the temp table in the logged dbspace but makes it a logged
> > temp table despite the WITH NO LOG clause. With that clause set it is
> > rejecting the request.
> > That might be a change in behavior between 11.70 and 12.10. Dunno.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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.
> >
> > On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> > <Dirk.Moolman@mtn.co.za>
> > wrote:
> >
> > > 2 Versions:
> > >
> > > From: Version 11.70.FC8
> > > To: Version 12.10.FC4
> > >
> > > AIX 6
> > >
> > > Ok, here goes. Please note that I place "error" in quotes, because
> I
> > > do not believe this is an error. I just want to confirm my theory.
> > >
> > > We did a test upgrade from version 11 to version 12, and the
> > following
> > > statement failed on version 12 (where it was working previously on
> > > version
> > > 11)
> > >
> > > CREATE TEMP TABLE tmp_temptable> > >
> > > (
> > >
> > > psh_id INTEGER,
> > >
> > > psh_service_code CHAR(4),
> > >
> > > psh_msisdn_no CHAR(15),
> > >
> > > psh_subscriber_id INTEGER,
> > >
> > > psd_param_id INTEGER,
> > >
> > > psd_param_value CHAR(20)
> > >
> > > ) WITH NO LOG IN inhousedbs
> > >
> > > SQL statement error number -229.
> > > Could not open or create a temporary file.
> > > SYSTEM error number -130.
> > > ISAM error: no such DBspace> > >
> > > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but
> > > an actual dbspace. I believe that the previous version of Informix
> > > was less strict, and still allowed this syntax, but that version 12
> > > tightened the rules, and now forces all TEMP tables to go to
> > > actually TEMP dbspaces.
> > >
> > > So I do not believe this to be an error, but the way Informix is
> > > actually supposed to work.
> > >
> > > Can someone confirm this please ?
> > >
> > > Regards
> > > Dirk
> > >
> > > ________________________________
> > >
> > > NOTE: This e-mail message is subject to the MTN Group disclaimer
> see
> > > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > >
> > >
> > >
> > >
> >
> **********************************************************************
> > *
> > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a11c3bd50dbc0c4050251659a
> >
> >
> >
> **********************************************************************
> > *
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> ________________________________
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
________________________________
NOTE: This e-mail message is subject to the MTN Group disclaimer see
https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
Hashed out is "not set". But then you have to consider what is the default.
In this case is "0".
If you ever get in doubt about that query sysmaster:syscfgtab where cf_name
= 'YOUR_PARAMETER'.
You'll see the default, the configured value (if any) and the effective
(which could change with onmode -wm)
Regards
On Fri, Sep 5, 2014 at 2:45 PM, Dirk Moolma.... <Dirk.Moolman@mtn.co.za>
wrote:
> I assume that both have the same meaning
>
> 0 = not set
>
> Hashed out = not set
>
> ?
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Dirk Moolma....
> > Sent: Friday, 05 September 2014 03:37 PM
> > To: ids@iiug.org
> > Subject: RE: Informix 11 to 12 Upgrade ... "error" [33685]
> >
> > Ok, interesting.
> >
> > In version 11 it is in the ONCONFIG:
> >
> > TEMPTAB_NOLOG 0> >
> > But in version 12, the new version, it is hashed out completely
> >
> > #TEMPTAB_NOLOG 0
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > Art Kagel
> > > Sent: Friday, 05 September 2014 03:25 PM
> > > To: ids@iiug.org
> > > Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
> > >
> > > Dirk:
> > >
> > > This is definitely a "it's supposed to work that way." But there may
> > > be a contributing item. Do you have TEMPTAB_NOLOG set in both
> > versions'
> > > ONCONFIG or only in the v12 ONCONFIG?
> > >
> > > Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-
> > > logging unless they are placed, without the WITH NO LOG clause, in a
> > > logged temp table. Without TEMPTAB_NOLOG set the engine probably
> > > creates the temp table in the logged dbspace but makes it a logged
> > > temp table despite the WITH NO LOG clause. With that clause set it is
> > > rejecting the request.
> > > That might be a change in behavior between 11.70 and 12.10. Dunno.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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.
> > >
> > > On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> > > <Dirk.Moolman@mtn.co.za>
> > > wrote:
> > >
> > > > 2 Versions:
> > > >
> > > > From: Version 11.70.FC8
> > > > To: Version 12.10.FC4
> > > >
> > > > AIX 6
> > > >
> > > > Ok, here goes. Please note that I place "error" in quotes, because
> > I
> > > > do not believe this is an error. I just want to confirm my theory.
> > > >
> > > > We did a test upgrade from version 11 to version 12, and the
> > > following
> > > > statement failed on version 12 (where it was working previously on
> > > > version
> > > > 11)
> > > >
> > > > CREATE TEMP TABLE tmp_temptable> > > >
> > > > (
> > > >
> > > > psh_id INTEGER,
> > > >
> > > > psh_service_code CHAR(4),
> > > >
> > > > psh_msisdn_no CHAR(15),
> > > >
> > > > psh_subscriber_id INTEGER,
> > > >
> > > > psd_param_id INTEGER,
> > > >
> > > > psd_param_value CHAR(20)
> > > >
> > > > ) WITH NO LOG IN inhousedbs
> > > >
> > > > SQL statement error number -229.
> > > > Could not open or create a temporary file.
> > > > SYSTEM error number -130.
> > > > ISAM error: no such DBspace> > > >
> > > > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but
> > > > an actual dbspace. I believe that the previous version of Informix
> > > > was less strict, and still allowed this syntax, but that version 12
> > > > tightened the rules, and now forces all TEMP tables to go to
> > > > actually TEMP dbspaces.
> > > >
> > > > So I do not believe this to be an error, but the way Informix is
> > > > actually supposed to work.
> > > >
> > > > Can someone confirm this please ?
> > > >
> > > > Regards
> > > > Dirk
> > > >
> > > > ________________________________
> > > >
> > > > NOTE: This e-mail message is subject to the MTN Group disclaimer
> > see
> > > > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > > >
> > > >
> > > >
> > > >
> > >
> > **********************************************************************
> > > *
> > > ********
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001a11c3bd50dbc0c4050251659a
> > >
> > >
> > >
> > **********************************************************************
> > > *
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> > ________________________________
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> ________________________________
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--bcaec51869dc919eff050251be41
Hmm, the documentation doesn't say what the default is if you do not have
it set in the ONCONFIG. Dirk: What is show for the effective value for
that in sysmaster:sysconfig?
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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.
On Fri, Sep 5, 2014 at 9:36 AM, Dirk Moolma.... <Dirk.Moolman@mtn.co.za>
wrote:
> Ok, interesting.
>
> In version 11 it is in the ONCONFIG:
>
> TEMPTAB_NOLOG 0>
> But in version 12, the new version, it is hashed out completely
>
> #TEMPTAB_NOLOG 0
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Friday, 05 September 2014 03:25 PM
> > To: ids@iiug.org
> > Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
> >
> > Dirk:
> >
> > This is definitely a "it's supposed to work that way." But there may be
> > a contributing item. Do you have TEMPTAB_NOLOG set in both versions'
> > ONCONFIG or only in the v12 ONCONFIG?
> >
> > Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-
> > logging unless they are placed, without the WITH NO LOG clause, in a
> > logged temp table. Without TEMPTAB_NOLOG set the engine probably
> > creates the temp table in the logged dbspace but makes it a logged temp
> > table despite the WITH NO LOG clause. With that clause set it is
> > rejecting the request.
> > That might be a change in behavior between 11.70 and 12.10. Dunno.
> >
> > Art
> >
> > Art S. Kagel, Principal Consultant
> > ASK Database Management
> >
> > 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.
> >
> > On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> > <Dirk.Moolman@mtn.co.za>
> > wrote:
> >
> > > 2 Versions:
> > >
> > > From: Version 11.70.FC8
> > > To: Version 12.10.FC4
> > >
> > > AIX 6
> > >
> > > Ok, here goes. Please note that I place "error" in quotes, because I
> > > do not believe this is an error. I just want to confirm my theory.
> > >
> > > We did a test upgrade from version 11 to version 12, and the
> > following
> > > statement failed on version 12 (where it was working previously on
> > > version
> > > 11)
> > >
> > > CREATE TEMP TABLE tmp_temptable> > >
> > > (
> > >
> > > psh_id INTEGER,
> > >
> > > psh_service_code CHAR(4),
> > >
> > > psh_msisdn_no CHAR(15),
> > >
> > > psh_subscriber_id INTEGER,
> > >
> > > psd_param_id INTEGER,
> > >
> > > psd_param_value CHAR(20)
> > >
> > > ) WITH NO LOG IN inhousedbs
> > >
> > > SQL statement error number -229.
> > > Could not open or create a temporary file.
> > > SYSTEM error number -130.
> > > ISAM error: no such DBspace> > >
> > > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but an
> > > actual dbspace. I believe that the previous version of Informix was
> > > less strict, and still allowed this syntax, but that version 12
> > > tightened the rules, and now forces all TEMP tables to go to actually
> > > TEMP dbspaces.
> > >
> > > So I do not believe this to be an error, but the way Informix is
> > > actually supposed to work.
> > >
> > > Can someone confirm this please ?
> > >
> > > Regards
> > > Dirk
> > >
> > > ________________________________
> > >
> > > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > >
> > >
> > >
> > >
> > ***********************************************************************
> > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a11c3bd50dbc0c4050251659a
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> ________________________________
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0158ad540b3d66050251c071
But all ONCONFIG variables have an explicit NOT SET value usually described
in the Administrator's Reference manual. This one is not described, so I
don't know what the "default" value is.
Art
Art S. Kagel, Principal Consultant
ASK Database Management
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.
On Fri, Sep 5, 2014 at 9:45 AM, Dirk Moolma.... <Dirk.Moolman@mtn.co.za>
wrote:
> I assume that both have the same meaning
>
> 0 = not set
>
> Hashed out = not set
>
> ?
>
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Dirk Moolma....
> > Sent: Friday, 05 September 2014 03:37 PM
> > To: ids@iiug.org
> > Subject: RE: Informix 11 to 12 Upgrade ... "error" [33685]
> >
> > Ok, interesting.
> >
> > In version 11 it is in the ONCONFIG:
> >
> > TEMPTAB_NOLOG 0> >
> > But in version 12, the new version, it is hashed out completely
> >
> > #TEMPTAB_NOLOG 0
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > > Art Kagel
> > > Sent: Friday, 05 September 2014 03:25 PM
> > > To: ids@iiug.org
> > > Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
> > >
> > > Dirk:
> > >
> > > This is definitely a "it's supposed to work that way." But there may
> > > be a contributing item. Do you have TEMPTAB_NOLOG set in both
> > versions'
> > > ONCONFIG or only in the v12 ONCONFIG?
> > >
> > > Guessing here: If TEMPTAB_NOLOG is set then all temp tables are non-
> > > logging unless they are placed, without the WITH NO LOG clause, in a
> > > logged temp table. Without TEMPTAB_NOLOG set the engine probably
> > > creates the temp table in the logged dbspace but makes it a logged
> > > temp table despite the WITH NO LOG clause. With that clause set it is
> > > rejecting the request.
> > > That might be a change in behavior between 11.70 and 12.10. Dunno.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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.
> > >
> > > On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> > > <Dirk.Moolman@mtn.co.za>
> > > wrote:
> > >
> > > > 2 Versions:
> > > >
> > > > From: Version 11.70.FC8
> > > > To: Version 12.10.FC4
> > > >
> > > > AIX 6
> > > >
> > > > Ok, here goes. Please note that I place "error" in quotes, because
> > I
> > > > do not believe this is an error. I just want to confirm my theory.
> > > >
> > > > We did a test upgrade from version 11 to version 12, and the
> > > following
> > > > statement failed on version 12 (where it was working previously on
> > > > version
> > > > 11)
> > > >
> > > > CREATE TEMP TABLE tmp_temptable> > > >
> > > > (
> > > >
> > > > psh_id INTEGER,
> > > >
> > > > psh_service_code CHAR(4),
> > > >
> > > > psh_msisdn_no CHAR(15),
> > > >
> > > > psh_subscriber_id INTEGER,
> > > >
> > > > psd_param_id INTEGER,
> > > >
> > > > psd_param_value CHAR(20)
> > > >
> > > > ) WITH NO LOG IN inhousedbs
> > > >
> > > > SQL statement error number -229.
> > > > Could not open or create a temporary file.
> > > > SYSTEM error number -130.
> > > > ISAM error: no such DBspace> > > >
> > > > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP), but
> > > > an actual dbspace. I believe that the previous version of Informix
> > > > was less strict, and still allowed this syntax, but that version 12
> > > > tightened the rules, and now forces all TEMP tables to go to
> > > > actually TEMP dbspaces.
> > > >
> > > > So I do not believe this to be an error, but the way Informix is
> > > > actually supposed to work.
> > > >
> > > > Can someone confirm this please ?
> > > >
> > > > Regards
> > > > Dirk
> > > >
> > > > ________________________________
> > > >
> > > > NOTE: This e-mail message is subject to the MTN Group disclaimer
> > see
> > > > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > > >
> > > >
> > > >
> > > >
> > >
> > **********************************************************************
> > > *
> > > ********
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --001a11c3bd50dbc0c4050251659a
> > >
> > >
> > >
> > **********************************************************************
> > > *
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> > ________________________________
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> > ***********************************************************************
> > ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
> ________________________________
>
> NOTE: This e-mail message is subject to the MTN Group disclaimer see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c372be3db420050251c6df
Documentation:
TEMPTAB_NOLOG
range of values
0 = Enable logical logging on temporary table operations
1 = Disable logical logging on temporary table operations
So in this case the parameter played no role. It wasn't "active" in both cases
/ both versions.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, 05 September 2014 03:52 PM
> To: ids@iiug.org
> Subject: Re: Informix 11 to 12 Upgrade ... "error" [33688]
>
> Hmm, the documentation doesn't say what the default is if you do not
> have it set in the ONCONFIG. Dirk: What is show for the effective value
> for that in sysmaster:sysconfig?
>
> Art
>
> Art S. Kagel, Principal Consultant
> ASK Database Management
>
> 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.
>
> On Fri, Sep 5, 2014 at 9:36 AM, Dirk Moolma....
> <Dirk.Moolman@mtn.co.za>
> wrote:
>
> > Ok, interesting.
> >
> > In version 11 it is in the ONCONFIG:
> >
> > TEMPTAB_NOLOG 0> >
> > But in version 12, the new version, it is hashed out completely
> >
> > #TEMPTAB_NOLOG 0
> >
> > > -----Original Message-----
> > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > > Of Art Kagel
> > > Sent: Friday, 05 September 2014 03:25 PM
> > > To: ids@iiug.org
> > > Subject: Re: Informix 11 to 12 Upgrade ... "error" [33684]
> > >
> > > Dirk:
> > >
> > > This is definitely a "it's supposed to work that way." But there
> may
> > > be a contributing item. Do you have TEMPTAB_NOLOG set in both
> versions'
> > > ONCONFIG or only in the v12 ONCONFIG?
> > >
> > > Guessing here: If TEMPTAB_NOLOG is set then all temp tables are
> non-
> > > logging unless they are placed, without the WITH NO LOG clause, in
> a
> > > logged temp table. Without TEMPTAB_NOLOG set the engine probably
> > > creates the temp table in the logged dbspace but makes it a logged
> > > temp table despite the WITH NO LOG clause. With that clause set it
> > > is rejecting the request.
> > > That might be a change in behavior between 11.70 and 12.10. Dunno.
> > >
> > > Art
> > >
> > > Art S. Kagel, Principal Consultant
> > > ASK Database Management
> > >
> > > 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.
> > >
> > > On Fri, Sep 5, 2014 at 9:07 AM, Dirk Moolma....
> > > <Dirk.Moolman@mtn.co.za>
> > > wrote:
> > >
> > > > 2 Versions:
> > > >
> > > > From: Version 11.70.FC8
> > > > To: Version 12.10.FC4
> > > >
> > > > AIX 6
> > > >
> > > > Ok, here goes. Please note that I place "error" in quotes,
> because
> > > > I do not believe this is an error. I just want to confirm my
> theory.
> > > >
> > > > We did a test upgrade from version 11 to version 12, and the
> > > following
> > > > statement failed on version 12 (where it was working previously
> on
> > > > version
> > > > 11)
> > > >
> > > > CREATE TEMP TABLE tmp_temptable> > > >
> > > > (
> > > >
> > > > psh_id INTEGER,
> > > >
> > > > psh_service_code CHAR(4),
> > > >
> > > > psh_msisdn_no CHAR(15),
> > > >
> > > > psh_subscriber_id INTEGER,
> > > >
> > > > psd_param_id INTEGER,
> > > >
> > > > psd_param_value CHAR(20)
> > > >
> > > > ) WITH NO LOG IN inhousedbs
> > > >
> > > > SQL statement error number -229.
> > > > Could not open or create a temporary file.
> > > > SYSTEM error number -130.
> > > > ISAM error: no such DBspace> > > >
> > > > Also note that "inhousedbs" is not a TEMP space (DBSPACETEMP),
> but
> > > > an actual dbspace. I believe that the previous version of
> Informix
> > > > was less strict, and still allowed this syntax, but that version
> > > > 12 tightened the rules, and now forces all TEMP tables to go to
> > > > actually TEMP dbspaces.
> > > >
> > > > So I do not believe this to be an error, but the way Informix is
> > > > actually supposed to work.
> > > >
> > > > Can someone confirm this please ?
> > > >
> > > > Regards
> > > > Dirk
> > > >
> > > > ________________________________
> > > >
> > > > NOTE: This e-mail message is subject to the MTN Group disclaimer
> > > > see
> https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> > > >
> > > >
> > > >
> > > >
> > >
> ********************************************************************
> > > ***
> > > ********
> > > > Forum Note: Use "Reply" to post a response in the discussion
> forum.
> > > >
> > > >
> > >
> > > --001a11c3bd50dbc0c4050251659a
> > >
> > >
> > >
> ********************************************************************
> > > ***
> > > ********
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> > ________________________________
> >
> > NOTE: This e-mail message is subject to the MTN Group disclaimer see
> > https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx
> >
> >
> >
> >
> ***********************************************************************
> ********
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --089e0158ad540b3d66050251c071
>
>
> ***********************************************************************
> ********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
________________________________
NOTE: This e-mail message is subject to the MTN Group disclaimer see
https://www.mtn.co.za/SUPPORT/LEGAL/Pages/EmailDisclaimer.aspx