Odd one
Art Kagel's utility creates a temp table and queries it using ROWIDs, but when DBSPACETEMP lists several temp dbspaces the table is fragmented round-robin and the query fails with error -857 (ROWIDs do not exist). He didn't want to blindly add WITH ROWIDs, parse DBSPACETEMP, or query sysmaster, fearing sysmaster is unreachable from an unlogged database. John Miller confirmed sysmaster has been exempt from the same-logging-mode restriction since v7, so it can always be queried, and suggested selecting one temp dbspace (flag 0x2000 in sysmaster:sysdbstab, using bitand or bitval). Art adopted that: pick a single temp dbspace and name it explicitly in the CREATE TEMP TABLE ... IN clause. A rownum() suggestion was rejected as not available back to 11.50.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks: I have a temp table that I create in one of my tools. A query against that temp table uses rowids. If there are more than one temp dbspace listed in DBSPACETEMP the temp table is apparently being created fragmented across the multiple temp dbspaces because the query returns a -857 error (ROWIDs do not exist for table). There is no problem as long as there is only one temp dbspace listed in DBSPACETEMP. Since the utility cannot depend on there being only one temp dbspace, and I cannot include the WITH ROWIDs clause since there might be only a single temp dbspace, does anyone have a guess how I can safely create this temp table so it is usable? Looking at reformulating the query to not use ROWIDs, to detecting the number of temp dbspaces, and to explicitly including an IN clause specifying a single temp dbspace selected from sysmaster:sysdbspaces. However, none of these options is ideal. Example: if the current database has no logging I won't be able to get access to sysmaster. Parsing DBSPACETEMP isn't ideal either because a logged dbspace could be listed there. Ideas? 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. --001a1140c18a0abaa9051b035a6e
Haven't tried this myself, but could you explicitly put the temp table in
only 1 temp dbspace to avoid the round robin fragmentation?
create temp table t1 (f int) with no log in tempdbs01.
You would have to query sysmaster for a dbspace with is_temp set to 1
beforehand and maybe use rootdbs if a temp dbspace isn't found.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, July 16, 2015 2:48 PM
To: ids@iiug.org
Subject: Odd one [35474]
Folks:
I have a temp table that I create in one of my tools. A query against that
temp table uses rowids. If there are more than one temp dbspace listed in
DBSPACETEMP the temp table is apparently being created fragmented across the
multiple temp dbspaces because the query returns a -857 error (ROWIDs do not
exist for table). There is no problem as long as there is only one temp
dbspace listed in DBSPACETEMP. Since the utility cannot depend on there
being only one temp dbspace, and I cannot include the WITH ROWIDs clause
since there might be only a single temp dbspace, does anyone have a guess
how I can safely create this temp table so it is usable?
Looking at reformulating the query to not use ROWIDs, to detecting the
number of temp dbspaces, and to explicitly including an IN clause specifying
a single temp dbspace selected from sysmaster:sysdbspaces.
However, none of these options is ideal. Example: if the current database
has no logging I won't be able to get access to sysmaster. Parsing
DBSPACETEMP isn't ideal either because a logged dbspace could be listed
there.
Ideas?
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.
--001a1140c18a0abaa9051b035a6e
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
ignore my response, just saw you said this:
" to detecting the number of temp dbspaces, and to explicitly including an
IN clause specifying a single temp dbspace selected from
sysmaster:sysdbspaces."
i read good.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Andrew
Ford
Sent: Thursday, July 16, 2015 3:07 PM
To: ids@iiug.org
Subject: RE: Odd one [35476]
Haven't tried this myself, but could you explicitly put the temp table in
only 1 temp dbspace to avoid the round robin fragmentation?
create temp table t1 (f int) with no log in tempdbs01.
You would have to query sysmaster for a dbspace with is_temp set to 1
beforehand and maybe use rootdbs if a temp dbspace isn't found.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Thursday, July 16, 2015 2:48 PM
To: ids@iiug.org
Subject: Odd one [35474]
Folks:
I have a temp table that I create in one of my tools. A query against that
temp table uses rowids. If there are more than one temp dbspace listed in
DBSPACETEMP the temp table is apparently being created fragmented across the
multiple temp dbspaces because the query returns a -857 error (ROWIDs do not
exist for table). There is no problem as long as there is only one temp
dbspace listed in DBSPACETEMP. Since the utility cannot depend on there
being only one temp dbspace, and I cannot include the WITH ROWIDs clause
since there might be only a single temp dbspace, does anyone have a guess
how I can safely create this temp table so it is usable?
Looking at reformulating the query to not use ROWIDs, to detecting the
number of temp dbspaces, and to explicitly including an IN clause specifying
a single temp dbspace selected from sysmaster:sysdbspaces.
However, none of these options is ideal. Example: if the current database
has no logging I won't be able to get access to sysmaster. Parsing
DBSPACETEMP isn't ideal either because a logged dbspace could be listed
there.
Ideas?
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.
--001a1140c18a0abaa9051b035a6e
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
If the current database is no-logging and you query sysmaster this WILL
work as sysmaster is exempt from the logging restriction requiring all
databases to be of the same logging.
create database art;
select count(*)
from sysmaster:systables SS, systables S
where SS.tabid=3D S.tabid;
(count(*))
70
1 row(s) retrieved.
My suggestion would be to use the following:
select first 1 name from sysmaster:sysdbstab where bitand
(flags,'0x2000')>1;
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/16/2015 12:48:18 PM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 07/16/2015 12:53 PM
> Subject: Odd one [35474]
> Sent by: ids-bounces@iiug.org
>
> Folks:
>
> I have a temp table that I create in one of my tools. A query against
that
> temp table uses rowids. If there are more than one temp dbspace listed in
> DBSPACETEMP the temp table is apparently being created fragmented across
> the multiple temp dbspaces because the query returns a -857 error (ROWIDs
> do not exist for table). There is no problem as long as there is only one
> temp dbspace listed in DBSPACETEMP. Since the utility cannot depend on
> there being only one temp dbspace, and I cannot include the WITH ROWIDs
> clause since there might be only a single temp dbspace, does anyone have
a
> guess how I can safely create this temp table so it is usable?
>
> Looking at reformulating the query to not use ROWIDs, to detecting the
> number of temp dbspaces, and to explicitly including an IN clause
> specifying a single temp dbspace selected from sysmaster:sysdbspaces.
> However, none of these options is ideal. Example: if the current database
> has no logging I won't be able to get access to sysmaster. Parsing
> DBSPACETEMP isn't ideal either because a logged dbspace could be listed
> there.
>
> Ideas?
>
> 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.
>
> --001a1140c18a0abaa9051b035a6e
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Is that exemption for sysmaster throughout all versions since 11.50 and
later or is it more recent?
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.
On Thu, Jul 16, 2015 at 4:53 PM, John Miller iii <miller3@us.ibm.com> wrote:
> If the current database is no-logging and you query sysmaster this WILL
> work as sysmaster is exempt from the logging restriction requiring all
> databases to be of the same logging.
>
> create database art;
> select count(*)
> from sysmaster:systables SS, systables S
> where SS.tabid=3D S.tabid;>
> (count(*))
>
> 70
>
> 1 row(s) retrieved.
>
> My suggestion would be to use the following:
>
> select first 1 name from sysmaster:sysdbstab where bitand
> (flags,'0x2000')>1;>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 07/16/2015 12:48:18 PM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 07/16/2015 12:53 PM
> > Subject: Odd one [35474]
> > Sent by: ids-bounces@iiug.org
> >
> > Folks:
> >
> > I have a temp table that I create in one of my tools. A query against
> that
> > temp table uses rowids. If there are more than one temp dbspace listed in
>
> > DBSPACETEMP the temp table is apparently being created fragmented across
> > the multiple temp dbspaces because the query returns a -857 error (ROWIDs
>
> > do not exist for table). There is no problem as long as there is only one
>
> > temp dbspace listed in DBSPACETEMP. Since the utility cannot depend on
> > there being only one temp dbspace, and I cannot include the WITH ROWIDs
> > clause since there might be only a single temp dbspace, does anyone have
> a
> > guess how I can safely create this temp table so it is usable?
> >
> > Looking at reformulating the query to not use ROWIDs, to detecting the
> > number of temp dbspaces, and to explicitly including an IN clause
> > specifying a single temp dbspace selected from sysmaster:sysdbspaces.
> > However, none of these options is ideal. Example: if the current database
>
> > has no logging I won't be able to get access to sysmaster. Parsing
> > DBSPACETEMP isn't ideal either because a logged dbspace could be listed
> > there.
> >
> > Ideas?
> >
> > 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.
> >
> > --001a1140c18a0abaa9051b035a6e
> >
> >
> >
>
> ***************************************************************************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1140c18a43e4f9051b049756
Art You posted about rownum() function in a blog in Sept 2012, is that a possible solution ?? Keith On 16 July 2015 at 20:48, Art Kagel <art.kagel@gmail.com> wrote: > Folks: > > I have a temp table that I create in one of my tools. A query against that > temp table uses rowids. If there are more than one temp dbspace listed in > DBSPACETEMP the temp table is apparently being created fragmented across > the multiple temp dbspaces because the query returns a -857 error (ROWIDs > do not exist for table). There is no problem as long as there is only one > temp dbspace listed in DBSPACETEMP. Since the utility cannot depend on > there being only one temp dbspace, and I cannot include the WITH ROWIDs > clause since there might be only a single temp dbspace, does anyone have a > guess how I can safely create this temp table so it is usable? > > Looking at reformulating the query to not use ROWIDs, to detecting the > number of temp dbspaces, and to explicitly including an IN clause > specifying a single temp dbspace selected from sysmaster:sysdbspaces. > However, none of these options is ideal. Example: if the current database > has no logging I won't be able to get access to sysmaster. Parsing > DBSPACETEMP isn't ideal either because a logged dbspace could be listed > there. > > Ideas? > > 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. > > --001a1140c18a0abaa9051b035a6e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7bae49444c95a6051b04dcf3
This logging exception was introduced in version 7.
I do not remember which version the bit operators where introduced. I
think 11.10, but you can use sysmaster:bitval() to be safe.
John F. Miller III
STSM, Lead Architect
miller3@us.ibm.com
503-747-1366
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 07/16/2015 02:17:18 PM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org
> Date: 07/16/2015 02:18 PM
> Subject: Re: Odd one [35481]
> Sent by: ids-bounces@iiug.org
>
> Is that exemption for sysmaster throughout all versions since 11.50 and
> later or is it more recent?
>
> 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.
>
> On Thu, Jul 16, 2015 at 4:53 PM, John Miller iii <miller3@us.ibm.com>
wrote:
>
> > If the current database is no-logging and you query sysmaster this WILL
> > work as sysmaster is exempt from the logging restriction requiring all
> > databases to be of the same logging.
> >
> > create database art;
> > select count(*)
> > from sysmaster:systables SS, systables S
> > where SS.tabid=3D3D S.tabid;> >
> > (count(*))
> >
> > 70
> >
> > 1 row(s) retrieved.
> >
> > My suggestion would be to use the following:
> >
> > select first 1 name from sysmaster:sysdbstab where bitand
> > (flags,'0x2000')>1;> >
> > John F. Miller III
> > STSM, Lead Architect
> > miller3@us.ibm.com
> > 503-747-1366
> > IBM Informix Dynamic Server (IDS)
> >
> > ids-bounces@iiug.org wrote on 07/16/2015 12:48:18 PM:
> >
> > > From: "Art Kagel" <art.kagel@gmail.com>
> > > To: ids@iiug.org
> > > Date: 07/16/2015 12:53 PM
> > > Subject: Odd one [35474]
> > > Sent by: ids-bounces@iiug.org
> > >
> > > Folks:
> > >
> > > I have a temp table that I create in one of my tools. A query against
> > that
> > > temp table uses rowids. If there are more than one temp dbspace
listed in
> >
> > > DBSPACETEMP the temp table is apparently being created fragmented
across
> > > the multiple temp dbspaces because the query returns a -857 error
(ROWIDs
> >
> > > do not exist for table). There is no problem as long as there isonly
one
> >
> > > temp dbspace listed in DBSPACETEMP. Since the utility cannot depend
on
> > > there being only one temp dbspace, and I cannot include the WITH
ROWIDs
> > > clause since there might be only a single temp dbspace, does anyone
have
> > a
> > > guess how I can safely create this temp table so it is usable?
> > >
> > > Looking at reformulating the query to not use ROWIDs, to detecting
the
> > > number of temp dbspaces, and to explicitly including an IN clause
> > > specifying a single temp dbspace selected from sysmaster:sysdbspaces.
> > > However, none of these options is ideal. Example: if the
currentdatabase
> >
> > > has no logging I won't be able to get access to sysmaster. Parsing
> > > DBSPACETEMP isn't ideal either because a logged dbspace could be
listed
> > > there.
> > >
> > > Ideas?
> > >
> > > 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.
> > >
> > > --001a1140c18a0abaa9051b035a6e
> > >
> > >
> > >
> >
> >
>
***************************************************************************=
=3D
> > ****
> >
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
***************************************************************************=
****
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001a1140c18a43e4f9051b049756
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Hmm, interesting. Good idea, but not supported all the way back to 11.50 which I need to support. This would work for 12.10 though, so good idea, but not portable enough. This is for the myschema --dependency-order option and a new similar option for dbscript that depend on the hierarchical queries that have been available since v11.50.xC5 and later, so whatever solution I find has to work that far back. The code won't let you use that option if the engine is older than 11.50.xC5 so earlier isn't an issue. I think John's point that sysmaster is exempt from the requirement that remote databases have the same logging status as the current database will let me query sysmaster to find one temp dbspace to use explicitely in the create temp table statement. So, I'm good now. 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. On Thu, Jul 16, 2015 at 5:36 PM, Keith Simmons <smiley73@gmail.com> wrote: > Art > > You posted about rownum() function in a blog in Sept 2012, is that a > possible solution ?? > > Keith > > On 16 July 2015 at 20:48, Art Kagel <art.kagel@gmail.com> wrote: > > > Folks: > > > > I have a temp table that I create in one of my tools. A query against > that > > temp table uses rowids. If there are more than one temp dbspace listed in > > DBSPACETEMP the temp table is apparently being created fragmented across > > the multiple temp dbspaces because the query returns a -857 error (ROWIDs > > do not exist for table). There is no problem as long as there is only one > > temp dbspace listed in DBSPACETEMP. Since the utility cannot depend on > > there being only one temp dbspace, and I cannot include the WITH ROWIDs > > clause since there might be only a single temp dbspace, does anyone have > a > > guess how I can safely create this temp table so it is usable? > > > > Looking at reformulating the query to not use ROWIDs, to detecting the > > number of temp dbspaces, and to explicitly including an IN clause > > specifying a single temp dbspace selected from sysmaster:sysdbspaces. > > However, none of these options is ideal. Example: if the current database > > has no logging I won't be able to get access to sysmaster. Parsing > > DBSPACETEMP isn't ideal either because a logged dbspace could be listed > > there. > > > > Ideas? > > > > 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. > > > > --001a1140c18a0abaa9051b035a6e > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --047d7bae49444c95a6051b04dcf3 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113fe42827e673051b052268
Great John. Thanks.
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.
On Thu, Jul 16, 2015 at 5:42 PM, John Miller iii <miller3@us.ibm.com> wrote:
> This logging exception was introduced in version 7.
>
> I do not remember which version the bit operators where introduced. I
> think 11.10, but you can use sysmaster:bitval() to be safe.
>
> John F. Miller III
> STSM, Lead Architect
> miller3@us.ibm.com
> 503-747-1366
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 07/16/2015 02:17:18 PM:
>
> > From: "Art Kagel" <art.kagel@gmail.com>
> > To: ids@iiug.org
> > Date: 07/16/2015 02:18 PM
> > Subject: Re: Odd one [35481]
> > Sent by: ids-bounces@iiug.org
> >
> > Is that exemption for sysmaster throughout all versions since 11.50 and
> > later or is it more recent?
> >
> > 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.
> >
> > On Thu, Jul 16, 2015 at 4:53 PM, John Miller iii <miller3@us.ibm.com>
> wrote:
> >
> > > If the current database is no-logging and you query sysmaster this WILL
>
> > > work as sysmaster is exempt from the logging restriction requiring all
> > > databases to be of the same logging.
> > >
> > > create database art;
> > > select count(*)
> > > from sysmaster:systables SS, systables S
> > > where SS.tabid=3D3D S.tabid;> > >
> > > (count(*))
> > >
> > > 70
> > >
> > > 1 row(s) retrieved.
> > >
> > > My suggestion would be to use the following:
> > >
> > > select first 1 name from sysmaster:sysdbstab where bitand
> > > (flags,'0x2000')>1;> > >
> > > John F. Miller III
> > > STSM, Lead Architect
> > > miller3@us.ibm.com
> > > 503-747-1366
> > > IBM Informix Dynamic Server (IDS)
> > >
> > > ids-bounces@iiug.org wrote on 07/16/2015 12:48:18 PM:
> > >
> > > > From: "Art Kagel" <art.kagel@gmail.com>
> > > > To: ids@iiug.org
> > > > Date: 07/16/2015 12:53 PM
> > > > Subject: Odd one [35474]
> > > > Sent by: ids-bounces@iiug.org
> > > >
> > > > Folks:
> > > >
> > > > I have a temp table that I create in one of my tools. A query against
>
> > > that
> > > > temp table uses rowids. If there are more than one temp dbspace
> listed in
> > >
> > > > DBSPACETEMP the temp table is apparently being created fragmented
> across
> > > > the multiple temp dbspaces because the query returns a -857 error
> (ROWIDs
> > >
> > > > do not exist for table). There is no problem as long as there isonly
> one
> > >
> > > > temp dbspace listed in DBSPACETEMP. Since the utility cannot depend
> on
> > > > there being only one temp dbspace, and I cannot include the WITH
> ROWIDs
> > > > clause since there might be only a single temp dbspace, does anyone
> have
> > > a
> > > > guess how I can safely create this temp table so it is usable?
> > > >
> > > > Looking at reformulating the query to not use ROWIDs, to detecting
> the
> > > > number of temp dbspaces, and to explicitly including an IN clause
> > > > specifying a single temp dbspace selected from sysmaster:sysdbspaces.
>
> > > > However, none of these options is ideal. Example: if the
> currentdatabase
> > >
> > > > has no logging I won't be able to get access to sysmaster. Parsing
> > > > DBSPACETEMP isn't ideal either because a logged dbspace could be
> listed
> > > > there.
> > > >
> > > > Ideas?
> > > >
> > > > 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.
> > > >
> > > > --001a1140c18a0abaa9051b035a6e
> > > >
> > > >
> > > >
> > >
> > >
> >
>
> ***************************************************************************=
> =3D
>
> > > ****
> > >
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > >
> > >
> > >
> > >
> >
>
> ***************************************************************************=
> ****
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001a1140c18a43e4f9051b049756
> >
> >
> >
>
> ***************************************************************************=
> ****
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--047d7bd75bbe962f4a051b052374