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.

My DBA journey and database compression options

Career:

In October 2008, JAW has decided to join the blogesphere!

Back in 1985, I started out after matriculating through college (Can you say COBOL?) as a 3800 laser printer/computer operator. I thought I knew a lot about IT coming out of college but I found out that I didn't know a lot. Anyway I started to immerse myself in the technology of the time and started to get promoted a lot. I developed a love for CLIST and REXX programming.

IIRC, my first professional experience with a RDBMS (can you say VSAM,ISAM, or QSAM?) was DB2 V2.1 around July 1989 as I was what is known as a MVS (Z/OS) Systems Programmer. At various points I was a DB2 systems programmer, CICS systems programmer, VTAM/NCP/TCPIP systems programmer. Most of these types of jobs today are done by mainframe outsourcing outfits instead of in-house as the skill set is dwindling. I even have a file on the CBT mainframe shareware site at http://cbttape.org/. Around this time I started to learn as much as I could about databases as I began to see a brighter future in this. In the mid-to-late 90's I was doing DB2 System Programming and DB2 Application support which are usually separate roles in a mainframe shop along with becoming stronger in the programming languages of that platform. I have worked on DB2 all the way up to V9.1 on Z/OS and V9.7 on Windows.

In 1998 as a DB2 systems programmer I was given the opportunity to install and configure the Oracle Transparent Gateway for DB2. This simply allowed a DB2 subsystem's data to be accessed from Oracle 7.3 which had just made its way to the shop by 1998. Once you setup the gateway correctly you could go into Oracle and create a database link and a synonym and you did not know the data was in DB2 unless you SET AUTOTRACE ON or the like inside of SQLPLUS (or that you were pulling big data over the wire). The gateway on the mainframe was a DB2 application that understood Oracle and did the heavy work.

At this point I said DB2 is the king and what is this Oracle stuff?

However, I decided not to be like some peers and jumped into this thing with both feet as I was a young guy who could learn new stuff I started to buy Oracle books and pick up stuff from the contract Oracle DBA brought in to help build this UNIX based application that required data from mainframe DB2. The company also started to send me to class to learn Oracle and MQ-Series as queuing was coming into play.

During the push to Y2K (I was still primarily a mainframer) the company announced it would outsource my department because the mainframe support was too costly and that all new development would be Oracle. Since I was spending my own time and money to learn shell programming, AWK, SED and Oracle I was kept on for the new millennium and went into being an Oracle DBA full time in 1st quarter 2000! I was working on V7.3, V8, and starting to study 8i( 8.1.5 can you say shared pool contention???) which was installed on databases systems that year at my shop. I was working with some more experienced Oracle guys picking up all I could learn. I was able to quickly use what I knew about DB2, JCL, COBOL, BAL, and REXX and come up to speed on the similar Oracle/UNIX constructs. Eventually, I became the Lead Oracle DBA as fate would have it. IIRC, it was around 2003 that SQL Server 2000 made its way into the shop in a big way. Management decreed that new applications that did not require Oracle would be done on SQL Server.

They say that those who don't learn from history are doomed to repeat it. In 2004 I started to buy SQL Server books (LOL!) and began to learn SQL Server 2000. I found DTS to be pretty good for quick and dirty stuff. I started to learn VBSCRIPT, VB, and took classes on DOTNET and C development somewhere in this period as C# was to become the the new platform for the company I even dusted of some DOS SHELL programming stuff. I eventually re-wrote all of my Oracle UNIX scripts in VBscript to support Oracle on Windows and became a big proponent of RHEL Linux also. I started to play with ADO and later ADO.NET and started to have some fun at this time with that. We brought in strong SQL Server people which I picked up things from them also over time. When SQL Server 2005 came out I started learning about it and spent time comparing and contrasting the various features of the different platforms. In my spare time I am trying to learn something about SQL2008. Being a jack of all trades and master of none is tough. Some of the most fun I had in this period was using the various DOTNET providers for Oracle to access from the Visual Studio architect.

Most of my work time is spent with Oracle and DB2. Today, I am in the midst of learning Oracle 11G, Oracle Application Express (just created our first production APEX application with help from the APEX forum), SQL Developer (we are trying to get rid of TOAD) in addition to my regular work. I have found ADDM and SQL Tuning advisor to be great for my Oracle 10g applications.

I think of the big three that Oracle is the most feature rich followed by DB2 and SQL Server. As you should be able to tell I would not have said that years ago when I was a mainframe bigot!


Compression:

A concept where less is usually more! Every experienced IT person understands the concept.

In the DB2 world I first dealt with compression in V3.1 back in the mid-to-late nineties. It was a huge saver of disk. IIRC, we had a mainframe with DB2 Sort Assist to move some of the cycles off the mainframe into the firmware. The thing I liked about it was most was that the software came at no special cost as long as you had the proper hardware. This saved us reams of DASD over time and performance improved for us including our OLTP applications because more data was quickly available for processing! Basically, adding a COMPRESS operand to CREATE TABLESPACE or ALTER TABLESPACE did the trick.

In the Oracle world I noted that compression showed up around Oracle 9i. However, it was primarily of use for OLAP applications with a few restrictions that you can read about. To be fair, one aspect that Oracle had before DB2 was the ability to compress indexes which can buy you a lot with composite indexes. In Oracle 11g advanced compression came about and I would make comments to my Oracle representative about the country being in a recession and why this is not in the base stack charge of EE? Larry needs a new boat I guess. Based on my DB2 experience I can see getting the same boost for OLTP if you pay the extra money. I am still mad about that one though.

In the SQL2008 side of the house MSOFT has introduced compression. I need to read more about it and play with it when the chance presents itself. If you have tested it let me know. I would think that you would not use it in production until a few service packs go by. However, in SQL 2005 I was surprised how reliable mirroring was which is similar to Oracle Data Guard or DB2's HADR.

Next time we can talk about command line options for the big 3.

To paraphrase Nature Boy Ric Flair, "Diamonds are forever and so is database technology, WOO!".