The question came up again today: “how do I run a DB2 export through my ODBC connection?” Before recent versions of DB2, the answer was, “you can’t.” If you tried just running the command, DB2 would give you the classic SQL0104 “duh” error message: “…an unexpected token was found…”
That’s because administrative commands and utilities require special handling. And before DB2 8.2, they could only be run through a command line processor session. Programs could not use their normal database connections for things such as export, runstats, reorg, or “update db cfg.” The alternatives were often inelegant, such as calling cumbersome utility functions like db2Export/sqluexpr or shelling out to a script.
Fortunately, the new admin_cmd stored procedure lets you run several of these commands through a normal CLI or ODBC connection, much like any SQL statement. You just pass the command as a parameter; for example:
call admin_cmd(‘export to sales.ixf of ixf select * from sales’)
Even if you’re not writing code, admin_cmd is useful for doing maintenance and data movement directly from your favorite tools. Since so many programs and tools use ODBC connections, it’s a convenient and portable way of handling your DB2 administrivia.
Note that sales.ixf will be written to DB2’s current (?) directory on the DB2 server machine, not on the DB2 client machine. So the client may want to ask for the file to be written to some share known to both machines, in order for the client to be able to access the file.
Good point, Wayne: the export is written on the DB2 server. So if running from a different machine, it helps to specify a full path to control where it lands there. For example:
call admin_cmd(‘export to g:\shared\exports\sales.ixf of ixf select * from sales’)
I like your suggestion of using a common/known shared path to make it easy to find.