dbexport failure

IDS 12.10.FC12 Solaris 10 1/13 On March 31, a contract we had with some software developers expired. I am taking down some resources, including an Informix database, that we supplied for their use during the contract. I intended to do a dbexport of their (experimental, development) database and put it in storage. I was surprised to find that dbexport simply fails to complete. It produces absolutely no error messages. There are no errors in the online.log. It just stops after about 1,500 tables (just a few dozen shy of completion). Needless to say, it didn't get to any of the stored procedures, etc. As I stated, it doesn't complain about anything. It simply terminates. This contractor's database was in its own separate instance, and nothing else is there, so I used ontape to take a Level 0 archive, which succeeded. So, I do have an archive to put in storage. But, I wondered why dbexport failed. Did a little looking at the table it was attempting to export when it terminated. I observed that all the columns are NULLable, except one. However, that column (specified as NOT NULL in the DDL) does, in fact, have some rows that are NULL. How that could happen, I don't know. Maybe the table was originally created with all rows NULLable, and then (after some rows were entered) ALTERed so that row became NOT NULL. Could this violation in the actual data, of the DDL "NOT NULL" constraint explain dbexport's failure? But, if it does, why would dbexport be (completely) silent about it. dbexport simply terminates with no error message (and also without the "dbexport complete" message that it always produces at the end of a successful export). I'm scratching my head. DG

Hi, I don't use dbexport much but I have observed in the past that it is not tolerant of errors and can abort without a helpful message. For example I raised this bug five years ago where this was the behaviour (this is fixed in your version): IT03033 Dbschema or dbexport aborting if unique index used by primary key constraint was dropped So I'd say that if there is a constraint violation in your data it certain sounds plausible that it is the cause. Incidentally I am not sure how you could have ended up with nulls in a "not null" column as this obviously shouldn't be possible. The only possible causes that spring to mind are: - unsupported updates to system tables as user "informix". - Informix defect. Ben.

Thank you, Ben. This database was totally the private, unmanaged-by-us, domain of the contractors. I don't know what they did or didn't do. So, they could have worked in the system catalog. I looked again, and it is s type SERIAL column, and the rows that have no value in the problematic column actually display "Server Generated". I wonder if the table might originally have been created with that column as something else (that was nullable) and then, later, ALTERed to be type SERIAL, with nothing done to populate the original rows that were NULL in that column. I don't want to take the time to try this because that database really doesn't matter to anyone, any more. And, I have an ontape backup of that instance, if ever needed for anything. DG

I was going to let this go by, but now realize we have to solve it, so will open a tech support case. But, here is what I know. The table is created within an SPL by a "CREATE TABLE... AS..." statement. This statement is a single SQL command. It is over 500 lines long, has many subselects and joins (both inner and outer), and lots of alias names (SELECTing FROM <alias>) where the alias refers to a SELECT statement (that also contains a nested alisa), etc. The formating makes it difficult to unravel, and there are no intermediate results or tables to help "chunk" my analysis to determine what it is doing. But, at the end of the day, it produces a table whose DDL has a column specified as type SERIAL NOT NULL, and some rows in the table are NULL in the column. I can tell that there is no manipulation of the system catalog. I don't even know if I can come up with a simple version to provide a demonstration for tech support. There are many tables, and many joins. The contractors are gone, so can't go back to them. On the surface, it appears to me that the SQL created a table that should not be possible. The table has no PK, no indices, and no constraints. Just as plain a table as you can imagine. It is interesting to me that dbexport simply terminates when it reaches this table. No error. Nothing. Just stops. However myexport runs to successful completion, and DOES export this table. I examine the unl file, and, sure enough, the SERIAL NOT NULL column is NULL. But, I am highly suspicious that either dbimport or myimport will be able to import it because the column is NOT NULL, and the data is NULL in that column. I suspect an Informix bug, and will open a case, but fear it may not do much good because I may be unable to come up with a compact demonstration of the problem. Sigh. DG

Note I say, "resolved", not "solved". I just don't have time to work all the details. This was a side-bar to the process of getting myexport to work properly in our system, which, itself, was a sidebar to another task. I feel like I have encountered stack overflow! Anyway, the "fix" was to CAST to INT the SERIAL column in the outer-most SELECT of the complex, 500+ line, single SQL query ("CREATE TABLE AS... ") that was causing the problem. Now the column produced in the target table by the "CREATE TABLE AS..." statement is an INT, and the NULLs are OK. dbexport now runs to successful completion. Why Informix executes a "CREATE TABLE AS... " statement which creates a column that is SERIAL and also has NULLs, without terminating and generating an error message is still a mystery, and beyond me. But, then, a lot of things are above my pay grade! Did not open a case on this, after all. A matter of available time. Regards, DG

Why don't you ttry John Miller's 'export' stored procedure? It will work on any informix above 11.50 xC3 ( that is having external tables)