Informix

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

As Mike said - the "- ss" is 99 % obligatory. Just keep in mind to remove old "<dbname>.exp" directory before new execution with "-ss" option.

After that you can change(if it is necessary):

• lock mode
• name of dbspaces, blobspaces, sbspaces
• partition/fragmentation strategy and so on...

Regards,

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Guillermo:

OK, I am assuming that the iurixdbs dbspace is default pagesize (2K? 4K?) and the tca_texto column is being stored in-table so in the same dbspace. I do see that the extent sizing is reasonably large (I feared that the table was being created with the default extent sizing of 8, 8), so that's not the issue.

You are not using replication (HDR or RSS) so it might be better to move that column into a blobspace. I don't think that should cause the load of that table to hang though. I do notice from the onstat -x output you sent previously that you seem to be stuck in a long checkpoint. Are the logical logs full and not backed up? What do 'onstat -F' and 'onstat -R' show?

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Hello Art, Here I show you the results of the commands you request:

Guillermo:

OK, I am assuming that the iurixdbs dbspace is default pagesize (2K? 4K?) and the tca_texto column is being stored in-table so in the same dbspace. I do see that the extent sizing is reasonably large (I feared that the table was being created with the default extent sizing of 8, 8), so that's not the issue.

You are not using replication (HDR or RSS) so it might be better to move that column into a blobspace. I don't think that should cause the load of that table to hang though. I do notice from the onstat -x output you sent previously that you seem to be stuck in a long checkpoint. Are the logical logs full and not backed up? What do 'onstat -F' and 'onstat -R' show?

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Guillermo:

The FGWrites number of almost 103,000 is a telling smoking gun. That value should ALWAYS be zero (OK an occasional 1 is acceptable). You either have a serious IO performance problem or a serious server configuration problem! You should NEVER see Foreground Writes in your server.

If this is a production server or otherwise you want professional help with this, reach out to me directly or through my web site (https://www.askdbmgt.com/).

Hello Art, Here I show you the results of the commands you request:

Guillermo:

OK, I am assuming that the iurixdbs dbspace is default pagesize (2K? 4K?) and the tca_texto column is being stored in-table so in the same dbspace. I do see that the extent sizing is reasonably large (I feared that the table was being created with the default extent sizing of 8, 8), so that's not the issue.

You are not using replication (HDR or RSS) so it might be better to move that column into a blobspace. I don't think that should cause the load of that table to hang though. I do notice from the onstat -x output you sent previously that you seem to be stuck in a long checkpoint. Are the logical logs full and not backed up? What do 'onstat -F' and 'onstat -R' show?

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Guillermo:

The FGWrites number of almost 103,000 is a telling smoking gun. That value should ALWAYS be zero (OK an occasional 1 is acceptable). You either have a serious IO performance problem or a serious server configuration problem! You should NEVER see Foreground Writes in your server.

If this is a production server or otherwise you want professional help with this, reach out to me directly or through my web site (https://www.askdbmgt.com/).

Hello Art, Here I show you the results of the commands you request:

Guillermo:

OK, I am assuming that the iurixdbs dbspace is default pagesize (2K? 4K?) and the tca_texto column is being stored in-table so in the same dbspace. I do see that the extent sizing is reasonably large (I feared that the table was being created with the default extent sizing of 8, 8), so that's not the issue.

You are not using replication (HDR or RSS) so it might be better to move that column into a blobspace. I don't think that should cause the load of that table to hang though. I do notice from the onstat -x output you sent previously that you seem to be stuck in a long checkpoint. Are the logical logs full and not backed up? What do 'onstat -F' and 'onstat -R' show?

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!

Hello! I wanted to tell you that I have been able to solve the problem, observing carefully the informix error log I found that there was a problem with the transaction log configuration. I basically configured it and the dbimport worked, I say basically because I can not understand the operation of the transaction log in informix ids 10 (in my case I come from postgres). If you have any article to read that you recommend me, I would appreciate it.
Greetings!

Guillermo:

The FGWrites number of almost 103,000 is a telling smoking gun. That value should ALWAYS be zero (OK an occasional 1 is acceptable). You either have a serious IO performance problem or a serious server configuration problem! You should NEVER see Foreground Writes in your server.

If this is a production server or otherwise you want professional help with this, reach out to me directly or through my web site (https://www.askdbmgt.com/).

Hello Art, Here I show you the results of the commands you request:

Guillermo:

OK, I am assuming that the iurixdbs dbspace is default pagesize (2K? 4K?) and the tca_texto column is being stored in-table so in the same dbspace. I do see that the extent sizing is reasonably large (I feared that the table was being created with the default extent sizing of 8, 8), so that's not the issue.

You are not using replication (HDR or RSS) so it might be better to move that column into a blobspace. I don't think that should cause the load of that table to hang though. I do notice from the onstat -x output you sent previously that you seem to be stuck in a long checkpoint. Are the logical logs full and not backed up? What do 'onstat -F' and 'onstat -R' show?

Again, thank you very much...

create table "informix".tca 
  (
    org_codigo varchar(6),
    tcc_codigo varchar(3),
    hca_numero integer,
    dac_codigo varchar(6),
    hac_numero integer,
    hca_anio smallint,
    hac_anio smallint,
    tca_texto byte
  ) 
  fragment by expression 
    partition part_1 (hac_anio <= 11 ) in iurixdbs ,
    partition part_2 (hac_anio = 12 ) in iurixdbs ,
    partition part_3 (hac_anio = 13 ) in iurixdbs ,
    partition part_4 (hac_anio = 14 ) in iurixdbs ,
    partition part_5 (hac_anio = 15 ) in iurixdbs ,
    partition part_6 (hac_anio = 16 ) in iurixdbs ,
    partition part_7 (hac_anio = 17 ) in iurixdbs ,
    partition part_8 (hac_anio = 18 ) in iurixdbs ,
    partition part_9 (hac_anio = 19 ) in iurixdbs ,
    partition part_10 (hac_anio = 20 ) in iurixdbs ,
    partition part_11 (hac_anio = 21 ) in iurixdbs ,
    partition part_12 (hac_anio = 22 ) in iurixdbs ,
    partition part_13 (hac_anio = 23 ) in iurixdbs ,
    partition part_14 (hac_anio = 24 ) in iurixdbs ,
    partition part_15 (hac_anio = 25 ) in iurixdbs ,
    partition part_16 (hac_anio = 26 ) in iurixdbs ,
    partition part_17 (hac_anio = 27 ) in iurixdbs ,
    partition part_18 (hac_anio = 28 ) in iurixdbs ,
    partition part_19 (hac_anio >= 29 ) in iurixdbs  
  extent size 262768 next size 26276 lock mode row;

revoke all on "informix".tca from "public";

Guillermo:

On the "hang", what does the schema in the .sql file look like now that you exported with -ss? I'm wondering about extent sizing and whether the tca_texto blob column is "in table" or in a blob space.

Thanks for your answer, I have modified the .sql file generated by the dbexport changing the lock mode to row in all tables, now all tables have that mode, including the fragmented one that gives me problems.
Art, I don't know how to create a RAW type table and then modify it to standard, can you tell me how please?
When I execute the dbimport it stays in the "unload file name..." of the big table and apparently it does not advance any more.
Here are some screenshots of the informix status. 

Guillermo:

First, yes, all of your tables should be run in ROW level locking mode, not page, unless this is a data warehouse with only slow changing data. Second, you might want to have the table imported as a non-logged database (or modify the <database>.sql file so that this one table is created as a RAW table the alter it back to STANDARD after the import completes. That will be faster and require fewer locks.

Good morning, I was able to do the export with the -ss option for the partitioned table and I got all the information without any problem, but when I go to do the import in the other server, the process does not finish and if I look at the status with onstat -x or -u I can see there are locks, with no other users accessing. Is it possible to put some additional parameter in the dbimport or change the lock mode of the table (currently it is in lock mode page)? 

Run the dbexport with the "-ss" option.  This will preserve the database specific elements, such as dbspaces, extent sizes, lock modes, and the fragmentation clause.

The target instance must have these same dbspaces.

Hello good day! in Informix IDS 10 I need to duplicate a database in another server with the same characteristics. The database has a very large table that is partitioned in several fragments. When I do dbexport and dbimport I get an error with this table:

Is there any way for dbexport/import to keep the partitioning definition? if not, what other way do you recommend?

Thank you very much!