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.