Netezza Scripting – Securely Call nzsql or nzload Without Disclosing Password

You'd use the sp_OA% stored procs for this. Or CLR since SQL Server 2005.

It can't be done via T-SQL directly because T-SQL is for data manipulation. So you have to use one of the 2 methods above. Unless you want to use xp_cmdshell to run a powershell script.

This also brings up one limitation of T-SQL: how to get an object definition to disk? And I guess one reason why you asked this. Again, CLR or sp_OA% will do it.

One thing to note is that almost every method and property in SMO maps to a SQL command or query. So using T-SQL to call SMO which is effectively T-SQL is circular.

And to get the stored procedure definition you'd use OBJECT_DEFINITION... to get the other properties available in SMO you'd use OBJECT_PROPERTY or query the system objects.

The end of your post is really the important bit:

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".