DB2 Command Line Processing

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.

About rpillai

I am a technology enthusiasts and love to work with databases and other technology. Learning new things everyday and don't think the path ever ends ...
This entry was posted in db2 and tagged , , , , , , , , . Bookmark the permalink.