import error
A dbimport into IDS 11.50 on Solaris (data exported from 9.21) failed with error -1218 "String to date conversion error" on a table containing a DATETIME YEAR TO FRACTION(3) column. Setting DBTIME didn't help. Using dbload to see a rejected row revealed the real culprit: the table's plain DATE columns were unloaded as MM/DD/YYYY, while DBDATE was set to Y4MD-. Art Kagel pointed out DBDATE applies to DATE (not DATETIME) columns; changing DBDATE to "MDY4/" fixed the load.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am trying to do an import, but I am having an issue. A table has a
field with the following type:
datetime year to fraction(3)
When I run the dbimport, I get the following message:
-1218 String to date conversion error
The unload file has what looks like a date to me:
2005-10-19 08:12:22.000
I also checked to make sure that the unload file has the correct number
of fields and it does. I also tried to create the table and then load
the file in dbaccess using the "load from..." sql and it gave me the
same error. Has anyone else encountered this and figured out a way to
resolve it? I'm looking online but I haven't found anything useful.
I am running this on a Sun Solaris 10 box with Informix version
11.50.UC2.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
Regards.
Have you tryed to import the data between 'XXXXX' ???
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
Schleicher, Keith escreveu:
> I am trying to do an import, but I am having an issue. A table has a
> field with the following type:
>
> datetime year to fraction(3)
>
> When I run the dbimport, I get the following message:
>
> -1218 String to date conversion error
>
> The unload file has what looks like a date to me:
>
> 2005-10-19 08:12:22.000
>
> I also checked to make sure that the unload file has the correct number
> of fields and it does. I also tried to create the table and then load
> the file in dbaccess using the "load from..." sql and it gave me the
> same error. Has anyone else encountered this and figured out a way to
> resolve it? I'm looking online but I haven't found anything useful.
>
> I am running this on a Sun Solaris 10 box with Informix version
> 11.50.UC2.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
I have been able to create other tables and import the data through dbimport,
but this table doesn't seem to want to get the data loaded.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Alexandre Marini
Sent: Friday, July 31, 2009 12:07 PM
To: ids@iiug.org
Subject: Re: import error [16541]
Regards.
Have you tryed to import the data between 'XXXXX' ???
Alexandre Marini
Tecnologia da Informação - DBA
SEFAZ-MS / SGI-UIMP / Sistemas IBM-Informix
Schleicher, Keith escreveu:
> I am trying to do an import, but I am having an issue. A table has a
> field with the following type:
>
> datetime year to fraction(3)
>
> When I run the dbimport, I get the following message:
>
> -1218 String to date conversion error
>
> The unload file has what looks like a date to me:
>
> 2005-10-19 08:12:22.000
>
> I also checked to make sure that the unload file has the correct number
> of fields and it does. I also tried to create the table and then load
> the file in dbaccess using the "load from..." sql and it gave me the
> same error. Has anyone else encountered this and figured out a way to
> resolve it? I'm looking online but I haven't found anything useful.
>
> I am running this on a Sun Solaris 10 box with Informix version
> 11.50.UC2.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Try explicitly setting the environment variable DBTIME as follows:
export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
That's the default for the default domain and matches your input strings.
Don't know why it's not working. Are there any spaces around the datetime
string in the input field?
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I am trying to do an import, but I am having an issue. A table has a
> field with the following type:
>
> datetime year to fraction(3)
>
> When I run the dbimport, I get the following message:
>
> -1218 String to date conversion error
>
> The unload file has what looks like a date to me:
>
> 2005-10-19 08:12:22.000
>
> I also checked to make sure that the unload file has the correct number
> of fields and it does. I also tried to create the table and then load
> the file in dbaccess using the "load from..." sql and it gave me the
> same error. Has anyone else encountered this and figured out a way to
> resolve it? I'm looking online but I haven't found anything useful.
>
> I am running this on a Sun Solaris 10 box with Informix version
> 11.50.UC2.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b1f4831dbb047003a4fe
I did that and I'm still getting the same error. I am just doing a
"dbimport <databasename> -d <dbspacename>" command.
There aren't any spaces around the datetime string. In the unload file
it is |datetime|.
I did see something about older versions of Informix giving this error
when the field size is greater than 20 characters and the query is doing
an insert. I don't know if this was fixed, or if it could be because I
exported the data from a server that is running Informix 9.21.UC2.
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Friday, July 31, 2009 12:21 PM
To: ids@iiug.org
Subject: Re: import error [16543]
Try explicitly setting the environment variable DBTIME as follows:
export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
That's the default for the default domain and matches your input
strings.
Don't know why it's not working. Are there any spaces around the
datetime
string in the input field?
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I am trying to do an import, but I am having an issue. A table has a
> field with the following type:
>
> datetime year to fraction(3)
>
> When I run the dbimport, I get the following message:
>
> -1218 String to date conversion error
>
> The unload file has what looks like a date to me:
>
> 2005-10-19 08:12:22.000
>
> I also checked to make sure that the unload file has the correct
number
> of fields and it does. I also tried to create the table and then load
> the file in dbaccess using the "load from..." sql and it gave me the
> same error. Has anyone else encountered this and figured out a way to
> resolve it? I'm looking online but I haven't found anything useful.
>
> I am running this on a Sun Solaris 10 box with Informix version
> 11.50.UC2.
>
> Keith Schleicher
>
> IT Database Administrator
>
> B2-253B-A
>
> Office: 847-286-4027
>
> Cell: 224-210-8358
>
> Blackberry: 2242108358@messaging.sprintpcs.com
>
> Page: 2242108358@sprint.skytel.com
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b1f4831dbb047003a4fe
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
The server the data was exported from should not have caused this error. It
may be that there are one or two rows in the export file that are not
properly formatted, which may have been caused by a bug in the 9.21
dbexport. One way to check would be to create the table by hand and import
that single file (the filename is listed in a comment in the .sql file in
the dbexport .exp directory) using dbload. Give dbload an error count (say
1000) to ignore before erroring out and a file to put rejected records into
and you'll find the offending rows easily.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 1:59 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I did that and I'm still getting the same error. I am just doing a
> "dbimport <databasename> -d <dbspacename>" command.
>
> There aren't any spaces around the datetime string. In the unload file
> it is |datetime|.
>
> I did see something about older versions of Informix giving this error
> when the field size is greater than 20 characters and the query is doing
> an insert. I don't know if this was fixed, or if it could be because I
> exported the data from a server that is running Informix 9.21.UC2.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, July 31, 2009 12:21 PM
> To: ids@iiug.org
> Subject: Re: import error [16543]
>
> Try explicitly setting the environment variable DBTIME as follows:
>
> export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
>
> That's the default for the default domain and matches your input
> strings.
> Don't know why it's not working. Are there any spaces around the
> datetime
> string in the input field?
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I am trying to do an import, but I am having an issue. A table has a
> > field with the following type:
> >
> > datetime year to fraction(3)
> >
> > When I run the dbimport, I get the following message:
> >
> > -1218 String to date conversion error
> >
> > The unload file has what looks like a date to me:
> >
> > 2005-10-19 08:12:22.000
> >
> > I also checked to make sure that the unload file has the correct
> number
> > of fields and it does. I also tried to create the table and then load
> > the file in dbaccess using the "load from..." sql and it gave me the
> > same error. Has anyone else encountered this and figured out a way to
> > resolve it? I'm looking online but I haven't found anything useful.
> >
> > I am running this on a Sun Solaris 10 box with Informix version
> > 11.50.UC2.
> >
> > Keith Schleicher
> >
> > IT Database Administrator
> >
> > B2-253B-A
> >
> > Office: 847-286-4027
> >
> > Cell: 224-210-8358
> >
> > Blackberry: 2242108358@messaging.sprintpcs.com
> >
> > Page: 2242108358@sprint.skytel.com
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636c5b1f4831dbb047003a4fe
>
> ************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b7122a077a04700454f2
I tried using dbload and had the same issue with every single line.
Here is the output:
0 Row(s) loaded so far to table mqlog.
In INSERT statement number 1 of raw data file
./db_nros.exp/mqlog00104.unl.
Row number 101 is bad.
009|0002501|03/02/2006|00614091|1|42512|000|2005-10-19
08:12:24.000|S|C|R|000000440|03/02/2006|03/02/2006|03/03/2006|03/03/2006
|4.0|4.0|0.0|0.0|N|N|N|00| | |G|00|G|NONAME|1138939301|R|
String to date conversion error
I have checked the DBDATE and that looks fine:
IFMX11:>echo $DBDATE
Y4MD-
Looks like all of the rows are "offenders."
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Friday, July 31, 2009 1:10 PM
To: ids@iiug.org
Subject: Re: import error [16545]
The server the data was exported from should not have caused this error.
It
may be that there are one or two rows in the export file that are not
properly formatted, which may have been caused by a bug in the 9.21
dbexport. One way to check would be to create the table by hand and
import
that single file (the filename is listed in a comment in the .sql file
in
the dbexport .exp directory) using dbload. Give dbload an error count
(say
1000) to ignore before erroring out and a file to put rejected records
into
and you'll find the offending rows easily.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 1:59 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I did that and I'm still getting the same error. I am just doing a
> "dbimport <databasename> -d <dbspacename>" command.
>
> There aren't any spaces around the datetime string. In the unload file
> it is |datetime|.
>
> I did see something about older versions of Informix giving this error
> when the field size is greater than 20 characters and the query is
doing
> an insert. I don't know if this was fixed, or if it could be because I
> exported the data from a server that is running Informix 9.21.UC2.
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, July 31, 2009 12:21 PM
> To: ids@iiug.org
> Subject: Re: import error [16543]
>
> Try explicitly setting the environment variable DBTIME as follows:
>
> export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
>
> That's the default for the default domain and matches your input
> strings.
> Don't know why it's not working. Are there any spaces around the
> datetime
> string in the input field?
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I am trying to do an import, but I am having an issue. A table has a
> > field with the following type:
> >
> > datetime year to fraction(3)
> >
> > When I run the dbimport, I get the following message:
> >
> > -1218 String to date conversion error
> >
> > The unload file has what looks like a date to me:
> >
> > 2005-10-19 08:12:22.000
> >
> > I also checked to make sure that the unload file has the correct
> number
> > of fields and it does. I also tried to create the table and then
load
> > the file in dbaccess using the "load from..." sql and it gave me the
> > same error. Has anyone else encountered this and figured out a way
to
> > resolve it? I'm looking online but I haven't found anything useful.
> >
> > I am running this on a Sun Solaris 10 box with Informix version
> > 11.50.UC2.
> >
> > Keith Schleicher
> >
> > IT Database Administrator
> >
> > B2-253B-A
> >
> > Office: 847-286-4027
> >
> > Cell: 224-210-8358
> >
> > Blackberry: 2242108358@messaging.sprintpcs.com
> >
> > Page: 2242108358@sprint.skytel.com
> >
> >
> >
> >
>
************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636c5b1f4831dbb047003a4fe
>
>
************************************************************************
> *******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636c5b7122a077a04700454f2
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
WHOA! First, DBDATE isn't used for DATETIME columns, DBTIME is. DBDATE is
used to convert DATE type columns and you have it set wrong for the format
of the five DATE columns in that data set. The order of fields and the
field delimiter are all incorrect. Change DBDATE to "MDY4/" and all should
be well.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 2:23 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I tried using dbload and had the same issue with every single line.
> Here is the output:
>
> 0 Row(s) loaded so far to table mqlog.
>
> In INSERT statement number 1 of raw data file
> ../db_nros.exp/mqlog00104.unl.
> Row number 101 is bad.
> 009|0002501|03/02/2006|00614091|1|42512|000|2005-10-19
> 08:12:24.000|S|C|R|000000440|03/02/2006|03/02/2006|03/03/2006|03/03/2006
> |4.0|4.0|0.0|0.0|N|N|N|00| | |G|00|G|NONAME|1138939301|R|
>
> String to date conversion error
>
> I have checked the DBDATE and that looks fine:
>
> IFMX11:>echo $DBDATE
> Y4MD-
>
> Looks like all of the rows are "offenders."
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, July 31, 2009 1:10 PM
> To: ids@iiug.org
> Subject: Re: import error [16545]
>
> The server the data was exported from should not have caused this error.
> It
> may be that there are one or two rows in the export file that are not
> properly formatted, which may have been caused by a bug in the 9.21
> dbexport. One way to check would be to create the table by hand and
> import
> that single file (the filename is listed in a comment in the .sql file
> in
> the dbexport .exp directory) using dbload. Give dbload an error count
> (say
> 1000) to ignore before erroring out and a file to put rejected records
> into
> and you'll find the offending rows easily.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 1:59 PM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I did that and I'm still getting the same error. I am just doing a
> > "dbimport <databasename> -d <dbspacename>" command.
> >
> > There aren't any spaces around the datetime string. In the unload file
>
> > it is |datetime|.
> >
> > I did see something about older versions of Informix giving this error
>
> > when the field size is greater than 20 characters and the query is
> doing
> > an insert. I don't know if this was fixed, or if it could be because I
>
> > exported the data from a server that is running Informix 9.21.UC2.
> >
> > Keith Schleicher
> > IT Database Administrator
> > B2-253B-A
> > Office: 847-286-4027
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Friday, July 31, 2009 12:21 PM
> > To: ids@iiug.org
> > Subject: Re: import error [16543]
> >
> > Try explicitly setting the environment variable DBTIME as follows:
> >
> > export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
> >
> > That's the default for the default domain and matches your input
> > strings.
> > Don't know why it's not working. Are there any spaces around the
> > datetime
> > string in the input field?
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
> > Keith.Schleicher@searshc.com> wrote:
> >
> > > I am trying to do an import, but I am having an issue. A table has a
>
> > > field with the following type:
> > >
> > > datetime year to fraction(3)
> > >
> > > When I run the dbimport, I get the following message:
> > >
> > > -1218 String to date conversion error
> > >
> > > The unload file has what looks like a date to me:
> > >
> > > 2005-10-19 08:12:22.000
> > >
> > > I also checked to make sure that the unload file has the correct
> > number
> > > of fields and it does. I also tried to create the table and then
> load
> > > the file in dbaccess using the "load from..." sql and it gave me the
>
> > > same error. Has anyone else encountered this and figured out a way
> to
> > > resolve it? I'm looking online but I haven't found anything useful.
> > >
> > > I am running this on a Sun Solaris 10 box with Informix version
> > > 11.50.UC2.
> > >
> > > Keith Schleicher
> > >
> > > IT Database Administrator
> > >
> > > B2-253B-A
> > >
> > > Office: 847-286-4027
> > >
> > > Cell: 224-210-8358
> > >
> > > Blackberry: 2242108358@messaging.sprintpcs.com
> > >
> > > Page: 2242108358@sprint.skytel.com
> > >
> > >
> > >
> > >
> >
> ************************************************************************
>
> > *******
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --001636c5b1f4831dbb047003a4fe
> >
> >
> ************************************************************************
>
> > *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
> ************************************************************************
> *******
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636c5b7122a077a04700454f2
>
> *************************************************************
It's working now. Thanks!
Keith Schleicher
IT Database Administrator
B2-253B-A
Office: 847-286-4027
Cell: 224-210-8358
Blackberry: 2242108358@messaging.sprintpcs.com
Page: 2242108358@sprint.skytel.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Art Kagel
Sent: Friday, July 31, 2009 1:38 PM
To: ids@iiug.org
Subject: Re: import error [16547]
WHOA! First, DBDATE isn't used for DATETIME columns, DBTIME is. DBDATE
is
used to convert DATE type columns and you have it set wrong for the
format
of the five DATE columns in that data set. The order of fields and the
field delimiter are all incorrect. Change DBDATE to "MDY4/" and all
should
be well.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and
do not reflect on my employer, Oninit, the IIUG, nor any other
organization
with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 2:23 PM, Schleicher, Keith <
Keith.Schleicher@searshc.com> wrote:
> I tried using dbload and had the same issue with every single line.
> Here is the output:
>
> 0 Row(s) loaded so far to table mqlog.
>
> In INSERT statement number 1 of raw data file
> ../db_nros.exp/mqlog00104.unl.
> Row number 101 is bad.
> 009|0002501|03/02/2006|00614091|1|42512|000|2005-10-19
>
08:12:24.000|S|C|R|000000440|03/02/2006|03/02/2006|03/03/2006|03/03/2006
> |4.0|4.0|0.0|0.0|N|N|N|00| | |G|00|G|NONAME|1138939301|R|
>
> String to date conversion error
>
> I have checked the DBDATE and that looks fine:
>
> IFMX11:>echo $DBDATE
> Y4MD-
>
> Looks like all of the rows are "offenders."
>
> Keith Schleicher
> IT Database Administrator
> B2-253B-A
> Office: 847-286-4027
> Cell: 224-210-8358
> Blackberry: 2242108358@messaging.sprintpcs.com
> Page: 2242108358@sprint.skytel.com
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, July 31, 2009 1:10 PM
> To: ids@iiug.org
> Subject: Re: import error [16545]
>
> The server the data was exported from should not have caused this
error.
> It
> may be that there are one or two rows in the export file that are not
> properly formatted, which may have been caused by a bug in the 9.21
> dbexport. One way to check would be to create the table by hand and
> import
> that single file (the filename is listed in a comment in the .sql file
> in
> the dbexport .exp directory) using dbload. Give dbload an error count
> (say
> 1000) to ignore before erroring out and a file to put rejected records
> into
> and you'll find the offending rows easily.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own
opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other
> organization
> with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 1:59 PM, Schleicher, Keith <
> Keith.Schleicher@searshc.com> wrote:
>
> > I did that and I'm still getting the same error. I am just doing a
> > "dbimport <databasename> -d <dbspacename>" command.
> >
> > There aren't any spaces around the datetime string. In the unload
file
>
> > it is |datetime|.
> >
> > I did see something about older versions of Informix giving this
error
>
> > when the field size is greater than 20 characters and the query is
> doing
> > an insert. I don't know if this was fixed, or if it could be because
I
>
> > exported the data from a server that is running Informix 9.21.UC2.
> >
> > Keith Schleicher
> > IT Database Administrator
> > B2-253B-A
> > Office: 847-286-4027
> > Cell: 224-210-8358
> > Blackberry: 2242108358@messaging.sprintpcs.com
> > Page: 2242108358@sprint.skytel.com
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
Of
> > Art Kagel
> > Sent: Friday, July 31, 2009 12:21 PM
> > To: ids@iiug.org
> > Subject: Re: import error [16543]
> >
> > Try explicitly setting the environment variable DBTIME as follows:
> >
> > export DBTIME="'%Y-%m-%d %H:%M:%S.%F5"
> >
> > That's the default for the default domain and matches your input
> > strings.
> > Don't know why it's not working. Are there any spaces around the
> > datetime
> > string in the input field?
> >
> > Art
> >
> > Art S. Kagel
> > Oninit (www.oninit.com)
> > IIUG Board of Directors (art@iiug.org)
> >
> > Disclaimer: Please keep in mind that my own opinions are my own
> opinions
> > and
> > do not reflect on my employer, Oninit, the IIUG, nor any other
> > organization
> > with which I am associated either explicitly or implicitly. 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 Fri, Jul 31, 2009 at 12:20 PM, Schleicher, Keith <
> > Keith.Schleicher@searshc.com> wrote:
> >
> > > I am trying to do an import, but I am having an issue. A table has
a
>
> > > field with the following type:
> > >
> > > datetime year to fraction(3)
> > >
> > > When I run the dbimport, I get the following message:
> > >
> > > -1218 String to date conversion error
> > >
> > > The unload file has what looks like a date to me:
> > >
> > > 2005-10-19 08:12:22.000
> > >
> > > I also checked to make sure that the unload file has the correct
> > number
> > > of fields and it does. I also tried to create the table and then
> load
> > > the file in dbaccess using the "load from..." sql and it gave me
the
>
> > > same error. Has anyone else encountered this and figured out a way
> to
> > > resolve it? I'm looking online but I haven't found anything
useful.
> > >
> > > I am running this on a Sun Solaris 10 box with Informix version
> > > 11.50.UC2.
> > >
> > > Keith Schleicher
> > >
> > > IT Database Administrator
> > >
> > > B2-253B-A
> > >
> > > Office: 847-286-4027
> > >
> > > Cell: 224-210-8358
> > >
> > > Blackberry: 2242108358@messaging.sprintpcs.com
> > >
> > > Page: 2242108358@sprint.skytel.com
> > >
> > >
> > >
> > >
> >
>
************************************************************************
>
> > *******
> > > Forum Note: Use "Reply" to post a response in the discussion
forum.
> > >
>
On Fri, Jul 31, 2009 at 09:20, Schleicher,
Keith<Keith.Schleicher@searshc.com> wrote:
> I am trying to do an import, but I am having an issue. A table has a
> field with the following type:
>
> datetime year to fraction(3)
>
> When I run the dbimport, I get the following message:
>
> -1218 String to date conversion error
>
> The unload file has what looks like a date to me:
>
> 2005-10-19 08:12:22.000
>
> I also checked to make sure that the unload file has the correct number
> of fields and it does. I also tried to create the table and then load
> the file in dbaccess using the "load from..." sql and it gave me the
> same error. Has anyone else encountered this and figured out a way to
> resolve it? I'm looking online but I haven't found anything useful.
>
> I am running this on a Sun Solaris 10 box with Informix version
> 11.50.UC2.
If setting DBTIME explicitly fixes the issue (as some emails suggest),
then either you have DBTIME set to an invalid value (or to a value
that's not valid for this data) or there is a bug, perhaps an
uninitialized variable bug, in DB-Import. Please check whether you
have DBTIME set normally, and whether the problem reproduces when the
env var is not set. If so, then I think you should contact
IBM/Informix Tech Support.
I don't have a high regard for the design of DBTIME (reasons available
in old diatribes, I think; I don't plan to repeat them here) - but if
it works for you, that's good.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/
"Blessed are we who can laugh at ourselves, for we shall never cease
to be amused."
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.
Stephen Leacock - "I detest life-insurance agents: they always argue
that I shall some day die, which is not so." -
http://www.brainyquote.com/quotes/authors/s/stephen_leacock.html