Tag: sql

LCUSER.DUAL is an undefined name doing IBM Connections 3.0.1 side-by-side migration

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.