Yesterday, I had posted about db2batch. One of my blog reader who was pretty new DB2 asked me how to run a set of sql from a file. Thats when I realized my blog doesn’t cover newbies, so thought I will write this up…
DB2 has a command line interface or can use the db2 command editor which is a GUI based. I like command line, so here is how you can do it.
- In Windows :
From the Run prompt, type db2cmd and hit enter
Now you should have a command window with the title DB2 CLP, but still give you a drive letter prompt.
- In Unix/Linux/Mac:
Start a terminal session, make sure you have executed the db2profile to have the db2 environment setup for your userid. If you are not sure, then execute this ./sqllib/db2profile . This would set the environment up.
For ease of illustration here is an example :
This screen shot shows execution of a file that contains one sql
This screenshot shows execution of a with multiple sql
This screenshot illustrates how to turn off auto commit and how to rollback an sql
If you omit the ‘s’ from the ‘-svtf ‘ , db2 will process the rest of the sql in a file even if one of the sqls error out. In other words, ‘-s’ stops execution of sqls in a file when db2 encounters an error in the execution.