In the process of moving our internal IBM Connections 3.0.1 server to IBM Connections 4.0 we need to upgrade our DB2 to be 64 bit which is giving us some problems. After talking to IBM they convinced us to first do a side-by-side migration of our 3.0.1 DB2 databases to another 3.0.1 instance before upgrading the databases to 4.0. However in the process we discovered that the documentation for this process is inacurate so I wanted to post the solution here in case others needed it. Referring to the documentation (Migrating 3.0.1 data side-by-side) one of the steps is to record the sequence numbers for 4 DB2 sequences used for the draft tables. The progress of these sequences should be discoverable by using the following SQL but it fails.
(Profiles only.) Run the following commands to update the database sequence for DB2 or Oracle target databases: DB2 Run the following commands on the 3.0.1 source database: SELECT EMPINST.EMPINST.EXT_DRAFT_SEQ.NEXTVAL AS EXT_DRAFT_SEQ FROM DUAL; SELECT EMPINST.EMPINST.EMP_DRAFT_SEQ.NEXTVAL AS EMP_DRAFT_SEQ FROM DUAL; SELECT EMPINST.CHG_EMP_DRAFT_SEQ1.NEXTVAL AS CHG_EMP_DRAFT_SEQ1 FROM DUAL; SELECT EMPINST.CHG_EMP_DRAFT_SEQ2.NEXTVAL AS CHG_EMP_DRAFT_SEQ2 FROM DUAL; "SQL0204N "LCUSER.DUAL" is an undefined name. SQLSTATE=42704".
The problem is that the documented SQL references a non-existing table called “DUAL” and some table columns which then doesn’t make any sense either. What you actually want to look at are the actual sequences (see “SELECT SEQNAME FROM SYSCAT.SEQUENCES”). Once you know that it’s pretty easy to get the starting values.
Please note that if you’ve never used the draft functionality and DSML to sync changes back to LDAP you can happily omit these steps altogether.
4 thoughts on “LCUSER.DUAL is an undefined name doing IBM Connections 3.0.1 side-by-side migration”
DUAL is a built-in dummy DB2 view to use in a FROM clause, e.g. when you aren’t really querying data. (http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.apdv.porting.doc%2Fdoc%2Fr0052874.html)
Ok didn’t know but it doesn’t change the fact that the SQL doesn’t work.
Thanks for the hint Mikkel :).
I’m now using the following commands.
db2 "SELECT EMPINST.EXT_DRAFT_SEQ.NEXTVAL AS EXT_DRAFT_SEQ FROM SYSIBM.DUAL" – comment: only ones "EMPINST" and "SYSIBM.DUAL".
db2 "SELECT EMPINST.EMP_DRAFT_SEQ.NEXTVAL AS EMP_DRAFT_SEQ FROM SYSIBM.DUAL"
db2 "SELECT EMPINST.CHG_EMP_DRAFT_SEQ.NEXTVAL AS CHG_EMP_DRAFT_SEQ1 FROM DUAL" – comment: in my environment I have only "CHG_DRAFT_SEQ".
In my opinion IBM gave here the Oracle statements, when i compare this with side-by-side migration of 2.5 to 3 there was this in the documentation:
Thanks for your description, that i don’t need it when i don’t use draft.
Comments are closed.