DB2 Scripting Hacks for Linux, UNIX, and Windows

50 Pages·2008·0.37 MB·English

Session: D14
DB2 Scripting Hacks for
Linux, UNIX, and Windows
(Yes, Windows)
Fred Sobotka
FRS Consulting, Inc.
May 22, 2008 •10:30 a.m. –11:30 a.m.
Platform: DB2 for LUW
Abstract: Automation is not laziness. It is, in fact, an absolute necessity
to staying sane as a DBA. This session shows DBAs how to automate
even the most tedious of tasks by exploiting many diverse utilities that
are either part of the base operating system (Linux, UNIX or Windows)
or are freely obtainable online. The tips presented cover a wide range of
use cases and experience levels, but all are based on real-life scenarios
encountered by a battle-hardened DBA. The presentation uses
numerous functional examples to demonstrate how DBAs can apply
free yet powerful utilities to streamline many common tasks.
0
An hour of scripting power
• Working with (and around) the DB2 command line
• Perl, sed, awk, xargs, and other UNIX tools
• Bringing UNIX to Windows with Cygwin
• Powerful Windows scripting with PowerShell
• Job scheduler cage match (cron, schtasks, etc.)
1
But first, what exactly are hacks?
Definitions borrowed from the web:
““……ccrreeaattiivveerreecciippeess tthhaatt ssiimmpplliiffyy
wwhhaatt wweerree oonnccee ddiiffffiiccuulltt ttaasskkss””
““……[[aannsswweerrss ttoo qquueessttiioonnss]] tthhaatt
wwoouulldd ggeett aa ggoooodd qquueessttiioonn
ccoommmmeenntt rraatthheerr tthhaann aann RRTTFFMM!!
rreessppoonnssee""
““……iinndduussttrriiaall--ssttrreennggtthh,, rreeaall--wwoorrlldd,, tteesstteedd
ssoolluuttiioonnss ttoo pprraaccttiiccaall pprroobblleemmss””
““……eecclleeccttiiccttrriicckkssffoorr pprroobblleemmss tthhaatt aann
uunnaammbbiittiioouuss ppeerrssoonn ((aa nnoonn--hhaacckkeerr)) wwoouullddnn''tt
eevveerr ppuusshh tteecchhnnoollooggyy hhaarrdd eennoouugghh ttoo rruunn iinnttoo””
2
Hacks can sometimes be…
• Elegant
• Clever
• Perhaps even mystifying
But, at the very least, they are always…
• Time-saving
• Useful
• Indispensable (once they’re in your toolbox)
3
Maybe we should think of them this way
DDBB22
SSCCRRIIPPTTIINNGG
HHAACCKKSS
For Linux, UNIX,
and Windows
(Yes, Windows!)
Fred Sobotka
[email protected]
4
That sweet hunk of metal, by the way, is called the FUBAR. I’m not kidding.
4
How can scripting hacks help DB2 DBAs?
• Decrease reliance on bandwidth-intensive GUI tools
• You probably weren’t that fond of them anyway
• Improve script portability from UNIX to Windows
• Strengthen security by eliminating stored passwords
• Anything to get those cranky sysadmins off your back
• Reduce # of keystrokes needed for common tasks
5
Although graphical tools like the DB2 Control Center can be useful in helping
DBAs understand what commands are necessary to carry out specific tasks,
that doesn’t necessarily mean that DBAs will continue to rely on db2cc to carry
out those tasks again and again. Typically, the DBA obtains the SQL
command string from db2cc the first time, captures and saves the command
into a script, and rarely (if ever) returns to db2cc for that command.
If you have a UNIX-style shell for Windows, there are minor programming
tweaks you can carry out to ensure that your scripts run equally well on UNIX
and on Windows.
Public-key crypto (as implemented by the SSH protocol), enables us to
communicate with and control remote servers in a secure manner.
Reducing keystrokes not only means reducing typing time, but it also means
that fewer mistakes are likely to occur.
5
Getting the most out of the CLP
6
Anatomy of DB2’s Command Line Processor
Inter-process
ddbb22 communication db2bp
((oorr DDBB22..EEXXEE)) (or DB2BP.EXE)
•Front-end process •Back-end process
•Transitory •Keeps running
•This is what you run •This part accesses DB2
……aanndd tthhiiss mmaatttteerrss bbeeccaauussee??
7
It’s important to understand the basic design of DB2’s command line processor
because it influences the way we write our scripts.
7
DB2 back-end process and the UNIX shell
$ . /home/db2inst1/sqllib/db2profile No db2bp process has been created yet.
$ db2 list db directory db2bp process now exists.
$ db2 connect to sample Same db2bp process is re-used,
$ db2 select "count(*)" from staff …and re-used again.
$ db2 connect reset db2bp stillexists after disconnecting.
$ db2 terminate Now db2bp is gone.
An interactive shell or batch script sends all of its DB2 commands through a
single instance of the db2bp backend process.
Every distinct shell or script that interacts with DB2 is assigned a unique
instance of db2bp. Multiple shells cannotshare the same db2bp process.
8
The db2bp process is created on demand during the first use of the CLP, not
when db2profile is sourced. The DB2 documentation has been a bit confusing
about this, since it describes db2bp’s purpose as maintaining the database
connection. This interpretation can lead to the mistaken assumption that
db2bp only exists when a DB2 database connection is open.
8
CLP example – Linux and UNIX
1stcommand: db2 "connect to proddb"
bbaasshh
SShheellll pprroocceessss ddbb22
SSyysstteemm ppiidd 110000 CCLLPP pprroocceessss
SSyysstteemm ppiidd 110011 1stcommand triggers
creation of db2bp
2ndcommand: CLI connection
db2 "select \
max(order_time) \ ddbb22bbpp
from orders" ddbb22 BBaacckk--eenndd pprroocceessss DDBB22
CCLLPP pprroocceessss SSyysstteemm ppiidd 110022
SSyysstteemm ppiidd 110033
2ndcommand
reuses db2bp
TTiieedd ttoo bbaasshh
pprroocceessss ((ppiidd 110000))
9
In this diagram, bash was the process that actually sourced db2profile. This
means that any DB2 processes launched directly from that bash session reuse
the db2bp that was initially created.
9