myschema , SE and DBINFO
JaxenT
I built utils2_ak on Fedora core 6 and am trying to run against an SE
instance. First I had to comment out line 537 in dbcopy.ec "EXEC SQL
SET ISOLATION COMMITTED READ LAST COMMITTED;" it gave a syntax error
but shouldn't come into play on my box as it is a development machine
that no one else logs into. No transactions are running.
I get an error "Error preparing server version stmt: error=-728,
isam=0" when I run './myschema -v -d /my/db/dir -S'. Running the
query:
SELECT DBINFO( 'version', 'major' )
FROM "informix".systables where tabid = 1
gives me "728: Unknown first argument of dbinfo(version)." But
finderr says that version is a valid argument. The SQLSyntax guide,
from last year, does not have version as a valid arugment, only
dbspace, sqlca.sqlerrd1, sqlca.sqlerrd2 and sessionid.
Any clues? Of course I need to run myschema because I have the
somewhat famous 1"00 - ISAM error: duplicate value for a record with
unique key." errors in my db.
Art S. Kagel
Hi Jaxen.
Although there's lots of support for SE still built into Myschema's
code, it's been years since I've tried to run it there, and obviously
I've introduced some IDS'ism's that are not sufficiently protected
under SE. Some of these I was aware of and missed, others, like the
lack of DBINFO('version' ...) support is one I wasn't even aware of.
I had thought that SE 7.2x supported that one.
I'll try to look at cleaning up the code for SE support and let you
know how it goes, but I'm not certain how much time I can put into
it at this time. The problem with the LAST COMMITTED support I added
to dostats when not running IDS 11.10, I know about and will be
releasing an update shortly. Anyone who needs that update before the
release and isn't comfortable mucking in the code to comment the line
out, please let me know.
Art S. Kagel
JaxenT
Don't go too crazy on fixing the SE stuff on my account, I am not sure
how many others are out there still with SE. I am starting the
process of unloading, deleting, creating and reloading the tables that
have the problem, there are several. Then I will export the db and
import it into IDS.
Thanks
Jackson
JaxenT
ISAM error to pop up? I have several different databases to export
and I would like to just write a script that finds the bad tables,
unloads them, drops, creates and reloads them. But I can't get the
duplicate value for a record with unique key error to pop up except
with a dbschema or dbexport -d /database. Even do a -t on the schema
will not issue the error.
Art S. Kagel
> Does anyone know how if there is a SQL command I can use to get the
> ISAM error to pop up? I have several different databases to export
> and I would like to just write a script that finds the bad tables,
> unloads them, drops, creates and reloads them. But I can't get the
> duplicate value for a record with unique key error to pop up except
> with a dbschema or dbexport -d /database. Even do a -t on the schema
> will not issue the error.
That's a tough one. The error happens because you have unnamed
constraints in your DB. When you create a constraint without a name
the DB assigns a name formed from the tabid and sequential constraint
# for the table, ie the first unnamed UNIQUE constraint for tabid 101
would be "U101_1". So, when you unload the schema with dbexport and
try to reload it, especially into a database that already has some
tables defined, what was table 101 is now table 104 and tabid 101
already has a constraint by that name so there's a clash. So, there's
no way to detect from the source database that this will happen at
reload time.
The solution is to recreate all indexes and constraints with explicit
names in the source database BEFORE exporting them, OR to modify the
schema file prior to importing the data tables to name these objects
explicitely at load time.
Art S. Kagel