Command line options for DB2, Oracle, and SQL Server

I have noticed , especially in the SQL Server world a tendency to believe that the GUI is the holy grail of database management for the DBA. You can't beat it for certain types of look-ups and quick hitters specifically if you don't feel like Goggling for syntax.

However, grizzled vet's know that the command line interface for DBA's allows you to script commands to run in batch. This is wonderful if say you need to create 100 database users for a request that comes up for the next day just as you are ready to go home. For instance, you could utilize Management Studio in SQL Server 2005 or 2008 and be there all night or if they supplied you a soft copy of the users you could edit this information into a file wrapping SP_ADDUSER or CREATE USER (If 2008) around the file and then quickly add it as a schedule task for osql (IIRC, Microsoft has a future replacement for osql in SQL2008 though it still is supported!).

Let's examine your options in the big 3 database vendors for command line execution of scripts.


DB2 for Z/OS:


You can use SPFUI from ISPF or you can execute DSNTEP2 (one of the DB2 sample programs that comes with the RDMS) using JCL similar to the below with slight modifications for your installation to the JOBCARD and the PLAN and LIB parameters. By submitting a job manually or having a scheduling tool submit it you can execute code as needed . Many dynamic DB2 SQL statements on the IBM mainframe can be executed this way by updating the SQL under the SYSIN card.


//JOBNAME (ACCT),'DSNTEP2',CLASS=Z,MSGCLASS=R,
// NOTIFY=&SYSUID ALL JOBS NEED ONE
//*
//STEP1 EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM (DB2D)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP2) LIB('DB2.RUNLIB.LOAD')
/*
//SYSIN DD *
SELECT DBNAME,TSNAME,PQTY,SQTY
FROM SYSIBM.SYSTABLEPART
WHERE DBNAME = 'DAPP01S2'
ORDER BY PQTY DESC

The above is just a simple catalog query for space information for a particular database.

DB2 UDB on LUW:

In DB2 on LUW you can script your code using the db2 command processor (DB2 before the command) and imbed it in the scripting language of choice for the platform. In the below example you invoke the db2 command processor by prefixing the command with db2 and then specifying a file with SQL statements in it. The below is for Windows and it resides in a .cmd file. You could then invoke it from the command line or you can use the DB2 Task Center to schedule the below command file as a job (You would not want a pause in the script if it was to be a scheduled job of course).


:REM
:REM -- run from c:\scripts
:REM
db2 connect to SAMPLE
echo . "Control C to kill before continuing else press enter"
pause
db2 -f input.sql -t > output.log
@echo off
echo "check log for errors"

In the above example the user that executes the command must have access to a database called SAMPLE. If so then they could execute the code inside of input.sql once they connected to the database.

Oracle on LUW:

In Oracle you can easily use SQLPLUS to run Oracle processes in batch. You can also use DBMS_SCHEDULER in 10g and above and DBMS_JOB if you are at a really back-level release of Oracle to schedule tasks.
Many times it is easier to use CRON instead of the internal tools; especially, if you want to control things even if the instance is down. We use some wrapper scripts that allow us to easily execute different tasks using the same script over and over without more PL/SQL.
The below example I utilize on Unix and Linux to create a job to clone a database to another server.


Note that the syntax for executing the script is in the comments for the script
more xrcpfiles.sql

REM collect the names of the oracle files in the database

-- ---------------------------------------------------- --
-- xrcpbuild.sql - RCP files from one machine to --
-- another. --
-- will build rcpcopy.sh in place --
-- --
-- sqlplus -s / @xrcpfiles targethost --
-- ---------------------------------------------------- --

set echo on
set feedback off
set heading off
set linesize 200
set pagesize 300
set verify off

define nhost ='&1'
define pref ='&2'

whenever sqlerror exit sql.sqlcode

spool $ORACLE_BASE/admin/@/create/rcpcopy.sh

prompt #! /bin/ksh
prompt #Name - rcpcopy.ksh copies
prompt
prompt if [ -z "$1" ]
prompt then
prompt echo "supply email address"
prompt exit 2
prompt fi
prompt
prompt if [ -z "$2" ]
prompt then
prompt echo "Is it a hot copy YES or NO"
prompt exit 2
prompt fi
prompt

prompt echo "start `date`"
prompt

prompt if [ $2 = "NO" ]
prompt then
select ' rcp -p ' rtrim(name)' &nhost:&pref'rtrim(name)';print rc=$?' from v$controlfile;
select ' rcp -p ' rtrim(member)' &nhost:&pref'rtrim(member)';print rc=$?' from v$logfile;
prompt fi
select 'rcp -p ' rtrim(name)' &nhost:&pref'rtrim(name)';print rc=$?' from v$datafile;
select 'rcp -p ' rtrim(name)' &nhost:&pref'rtrim(name)';print rc=$?' from v$tempfile;

prompt
prompt echo "end `date`"

prompt
prompt echo "rcp from `uname -na` completed" mailx -s "rcp_from_`uname -na`" $1
prompt
prompt exit 0

spool off

exit

sqlplus -s / @xrcpfiles targethost

The -s says to run in silent mode and the script is after the @ sign. The targethost in this case is a parameter of the target.
I use operating authentication on my Oracle script environments so that I don't have to specify passwords for cron jobs. That is what the / means in the above
By simply typing the above if your variables and SQLPATH are setup or if you run the above from where the script lives you can use SQLPLUS to drive many batch processes.
Any SQLPLUS script could then be executed similar to the above or as a CRON scheduled task or a Windows scheduled tasks if done under windows .


SQL Server:


In SQL Server one option you have is if you are running the SQL Server Agent you can create jobs to batch up various T-SQL processes using Management Studio (You can also do it using the appropriate sp_ stored procedure).

There are times though with one-off processes that this can be a bunch of work with the various options available in the GUI.

Many times it is simplier for me to just place the commands in a file and pass them to osql which I can run at the commandline or even pass osql as a Windows scheduled task.
In SQL Server one way I have scheduled tasks is to create a script and then utilized osql to run it in batch. I believe in 2008 there is a replacment for osql which appears be sun-setting. It should not be much different.


osql -d dbase -E -S server\instance -i e:\scripts\truncates.sql


The above using a -E which is trusted which means you are using Windows authentication. The -d specifies the database where it will run and -S is the named instance or default instance and the -e specifies the file. It would execute the truncate statements specified in the file passed using the -e switch.


I hope you have gotten something out of the ways you can execute SQL statements from the command line in various RDBMS systems. There are other ways to do this that we did not discuss.



Next time we will look at some high availability options.