Strange behaviour when using a view in 11.70.FC2
A query against a view on IDS 11.70.FC2 (Solaris) failed with -264/-131 "could not write to a temporary file / no free disk space", apparently using rootdbs instead of the four large temp dbspaces, even though DBSPACETEMP was set correctly; running the same SQL inline worked fine. Suggestions were to verify DBSPACETEMP (onstat -d flag T, sysmaster:sysconfig, env vars), bounce the server, and force unlogged temp tables via TEMPTAB_NOLOG=1 (onmode -wf) or WITH NO LOG; PSORT_DBTEMP was also mentioned for sorts. Setting TEMPTAB_NOLOG alone didn't help, but after dropping and recreating the views with that setting active, the problem disappeared.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
All,
Informix 11.70.FC2 running on Sun Solaris 5.10.
One of our developers has written a query and implemented it in a view. This
is shown below. When you run a query using the view ( also shown below ) I get
the following error:
Error: Could not write to a
temporary file.
SQLState: IX000
ErrorCode: -264
Error: ISAM error: no free disk
space
SQLState: IX000
ErrorCode: -131
Position: 218
For some reason it is using the root dbspace and is not using the 4 very large
temporary spaces I have allocated for it. The value of DBSPACETEMP is
correctly set. When I amend the SQL and incorporate the view definition it
works perfectly well. Am I missing a configuration parameter or something? Has
anyone else experienced similar problems? Any suggestions?
Regards
Andy Grantham.
Query
-------
SELECT skip 0 first 15
OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail
FROM vcps_movementscurraccount
where AccountNumber='0000000013'
and ohimreference like '%307'
order by AccountingDate desc;
View definition
------------------
create view "euroadm".vcps_movementscurraccount
(ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
)::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
;
Are you using "with no log" clause? If not the engine uses the rootdbs.
Have you bounce the instance after configuring the dbspacetemp?
http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm
.adref.doc%2Fids_adr_0046.htm
Select * from table
Insert into tmp_table with no log;
Celso Cabral Coimbra
Administrador de Banco de Dados
ClearTech Ltda
"Trust at the heart of Communications"
Tel. (11) 3576-4509
-----Mensagem original-----
De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de Andrew
Grantham
Enviada em: quarta-feira, 7 de março de 2012 12:38
Para: ids@iiug.org
Assunto: Strange behaviour when using a view in 11.70.FC2 [26470]
All,
Informix 11.70.FC2 running on Sun Solaris 5.10.
One of our developers has written a query and implemented it in a view. This
is shown below. When you run a query using the view ( also shown below ) I get
the following error:
Error: Could not write to a
temporary file.
SQLState: IX000
ErrorCode: -264
Error: ISAM error: no free disk
space
SQLState: IX000
ErrorCode: -131
Position: 218
For some reason it is using the root dbspace and is not using the 4 very large
temporary spaces I have allocated for it. The value of DBSPACETEMP is
correctly set. When I amend the SQL and incorporate the view definition it
works perfectly well. Am I missing a configuration parameter or something? Has
anyone else experienced similar problems? Any suggestions?
Regards
Andy Grantham.
Query
-------
SELECT skip 0 first 15
OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail
FROM vcps_movementscurraccount
where AccountNumber='0000000013'
and ohimreference like '%307'
order by AccountingDate desc;
View definition
------------------
create view "euroadm".vcps_movementscurraccount
(ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
)::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
;
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Better still, set TEMPTAB_NOLOG 1 in your $ONCONFIG file and restart, so that
WITH NO LOG becomes the default behaviour. Also check your temp spaces really
have been created with the "-t" flag by looking for "T" in "onstat -d".
You can also set environment variable PSORT_DBTEMP to do the ORDER BY in the
file system rather than temp dbspaces, which is also quicker, especially if
you create a RAM disk.
Regards,
Doug Lawry
Have you bounced the server since changing DBSPACETEMP in the ONCONFIG
file?
What does the engine think this is set to (select cf_effective from
sysmaster:sysconfig where cf_name = 'DBSPACETEMP';)?
Does the user have the DBSPACETEMP environment variable set to a different
value in the environment than the setting in the ONCONFIG?
If all of that checks out, try running this command then try the query
again:
onmode -wf TEMPTAB_NOLOG=1
That will force the default logging mode for temp tables to be unlogged
(otherwise the default is logged and logged tables cannot be created in
temp dbspaces). Also, if you don't want to keep this set in your ONCONFIG
file permanently, include some normal dbspace (other than rootdbs) that has
low IO activity in DBSPACETEMP to hold logged temp tables.
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 Wed, Mar 7, 2012 at 10:37 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> All,
>
> Informix 11.70.FC2 running on Sun Solaris 5.10.
>
> One of our developers has written a query and implemented it in a view.
> This
> is shown below. When you run a query using the view ( also shown below ) I
> get
> the following error:
>
> Error: Could not write to a
> temporary file.
>
> SQLState: IX000
>
> ErrorCode: -264
>
> Error: ISAM error: no free disk
> space
>
> SQLState: IX000
>
> ErrorCode: -131
>
> Position: 218
>
> For some reason it is using the root dbspace and is not using the 4 very
> large
> temporary spaces I have allocated for it. The value of DBSPACETEMP is
> correctly set. When I amend the SQL and incorporate the view definition it
> works perfectly well. Am I missing a configuration parameter or something?
> Has
> anyone else experienced similar problems? Any suggestions?
>
> Regards
> Andy Grantham.
>
> Query
> -------
>
> SELECT skip 0 first 15
> OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail>
> FROM vcps_movementscurraccount
>
> where AccountNumber='0000000013'
> and ohimreference like '%307'
>
> order by AccountingDate desc;
>
> View definition
> ------------------
>
> create view "euroadm".vcps_movementscurraccount
> (ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
> select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
>
> ::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
>
> END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
>
> ,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
>
> )::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
>
> END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
>
> ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
>
> from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
>
> x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
>
> IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
>
> ;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340cbdcfc4c904baaafc15
Unfortunately your suggestions did not solve the problem. I ran the commands
below and can confirm that the temporary spaces were returned. This instance
has been stopped and restarted many times. The value of DBSPACETEMP is not
altered in any way. Any more ideas?
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Strange behaviour when using a view in 11..... [26479]
> Date: Wed, 7 Mar 2012 13:04:19 -0500
>
> Have you bounced the server since changing DBSPACETEMP in the ONCONFIG
> file?
>
> What does the engine think this is set to (select cf_effective from
> sysmaster:sysconfig where cf_name = 'DBSPACETEMP';)?
>
> Does the user have the DBSPACETEMP environment variable set to a different
> value in the environment than the setting in the ONCONFIG?
>
> If all of that checks out, try running this command then try the query
> again:
>
> onmode -wf TEMPTAB_NOLOG=1>
> That will force the default logging mode for temp tables to be unlogged
> (otherwise the default is logged and logged tables cannot be created in
> temp dbspaces). Also, if you don't want to keep this set in your ONCONFIG
> file permanently, include some normal dbspace (other than rootdbs) that has
> low IO activity in DBSPACETEMP to hold logged temp tables.
>
> 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 Wed, Mar 7, 2012 at 10:37 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
>
> > All,
> >
> > Informix 11.70.FC2 running on Sun Solaris 5.10.
> >
> > One of our developers has written a query and implemented it in a view.
> > This
> > is shown below. When you run a query using the view ( also shown below ) I
> > get
> > the following error:
> >
> > Error: Could not write to a
> > temporary file.
> >
> > SQLState: IX000
> >
> > ErrorCode: -264
> >
> > Error: ISAM error: no free disk
> > space
> >
> > SQLState: IX000
> >
> > ErrorCode: -131
> >
> > Position: 218
> >
> > For some reason it is using the root dbspace and is not using the 4 very
> > large
> > temporary spaces I have allocated for it. The value of DBSPACETEMP is
> > correctly set. When I amend the SQL and incorporate the view definition it
> > works perfectly well. Am I missing a configuration parameter or something?
> > Has
> > anyone else experienced similar problems? Any suggestions?
> >
> > Regards
> > Andy Grantham.
> >
> > Query
> > -------
> >
> > SELECT skip 0 first 15
> > OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail> >
> > FROM vcps_movementscurraccount
> >
> > where AccountNumber='0000000013'
> > and ohimreference like '%307'
> >
> > order by AccountingDate desc;
> >
> > View definition
> > ------------------
> >
> > create view "euroadm".vcps_movementscurraccount
> > (ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
> > select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
> >
> > ::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
> >
> > END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
> >
> > ,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
> >
> > )::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
> >
> > END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
> >
> > ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
> >
> > from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
> >
> > x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
> >
> > IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
> >
> > ;
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340cbdcfc4c904baaafc15
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Without access to the server, I'm about done.
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, Mar 8, 2012 at 4:37 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> Unfortunately your suggestions did not solve the problem. I ran the
> commands
> below and can confirm that the temporary spaces were returned. This
> instance
> has been stopped and restarted many times. The value of DBSPACETEMP is not
> altered in any way. Any more ideas?
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: Strange behaviour when using a view in 11..... [26479]
> > Date: Wed, 7 Mar 2012 13:04:19 -0500
> >
> > Have you bounced the server since changing DBSPACETEMP in the ONCONFIG
> > file?
> >
> > What does the engine think this is set to (select cf_effective from
> > sysmaster:sysconfig where cf_name = 'DBSPACETEMP';)?
> >
> > Does the user have the DBSPACETEMP environment variable set to a
> different
> > value in the environment than the setting in the ONCONFIG?
> >
> > If all of that checks out, try running this command then try the query
> > again:
> >
> > onmode -wf TEMPTAB_NOLOG=1> >
> > That will force the default logging mode for temp tables to be unlogged
> > (otherwise the default is logged and logged tables cannot be created in
> > temp dbspaces). Also, if you don't want to keep this set in your ONCONFIG
> > file permanently, include some normal dbspace (other than rootdbs) that
> has
> > low IO activity in DBSPACETEMP to hold logged temp tables.
> >
> > 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 Wed, Mar 7, 2012 at 10:37 AM, Andrew Grantham
> <agrantha@hotmail.com>wrote:
> >
> > > All,
> > >
> > > Informix 11.70.FC2 running on Sun Solaris 5.10.
> > >
> > > One of our developers has written a query and implemented it in a view.
> > > This
> > > is shown below. When you run a query using the view ( also shown below
> ) I
> > > get
> > > the following error:
> > >
> > > Error: Could not write to a
> > > temporary file.
> > >
> > > SQLState: IX000
> > >
> > > ErrorCode: -264
> > >
> > > Error: ISAM error: no free disk
> > > space
> > >
> > > SQLState: IX000
> > >
> > > ErrorCode: -131
> > >
> > > Position: 218
> > >
> > > For some reason it is using the root dbspace and is not using the 4
> very
> > > large
> > > temporary spaces I have allocated for it. The value of DBSPACETEMP is
> > > correctly set. When I amend the SQL and incorporate the view
> definition it
> > > works perfectly well. Am I missing a configuration parameter or
> something?
> > > Has
> > > anyone else experienced similar problems? Any suggestions?
> > >
> > > Regards
> > > Andy Grantham.
> > >
> > > Query
> > > -------
> > >
> > > SELECT skip 0 first 15
> > > OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail> > >
> > > FROM vcps_movementscurraccount
> > >
> > > where AccountNumber='0000000013'
> > > and ohimreference like '%307'
> > >
> > > order by AccountingDate desc;
> > >
> > > View definition
> > > ------------------
> > >
> > > create view "euroadm".vcps_movementscurraccount
> > > (ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
> > > select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
> > >
> > > ::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
> > >
> > > END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
> > >
> > > ,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
> > >
> > > )::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
> > >
> > > END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
> > >
> > > ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
> > >
> > > from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
> > >
> > > x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
> > >
> > > IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
> > >
> > > ;
> > >
> > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --14dae9340cbdcfc4c904baaafc15
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba6e8fbe36522004baba3ab6
Art,
Just found out from our developers that they dropped and recreated the views (
once the onmode -wf TEMPTAB_NOLOG=1 was run ) and now the problem is
gone. One for the files I think.
Thanks for your help.
Regards
Andy Grantham.
> To: ids@iiug.org
> From: art.kagel@gmail.com
> Subject: Re: Strange behaviour when using a view in 11..... [26489]
> Date: Thu, 8 Mar 2012 07:15:17 -0500
>
> Without access to the server, I'm about done.
>
> 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, Mar 8, 2012 at 4:37 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
>
> > Unfortunately your suggestions did not solve the problem. I ran the
> > commands
> > below and can confirm that the temporary spaces were returned. This
> > instance
> > has been stopped and restarted many times. The value of DBSPACETEMP is not
> > altered in any way. Any more ideas?
> >
> > > To: ids@iiug.org
> > > From: art.kagel@gmail.com
> > > Subject: Re: Strange behaviour when using a view in 11..... [26479]
> > > Date: Wed, 7 Mar 2012 13:04:19 -0500
> > >
> > > Have you bounced the server since changing DBSPACETEMP in the ONCONFIG
> > > file?
> > >
> > > What does the engine think this is set to (select cf_effective from
> > > sysmaster:sysconfig where cf_name = 'DBSPACETEMP';)?
> > >
> > > Does the user have the DBSPACETEMP environment variable set to a
> > different
> > > value in the environment than the setting in the ONCONFIG?
> > >
> > > If all of that checks out, try running this command then try the query
> > > again:
> > >
> > > onmode -wf TEMPTAB_NOLOG=1> > >
> > > That will force the default logging mode for temp tables to be unlogged
> > > (otherwise the default is logged and logged tables cannot be created in
> > > temp dbspaces). Also, if you don't want to keep this set in your ONCONFIG
> > > file permanently, include some normal dbspace (other than rootdbs) that
> > has
> > > low IO activity in DBSPACETEMP to hold logged temp tables.
> > >
> > > 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 Wed, Mar 7, 2012 at 10:37 AM, Andrew Grantham
> > <agrantha@hotmail.com>wrote:
> > >
> > > > All,
> > > >
> > > > Informix 11.70.FC2 running on Sun Solaris 5.10.
> > > >
> > > > One of our developers has written a query and implemented it in a view.
> > > > This
> > > > is shown below. When you run a query using the view ( also shown below
> > ) I
> > > > get
> > > > the following error:
> > > >
> > > > Error: Could not write to a
> > > > temporary file.
> > > >
> > > > SQLState: IX000
> > > >
> > > > ErrorCode: -264
> > > >
> > > > Error: ISAM error: no free disk
> > > > space
> > > >
> > > > SQLState: IX000
> > > >
> > > > ErrorCode: -131
> > > >
> > > > Position: 218
> > > >
> > > > For some reason it is using the root dbspace and is not using the 4
> > very
> > > > large
> > > > temporary spaces I have allocated for it. The value of DBSPACETEMP is
> > > > correctly set. When I amend the SQL and incorporate the view
> > definition it
> > > > works perfectly well. Am I missing a configuration parameter or
> > something?
> > > > Has
> > > > anyone else experienced similar problems? Any suggestions?
> > > >
> > > > Regards
> > > > Andy Grantham.
> > > >
> > > > Query
> > > > -------
> > > >
> > > > SELECT skip 0 first 15
> > > > OhimReference, AccountNumber, AccountingDate, LegalDate, Amount, Detail> > > >
> > > > FROM vcps_movementscurraccount
> > > >
> > > > where AccountNumber='0000000013'
> > > > and ohimreference like '%307'
> > > >
> > > > order by AccountingDate desc;
> > > >
> > > > View definition
> > > > ------------------
> > > >
> > > > create view "euroadm".vcps_movementscurraccount
> > > > (ohimreference,accountnumber,accountingdate,legaldate,amount,detail) as
> > > > select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
> > > >
> > > > ::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
> > > >
> > > > END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
> > > >
> > > > ,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
> > > >
> > > > )::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
> > > >
> > > > END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
> > > >
> > > > ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
> > > >
> > > > from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
> > > >
> > > > x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
> > > >
> > > > IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
> > > >
> > > > ;
> > > >
> > > >
> > > >
> > > >
> > >
> >
> >
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --14dae9340cbdcfc4c904baaafc15
> > >
> > >
> > >
> >
> >
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba6e8fbe36522004baba3ab6
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Interesting. I'll have to file that one away for next time. Thanks for
the update.
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, Mar 8, 2012 at 8:39 AM, Andrew Grantham <agrantha@hotmail.com>wrote:
> Art,
>
> Just found out from our developers that they dropped and recreated the
> views (
> once the onmode -wf TEMPTAB_NOLOG=1 was run ) and now the problem is
> gone. One for the files I think.
>
> Thanks for your help.
>
> Regards
>
> Andy Grantham.
>
> > To: ids@iiug.org
> > From: art.kagel@gmail.com
> > Subject: Re: Strange behaviour when using a view in 11..... [26489]
> > Date: Thu, 8 Mar 2012 07:15:17 -0500
> >
> > Without access to the server, I'm about done.
> >
> > 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, Mar 8, 2012 at 4:37 AM, Andrew Grantham <agrantha@hotmail.com
> >wrote:
> >
> > > Unfortunately your suggestions did not solve the problem. I ran the
> > > commands
> > > below and can confirm that the temporary spaces were returned. This
> > > instance
> > > has been stopped and restarted many times. The value of DBSPACETEMP is
> not
> > > altered in any way. Any more ideas?
> > >
> > > > To: ids@iiug.org
> > > > From: art.kagel@gmail.com
> > > > Subject: Re: Strange behaviour when using a view in 11..... [26479]
> > > > Date: Wed, 7 Mar 2012 13:04:19 -0500
> > > >
> > > > Have you bounced the server since changing DBSPACETEMP in the
> ONCONFIG
> > > > file?
> > > >
> > > > What does the engine think this is set to (select cf_effective from
> > > > sysmaster:sysconfig where cf_name = 'DBSPACETEMP';)?
> > > >
> > > > Does the user have the DBSPACETEMP environment variable set to a
> > > different
> > > > value in the environment than the setting in the ONCONFIG?
> > > >
> > > > If all of that checks out, try running this command then try the
> query
> > > > again:
> > > >
> > > > onmode -wf TEMPTAB_NOLOG=1> > > >
> > > > That will force the default logging mode for temp tables to be
> unlogged
> > > > (otherwise the default is logged and logged tables cannot be created
> in
> > > > temp dbspaces). Also, if you don't want to keep this set in your
> ONCONFIG
> > > > file permanently, include some normal dbspace (other than rootdbs)
> that
> > > has
> > > > low IO activity in DBSPACETEMP to hold logged temp tables.
> > > >
> > > > 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 Wed, Mar 7, 2012 at 10:37 AM, Andrew Grantham
> > > <agrantha@hotmail.com>wrote:
> > > >
> > > > > All,
> > > > >
> > > > > Informix 11.70.FC2 running on Sun Solaris 5.10.
> > > > >
> > > > > One of our developers has written a query and implemented it in a
> view.
> > > > > This
> > > > > is shown below. When you run a query using the view ( also shown
> below
> > > ) I
> > > > > get
> > > > > the following error:
> > > > >
> > > > > Error: Could not write to a
> > > > > temporary file.
> > > > >
> > > > > SQLState: IX000
> > > > >
> > > > > ErrorCode: -264
> > > > >
> > > > > Error: ISAM error: no free disk
> > > > > space
> > > > >
> > > > > SQLState: IX000
> > > > >
> > > > > ErrorCode: -131
> > > > >
> > > > > Position: 218
> > > > >
> > > > > For some reason it is using the root dbspace and is not using the 4
> > > very
> > > > > large
> > > > > temporary spaces I have allocated for it. The value of DBSPACETEMP
> is
> > > > > correctly set. When I amend the SQL and incorporate the view
> > > definition it
> > > > > works perfectly well. Am I missing a configuration parameter or
> > > something?
> > > > > Has
> > > > > anyone else experienced similar problems? Any suggestions?
> > > > >
> > > > > Regards
> > > > > Andy Grantham.
> > > > >
> > > > > Query
> > > > > -------
> > > > >
> > > > > SELECT skip 0 first 15
> > > > > OhimReference, AccountNumber, AccountingDate, LegalDate, Amount,> Detail
> > > > >
> > > > > FROM vcps_movementscurraccount
> > > > >
> > > > > where AccountNumber='0000000013'
> > > > > and ohimreference like '%307'
> > > > >
> > > > > order by AccountingDate desc;
> > > > >
> > > > > View definition
> > > > > ------------------
> > > > >
> > > > > create view "euroadm".vcps_movementscurraccount
> > > > >
> (ohimreference,accountnumber,accountingdate,legaldate,amount,detail)
> as
> > > > > select CASE WHEN (x1.odpdetail IS NULL ) THEN x0.idpayment
> > > > >
> > > > > ::char(20) ELSE ((x1.idpayment || ':' ) || x1.odpdetail )
> > > > >
> > > > > END ,x0.identifiedaccount ,x0.dtpayment ,x0.dtreceptpay
> > > > >
> > > > > ,CASE WHEN (x0.stpayment = 6 ) THEN NVL (x1.mtpdetail ,x0.mtpayment
> > > > >
> > > > > )::float ELSE -( NVL (x1.mtpdetail ,x0.mtpayment )) ::float
> > > > >
> > > > > END ,CASE WHEN (x0.stpayment = 6 ) THEN 'Credit on Account'
> > > > >
> > > > > ELSE NVL (x1.descpdetail ,NVL (x0.descpayment ,'' )) END
> > > > >
> > > > > from "euroadm".paymentline x0 ,outer("euroadm".paymentdetail
> > > > >
> > > > > x1 ) where (((x1.idpayment = x0.idpayment ) AND (x1.stpdetail
> > > > >
> > > > > IS NULL ) ) OR (x1.stpdetail NOT IN (11 ,21 ,22 ,23 )) )
> > > > >
> > > > > ;
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > >
> > >
> >
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >@@NL
Related threads
- Re: oninit: Dictionary Cache and SPL Routine Cache error
- Re: oninit: Dictionary Cache and SPL Routine Cache error
- Help Needed in Phoenix
- INFORMIX 9.40 LINUX