External Tables decimal extract problem
A user running two Informix 11.70 instances on the same Windows 2008 R2 box found that unloading to external tables produced decimal values padded with many trailing zeros (e.g. 2.0000000000000000) on one instance, while the other wrote plain values; dates also differed in format. Suggestions included checking DBFLTMASK via sysmaster:sysenv/sysenvses, database ANSI/NLS/locale settings and onstat -g env, but all came back identical, and setting DBFLTMASK had no effect on the unload. Andreas noted DBFLTMASK applies only to dbaccess/onpload, not to server-side external table unloads, and that the zero-padded output appears to be hard-coded behaviour — the real puzzle being why one instance differed. The thread ends with a request for schemas, version, platform and the unload query; no resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi, I have 2 informix 11.70 instances in the same server on Windows 2008 R2, I export the database with External tables, one of this instances is exporting the decimals whith that format 2.0000000000000000|AAA 1.0000000000000000|AAA the other databasde is exporting decimals very well without adding the zeros 2|AAA 1|AAA the schema table is the same, for example: field1 decimal(1) so the question is why the second instance is adding zeros to decimal fields, and is there a way to tell external tables to not add zeros, because the problem is that the size of export is so big. thanks in advance
The environment DBFLTMASK determines the number of digits that are to be displayed to the right of the decimal symbol when converting a numeric value to a string. It defaults to the maximum #digits available. It is likely the the environment was different for each of the engines at startup or in the environments of the client's querying each. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Mar 22, 2016 at 3:13 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > hi, I have 2 informix 11.70 instances in the same server on Windows 2008 > R2, I > export the database with External tables, one of this instances is > exporting > the decimals whith that format > 2.0000000000000000|AAA > 1.0000000000000000|AAA > the other databasde is exporting decimals very well without adding the > zeros > 2|AAA > 1|AAA > the schema table is the same, for example: field1 decimal(1) > so the question is why the second instance is adding zeros to decimal > fields, > and is there a way to tell external tables to not add zeros, because the > problem is that the size of export is so big. > > thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ecae63926a2052ea82d66
Hi, where can I find this environement variable, the "instance_name.cmd" are the same for the two databases.
For the server's environment:
select *
from sysmaster:sysenv
where env_name = 'DBFLTMASK';
For client session's environment:
select *
from sysmaster:sysenvses
where envses_sid = <session id>
and envses_name = 'DBFLTMASK';
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.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 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, Mar 22, 2016 at 5:17 PM, CHALLENGER212 ABDERRAFI <
abderrafi212@gmail.com> wrote:
> Hi, where can I find this environement variable, the "instance_name.cmd"
> are
> the same for the two databases.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0139fddc721cd3052ea9e422
It returns no rows, I execute on the database and also on sysmaster , the result is empty, should try on sysadmin ?
No. That means defaults are being used, so both engines shoukd be returning the same strings. Time for a PMR Art On Mar 22, 2016 17:52, "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> wrote: > It returns no rows, I execute on the database and also on sysmaster , the > result is empty, should try on sysadmin ? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113eca06d4d6a1052eae496c
Hi, no one return in external table correct values whithout all zeros and the second returns the zeros.
One thing to note about reading from external tables is that by default the files are text so if the zero's are in the file, the will display when you read the data in. The exception would be if the mode of the external table when it was created was "informix" binary mode rather then "fixed" or "delimited".' So, I'm saying that the problem may not be at the read side but on the system where the files were created. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 23, 2016 at 2:19 PM, CHALLENGER212 ABDERRAFI < abderrafi212@gmail.com> wrote: > Hi, no one return in external table correct values whithout all zeros and > the > second returns the zeros. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113eca06a4a3d8052ebb6d6d
But the problem, is that the two instances are on the same server or system, and one is generating the zeros and the other is not ?? I try add set DBFLTMASK on the file ".cmd" but it does not work, it generate always these zeros, so what could be the solution plz..
Are you absolutely sure that the columns in both source databases are defined EXACTLY the same, and that one is not an integer? This just sounds too weird... How have you defined the external tables? -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of CHALLENGER212 ABDERRAFI Sent: Wednesday, March 23, 2016 1:40 PM To: ids@iiug.org Subject: Re: External Tables decimal extract problem [36840] But the problem, is that the two instances are on the same server or system, and one is generating the zeros and the other is not ?? I try add set DBFLTMASK on the file ".cmd" but it does not work, it generate always these zeros, so what could be the solution plz.. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.
The external table is defined with "sameas", and yes the two columns are the
same, its decimal(3) , I check by dbschema of both tables, but the problem is
the fiorst instance generate correctly the numbers and the second not, also I
just notice that the dates for the first are generated in this format
"YYYY-MM-DD" and in the second its generated in this format "DD/MM/YYYY", I
dont understand whtas happening whith this second database ???
This is a long shot, but is one of the databases defined as ansi or with a
different locale?
Does this show the same for your two databases:
select
d.name[1,20],
d.is_ansi,
d.is_nls,
d.is_case_insens ,
l.dbs_collate[1,15]
from sysmaster:sysdatabases d, sysmaster:sysdbslocale l
where d.name = l.dbs_dbsname;
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
CHALLENGER212 ABDERRAFI
Sent: Wednesday, March 23, 2016 2:11 PM
To: ids@iiug.org
Subject: Re: RE: External Tables decimal extract problem [36842]
The external table is defined with "sameas", and yes the two columns are the
same, its decimal(3) , I check by dbschema of both tables, but the problem
is the fiorst instance generate correctly the numbers and the second not,
also I just notice that the dates for the first are generated in this format
"YYYY-MM-DD" and in the second its generated in this format "DD/MM/YYYY", I
dont understand whtas happening whith this second database ???
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Unfortunately the same
name is_ansi is_nls is_case_insens dbs_collate
BaseOK 0 0 0 en_US.819
BaseKO 0 0 0 en_US.819
I have set DBFLTMASK before launching dbacces and I notice that:
For selecting I have the good value for right add zeros, but when I launch
thrue external I have always the same 16 zeros which are added, and its the
same cas for the first database, its ignoring DBFLTMASK when using external
table, so is it a way to add this parameter on the informix engine in order to
be seen by tis query
select *
from sysmaster:sysenvses
where envses_name ='DBFLTMASK';
because, this parameter is not visible on the result of the query even if its
used when extracting to screen via select but not when unloading via external
tables.
what's onstat -g env telling for the two servers?
Some locale setting, or maybe DBMONEY, and probably in server env, might=20
be at play here.
Re. DBFLTMASK, this only applies to dbaccess and onpload clients, not for=20
anything the server would do itself (like unloading to external tables.)
HTH,
Andreas
From: "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com>
To: ids@iiug.org
Date: 23.03.2016 21:51
Subject: Re: RE: RE: External Tables decimal extract pr.... [36844]
Sent by: ids-bounces@iiug.org
Unfortunately the same=20
name is=5Fansi is=5Fnls is=5Fcase=5Finsens dbs=5Fcollate=20
BaseOK 0 0 0 en=5FUS.819=20
BaseKO 0 0 0 en=5FUS.819=20
I have set DBFLTMASK before launching dbacces and I notice that:=20
For selecting I have the good value for right add zeros, but when I launch =
thrue external I have always the same 16 zeros which are added, and its=20
the=20
same cas for the first database, its ignoring DBFLTMASK when using=20
external=20
table, so is it a way to add this parameter on the informix engine in=20
order to=20
be seen by tis query=20
select *=20
from sysmaster:sysenvses=20
where envses=5Fname =3D'DBFLTMASK';=20
because, this parameter is not visible on the result of the query even if=20
its=20
used when extracting to screen via select but not when unloading via=20
external=20
tables.=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Onstat -g env result is the same for two servers, I have create an other database on that server and I have create a table with some decimal(3) fileds, always same problem with external table it add a lot of zeros in extracting thrue external.
From what I could find out this is pretty hard coded behavior, any simple=20 test in default env would show this, and the question rather is why/how=20 can it behave differently in your 'good' case? We'd definitely need more details: - table and ext. table schemas - version - platform - unload query Andreas From: "CHALLENGER212 ABDERRAFI" <abderrafi212@gmail.com> To: ids@iiug.org Date: 24.03.2016 09:41 Subject: Re: RE: RE: External Tables decimal extract pr.... [36846] Sent by: ids-bounces@iiug.org Onstat -g env result is the same for two servers, I have create an other=20 database on that server and I have create a table with some decimal(3)=20 fileds,=20 always same problem with external table it add a lot of zeros in=20 extracting=20 thrue external.=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20