Fw: Why dbexport places procs before index builds?

This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . My question has to do with dbexport/dbimport. We know that dbexport is writing out the “build index” statements *after* the stored procedures and functions. The “update statistics” lines do not have one with “for routine” . So it seems like the query plans may be sub-optimal for some of the procedures and functions. Maybe that is why I always run slower after importing until I update the statistics. What am I missing here?

original post: This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . My question has to do with dbexport/dbimport. We know that dbexport is writing out the “build index” statements *after* the stored procedures and functions. The “update statistics” lines do not have one with “for routine” . So it seems like the query plans may be sub-optimal for some of the procedures and functions. Maybe that is why I always run slower after importing until I update the statistics. What am I missing here? Response: Well, my guess would be that the procedures are created prior to the indexes because you could have functional indexes, which would have dependencies on the procedures existing before the index can get built. Jacques Renaut IBM Informix Advanced Support APD Team

It's a catch 22 Bill. There may be functional indexes so the functions have to be printed before indexes. When I was updating myschema to work better with myexport/myimport I had to deal with this exact issue. My solution was to output functions before tables and indexes and procedures after. Not perfect, but... Hmm, recompiling everything after the data load, though, that's a good thought! 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 Tue, Feb 7, 2012 at 3:10 PM, Bill Hamilton <garage_dba@verizon.net>wrote: > This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . > My question has to do with dbexport/dbimport. > We know that dbexport is writing out the “build index” statements *after* > the stored procedures and functions. > The “update statistics” lines do not have one with “for routine” . > So it seems like the query plans may be sub-optimal for some of the > procedures > and functions. > Maybe that is why I always run slower after importing until I update the > statistics. > What am I missing here? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f234cd3e8adb904b8661bab

Thanks. I did not remember "functional indexes". I have not used them= for=20 anything yet. I guess most folks don't have to contend with dbexport/dbimport very = much. BTW, I also noticed that the new dbexport switch to leave off the own= er=20 (-nw) is not doing that all the way through the export and this causes failures upon import. -----Original Message-----=20 =46rom: Art Kagel Sent: Tuesday, February 07, 2012 3:03 PM To: ids@iiug.org Subject: Re: Fw: Why dbexport places procs before index.... [26203] It's a catch 22 Bill. There may be functional indexes so the function= s have to be printed before indexes. When I was updating myschema to wo= rk better with myexport/myimport I had to deal with this exact issue. My solution was to output functions before tables and indexes and proced= ures after. Not perfect, but... Hmm, recompiling everything after the data load, though, that's a good thought! 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 opini= ons 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 affiliat= ed nor those of the entities themselves. On Tue, Feb 7, 2012 at 3:10 PM, Bill Hamilton <garage_dba@verizon.net= >wrote: > This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . > My question has to do with dbexport/dbimport. > We know that dbexport is writing out the =E2=80=9Cbuild index=E2= =80=9D statements *after* > the stored procedures and functions. > The =E2=80=9Cupdate statistics=E2=80=9D lines do not have one with = =E2=80=9Cfor routine=E2=80=9D . > So it seems like the query plans may be sub-optimal for some of the > procedures > and functions. > Maybe that is why I always run slower after importing until I updat= e the > statistics. > What am I missing here? > >

You can use myschema with the -l option to generate a dbimport compatible schema file and if you add the -O option it will suppress owners and AS "owner" clauses throughout. 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 Tue, Feb 7, 2012 at 5:01 PM, Bill Hamilton <garage_dba@verizon.net>wrote: > Thanks. I did not remember "functional indexes". I have not used them= > for=20 > anything yet. > I guess most folks don't have to contend with dbexport/dbimport very = > much. > BTW, I also noticed that the new dbexport switch to leave off the own= > er=20 > (-nw) is not doing that all the way through the export > and this causes failures upon import. > > -----Original Message-----=20 > =46rom: Art Kagel > Sent: Tuesday, February 07, 2012 3:03 PM > To: ids@iiug.org > Subject: Re: Fw: Why dbexport places procs before index.... [26203] > > It's a catch 22 Bill. There may be functional indexes so the function= > s > have to be printed before indexes. When I was updating myschema to wo= > rk > better with myexport/myimport I had to deal with this exact issue. My > solution was to output functions before tables and indexes and proced= > ures > after. Not perfect, but... Hmm, recompiling everything after the data > load, though, that's a good thought! > > 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 opini= > ons > 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 affiliat= > ed nor > those of the entities themselves. > > On Tue, Feb 7, 2012 at 3:10 PM, Bill Hamilton <garage_dba@verizon.net= > >wrote: > > > This is IDS 11.70.TC4IE on Windows 2008 Enterprise, SP2 . > > My question has to do with dbexport/dbimport. > > We know that dbexport is writing out the =E2=80=9Cbuild index=E2= > =80=9D statements *after* > > the stored procedures and functions. > > The =E2=80=9Cupdate statistics=E2=80=9D lines do not have one with = > =E2=80=9Cfor routine=E2=80=9D . > > So it seems like the query plans may be sub-optimal for some of the > > procedures > > and functions. > > Maybe that is why I always run slower after importing until I updat= > e the > > statistics. > > What am I missing here? > > > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8bac5847ed04b867070c