Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I realize this is a somewhat more subjective question, but I am looking to the community for guidance.

Our company is fairly new to having DBAs. We used to use DB2 for i on the IBM's iSeries servers, so there really wasn't a need for a DBA (at least operationally) as the OS pretty much managed everything for us. Within the last few years we started to move to DB2 LUW on AIX. Thus a need for DBAs was born. Three of us were interested in making the career change from developers to DBAs (of which I am one of them).

Because we are on AIX, we defaulted to using ksh as our scripting language for database management functions (creation, deployment, operations, etc.). I have found ksh to be quite powerful, yet some of the functions I need to perform get rather complex. An example of this is my question over on Unix and Linux StackExchange.

Based on that question, as well as reading blogs by other DB2 administrators, I see a general suggestion that Perl is better suited for administration scripting than ksh is.

I realize that the Unix/Linux forum would have their own thoughts on such a thing, but I wish to approach this from a database administration viewpoint.

Regardless of DBMS, which scripting language seems better suited for administration on the Unix/Linux platform? Perl? Ksh? (or perhaps other?). What strengths and weaknesses in your use of scripting languages did you encounter as a DBA, and why did you pick the language you did?

What I don't want is a highly subjective flame war on "My language is better than your language!" kind of stuff, like I used to see in the development community regarding say Java vs Ruby, etc. I am looking for honest, as objective-as-possible, information regarding what best suits the needs of database administrators.

2 Answers
2

information regarding what best suits the needs of database administrators.

So let's start there.

What are your needs as a DBA? You generally have two realms of operation: system level maintenance, and database level maintenance (whereby all actions are merely maintaining the system for better performance ... look, don't get pedantic, go with me here). So you have two realms to consider.

ksh - The Korn Shell is one of the older Unix operational shells, used to control the server. The goal of ksh is to manage a server, and allow one to do all the things that the traditional Bourne shell (not the Bourne Again SHell that most people are familiar with, this is older vintage) allowed, with a few differences (this shell had better support for things like arithmetic and arrays, making it easier to program in some regards). It is the default shell on AIX.

perl - Perl is a high-level general purpose programming language. It is designed to be run on top of a shell, and was designed primarily with reporting in mind. It combined features of shell scripting and C, as well as regex technologies to attempt to make it better than all of the previous. A lot of admins around the planet like perl because it makes system management easier, because of the combination of shell capabilities and report generation speed up (it being a programming language). Perl scripts can be run as simply as shell scripts, and there is even a convention in the Unix world of having the first line indicate what will run the script (the familiar hashbang line). So running perl scripts is as easy as running a shell script.

The difference, then, is do you need a full-fledged programming language, or do you need a basic shell script? The answer then becomes use the one that gives what you need, as both are highly useful to you. If you want stats, that's impossible to give, we each have our own style. Just agree on something internally and call it that (if you're going to set a standard, make it perl as that has more features, and you never want to get your hand caught because you set an unrealistic standard. Perl is also more extensible in the future). Better to restrict yourself to two or three languages in this case.

Back at the beginning I mentioned that you have two options: system level and database level.

If your purpose is primarily system level, then we've answered all the questions here (use the one that suits your needs). If you also need to program into the database then you really need to just standardize on perl or get really used to command line syntaxes, because Perl has modules for every conceivable database on the planet somewhere, and if one doesn't exist, it can be written (example: DBI -> DBD:DB2 -> DB2 client).

As an aside, I do Microsoft SQL Server and C#, so I live in the Windows world, and I use PowerShell over DOS Batch files. PowerShell is in many ways similar to perl, and gives me a lot more flexibility than DOS batch programming (but there are plenty of admins in the world still using old DOS scripts to manage their systems). While the DOS Batch system allows for arrays, loops, variables and all the rest, it's much easier to program in a programming language instead of a shell script, so I'm able to be much more productive.

At the end of the day, the real question is what features do you need in your scripting environment, and we can't help you with that. But I will always opt for more clarity, maintainability and extensibility, over "this is what shipped in the box".

Agreed. Some of what I am running into is trying to capture and parse what is written to the console by DB2 so I can know whether something is an error, a warning, or information, and then act accordingly. So this means a lot of if/else control structures, regex, etc. I do find this a bit challenging in ksh, though I have managed to do some of what I need. Do you do anything similar with your jobs? Is this one reason why you use PowerShell?
–
Chris AldrichJan 3 '13 at 17:47

That is indeed one of the many reasons I use powershell. Capturing output there is as simple as $var = @( command -flag -flag -flag ) and voila, output in an array. :D
–
jcolebrand♦Jan 3 '13 at 17:59

ksh or bash let you script items through SQL*Plus, and you can do quite complex stuff through this. However, shell scripting tends to be a bit 'write-only' and sh and its derivatives aren't really much good for developing complex program logic. For running automated tasks they're OK. For complex client-side data manipulation, not so good (sed/awk notwithstanding).

Perl has various Oracle libraries such as DBD:Oracle available through CPAN, and is somewhat better for work that involves complex processing client-side as its type system is a bit more sophisticated than 'line of text'. Another option that's quite good for this is python and cx_Oracle.