output of dbexport
In Informix 11.50.FC8, dbexport output inconsistently omits owner prefixes from UPDATE STATISTICS statements while including them in other database objects, causing errors when recreating ANSI databases. Jonathan Leffler confirmed this is a bug; the owner prefix should appear in all statements unless the '-nw' flag is used. Upgrading to 11.70 was suggested as a potential solution.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, Guys,
Build Version: 11.50.FC8
Build OS: Linux 2.6.9-34.ELsmp
The output of dbexport normally has the owner in front of a object, like,
create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
.....
grant select on "informix".inv_np_fixed to "public" as "informix";
Where "fqu" and " informix" is the owner. Apparently, the owner is needed
when you want to create a ANSI database from the dbexport output.
But, the Update Statistics statements( generated by dbexport) do not have
the owner pre-appended , like,
update statistics high for table acq_req_rig (acq_req_id) resolution0.50000 ;
update statistics medium for table acq_req_rig (descriptor_id, file_name,
host_name, host_os, host_path,host_proto, pattern)resolution 2.500000.95000 ;
..................
Which can not be used to create ANSI database directly.
Is there a option to allow the dbexport generated Update Statistics
statements to have the owner name pre-append the table name?
Thanks,
Frank
--00151747b28652b94404ae17a795
Yesssssssssssssssssss
Upgrade your version to 11.70 releases!
There´s a new flag on dbexport/import for this.
Regards.
Em 29/09/2011 13:15, FRANK escreveu:
> Hi, Guys,
>
> Build Version: 11.50.FC8
> Build OS: Linux 2.6.9-34.ELsmp
>
> The output of dbexport normally has the owner in front of a object, like,
>
> create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
> ......
>
> grant select on "informix".inv_np_fixed to "public" as "informix";>
> Where "fqu" and " informix" is the owner. Apparently, the owner is needed
> when you want to create a ANSI database from the dbexport output.
>
> But, the Update Statistics statements( generated by dbexport) do not have
> the owner pre-appended , like,
>
> update statistics high for table acq_req_rig (acq_req_id) resolution> 0.50000 ;
> update statistics medium for table acq_req_rig (descriptor_id, file_name,
> host_name, host_os, host_path,host_proto, pattern)resolution 2.50000> 0.95000 ;
> ...................
>
> Which can not be used to create ANSI database directly.
>
> Is there a option to allow the dbexport generated Update Statistics
> statements to have the owner name pre-append the table name?
>
> Thanks,
> Frank
>
> --00151747b28652b94404ae17a795
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
<Cert-Info-Mgmt_color.jpg>
IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
V11.70
IBM Information Management Informix Technical Professional v3
Sorry, the new feature I mean was "-nw" flag, that totally supress the
owners of database objects.
The link below shows it:
http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.mig.doc/ids_
mig_116.htm?resultof=%22%64%62%65%78%70%6f%72%74%22%20
I don´t know if you really need table owners in update statistic
commands, I never used it.
Regards.
Em 29/09/2011 13:25, Alexandre Marini escreveu:
> Yesssssssssssssssssss
> Upgrade your version to 11.70 releases!
> There´s a new flag on dbexport/import for this.
>
> Regards.
>
> Em 29/09/2011 13:15, FRANK escreveu:
>> Hi, Guys,
>>
>> Build Version: 11.50.FC8
>> Build OS: Linux 2.6.9-34.ELsmp
>>
>> The output of dbexport normally has the owner in front of a object, like,
>>
>> create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
>> ......
>>
>> grant select on "informix".inv_np_fixed to "public" as "informix";>>
>> Where "fqu" and " informix" is the owner. Apparently, the owner is needed
>> when you want to create a ANSI database from the dbexport output.
>>
>> But, the Update Statistics statements( generated by dbexport) do not have
>> the owner pre-appended , like,
>>
>> update statistics high for table acq_req_rig (acq_req_id) resolution>> 0.50000 ;
>> update statistics medium for table acq_req_rig (descriptor_id, file_name,
>> host_name, host_os, host_path,host_proto, pattern)resolution 2.50000>> 0.95000 ;
>> ...................
>>
>> Which can not be used to create ANSI database directly.
>>
>> Is there a option to allow the dbexport generated Update Statistics
>> statements to have the owner name pre-append the table name?
>>
>> Thanks,
>> Frank
>>
>> --00151747b28652b94404ae17a795
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
>
> --
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
>
> <Cert-Info-Mgmt_color.jpg>
>
> IBM Certified System Administrator - Informix Dynamic Server V10 / V11
> / V11.70
>
> IBM Information Management Informix Technical Professional v3
>
--
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
<Cert-Info-Mgmt_color.jpg>
IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
V11.70
IBM Information Management Informix Technical Professional v3
Alex,
Yes. I did not care that before either.
The reason for the needed owner is that it will report error on the "update
statistics.." if you take the output to create a ANSI database.
Thanks,
Frank
On Thu, Sep 29, 2011 at 1:45 PM, Alexandre Marini <amarini@fazenda.ms.gov.br
> wrote:
> Sorry, the new feature I mean was "-nw" flag, that totally supress the
> owners of database objects.
>
> The link below shows it:
>
>
http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.mig.doc/ids_
mig_116.htm?resultof=%22%64%62%65%78%70%6f%72%74%22%20
>
> I don´t know if you really need table owners in update statistic commands,
> I never used it.
>
> Regards.
>
> Em 29/09/2011 13:25, Alexandre Marini escreveu:
>
> Yesssssssssssssssssss
> Upgrade your version to 11.70 releases!
> There´s a new flag on dbexport/import for this.
>
> Regards.
>
> Em 29/09/2011 13:15, FRANK escreveu:
>
> Hi, Guys,
>
> Build Version: 11.50.FC8
> Build OS: Linux 2.6.9-34.ELsmp
>
> The output of dbexport normally has the owner in front of a object, like,
>
> create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
> ......
>
> grant select on "informix".inv_np_fixed to "public" as "informix";>
> Where "fqu" and " informix" is the owner. Apparently, the owner is needed
> when you want to create a ANSI database from the dbexport output.
>
> But, the Update Statistics statements( generated by dbexport) do not have
> the owner pre-appended , like,
>
> update statistics high for table acq_req_rig (acq_req_id) resolution> 0.50000 ;
> update statistics medium for table acq_req_rig (descriptor_id, file_name,
> host_name, host_os, host_path,host_proto, pattern)resolution 2.50000> 0.95000 ;
> ...................
>
> Which can not be used to create ANSI database directly.
>
> Is there a option to allow the dbexport generated Update Statistics
> statements to have the owner name pre-append the table name?
>
> Thanks,
> Frank
>
> --00151747b28652b94404ae17a795
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
>
> --
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
>
> <http://Cert-Info-Mgmt_color.jpg>****
>
> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
> V11.70****
>
> IBM Information Management Informix Technical Professional v3****
>
> ** **
>
>
>
> --
>
> Alexandre Marini
>
> Tecnologia da Informação - DBA
>
> SEFAZ-MS / SGI-UGSR / Sistemas IBM-Informix
>
> <http://Cert-Info-Mgmt_color.jpg>****
>
> IBM Certified System Administrator - Informix Dynamic Server V10 / V11 /
> V11.70****
>
> IBM Information Management Informix Technical Professional v3****
>
> ** **
>
--0015174781e6e055cd04ae18611a
On Thu, Sep 29, 2011 at 10:15, FRANK <yunyaoqu@gmail.com> wrote:
> Build Version: 11.50.FC8
> Build OS: Linux 2.6.9-34.ELsmp
>
> The output of dbexport normally has the owner in front of a object, like,
>
> create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
> ......
>
> grant select on "informix".inv_np_fixed to "public" as "informix";>
> Where "fqu" and " informix" is the owner. Apparently, the owner is needed
> when you want to create a ANSI database from the dbexport output.
>
> But, the Update Statistics statements( generated by dbexport) do not have
> the owner pre-appended , like,
>
> update statistics high for table acq_req_rig (acq_req_id) resolution> 0.50000 ;
> update statistics medium for table acq_req_rig (descriptor_id, file_name,
> host_name, host_os, host_path,host_proto, pattern)resolution 2.50000> 0.95000 ;
> ...................
>
That's a bug. Please report it as such. The table names should have the
owner prefix (unless the '-nw' option Alexandre referred to is in effect),
even in UPDATE STATISTICS statements. If need so be, you can tell them I
said so.
> Which can not be used to create ANSI database directly.
>
> Is there a option to allow the dbexport generated Update Statistics
> statements to have the owner name pre-append the table name?
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--000e0cd3421c831a4304ae18b748
Get my dbschema replacement utility, myschema. The '-l' option with -u will
generate a schema file that is compatible with dbimport. By default it will
include owner clauses in all object references (-O turns them off across the
board if you need that). You can substitute the myschema output file for
the one that dbexport produces with one caviat - if the number of rows in
the tables has changed between running dbexport and running myschema
dbimport will complain, so you may have to edit the file to patch the row
counts.
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, Sep 29, 2011 at 1:15 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Hi, Guys,
>
> Build Version: 11.50.FC8
> Build OS: Linux 2.6.9-34.ELsmp
>
> The output of dbexport normally has the owner in front of a object, like,
>
> create trigger "fqu".abc_select_a_trigger select on "fqu".abc_test
> ......
>
> grant select on "informix".inv_np_fixed to "public" as "informix";>
> Where "fqu" and " informix" is the owner. Apparently, the owner is needed
> when you want to create a ANSI database from the dbexport output.
>
> But, the Update Statistics statements( generated by dbexport) do not have
> the owner pre-appended , like,
>
> update statistics high for table acq_req_rig (acq_req_id) resolution> 0.50000 ;
> update statistics medium for table acq_req_rig (descriptor_id, file_name,
> host_name, host_os, host_path,host_proto, pattern)resolution 2.50000> 0.95000 ;
> ...................
>
> Which can not be used to create ANSI database directly.
>
> Is there a option to allow the dbexport generated Update Statistics
> statements to have the owner name pre-append the table name?
>
> Thanks,
> Frank
>
> --00151747b28652b94404ae17a795
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--90e6ba212485cf0e3d04ae45ef6e