Daily Archives: February 18, 2010

admin_cmd can

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.