SQL 212/ISAM 131 Errors in dbimport

I am attempting to do a dbimport into a newly created Informix instance. It fails with an SQL Code 212 "Cannot add index", and an ISAM Code 131 "ISAM error: no free disk space". There are only two dbspaces (rootdbs and tempdbs) and two sbspaces (blobsbs and erqsbs) in this instance. Previously, this exact same dbexport was successfully dbimported into the original Informix instance on the same box. At the time of that earlier successful import, all four of the spaces in that first instance had less space remaining in them than the new spaces in the new instance. Actually, I first tried to do the import with a tempdbs that was only about half the size of the tempdbs in instance #1. About 1 hour 20 minutes into the import, it failed with the 212/131 errors. I doubled the size of the tempdbs and tried again. Same failure, at the same time (about 1 hr 20 min), and same place in the dbimport script. I increased the tempdbs a little more (to make absolutely certain that it is now larger than the tempdbs on the first instance), and, again, failure. Same time duration, same place in the dbimport script. Here is the tail of dbimport.out: { TABLE "informix".omp_update_events row size = 32030 number of columns = 5 index size = 18 } { unload file name = omp_u02866.unl number of rows = 64500 } create table "informix".omp_update_events ( upid serial not null , omp_id integer not null , updt datetime year to fraction(5) not null , upusr char(8) not null , changes lvarchar(32000), primary key (upid) ); revoke all on "informix".omp_update_events from "public" as "informix"; { TABLE "informix".omp_update_compare row size = 31303 number of columns = 10 index size = 265 } { unload file name = omp_u02867.unl number of rows = 1170120 } create table "informix".omp_update_compare ( serialnum serial not null , omp_id integer not null , fldname varchar(255) not null , intval1 integer, intval2 integer, dateval1 datetime year to fraction(5), dateval2 datetime year to fraction(5), strval1 lvarchar(15000), strval2 lvarchar(15000), changedesc lvarchar(1000), primary key (omp_id,fldname) ); *** execute sqlobj 212 - Cannot add index. 131 - ISAM error: no free disk space BTW, the column "fldname" is NOT NULL; its max length is 22, and its min is 3. I am assuming that the error messages are actually appearing at the point in the code where the problem is. Hence, I am assuming that there is some problem with the "omp_update_compare" table, probably in the attempt to build the primary key index. I would ask for any suggestions to aid in getting this file to import. Space wouldn't really seem to be the issue, since, as I mentioned, all the dbspaces and sbspaces in this current instance are larger than the space that was available in those same spaces in the first instance (in which the import was successful). Currently, immediately after the failure termination, Server Studio shows rootdbs at 50% used, blobsbs at 26%, erqsbs at 22%, and tempdbs at 0%. (This is after a post-termination refresh of the Server Studio storage display screen, so those are the actual final values.) During occasional spot checks during the import, I never observed the tempdbs to go above 0%. I don't know what's wrong, or how to fix it. Thank you for any comments or suggestions. Regards, DG P.S. In the past, when I have had issues with dbimport, it was always after the table imports. Thus, I could fix whatever the problem was, and then "pick it up again" by running the remainder of the dbimport script as an sql file from DBaccess. However, I don't know how to resume the dbimport while it is still in the table portion of the script. Perhaps, I could strip out the remaining table portion, and run it as a new dbimport of a new database with a different name. I would keep chopping away until I could isolate the problem table (thinking it is the table indicated above, in the tail of dbimport.out) If I can get the tables into Informix, I think I can "brute force" the rest of the way. If I have to manually import a small number of tables, that is OK, too.

Some thoughts: 1 - Make sure that FILLFACTOR is set to 90% or higher (during the dbimport I would set it to 99 or 100% since these primary keys start with a sequentially increasing value). 2 - Make sure that MAX_FILL_DATA_PAGES is set at least during the dbimport. 3 - Verify that the temp dbspace is indeed marked as temp and that it is listed in the DBSPACETEMP parameter in the ONCONFIG file. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 6, 2016 at 6:05 PM, DAVID GROVE <david.grove@alaska.gov> wrote: > I am attempting to do a dbimport into a newly created Informix instance. It > fails with an SQL Code 212 "Cannot add index", and an ISAM Code 131 "ISAM > error: no free disk space". > > There are only two dbspaces (rootdbs and tempdbs) and two sbspaces (blobsbs > and erqsbs) in this instance. > > Previously, this exact same dbexport was successfully dbimported into the > original Informix instance on the same box. At the time of that earlier > successful import, all four of the spaces in that first instance had less > space remaining in them than the new spaces in the new instance. > > Actually, I first tried to do the import with a tempdbs that was only about > half the size of the tempdbs in instance #1. About 1 hour 20 minutes into > the > import, it failed with the 212/131 errors. I doubled the size of the > tempdbs > and tried again. Same failure, at the same time (about 1 hr 20 min), and > same > place in the dbimport script. I increased the tempdbs a little more (to > make > absolutely certain that it is now larger than the tempdbs on the first > instance), and, again, failure. Same time duration, same place in the > dbimport > script.> > Here is the tail of dbimport.out: > > { TABLE "informix".omp_update_events row size = 32030 number of columns = 5 > index size = 18 } > > { unload file name = omp_u02866.unl number of rows = 64500 } > > create table "informix".omp_update_events > ( > > upid serial not null , > > omp_id integer not null , > > updt datetime year to fraction(5) not null , > > upusr char(8) not null , > > changes lvarchar(32000), > > primary key (upid) > ); > > revoke all on "informix".omp_update_events from "public" as "informix";> > { TABLE "informix".omp_update_compare row size = 31303 number of columns = > 10 > index size = 265 } > > { unload file name = omp_u02867.unl number of rows = 1170120 } > > create table "informix".omp_update_compare > ( > > serialnum serial not null , > > omp_id integer not null , > > fldname varchar(255) not null , > > intval1 integer, > > intval2 integer, > > dateval1 datetime year to fraction(5), > > dateval2 datetime year to fraction(5), > > strval1 lvarchar(15000), > > strval2 lvarchar(15000), > > changedesc lvarchar(1000), > > primary key (omp_id,fldname) > ); > *** execute sqlobj > 212 - Cannot add index. > > 131 - ISAM error: no free disk space > > BTW, the column "fldname" is NOT NULL; its max length is 22, and its min > is 3. > > I am assuming that the error messages are actually appearing at the point > in > the code where the problem is. Hence, I am assuming that there is some > problem > with the "omp_update_compare" table, probably in the attempt to build the > primary key index. > > I would ask for any suggestions to aid in getting this file to import. > Space > wouldn't really seem to be the issue, since, as I mentioned, all the > dbspaces > and sbspaces in this current instance are larger than the space that was > available in those same spaces in the first instance (in which the import > was > successful). Currently, immediately after the failure termination, Server > Studio shows rootdbs at 50% used, blobsbs at 26%, erqsbs at 22%, and > tempdbs > at 0%. (This is after a post-termination refresh of the Server Studio > storage > display screen, so those are the actual final values.) During occasional > spot > checks during the import, I never observed the tempdbs to go above 0%. > > I don't know what's wrong, or how to fix it. > > Thank you for any comments or suggestions. > > Regards, > > DG > > P.S. In the past, when I have had issues with dbimport, it was always after > the table imports. Thus, I could fix whatever the problem was, and then > "pick > it up again" by running the remainder of the dbimport script as an sql file > from DBaccess. However, I don't know how to resume the dbimport while it is > still in the table portion of the script. Perhaps, I could strip out the > remaining table portion, and run it as a new dbimport of a new database > with a > different name. I would keep chopping away until I could isolate the > problem > table (thinking it is the table indicated above, in the tail of > dbimport.out) > If I can get the tables into Informix, I think I can "brute force" the > rest of > the way. If I have to manually import a small number of tables, that is OK, > too. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c194348a3444e053e39c64a

FILLFACTOR 99 MAX_FILL_FACTOR 1onstat -d confirms tempdbs has "T" flag set. Ran again, and failed exactly same place and same time. THen looked at the script again. Noticed the two lvarchar(15000) columns. Some googling and documentation led me to believe that dbimport was using both those (and other columns) to require large extents. And the table has over a million rows. A little research in the actual table data showed the actual maximum length of those two columns was 265 bytes. I edited the script to use lvarchar(500), instead of lvarchar(15000) for both of those columns. Failed again in exactly the same way, time, and place. I am still scratching my head. DG

I also noticed in the documentation for 12.1 (which is what we are using), it described the "-D" option for dbimport, which disregards the row length calculation (hence reducing space). I tried to use it, but, in spite of the documentation, that produces a syntax error, and I could not use it. DG

Hi David, Use it to create table EXTENT SIZE to specify the size of the table. dbschema -ss to show the previous size. Petr Dne 7.10.2016 v 08:20 DAVID GROVE napsal(a): > FILLFACTOR 99 > MAX_FILL_FACTOR 1> onstat -d confirms tempdbs has "T" flag set.> > Ran again, and failed exactly same place and same time. > > THen looked at the script again. Noticed the two lvarchar(15000) columns. Some > googling and documentation led me to believe that dbimport was using both > those (and other columns) to require large extents. And the table has over a > million rows. A little research in the actual table data showed the actual > maximum length of those two columns was 265 bytes. I edited the script to use > lvarchar(500), instead of lvarchar(15000) for both of those columns. > > Failed again in exactly the same way, time, and place. > > I am still scratching my head. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

I will try editing the dbimport script to specify first and next extent. Something sure seems strange with the space, though. I observe in the log that, at the time of failure termination, there is a warning that rootdbs is full. But, here is onstat -d: informix@ifmx-test-jnu>onstat -d IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 12:01:07 --13107200 Kbytes Dbspaces address number flags fchunk nchunks pgsize flags owner name 113310028 1 0x60001 1 1 2048 N BA informix rootdbs 114f11ba8 2 0x42001 2 3 2048 N TBA informix tempdbs 114f11d50 3 0x48001 3 1 2048 N SBA informix blobsbs 114f12028 4 0x48001 4 1 2048 N SBA informix erqsbs 4 active, 2047 maximum Chunks address chunk/dbs offset size free bpages flags pathname 1133101d0 1 1 0 31457280 15640707 PO-B-- /opt/informix2/rdsk/rootdbs_1 114f121d0 2 2 0 2097152 2097099 PO-B-- /opt/informix2/rdsk/tempdbs_1 114f13028 3 3 0 15728640 11652106 15332760 POSB-- /opt/informix2/rdsk/blobsbs_1 Metadata 395827 269785 395827 114f14028 4 4 0 524288 407740 407740 POSB-- /opt/informix2/rdsk/erqsbs_1 Metadata 116495 93608 116495 114f15028 5 2 0 2097152 2097149 PO-B-- /opt/informix2/rdsk/tempdbs_2 114f16028 6 2 524288 262144 262141 PO-B-- /opt/informix2/rdsk/tempdbs_3 6 active, 32766 maximum NOTE: The values in the "size" and "free" columns for DBspace chunks are displayed in terms of "pgsize" of the DBspace to which they belong. Expanded chunk capacity mode: always informix@ifmx-test-jnu> Seems like lots of room. DG

I apologize for continued response to my own thread. I think I may know why reducing the lvarchar(15000) to lvarchar(500) failed to make any difference. I didn't change the row size, as indicated in the "comment" field of the dbimport script. And the "comments" in that script aren't really comments, but are used by dbimport, right? So, dbimport would still be acting as if the row size were still huge, as in a little more than 31K! So, I will try manually editing that tidbit, too. DG

Sent from my iPhone > On 7 Oct. 2016, at 6:21 pm, DAVID GROVE <david.grove@alaska.gov> wrote: > > I apologize for continued response to my own thread. > > I think I may know why reducing the lvarchar(15000) to lvarchar(500) failed to > make any difference. I didn't change the row size, as indicated in the > "comment" field of the dbimport script. And the "comments" in that script > aren't really comments, but are used by dbimport, right? So, dbimport would > still be acting as if the row size were still huge, as in a little more than > 31K! So, I will try manually editing that tidbit, too. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --Apple-Mail-15C74785-0B84-460F-9478-139392F01EE3

What dbspace is the database created in? You could specify the table location in the create table using IN clause. Sent from my iPhone > On 7 Oct. 2016, at 6:21 pm, DAVID GROVE <david.grove@alaska.gov> wrote: > > I apologize for continued response to my own thread. > > I think I may know why reducing the lvarchar(15000) to lvarchar(500) failed to > make any difference. I didn't change the row size, as indicated in the > "comment" field of the dbimport script. And the "comments" in that script > aren't really comments, but are used by dbimport, right? So, dbimport would > still be acting as if the row size were still huge, as in a little more than > 31K! So, I will try manually editing that tidbit, too. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > --Apple-Mail-DAD63C04-3012-4D53-9A3C-9DDBD48DFF2E

This database is using rootdbs. Long story about that... But, editing both the actual DDL and the "comments" in the dbimport script SOLVED the problem. Apparently, dbimport uses the maximum possible values for column lengths, as opposed to actual values in the data. Made such a huge difference that it ran out of room. The -D option described in the documentation would probably have solved the problem, too, had it existed as described. Thank you all for your helpful comments. Regards, DG