There are many great tools for running DB2 commands; I often use TOAD, Control Center (db2ce), and a CLI-based tool I wrote. And with DB2 9.7, I’ve enjoyed experimenting with the new CLPPlus, IBM’s answer to SQL*Plus for Oracle refugees. But for those quick commands, I usually just pop open a DB2 Command Window (Windows) or bash shell (Linux) and type “db2” followed by the command. It works great nearly all the time. Emphasis on nearly.
Today, Wayne reported how this command taken verbatim from the DB2 documentation (the “File type modifiers for export” section) choked:
db2 export to delfile2 of del modified by timestampformat=”yyyy.mm.dd hh:mm tt” select * from schedule
It failed with the error: SQL3192N In the filetmod a user specified format “TIMESTAMPFORMAT” beginning with the string “yyyy” is not valid. And no value for timestampformat worked.
This is a case where bugs in CLP’s command-line parsing (particularly with double quotes) get in the way. The command works from inside CLP, Command Editor, and other tools, so you can just type db2 or db2ce and hit enter and then run the command (without “db2” in front) from there. Using admin_cmd from a CLI/ODBC tool also works, like so:
call admin_cmd(‘ export to delfile2 of del modified by timestampformat=”yyyy.mm.dd hh:mm tt” select * from schedule’)
Bugs like this have survived in the db2 command line for awhile (this fails even in 9.7). I’ll report it, but since CLPPlus is the new kid in town, the old db2 command line probably won’t get as much attention.