Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Of Interest » Articles of Interest » Quick Database Scripting - Part 1: What Are Scripting Languages and Why Are They Useful for Database Programming?
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3558
 

Quick Database Scripting - Part 1: What Are Scripting Languages and Why Are They Useful for Database Programming?

by Howard Fosdick

Part 1  |  Part 2

Ever need to get to your data in a hurry? Most relational databases have SQL front-ends and there are a plethora of query, reporting, and analysis tools out there. They’re your quickest means to access data. Use them whenever you can.

But sometimes you’ll face a problem that requires programmability. For example, maybe you need complex data analysis or want to automate administrative tasks. You might use tried-and-true solutions like hand-coded Java, COBOL, or C++. But coding in these languages takes a lot of effort. Something more productive is always welcome.

Open-source scripting languages fit this intermediate-level need. Scripting languages are the big software story of the decade. They are true programming languages that offer complete programmability, yet they are higher level than traditional programming languages. Each line of code in a scripting language typically does more than a line of code in a traditional language. This higher productivity means that scripting has, to some degree, displaced traditional programming. While object-oriented programming, or OOP, has been the center of attention over the past decade, scripting languages have quietly proliferated and spread. Go to any large bookstore and you’ll probably see more books today on scripting than on OOP. (Of course, scripting and object-oriented programming are not mutually exclusive. Some languages fit into both categories.)

The downside to scripting is that programs run more slowly than compiled programs. If you’re creating a high-volume OLTP transaction, this is critical. But for most situations, it makes sense to move from labor-intensive programming languages to those that place more of the programming burden on the machine.

Scripting languages are productive because they are glue languages. They are designed to stitch together existing software components. Most easily leverage operating system commands, programs, functions, services, commands, objects, widgets, controls, tools, and interfaces.

Scripting languages are interpreted. They translate and execute lines of code one at a time. Many have powerful, embedded interactive debuggers. These allow you to start and stop script execution at will, inspecting and even altering program variables. You can step through lines of code, as they execute, to see exactly what’s going on in the script. Correcting a logic error becomes a quick process.

Scripting languages are dynamic. Compiled means static, with predefined or declared variables, static-sized arrays or tables, fixed-length variables, unalterable data types, and everything that goes with it. Interpreted lends itself to a dynamic approach, with variable-length strings and arrays and transparent data conversions. Not all scripting languages are the same, but most leverage features like these to shift the burden of programming from the developer to the machine. Scripting typically automates variable and memory management for greater developer productivity than traditional or compiled languages.

Which Scripting Language?

While there is a major trend towards scripting, scripting languages are as varied as traditional programming languages. Which should you use?  In most cases, you won’t have a choice. Your organization will have already made the selection for you, and in the interest of “site standardization” you’ll be required you to use that language.

If you do get to choose your scripting language, consider the most popular free and open-source languages. These include Perl, Rexx, Python, Tcl/Tk, and the UNIX shell languages (like the Korn and Bash shells). Different languages have different strengths, and all these languages have earned a role in the industry. No language is, across the board, better than any other.

For quick database scripting in IT organizations, I recommend either Perl or Rexx. Perl and Rexx are both:

      • Widely popular.
          • This means good support, many free add-in tools and interfaces, and a vibrant peer community.
      • Universal.
          • They run on any platform under all the major operating systems.
      • Standardized.
          • Your scripting skills are transferable and your scripts are portable across the many systems on which these languages run.
      • Free and open source.
          • The free software movement has taken over scripting languages so  completely that all the major ones are free or open source. Why pay?
      • Procedural with object extensions.
          • Both Perl and Rexx offer object-oriented programming in a manner that is completely upwardly-compatible with the standard procedural language.
      • Database independent.
          • Both languages have database-independent relational interfaces based on major database standards like ODBC and the X/Open CLI.
      • Interface to all major database management systems.
          • Both languages interface to all DBMSs including commercial systems like  Oracle, DB2 UDB, and Microsoft SQL Server, and open-source systems like MySQL, PostgreSQL, and Ingres.


This article and its companion piece are intended to get you up and running as quickly as possible with database scripting. Perl offers many advantages. It is the most popular scripting language in the world, has a huge set of free modules in the online CPAN library, and is very powerful. Yet Rexx is far easier to learn and use. In fact, if you were to describe Rexx in a phrase, “powerful yet easy” would probably be it. For this reason, we choose Rexx for our programming examples. Our goal is for you be able to script DML and DDL for any major database by the end of our next article. With Rexx, we’ll meet that goal. Rexx is a great choice if you want to learn scripting quickly and need a cross-platform, database-independent tool to boot.

Getting Rexx

IBM invented Rexx 20 years ago. The company bundles Rexx with all its mainframe, mid-range, and low-end operating systems. Rexx’s popularity slipped as mainframe marketshare declined, but two events have led to a “Rexx renaissance.” First, IBM handed control of Rexx to the American National Standards Institute (ANSI). And second, the open-source movement exploded.

Ka-Boom! Today, there are eight free Rexx interpreters you can download for any imaginable platform. All meet the Rexx standards, so both your scripts and your skills are portable across any platform, database, or interpreter you might use. Each interpreter offers special strengths. Two are standard Rexx, plus extensions and tools for Windows. Two others are standard Rexx, plus object-oriented extensions. Still others are optimized for Linux or handhelds or ... you get the picture.

Table 1 lists the free, standard Rexx interpreters. For this article, I chose to use Regina because it runs on almost any platform, and is the most widely-used open-source Rexx. With Regina, you can run the database scripting examples presented in our next article under Windows, Linux, or UNIX.

Interpreter

Platforms

Comments

Regina

All major operating systems

Most popular. Runs almost everywhere.

Rexx/imc

Linux, UNIX, BSD

For Linux, UNIX, and BSD systems.

BRexx

Linux, UNIX, Windows, Windows CE, Mac OS, 16- and 32- bit DOS, others

For general use. Especially useful for handhelds and limited-resource computers.

Reginald

Windows

Comes with Windows tools and interfaces.

r4

Windows

Comes with Windows tools and interfaces.

Rexx for Palm OS

Palm OS

For handhelds running the Palm OS.

Open Object Rexx

Windows, Linux, UNIX

Standard Rexx extended for fully object-oriented programming.

roo!

Windows

Standard Rexx extended for fully object-oriented programming.

NetRexx

Any Java platform

The only non-standard Rexx interpreter. Use it in Java environments to script applets, applications, classes, beans, and servlets.

Table 1: Free Rexx interpreters.

All Rexx interpreters meet the international Rexx standards. Most also add special “extended” features for their particular platforms and operating systems.

Download Regina from SourceForge (the big open-source Web site); the download comes with install instructions and full documentation. The manner in which you install the product conforms to the practices typical of your operating system. For example, under Windows, just download the *.exe file and double-click on it. The Windows Installer starts and you perform a typical Windows product install. Similarly, for a Linux or UNIX system, download the *.tar.gz file. Decompress and de-archive the download file, then run the configure and make operating system commands. Or, use the Red Hat Package Manager to install Regina in a single command. Download the *.rpm file and issue the rpm command to install Regina.

Table 2 lists Rexx resources. We picked Rexx because it’s easy, so you won’t need to refer to these resources to follow the scripting examples in our next article. However, we list them in case you want more information beyond what these two articles provide.

Resource Links

Book

Rexx Programmer’s Reference includes a tutorial and covers free Rexx, its interpreters, interfaces, and tools.

Forums

There are several active Rexx forums, including some that focus on Rexx on the mainframe and Rexx under Windows.

User Groups

Visit the Rexx Language Association.

Tools and Interfaces

This site and this one should get you started with the many free tools and interfaces available.

Database interfaces

Open-source Rexx/SQL, IBM’s Rexx-to-DB2 proprietary interface.

IBM’s Rexx Web site

IBM offers plenty of good material at their Rexx Web site.

Table 2: Rexx resources.

Interfacing Rexx to Your Database

There are several different database interfaces for Rexx. For example, IBM ships a proprietary Rexx interface with DB2 UDB. The IBM Rexx/DB2 interface supports mainframes, Windows, Linux, and UNIX systems. It comes complete with sample programs.

We take an open-source approach in this article. The open-source Rexx/SQL interface connects Rexx scripts to almost any database. These include commercial systems like Oracle, DB2 UDB, and Microsoft SQL Server, and open-source databases like MySQL, PostgreSQL, and Ingres.

Rexx/SQL supports all the features you’d expect in a database interface. These include the ability to issue all kinds of SQL statements, including DML, DDL, and DCL; return codes and communication through the SQL Communications Area or SQLCA; cursor processing; multiple simultaneous database connections; the ability to configure and control database connections; and many other features. Rexx/SQL conforms to the X/Open CLI and ODBC API database standards.

Table 3 lists the commands for which Rexx/SQL provides database scripts. Rexx scripts simply issue these commands to communicate with their target database. Rexx/SQL includes all the commands you would expect in any standards-conformant database interface. These allow scripts to connect to databases, issue SQL, control the nature of the database connection, and disconnect from the database at the end of processing.

Rexx/SQL Functions Use
SQLConnect Creates a connection to a database.
SQLDisconnect Terminates a database connection
SQLDefault Sets the default database connection.
SQLCommand Executes any SQL statement as a single step.
SQLPrepare Prepares a SQL statement for processing.
SQLDispose De-allocates the work area associated with a SQL statement.
SQLOpen Opens the cursor for a SELECT statement.
SQLClose Closes a cursor, frees its resources.
SQLFetch Fetches the next one (or more) row(s).
SQLExecute Executes a prepared INSERT, UPDATE, or DELETE statement.
SQLDescribe Describes expressions returned by a SELECT statement.
SQLCommit Rolls back database changes.
SQLGetdata Extracts specified column data from a row.
SQLGetinfo Returns information about a database.
SQLVariable Returns or sets database options.
SQLLoadFuncs Loads all Rexx/SQL functions for use by a script.
SQLDropFuncs

Ends Rexx/SQL use and frees resources.

Table 3 Rexx/SQL commands.

Download Rexx/SQL from Sourceforge, nand download the file type appropriate to your operating system (e.g., download the *.exe file for Windows and either the *.tar.gz or *.rpm files for Linux or UNIX).

For our example scripts in the next article, we’ll use the open-source MySQL database as our target database system. So, download the Rexx/SQL driver or database interface for MySQL. (Although we use MySQL for the example scripts in our next article, we’ll show you how to target DB2 UDB and Oracle databases with Rexx scripts as well.) 

Installing Rexx/SQL is as easy as decompressing the download file. For Linux and UNIX systems, you’ll have to set an environmental variable so that the product’s shared library can be located. This simple action is fully described in the install documentation.

Next Time

This article discussed why scripting languages are ideal for quick database programming. Scripting offers quicker development than traditional programming languages while providing the flexibility and programmability that query and reporting tools lack.

Rexx and Perl are good choices for IT database scripting. Rexx is especially suitable because it combines power with ease of learning and ease of use. Even if you’ve never programmed in Rexx before, you can learn to script database tasks with it very quickly.

Our next article will walk you through several Rexx scripts that perform database processing. We’ll show you how to connect to a database, issue DML and DDL statements, and handle common error conditions. By the end, you’ll be fully capable of quickly scripting common database tasks. You’ll be able to write programs that issue any DML, DDL, or DCL statements you want. Stay tuned!

--

Howard Fosdick is an independent DBA consultant who has worked with most major scripting languages. His book Rexx Programmer’s Reference starts with an easy tutorial and then covers everything you’ll want to know about Rexx, its interfaces, the available interpreters, and database programming.

 


Howard Fosdick
Last modified 2006-01-04 11:26 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone