Hi all.
My group is going to be doing a major database migration in a couple
of weeks and I just discovered the dbcopy utility and thought it would
be just the thing. Unfortunately I have been having problems with
dbcopy. I have spent the last couple of days going over the Informix
Fetch Array documentation and the dbcopy source and I am stumped.


When I run the program with the following command line:
../dbcopy -t item_test -T item_test -d bas -D loutest -g

I get the following results:
Selecting data from bas@mem_dev_tcp with:
SELECT * FROM item_test;


Inserting data to loutest@mem_dev_tcp with:
INSERT INTO item_test (
item_id,
item_class,
item_instance_name,
item_create_date,
item_blob,
acc_id,
item_type,
user_id,
prod_id,
lc_date
) values (
?, ?, ?, ?, ?, ?, ?, ?, ?, ? );

Error fetching row! Code=-19986, ISAM=0.
Segmentation Fault(coredump)

(and what is error -19986: I don't see it documented anywhere or
mentioned on groups anywhere?)




The schema for the table I am testing with is as follows:
DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC2
Copyright (C) Informix Software, Inc., 1984-1997
Software Serial Number AAA#B000000
{ TABLE "klimoffl".item_test row size = 364 number of columns = 10
index size = 0
}
create table "klimoffl".item_test
(
item_id serial not null ,
item_class integer,
item_instance_name varchar(255),
item_create_date datetime year to second
default current year to second,
item_blob text,
acc_id char(10),
item_type char(1),
user_id char(15),
prod_id char(2),
lc_date datetime year to second
default current year to second
);
revoke all on "klimoffl".item_test from "public";


The source table has 24 rows and the largest blob is 1490 bytes. If I
load another 42 rows into the source table (the largest is still 1490
bytest) I get an error -465 "No more memory for locator buffer." My
system has plenty of swap space ...

The esql library I linked against is:
IBM/Informix-Client SDK Version 2.81.UC1
IBM/Informix EMBEDDED SQL for C Version 9.53.UC1
Copyright (C) 1991-2003 IBM

The version of dbcopy is
dbcopy.ec: Revision: 1.40 Copyright 1996-2003 by Art S. Kagel.
License for private usage (including use by commercial
enterprise
for its own needs) is granted. Commercial and exploitation
rights
are reserved.


The database is running IDS v9.40 FC2 and lives on an AlphaServer
running Tru64 5.1B .

dbcopy is running on a Solaris system running Solaris 7.

The compile line for dbcopy was:
esql -static dbcopy.ec -o dbcopy


Thanks in advance - any help would be greatly appreciated,

Lou



BTW, if anybody is listening - here is a comment on the Informix
documentation:
dbcopy deals with several Fetch Array issues that aren't documented
and the example programs ignore. The one I ran into is that the
documentation and the example programs recommend summing sqllen to
determine the length of the input buffer which won't work for the
SQLDTIME data type. dbcopy uses rtypalign() to determine the length of
the input buffer. Also, the examples use sqllen as input to
rtypmsize() but dbcopy hard codes this value for SQLDTIME data type.

Use HPL (High Performance Loader) instead. Inserting each record to databse
will take a looooong time, if you don't have an empty original database.
Check posts in this newsgroup.

"Lou Klimoff" <klimoffl@hotmail.com> wrote in message
news:497dc579.0309220726.afa40b2@posting.google.co m...
> Hi all.
> My group is going to be doing a major database migration in a couple
> of weeks and I just discovered the dbcopy utility and thought it would
> be just the thing. Unfortunately I have been having problems with
> dbcopy. I have spent the last couple of days going over the Informix
> Fetch Array documentation and the dbcopy source and I am stumped.
>
>
> When I run the program with the following command line:
> ./dbcopy -t item_test -T item_test -d bas -D loutest -g
>
> I get the following results:
> Selecting data from bas@mem_dev_tcp with:
> SELECT * FROM item_test;
>
>
> Inserting data to loutest@mem_dev_tcp with:
> INSERT INTO item_test (
> item_id,
> item_class,
> item_instance_name,
> item_create_date,
> item_blob,
> acc_id,
> item_type,
> user_id,
> prod_id,
> lc_date
> ) values (
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
>
> Error fetching row! Code=-19986, ISAM=0.
> Segmentation Fault(coredump)
>
> (and what is error -19986: I don't see it documented anywhere or
> mentioned on groups anywhere?)
>
>
>
>
> The schema for the table I am testing with is as follows:
> DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC2
> Copyright (C) Informix Software, Inc., 1984-1997
> Software Serial Number AAA#B000000
> { TABLE "klimoffl".item_test row size = 364 number of columns = 10
> index size = 0
> }
> create table "klimoffl".item_test
> (
> item_id serial not null ,
> item_class integer,
> item_instance_name varchar(255),
> item_create_date datetime year to second
> default current year to second,
> item_blob text,
> acc_id char(10),
> item_type char(1),
> user_id char(15),
> prod_id char(2),
> lc_date datetime year to second
> default current year to second
> );
> revoke all on "klimoffl".item_test from "public";
>
>
> The source table has 24 rows and the largest blob is 1490 bytes. If I
> load another 42 rows into the source table (the largest is still 1490
> bytest) I get an error -465 "No more memory for locator buffer." My
> system has plenty of swap space ...
>
> The esql library I linked against is:
> IBM/Informix-Client SDK Version 2.81.UC1
> IBM/Informix EMBEDDED SQL for C Version 9.53.UC1
> Copyright (C) 1991-2003 IBM
>
> The version of dbcopy is
> dbcopy.ec: Revision: 1.40 Copyright 1996-2003 by Art S. Kagel.
> License for private usage (including use by commercial
> enterprise
> for its own needs) is granted. Commercial and exploitation
> rights
> are reserved.
>
>
> The database is running IDS v9.40 FC2 and lives on an AlphaServer
> running Tru64 5.1B .
>
> dbcopy is running on a Solaris system running Solaris 7.
>
> The compile line for dbcopy was:
> esql -static dbcopy.ec -o dbcopy
>
>
> Thanks in advance - any help would be greatly appreciated,
>
> Lou
>
>
>
> BTW, if anybody is listening - here is a comment on the Informix
> documentation:
> dbcopy deals with several Fetch Array issues that aren't documented
> and the example programs ignore. The one I ran into is that the
> documentation and the example programs recommend summing sqllen to
> determine the length of the input buffer which won't work for the
> SQLDTIME data type. dbcopy uses rtypalign() to determine the length of
> the input buffer. Also, the examples use sqllen as input to
> rtypmsize() but dbcopy hard codes this value for SQLDTIME data type.

On Mon, 22 Sep 2003 11:26:40 -0400, Lou Klimoff wrote:

Hi,

I'll look into this and see what I can figure out. Meanwhile Milena's
suggestion to use the hploader is not a bad one at all.

I'll also comment on your notes below tomorrow.

Art S. Kagel


> Hi all.
> My group is going to be doing a major database migration in a couple of weeks
> and I just discovered the dbcopy utility and thought it would be just the
> thing. Unfortunately I have been having problems with dbcopy. I have spent the
> last couple of days going over the Informix Fetch Array documentation and the
> dbcopy source and I am stumped.
>
>
> When I run the program with the following command line: ./dbcopy -t item_test
> -T item_test -d bas -D loutest -g
>
> I get the following results:
> Selecting data from bas@mem_dev_tcp with:
> SELECT * FROM item_test;
>
>
> Inserting data to loutest@mem_dev_tcp with:
> INSERT INTO item_test (
> item_id,
> item_class,
> item_instance_name,
> item_create_date,
> item_blob,
> acc_id,
> item_type,
> user_id,
> prod_id,
> lc_date
> ) values (
> ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
>
> Error fetching row! Code=-19986, ISAM=0. Segmentation Fault(coredump)
>
> (and what is error -19986: I don't see it documented anywhere or mentioned on
> groups anywhere?)
>
>
>
>
> The schema for the table I am testing with is as follows: DBSCHEMA Schema
> Utility INFORMIX-SQL Version 9.40.FC2 Copyright (C) Informix Software,
> Inc., 1984-1997 Software Serial Number AAA#B000000 { TABLE
> "klimoffl".item_test row size = 364 number of columns = 10 index size = 0
> }
> create table "klimoffl".item_test
> (
> item_id serial not null ,
> item_class integer,
> item_instance_name varchar(255),
> item_create_date datetime year to second
> default current year to second,
> item_blob text,
> acc_id char(10),
> item_type char(1),
> user_id char(15),
> prod_id char(2),
> lc_date datetime year to second
> default current year to second
> );
> revoke all on "klimoffl".item_test from "public";
>
>
> The source table has 24 rows and the largest blob is 1490 bytes. If I load
> another 42 rows into the source table (the largest is still 1490 bytest) I get
> an error -465 "No more memory for locator buffer." My system has plenty of
> swap space ...
>
> The esql library I linked against is:
> IBM/Informix-Client SDK Version 2.81.UC1 IBM/Informix EMBEDDED SQL for C
> Version 9.53.UC1 Copyright (C) 1991-2003 IBM
>
> The version of dbcopy is
> dbcopy.ec: Revision: 1.40 Copyright 1996-2003 by Art S. Kagel.
> License for private usage (including use by commercial
> enterprise
> for its own needs) is granted. Commercial and exploitation
> rights
> are reserved.
>
>
> The database is running IDS v9.40 FC2 and lives on an AlphaServer running
> Tru64 5.1B .
>
> dbcopy is running on a Solaris system running Solaris 7.
>
> The compile line for dbcopy was:
> esql -static dbcopy.ec -o dbcopy
>
>
> Thanks in advance - any help would be greatly appreciated,
>
> Lou
>
>
>
> BTW, if anybody is listening - here is a comment on the Informix
> documentation:
> dbcopy deals with several Fetch Array issues that aren't documented and the
> example programs ignore. The one I ran into is that the documentation and the
> example programs recommend summing sqllen to determine the length of the input
> buffer which won't work for the SQLDTIME data type. dbcopy uses rtypalign() to
> determine the length of the input buffer. Also, the examples use sqllen as
> input to rtypmsize() but dbcopy hard codes this value for SQLDTIME data type.

O.K. I think I found one small bug in dbcopy -

In the routine void dumprec(struct sqlda *ldesc) the variable alen is
declared but in the CFIXCHARTYPE/CVCHARTYPE case (around line 1418)
alen is not initialized before it is passed to charcpy() . I think it
should have the line:

alen = strip((char *) col->sqldata, col->sqllen);

before the call to charcpy().

Even with that fix it is still not copying the table though :-(

Lou



"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.09.22.17.27.51.663710.10594@bloombe rg.net>...
> On Mon, 22 Sep 2003 11:26:40 -0400, Lou Klimoff wrote:
>
> Hi,
>
> I'll look into this and see what I can figure out. Meanwhile Milena's
> suggestion to use the hploader is not a bad one at all.
>
> I'll also comment on your notes below tomorrow.
>
> Art S. Kagel
>
>
> > Hi all.
> > My group is going to be doing a major database migration in a couple of weeks
> > and I just discovered the dbcopy utility and thought it would be just the
> > thing. Unfortunately I have been having problems with dbcopy. I have spent the
> > last couple of days going over the Informix Fetch Array documentation and the
> > dbcopy source and I am stumped.
> >
> >
> > When I run the program with the following command line: ./dbcopy -t item_test
> > -T item_test -d bas -D loutest -g
> >
> > I get the following results:
> > Selecting data from bas@mem_dev_tcp with:
> > SELECT * FROM item_test;
> >
> >
> > Inserting data to loutest@mem_dev_tcp with:
> > INSERT INTO item_test (
> > item_id,
> > item_class,
> > item_instance_name,
> > item_create_date,
> > item_blob,
> > acc_id,
> > item_type,
> > user_id,
> > prod_id,
> > lc_date
> > ) values (
> > ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
> >
> > Error fetching row! Code=-19986, ISAM=0. Segmentation Fault(coredump)
> >
> > (and what is error -19986: I don't see it documented anywhere or mentioned on
> > groups anywhere?)
> >
> >
> >
> >
> > The schema for the table I am testing with is as follows: DBSCHEMA Schema
> > Utility INFORMIX-SQL Version 9.40.FC2 Copyright (C) Informix Software,
> > Inc., 1984-1997 Software Serial Number AAA#B000000 { TABLE
> > "klimoffl".item_test row size = 364 number of columns = 10 index size = 0
> > }
> > create table "klimoffl".item_test
> > (
> > item_id serial not null ,
> > item_class integer,
> > item_instance_name varchar(255),
> > item_create_date datetime year to second
> > default current year to second,
> > item_blob text,
> > acc_id char(10),
> > item_type char(1),
> > user_id char(15),
> > prod_id char(2),
> > lc_date datetime year to second
> > default current year to second
> > );
> > revoke all on "klimoffl".item_test from "public";
> >
> >
> > The source table has 24 rows and the largest blob is 1490 bytes. If I load
> > another 42 rows into the source table (the largest is still 1490 bytest) I get
> > an error -465 "No more memory for locator buffer." My system has plenty of
> > swap space ...
> >
> > The esql library I linked against is:
> > IBM/Informix-Client SDK Version 2.81.UC1 IBM/Informix EMBEDDED SQL for C
> > Version 9.53.UC1 Copyright (C) 1991-2003 IBM
> >
> > The version of dbcopy is
> > dbcopy.ec: Revision: 1.40 Copyright 1996-2003 by Art S. Kagel.
> > License for private usage (including use by commercial
> > enterprise
> > for its own needs) is granted. Commercial and exploitation
> > rights
> > are reserved.
> >
> >
> > The database is running IDS v9.40 FC2 and lives on an AlphaServer running
> > Tru64 5.1B .
> >
> > dbcopy is running on a Solaris system running Solaris 7.
> >
> > The compile line for dbcopy was:
> > esql -static dbcopy.ec -o dbcopy
> >
> >
> > Thanks in advance - any help would be greatly appreciated,
> >
> > Lou
> >
> >
> >
> > BTW, if anybody is listening - here is a comment on the Informix
> > documentation:
> > dbcopy deals with several Fetch Array issues that aren't documented and the
> > example programs ignore. The one I ran into is that the documentation and the
> > example programs recommend summing sqllen to determine the length of the input
> > buffer which won't work for the SQLDTIME data type. dbcopy uses rtypalign() to
> > determine the length of the input buffer. Also, the examples use sqllen as
> > input to rtypmsize() but dbcopy hard codes this value for SQLDTIME data type.

I've done some more playing with dbcopy - IMHO, this problem is a bug
with the clientsdk.

I created the stores_demo database and created a twin table to the
catalog table in another database. I ran dbcopy on the catalog table
and it ran fine.

Then I created a variant of the catalog table with the varchar field
moved up just before the text field (similar to my other table
discussed below). The variant table looks like so:

DBSCHEMA Schema Utility INFORMIX-SQL Version 7.31.UC6
Copyright (C) Informix Software, Inc., 1984-1998
Software Serial Number ACN#A902708
{ TABLE "klimoffl".catalog2 row size = 377 number of columns = 6 index
size = 12
}
create table "klimoffl".catalog2
(
catalog_num serial not null ,
stock_num smallint not null ,
manu_code char(3) not null ,
cat_advert varchar(255,65),
cat_descr text,
cat_picture byte,
primary key (catalog_num)
);
revoke all on "klimoffl".catalog2 from "public";

Note that the cat_advert column was moved from the last column to just
before cat_descr. I loaded the data from the stores_demo catalog table
(using INSERT ... SELECT FROM...), created an empty twin table and ran
dbcopy - and lo and behold dbcopy crashes (and also crashes the
Informix engine, incidentally). It seems the clientsdk gets VERY
unhappy if a varchar field immediately precedes a text field for Fetch
Arrays.

In further news - I compiled dbcopy with CSDK version 2.40.UC1 and
dbcopy works like a champ - (but at this point I don't trust Fetch
Arrays).


Lou


klimoffl@hotmail.com (Lou Klimoff) wrote in message news:<497dc579.0309240628.3ac3719a@posting.google. com>...
> O.K. I think I found one small bug in dbcopy -
>
> In the routine void dumprec(struct sqlda *ldesc) the variable alen is
> declared but in the CFIXCHARTYPE/CVCHARTYPE case (around line 1418)
> alen is not initialized before it is passed to charcpy() . I think it
> should have the line:
>
> alen = strip((char *) col->sqldata, col->sqllen);
>
> before the call to charcpy().
>
> Even with that fix it is still not copying the table though :-(
>
> Lou
>
>
>
> "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.09.22.17.27.51.663710.10594@bloombe rg.net>...
> > On Mon, 22 Sep 2003 11:26:40 -0400, Lou Klimoff wrote:
> >
> > Hi,
> >
> > I'll look into this and see what I can figure out. Meanwhile Milena's
> > suggestion to use the hploader is not a bad one at all.
> >
> > I'll also comment on your notes below tomorrow.
> >
> > Art S. Kagel
> >
> >
> > > Hi all.
> > > My group is going to be doing a major database migration in a couple of weeks
> > > and I just discovered the dbcopy utility and thought it would be just the
> > > thing. Unfortunately I have been having problems with dbcopy. I have spent the
> > > last couple of days going over the Informix Fetch Array documentation and the
> > > dbcopy source and I am stumped.
> > >
> > >
> > > When I run the program with the following command line: ./dbcopy -t item_test
> > > -T item_test -d bas -D loutest -g
> > >
> > > I get the following results:
> > > Selecting data from bas@mem_dev_tcp with:
> > > SELECT * FROM item_test;
> > >
> > >
> > > Inserting data to loutest@mem_dev_tcp with:
> > > INSERT INTO item_test (
> > > item_id,
> > > item_class,
> > > item_instance_name,
> > > item_create_date,
> > > item_blob,
> > > acc_id,
> > > item_type,
> > > user_id,
> > > prod_id,
> > > lc_date
> > > ) values (
> > > ?, ?, ?, ?, ?, ?, ?, ?, ?, ? );
> > >
> > > Error fetching row! Code=-19986, ISAM=0. Segmentation Fault(coredump)
> > >
> > > (and what is error -19986: I don't see it documented anywhere or mentioned on
> > > groups anywhere?)
> > >
> > >
> > >
> > >
> > > The schema for the table I am testing with is as follows: DBSCHEMA Schema
> > > Utility INFORMIX-SQL Version 9.40.FC2 Copyright (C) Informix Software,
> > > Inc., 1984-1997 Software Serial Number AAA#B000000 { TABLE
> > > "klimoffl".item_test row size = 364 number of columns = 10 index size = 0
> > > }
> > > create table "klimoffl".item_test
> > > (
> > > item_id serial not null ,
> > > item_class integer,
> > > item_instance_name varchar(255),
> > > item_create_date datetime year to second
> > > default current year to second,
> > > item_blob text,
> > > acc_id char(10),
> > > item_type char(1),
> > > user_id char(15),
> > > prod_id char(2),
> > > lc_date datetime year to second
> > > default current year to second
> > > );
> > > revoke all on "klimoffl".item_test from "public";
> > >
> > >
> > > The source table has 24 rows and the largest blob is 1490 bytes. If I load
> > > another 42 rows into the source table (the largest is still 1490 bytest) I get
> > > an error -465 "No more memory for locator buffer." My system has plenty of
> > > swap space ...
> > >
> > > The esql library I linked against is:
> > > IBM/Informix-Client SDK Version 2.81.UC1 IBM/Informix EMBEDDED SQL for C
> > > Version 9.53.UC1 Copyright (C) 1991-2003 IBM
> > >
> > > The version of dbcopy is
> > > dbcopy.ec: Revision: 1.40 Copyright 1996-2003 by Art S. Kagel.
> > > License for private usage (including use by commercial
> > > enterprise
> > > for its own needs) is granted. Commercial and exploitation
> > > rights
> > > are reserved.
> > >
> > >
> > > The database is running IDS v9.40 FC2 and lives on an AlphaServer running
> > > Tru64 5.1B .
> > >
> > > dbcopy is running on a Solaris system running Solaris 7.
> > >
> > > The compile line for dbcopy was:
> > > esql -static dbcopy.ec -o dbcopy
> > >
> > >
> > > Thanks in advance - any help would be greatly appreciated,
> > >
> > > Lou
> > >
> > >
> > >
> > > BTW, if anybody is listening - here is a comment on the Informix
> > > documentation:
> > > dbcopy deals with several Fetch Array issues that aren't documented and the
> > > example programs ignore. The one I ran into is that the documentation and the
> > > example programs recommend summing sqllen to determine the length of the input
> > > buffer which won't work for the SQLDTIME data type. dbcopy uses rtypalign() to
> > > determine the length of the input buffer. Also, the examples use sqllen as
> > > input to rtypmsize() but dbcopy hard codes this value for SQLDTIME data type.