4.10.U2 SE was Re: <No Subject Supplied>

Note: Mark, please use a subject when you post. It makes it difficult to track particular issues w/o an applicable subject. Dave Snyder writes: |> Mark Heringslake writes: |> } Hello Fellow Netters..... |> } |> } I am running 4.10U2 version of Informix SE... |> } |> Good, don't upgrade to 5.00 just yet. There are problems with ROWID's |> which I'm guessing you use all over :-) |> |> } 1) I have a table that has a SERIAL field. For conversion purposes |> } I force inserted (via SQL) a row w/ a value of -1 (which I use for |> } any record that doesn't convert right). It took it and I can view |> } the data via SQL, but none of the other programs seem to "see" |> } this record. NOTE: This is NOT a problem, just curious as to why |> } it seems to be invisible. |> } |> } a) Is this an unusual situation ? |> } |> Very unusual... a SERIAL field should have a unique index on it. The fact |> that you can insert multiple -1's tells me that something is amiss. Only if you place an index on it. Too many of you are spoiled by ISED (the create table part of the isql menu) which places the unique index on a serial column for you. Creating a serial column via SQL will NOT automatically place an index on it. As per why other programs don't "see" it, it depends on how you are accessing the records, doesn't it? Perhaps you have a > 0 applied to that column? |> |> } b) Could this cause a problem in the future ? |> } |> Probably not. |> |> } c) Somewhat unrelated..... |> } How do I get the "serial" value to start over - do I have to |> } alter the table to remove the "serial" field and then alter it |> } again back to "serial"? |> } |> The serial value is stored in the .idx file for a table. The only way I |> can think of getting a serial field to start over is to enter 2,147,483,647 |> records or drop/re-create the table. You can "reset" a serial value by explicitly inserting a value into a serial column that is *greater* than the current value. For example, if your serial value is currently 500, and you insert a record whose value for that serial column is 600, the new "next" serial value will be 601. You only get the system generated value if you insert 0 for the serial column value. Setting the serial value to a value lower than the current "next" serial, will not affect it. However, if you insert the max int value, it will cause it to roll over back to 0. So Dave has the right idea; but you do not actually need to insert than many rows, just a row with that value for the serial column. |> |> } 2) I have a table that had a number of records, but now they're all |> } deleted. |> } |> } a) Why does it still take up space as if there were records in it? |> } |> Because the records were marked as deleted, not physically deleted. You'll |> notice that if you start adding new records, the file won't start growing |> until all of the holes left by the delete are filled in. Unix provides no mechanism to "shrink" a file other than to rewrite it. [the rest deleted] Dave