Stumbled on this very nice easy way to export to a delimited file from the DB2 Command Line Prompt (CLI) today.
db2 “EXPORT TO <filename> OF DEL MODIFIED BY NOCHARDEL <select statement>”
db2 “EXPORT TO result.csv OF DEL MODIFIED BY NOCHARDEL select * from empinst.employee”
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:
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.
DB2 Magazine blog on LUW Performance. For those not in the know when talking about DB2 LUW stands for “Linux, Unix and Windows” and testifies to the great difference between DB2 on LUW and zSeries/iSeries.
While looking to DB2 v. 9 I stumbled upon DB2 Developer Workbench which is the follow-up to the old suite of Swing based Java DB2 development applications which I must say was due for an overhaul. If DB2 Developer Workbench is the yardstick for future IBM development products based on the IBM Eclipse/Expeditor platform all I can say is wow!!
I already picture an integrated development platform for Lotus, DB2 and Java products (can’t make myself write Websp…). It’s gonna be a BIG install but imagine the applications you can build that leverage Notes 8 as the client for applications what access Notes, DB2 and Java resources. How’s that for composite applications.
Seems everything is melting together nicely…
Application development with DB2
One of those famous notes to self. A smart way to avoid using temporary tables in DB2: Using VALUES to build a recursive query