SEO

Some quick questions where does it put the file for a particular user?
Does it put the file in the home directory of the user? If the user is
a nologin user and doesn't have a home directory is there a way to
specify the directory that the server uses for the explain plan file?

If you can't currently specify the file name or location for the
explain plan I would like to request a feature

set explain on <to <filename>;

and the analogous

onmode -Y 1 <filename>

As background our database users aren't given home directories for
security reasons I am told (less you can do the less you can muck with
defense.)

bozon wrote:
> Some quick questions where does it put the file for a particular user?
> Does it put the file in the home directory of the user? If the user is
> a nologin user and doesn't have a home directory is there a way to
> specify the directory that the server uses for the explain plan file?
>
> If you can't currently specify the file name or location for the
> explain plan I would like to request a feature
>
> set explain on <to <filename>;
>
> and the analogous
>
> onmode -Y 1 <filename>
>
> As background our database users aren't given home directories for
> security reasons I am told (less you can do the less you can muck with
> defense.)
>

Dynamic explain plan puts the file sqexplain.out.<sessid> in the IDS startup
directory (normally $INFORMIXDIR) on the server host. Since this feature
can only be turned on by user informix if the actual session owner needs
access to the results the file will have to be copied or otherwise made
available by user informix on the server.

Art S. Kagel

OK, we usually start our instance up in the informix home directory
which our users don't have write access to. I certainly don't want to
give our users write access to the informix home directory. The dynamic
explain fails when logged in because the user can't write a file to the
informix home directory. This certainly isn't a good default.

Is there an environment variable that controls where this file goes?
How are you supposed to use this in a secure environment, I don't think
giving users write access to the informix home directory is a very good
idea. Well I hope someone has someone good ideas, I was happier when I
mistakenly thought it put it in the home directory of the user.

Art S. Kagel wrote:
> bozon wrote:
> > Some quick questions where does it put the file for a particular user?
> > Does it put the file in the home directory of the user? If the user is
> > a nologin user and doesn't have a home directory is there a way to
> > specify the directory that the server uses for the explain plan file?
> >
> > If you can't currently specify the file name or location for the
> > explain plan I would like to request a feature
> >
> > set explain on <to <filename>;
> >
> > and the analogous
> >
> > onmode -Y 1 <filename>
> >
> > As background our database users aren't given home directories for
> > security reasons I am told (less you can do the less you can muck with
> > defense.)
> >
>
> Dynamic explain plan puts the file sqexplain.out.<sessid> in the IDS startup
> directory (normally $INFORMIXDIR) on the server host. Since this feature
> can only be turned on by user informix if the actual session owner needs
> access to the results the file will have to be copied or otherwise made
> available by user informix on the server.
>
> Art S. Kagel

bozon wrote:
> OK, we usually start our instance up in the informix home directory
> which our users don't have write access to. I certainly don't want to
> give our users write access to the informix home directory. The dynamic
> explain fails when logged in because the user can't write a file to the
> informix home directory. This certainly isn't a good default.
>
> Is there an environment variable that controls where this file goes?
> How are you supposed to use this in a secure environment, I don't think
> giving users write access to the informix home directory is a very good
> idea. Well I hope someone has someone good ideas, I was happier when I
> mistakenly thought it put it in the home directory of the user.

I have to apologize. I hadn't run an onmode -Y recently and refreshed my
memory from the admin guide which seemed to imply the file would be written
to the IDS startup directory. That's not right.

I just gave it a try on my one IDS 10.00 DEV server and the file is actually
written to the user's home directory. So, you'll have to create a homedir
for nologin users. It can be a single tempdir with global write perms
shared by all nologin users. That will work even for nologin userids.
You'd just have to copy the sqexplain files from the server to someplace the
user has access to afterward.

We do something similar here for our development servers. Programmers do
not have login perms on those machines, but their home dirs are NFS mounted
on the development server and recorded in the /etc/passwd file so their
sqexplain files are available from the loginable DEV machines when they SET
EXPLAIN ON or apparently when the DBAs onmode -Y for them (don't get to do
that much since we're still on 7.31 - plans for upgrade next year.
Hopefully there's a cheetah in my future! 8-P ).

Art S. Kagel

> Art S. Kagel wrote:
>
>>bozon wrote:
>>
>>>Some quick questions where does it put the file for a particular user?
>>>Does it put the file in the home directory of the user? If the user is
>>>a nologin user and doesn't have a home directory is there a way to
>>>specify the directory that the server uses for the explain plan file?
>>>
>>>If you can't currently specify the file name or location for the
>>>explain plan I would like to request a feature
>>>
>>>set explain on <to <filename>;
>>>
>>>and the analogous
>>>
>>>onmode -Y 1 <filename>
>>>
>>>As background our database users aren't given home directories for
>>>security reasons I am told (less you can do the less you can muck with
>>>defense.)
>>>
>>
>>Dynamic explain plan puts the file sqexplain.out.<sessid> in the IDS startup
>>directory (normally $INFORMIXDIR) on the server host. Since this feature
>>can only be turned on by user informix if the actual session owner needs
>>access to the results the file will have to be copied or otherwise made
>>available by user informix on the server.
>>
>>Art S. Kagel
>
>

> bozon wrote:
> > OK, we usually start our instance up in the informix home directory
> > which our users don't have write access to. I certainly
> don't want to
> > give our users write access to the informix home directory. The
> > dynamic explain fails when logged in because the user can't write a
> > file to the informix home directory. This certainly isn't a
> good default.
> >
> > Is there an environment variable that controls where this file goes?
> > How are you supposed to use this in a secure environment, I don't
> > think giving users write access to the informix home directory is a
> > very good idea. Well I hope someone has someone good ideas, I was
> > happier when I mistakenly thought it put it in the home
> directory of the user.
>
> I have to apologize. I hadn't run an onmode -Y recently and
> refreshed my memory from the admin guide which seemed to
> imply the file would be written to the IDS startup directory.
> That's not right.
>
> I just gave it a try on my one IDS 10.00 DEV server and the
> file is actually written to the user's home directory. So,
> you'll have to create a homedir for nologin users. It can be
> a single tempdir with global write perms shared by all
> nologin users. That will work even for nologin userids.
> You'd just have to copy the sqexplain files from the server
> to someplace the user has access to afterward.
>
> We do something similar here for our development servers.
> Programmers do not have login perms on those machines, but
> their home dirs are NFS mounted on the development server
> and recorded in the /etc/passwd file so their sqexplain files
> are available from the loginable DEV machines when they SET
> EXPLAIN ON or apparently when the DBAs onmode -Y for them
> (don't get to do that much since we're still on 7.31 - plans
> for upgrade next year.
> Hopefully there's a cheetah in my future! 8-P ).
>
> Art S. Kagel
>
> > Art S. Kagel wrote:
> >
> >>bozon wrote:
> >>
> >>>Some quick questions where does it put the file for a
> particular user?
> >>>Does it put the file in the home directory of the user? If
> the user
> >>>is a nologin user and doesn't have a home directory is
> there a way to
> >>>specify the directory that the server uses for the explain
> plan file?
> >>>
> >>>If you can't currently specify the file name or location for the
> >>>explain plan I would like to request a feature
> >>>
> >>>set explain on <to <filename>;
> >>>
> >>>and the analogous
> >>>
> >>>onmode -Y 1 <filename>
> >>>
> >>>As background our database users aren't given home directories for
> >>>security reasons I am told (less you can do the less you can muck
> >>>with
> >>>defense.)
> >>>
> >>
> >>Dynamic explain plan puts the file sqexplain.out.<sessid>
> in the IDS
> >>startup directory (normally $INFORMIXDIR) on the server
> host. Since
> >>this feature can only be turned on by user informix if the actual
> >>session owner needs access to the results the file will have to be
> >>copied or otherwise made available by user informix on the server.
> >>
> >>Art S. Kagel
> >

It would be nice if we had the V8 capabilities for SQLEXPLAIN, i.e. the
ability to specifiy where the file is written. I believe this is in as a
feature request


Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com

GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend IDUG 2007 San Jose, North America
May 6-10, 2006
Visit http://www.iiug.org/conf for more information.

Paul Watson wrote:

> It would be nice if we had the V8 capabilities for SQLEXPLAIN, i.e. the
> ability to specifiy where the file is written. I believe this is in as a
> feature request
>

I believe this is a registered feature request, and I believe we may see it in the future. But I'd say it's not enough...
The explain plan should be available through SQL... The "trick" that Art mentioned should not be needed.
Any user with SQL access should be able to see the query plan of his last query...


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

I'm sure if it was easy to do it would have been done by now

Paul Watson
Tel: +44 1414161772
Mob: +44 7818003457
Web: www.oninit.com

GO FURTHER with DB2
GET THERE FASTER with Informix.
Attend IDUG 2007 San Jose, North America
May 6-10, 2006
Visit http://www.iiug.org/conf for more information.




> -----Original Message-----
> From: Fernando Nunes [mailto:spam@domus.online.pt]
> Posted At: 02 November 2006 17:55
> Posted To: comp.databases.informix
> Conversation: Dynamic Explain plan option in version 10
> Subject: Re: Dynamic Explain plan option in version 10
>
>
> Paul Watson wrote:
>
> > It would be nice if we had the V8 capabilities for SQLEXPLAIN, i.e.
> > the ability to specifiy where the file is written. I
> believe this is
> > in as a feature request
> >
>
> I believe this is a registered feature request, and I believe
> we may see it in the future. But I'd say it's not enough...
> The explain plan should be available through SQL... The
> "trick" that Art mentioned should not be needed.
> Any user with SQL access should be able to see the query plan
> of his last query...
>
>
> --
> Fernando Nunes
> Portugal
>
> http://informix-technology.blogspot.com
> My email works... but I don't check it frequently...

we've added the following to IDS Cheetah (next release).

SET EXPLAIN TO FILE '/tmp/myfile';

Those on the Beta program can see this now. Others will have to wait
for OpenBeta.
We're aware of onmode -Y issues. will keep you posted.

cheers,

Keshav.

Paul Watson wrote:
> I'm sure if it was easy to do it would have been done by now
>

You have a point... Of course is much easier to talk about it then to implement it.
But I'm sure we'll see a lot of difficult to implement features in the near future...

Regards.


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...