Dynamic Explain plan option in version 10

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.