 |
Forums |
Admin  Start New Thread
By: Philip Nelson
 RE: Moving table to a new database [ reply ]
2009-01-02 14:30
|
> Now if I could just keep Aptana Studio from crashing;-)
It does appear to be a bit "flakey" at times doesn't it !!!
Have you tried installing DataStudio on top of Aptana's copy of Eclipse ? It is something I've yet to get round to trying.
> BTW, when creating an application with Aptana Studio,
> it has a database option of 'DB2' but this must not be
> 'ibm_db' as it fails miserably. My guess is that this is for
> some other DB2 adapter. This isn't particularly a
> problem as I just manually create the database.yml
> file.
That will be for the older open-source DB2 driver, before the days of ibm_db, or even ibm_db2.
I must confess I keep going back to vi as my IDE of choice for development, with forays into DataStudio if I want to debug a stored procedure.
And when setting up new projects I always do it from the command line, as I have a series of steps to go through to prepare it for SVN at the same time (most of them derived from the Pragmatic Programmers book on deploying Rails applications).
Phil
(teamdba@scotdb.com)
|
By: David Adler
RE: Moving table to a new database [ reply ]
2009-01-02 14:03
|
Thank you so much for your help. This is exactly what I was looking for and works perfectly!
Now if I could just keep Aptana Studio from crashing;-)
BTW, when creating an application with Aptana Studio, it has a database option of 'DB2' but this must not be 'ibm_db' as it fails miserably. My guess is that this is for some other DB2 adapter. This isn't particularly a problem as I just manually create the database.yml file.
|
By: Philip Nelson
RE: Moving table to a new database [ reply ]
2009-01-02 11:58
|
And I also forgot to say that you may need to use the ALTER TABLE command to reset the current identity column value -
db2 "select max(id-col) from table"
db2 "alter table test alter id restart with <max-val-+-1>"
HTH
Phil Nelson
(teamdba@scotdb.com)
|
By: Philip Nelson
RE: Moving table to a new database [ reply ]
2009-01-02 11:52
|
Praveen,
I believe there IS something you can do about this.
There are a three "MODIFIED BY" options available to both LOAD and IMPORT which allow you to work around such problems : they are called IDENTITYMISSING, IDENTITYIGNORE and IDENTITYOVERRIDE.
See the information center link below for more details -
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.admin.dm.doc/doc/c0004591.html
In your case, when you have a file containing the identity column values and you want to retain these you would use IDENTITYOVERRIDE -
db2 "load from data.ixf of ixf modified by identityoverride replace into table"
If you used IDENTITYIGNORE it would ignore the values in the load file and generate new values instead, which obviously would not fit in with your foreign keys.
IDENTITYMISSING would be used when you had exported the data without the identity values and you wanted to generate new values. It then assumes that the data in the export file does not include the identity column, so you don't have to give a column list on the load.
You will also want to look at the SET INTEGRITY command as part of the load. You'd use "SET INTEGRITY OFF" before the load and then "SET INTEGRITY IMMEDIATE CHECKED" after the load to clear the "Check Pending" condition.
HTH
Phil Nelson
(teamdba@scotdb.com)
|
By: Praveen Devarao
RE: Moving table to a new database [ reply ]
2009-01-02 08:11
|
Hi David,
Unfortunately, there no such simple option with which you will be able to import a table with a column GENERATED ALWAYS AS IDENTITY. The only workaround as of now is to 1) recreate the table without GENERATED ALWAYS 2) load/import the data and then 3) alter the table to have the respective column as GENERATED ALWAYS AS IDENTITY.
I checked and also enquired about this and came to know that the import will work fine in case of GENERATED BY DEFAULT AS IDENTITY (the clause which ibm_db_adapter uses for the id column). However the same is not possible with a column defined as GENERATED ALWAYS AS IDENTITY.
Let me know if you require more info.
Thanking and wishing you a Happy New year
Praveen
|
By: David Adler
Moving table to a new database [ reply ]
2009-01-01 23:23
|
I've exported a table from one database in IXF format and have tried to import into another database using the IMPORT utility, but all rows are rejected because the ID column is defined as GENERATED ALWAYS.
I also tried using the LOAD utility but it also fails.
Since the ID values are used as foreign keys, I can't have the ID values re-generated.
Any ideas?
(I could manually CREATE the table and not use GENERATED ALWAYS but it seems like there should be a simple solution)
|
|
|
