Some additional DB2 notes
In my original article I wrote, that I had some problem with the to_char() function while moving the Oracle database structure to DB2. The problem wasn’t so much about, that to_char() isn’t supported on DB2 (it is, it’s basically the same thing as varchar_formar()), but that the Migration Toolkit had generated a 3 argument version of to_char(), the 3rd argument being some I18N parameter. Apparently, what the MTK deduced from the originating database didn’t fit with DB2 9.7. Anyway, this is probably still a MTK issue.
Working with DB2 for the last weeks has been mostly a pleasant experience. First of all there comes a GUI (db2cc aka the DB2 command center, which is basically a front end to a couple of graphical tools) with the database, which simplifies most administration tasks and which also provides a SQL editor to help with the development of your SQL queries. Therefore you don’t necessarily need any 3rd party software, to make your life easier. Second, if you look up the explanation of a particular SQL error (db2 ? SQL?????N), you will receive helpful suggestions, what might have caused the problem. This was frequently good enough for me to keep going without further reading in any manuals.
However I also have an issues with db2ce, the SQL command editor. When I try to save my developed SQL query to a file only the root (/) directory is offered as the destination location. I was unable to select a different location with the file selection box. If I tried to create a directory within the file selection dialog, I received a SQL22204N error and the dump/db2dasdiag.log file contained the following:
2009-08-25-16.19.31.198185+120 I5362G279 LEVEL: Error
PID : 27995 TID : 3041135504
FUNCTION: DB2 Tools, DB2 administration server, db2dasFMdirectoryCreate, probe:30
CALLED : DB2 Common, OSSe, ossDirectoryCreate
RETCODE : ECF=0x90000001 Access denied
Unfortunately there is no indication of the file path, which might cause the error.
The other issue concerns the user administration. I’m trying to give an additional system user access to some tables from the main schema. I’m received a large Java stack trace once I clicked in the respective dialog box to select the tables from the particular schema and I can’t proceed. Something like this:
....
at javax.swing.JComboBox.setEnabled(JComboBox.java:1381)
at com.ibm.db2.tools.common.AssistCombo.setEnabled(Unknown Source)
at db2_udb.UserGroupAddObjectDialog.itemStateChanged(Unknown Source)
at com.ibm.db2.tools.common.AssistCombo.notifyItemListeners(Unknown Source)
at com.ibm.db2.tools.common.AssistCombo.itemStateChanged(Unknown Source)
at javax.swing.JComboBox.fireItemStateChanged(JComboBox.java:1214)
at javax.swing.JComboBox.selectedItemChanged(JComboBox.java:1271)
at javax.swing.JComboBox.contentsChanged(JComboBox.java:1318)
at com.ibm.db2.tools.common.AssistCombo.contentsChanged(Unknown Source)
at javax.swing.AbstractListModel.fireContentsChanged(AbstractListModel.java:111)
at common.JComboBoxModel.setSelectedItem(Unknown Source)
at javax.swing.JComboBox.setSelectedItem(JComboBox.java:564)
at com.ibm.db2.tools.common.AssistCombo.setSelectedItem(Unknown Source)
at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:612)
at com.ibm.db2.tools.common.AssistCombo.setSelectedIndex(Unknown Source)
at common.ComboBox.setSelectedIndex(Unknown Source)
at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.jav
....
When I’m terminating the db2cc Java even segfaults:
Unhandled exception
Type=Segmentation error vmState=0x00000000
J9Generic_Signal_Number=00000004 Signal_Number=0000000b Error_Value=00000000 Signal_Code=00000001
Handler1=B7D150E5 Handler2=B7DB7A0D InaccessibleAddress=A9824680
EDI=A8B013DC ESI=00000000 EAX=A9824680 EBX=A946787C
ECX=00000000 EDX=A9824680
EIP=AA9149C8 ES=0000007B DS=0000007B ESP=A94676CC
EFlags=00010282 CS=00000073 SS=0000007B EBP=A94676DC
Module=/home/db2inst1/sqllib/lib32/db2jddba
Module_base_address=AA8BE000 Symbol=_ZN13jdbcAEProfileD1Ev
Symbol_address=AA91497C
Target=2_40_20081105_025433_lHdSMr (Linux 2.6.27-gentoo-r8)
CPU=x86 (4 logical CPUs) (0xbe195000 RAM)
----------- Stack Backtrace -----------
I guess I need to check the DB2 forums for some hints.
Originally I installed DB2 on a AMD64 server to get familiar with the software. The actual destination server however was a 32bit Intel box. There is a PDF in the DB2 9.7 documentation set, which describes the various available options. After some fiddling I choose the combination of db2look and db2move to transfer the data.
On the originating host I executed:
db2look -d httpd -z db2inst1 -dp -e -o struc.sql
db2move HTTPD EXPORT -sn DB2\*
Then the data was transferred to the destination host, where I executed the commands
db2 -tvf struc.sql
db2move httpd import -io insert
to restore the database. The “-io insert” is important here, otherwise you would have received errors in connection with tables, which reference foreign keys.
Otherwise it’s still smooth sailing with DB2.
Tags: