Script for DB2 Universal Database using REXX: Learn REXX fast

An introduction to scripting using REXX

If you learn Rexx, you’ll know a scripting language that runs everywhere from mainframes to handhelds—and everything in between. Rexx is a general-purpose language that's powerful enough for mainframes yet flexible enough for other platforms. Best of all, Rexx is easy to learn. Howard Fosdick introduces you to using Rexx with IBM® DB2® Universal Database™.

Share:

Howard Fosdick (hfosdick@compuserve.com), Consultant, founder and former president of IDUG, Independent consultant

Howard Fosdick is an independent DBA consultant who has experience working with most of the major scripting languages. His book, Rexx Programmer’s Reference, starts with an easy tutorial, then covers everything you need to know about Rexx and its tools.



18 August 2005

Introduction

If you’ve programmed under IBM operating systems, you’ve undoubtedly heard of Rexx. Rexx is the scripting and command language IBM bundles with all its mainframe, mid-range, and lower-end operating systems. What you might not be aware of is that Rexx also runs on almost every other operating system in the known universe. You can download Rexx free for all versions of Windows®, Linux, UNIX®, BSD, Mac OS, and DOS, and many other systems. It even runs on the three major operating systems for handheld devices: Windows CE, Palm OS, and Symbian/EPOC32.

What this means is, if you learn Rexx, you’ll know a scripting language that runs everywhere from mainframes to handhelds—and everything in between. Rexx is a general-purpose language that's powerful enough for mainframes yet flexible enough for other platforms. Best of all, Rexx is easy to learn.

In this article, I'll give you a quick introduction to Rexx as a basis for using it to access IBM DB2 UDB data. (In a follow-up article, I'll present and discuss more example Rexx scripts that go much further in explaining how to script Rexx specifically for DB2 applications.) I’ll briefly overview what the Rexx language looks like and show you how easy it is to learn. I’ll also describe the many free and open source Rexx interpreters available and tell you where to download them. Most importantly, I’ll direct you to other free sources of information on the Web (see Resources). Within a matter of days, you can learn to program using Rexx, a scripting language that applies to almost any programming problem and runs on almost any machine.


Instant Rexx

OK—now that you know Rexx is easy to learn, it's time to prove it. Here’s a complete Rexx script:

input = 'invoice_file.txt'            /* Name the input file       */

do while  lines(input) > 0		/* Do while "lines to read"   */
   
  	   input_line = linein(input)         /* Read in the next line     */

  	   if pos("INVOICE OVERDUE", input_line) > 0   then

              say "Found it!  Here's where:"  input_line     

  	   else
              say "Not found in this line:"   input_line     

end

This script reads all lines from an input file one at a time and scans them for the phrase INVOICE OVERDUE. If it finds this phrase, the script writes a message and the line in which it was found. It also displays rejected lines with an appropriate "not found" message.

In this script, the first line assigns the name of the input file, here named invoice_file.txt, to a variable named input. This is a simple assignment statement as is found in almost all programming languages. In Rexx, the character string or literal assigned to the variable may be enclosed in either single or double quotation marks:

input = ‘invoice_file.txt’           	/* Name the input file        */

The do while loop that follows drives the program by processing the input lines from the input file one at a time. It uses the built-in lines function to determine if there are more lines left to read in the input file. The lines function returns 0 when no more lines are left to read:

do while   lines(input) > 0		/* Do while there are lines to read  */

In Rexx, all built-in functions are identified easily because they always are followed immediately by parentheses that contain their input argument(s). There can be no space between the left parenthesis and the function it follows. The previous statement shows that the lines function takes one argument, which is the name of the file it should test for "end of file." Remember that in our script, the variable input refers to the input file named invoice_file.txt.

Now here’s the heart of the program. This line uses Rexx’s pos built-in function to determine whether the input line contains the character string INVOICE OVERDUE:

if pos("INVOICE OVERDUE", input_line) > 0   then

The pos function returns the position at which the phrase INVOICE OVERDUE occurs within the given line. Or, it returns 0 if the phrase is not found in the line.

The test in this statement enables the script to write a line to the display screen that tells whether the character string INVOICE OVERDUE exists in the input line. Rexx’s say instruction then writes the output line. In our example, the say instruction has two operands. The first is a character string literal, enclosed in quote marks, and the second is the variable that contains the line from the file. The say instruction can take as many operands as you like, and it concatenates them together automatically and sends them to the user’s display screen:

   if pos("INVOICE OVERDUE", input_line) > 0   then

      say "Found it!  Here’s where:"  input_line

   else
      say "Not found in this line:"   input_line

That’s it. You’ve read a complete Rexx script. You can see that, as languages go, Rexx requires very little in the way of syntax and has no "special characters." Is this what makes it an easy language?


Why Rexx is easy

The simple script I've shown demonstrates Rexx’s ease of use. For example, it shows that you do not have to declare or predefine program variables. Simply encode a new variable whenever and wherever you want, and Rexx defines it for you. For example, the first line in the program defined the variable input automatically by virtue of assigning it a value; it was not necessary to declare or predefine this variable prior to using it.

Nor is it necessary to declare the data type of a variable. All variables in Rexx are typeless. Rather, they contain variable-length strings and their contents define their data type. If you assign a variable a string that looks like a numeric value, Rexx will allow you to perform calculations on it. If you assign a variable a character string, Rexx will allow you to perform string manipulation operations on it (such as parsing, pattern-matching, or concatenation). Where some languages require you to predefine variables and assign them "data types," Rexx defines variables dynamically by their contents and use. Rexx transparently converts data as necessary when called for by various operations, and it automates much of the detail work that absorbs programmers using other languages.

Here is a simple example:

a = 3              /* Create a numeric variable                   */
b = "4"            /* This string value also represents a number  */ 
	
say    a + b       /* Displays the numeric result:  7             */

say  a  ||  b      /* Concatenate the two strings together…       */
                          /*  Displays:  34                              */
        
c = "Hi!"          /* Create variable that is a character string  */
	
say    a  +  c     /* This results in error!  You can not         */
                   /* add a character value to a number           */ 

->  SYNTAX:  Bad arithmetic conversion (error 41)

This code first creates two variables containing values representing numbers. (A number in Rexx is any string of digits, which optionally can contain a decimal place or be preceded by a sign. Numbers also can also be expressed exponentially in scientific or engineering notations.) The third statement shows how the two numbers can be added together, and the fourth statement shows they also can be treated as character strings consisting of a single digit each. The fourth statement concatenates or pastes together the two character strings—which happen to consist of digits—by the concatenation operator ||. The last two lines show that, while Rexx performs conversions automatically whenever possible, it will not let you add a non-numeric character string to a number; only variables that contain numeric values can be treated as numbers in calculations.

Let’s get back to our original example: the script that scans for the string INVOICE OVERDUE. Note that this script does not open its input file. Files are much like variables in that you do not have to define them explicitly prior to their use—you simply start reading or writing the file. Similarly, the program does not have to close the file after use as this is done automatically when the program terminates. I also didn’t end the script formally (you can code a Rexx exit instruction to end Rexx code if you like, but it’s not necessary here).

Beyond the implicit actions it takes on your behalf, Rexx is easy because it is a free-format language. You can use spacing, blank lines, and indentation however you feel most comfortable. As a simple example, you could have written the if statement like this and achieved the same result:

   	   if pos("INVOICE OVERDUE", input_line) > 0   
		then say "Found it!  Here’s where:"  input_line
		else say "Not found in this line:"   input_line

Rexx is as forgiving in terms of formatting as a programming language can be. It also is not case-sensitive. Its instructions, functions, and variables can be encoded in uppercase, lowercase, or mixed case. For example, you could have coded the variable name input_line as INPUT_LINE, Input_Line, or Input_line; it’s all the same to Rexx. Here’s yet another way to code the if statement that uses case to distinguish Rexx instructions and functions from the statement's other elements:
IF POS("INVOICE OVERDUE", Input_Line) > 0 THEN SAY "Found it! Here’s where:" Input_Line ELSE SAY "Not found in this line:" Input_Line


Why Rexx is powerful

Beyond its ease-of-use, Rexx’s basic design makes it a powerful language. As Figure 1 shows, Rexx consists of a small core of approximately 20 instructions. This core is then surrounded by a rich function set of about 70 built-in functions. This makes it easy to learn the basics while adding to your knowledge of the functions over time. It also means power. Beyond the built-in functions, Rexx scripts easily access dozens of free external function libraries, tools, and interfaces. These handle every imaginable interface, including database access, graphical user interfaces, XML, Web server programming, graphics, MIDI interfaces, Windows Registry updates, speech synthesis, and Apache programming—you name it, and Rexx can access it. You also can check out a few Web sites where you can access these free tools (see Resources).

Figure 1. The elements of Rexx
The elements of Rexx

Much of Rexx’s power comes from the fact that it is a "glue" language. Rexx scripts easily issue operating system commands. Consequently, IBM selected Rexx as its "command procedures language" years ago for its mainframes and other operating systems. Rexx glues together existing code—whether this code comes in the form of operating system commands, interface APIs, services, widgets, objects, functions, interfaces, or tools—and leverages it for greater productivity. This is what makes scripting languages such as Rexx so much more powerful than traditional, compiled programming languages such as C++, C, COBOL, or Java™. Although these languages can issue operating system commands and use generic interfaces with some effort, a scripting language such as Rexx is designed specifically to do just that.

One of Rexx’s most powerful features is its implementation of arrays (sometimes named tables in other programming languages). What makes arrays so powerful is that, not only can they be indexed by numeric subscripts, but you also can subscript them by character strings. Thus arrays support what is sometimes referred to as content-addressable memory or associative memory. This makes Rexx arrays a feature whose power extends far beyond the way tables are used in programming languages that support only numeric subscripting. For example, arrays can support dictionary look-up and form the basis for sophisticated kinds of data structures, such as key-value pairs, linked lists, doubly-linked lists, and trees. Arrays are a generic vehicle to associate variables and values in whatever way makes the most sense to the program, so they are more properly referred to as compound variables. Space forbids us from exploring this intriguing subject here, but you can find more information at the end of this article (see Resources).


Rexx features

Next, I'll briefly describe a few of Rexx’s other features. The language includes a complete set of instructions for the control of program logic (such as do, do while, if, if-then-else, select, call, return, and exit). It supports structured programming and modularity, which are principles widely accepted as best practices for program design. But Rexx is a power language and includes instructions for unstructured logic for those rare cases where you might need them. These instructions include iterate, leave, do until, do forever, and signal (which is roughly analogous to goto in other languages).

I’ve mentioned that Rexx contains some 70 built-in functions. These group into the categories of string manipulation, bit manipulation, numeric, input/output, conversion, environmental, and miscellaneous. Rexx scripts invoke external functions in the same manner as built-in or user-written functions (once access is established to the external function library). So when using an external or add-in function library, your Rexx code refers to those functions in the same way it would if they were a built-in feature of the language.

Like most interpreters, Rexx supports interactive debugging. You can run a script, stop it at any time, inspect its variables, and even re-run or alter the code. Rexx’s built-in debugger makes it easy to identify and fix logic errors. Interactive debugging generally is seen as superior to batch-mode debugging (though Rexx supports this, too).

Rexx also includes an exception or error handler. This traps a number of common error conditions (such as syntax errors or arithmetic overflow) and diverts the flow of control to a special error-handling routine you’ve written. The exception trapping provides a standard way to manage common errors from a single routine.

Rexx also supports a communication mechanism named the external data queue, but more typically is called simply the stack. A unique and powerful Rexx feature, the stack provides a vehicle for communication between Rexx routines or programs. Depending on the host operating system and the Rexx interpreter, the stack supports communications between different programs on the same machine or even between different programs on different machines. This includes remote communication across the Internet. Of course, Rexx scripts can also employ more traditional communications vehicles such as TCP/IP, FTP, sockets, and pipes.

Finally, I should mention the portability of Rexx scripts. Rexx has a strong language standard to which all the interpreters mentioned in this article conform. This not only makes your Rexx skills transferable, but it also renders scripts portable. Take a standard Rexx script, and you can port it easily across Linux, Windows, mainframes, handhelds—whatever you like. If your scripts issue operating system–specific commands, Rexx offers several free cross-platform interfaces to buffer scripts from this platform dependency.


Download free Rexx

I’ve mentioned that Rexx comes bundled with all IBM operating systems. In addition, you can download free or open source Rexx for any other operating system. There are eight free or open source Rexx interpreters. Each interpreter meets the Rexx standards, so your Rexx skills apply to all of them and your Rexx scripts will run under any of them. Where the interpreters differ is in the platforms they support and the "extras" they provide. For example, some interpreters run on handhelds, others offer operating –system–specific language extensions, and others support fully object-oriented programming.

Here’s a quick profile of six of the free Rexx interpreters:

Rexx interpreters
InterpreterPlatform(s)Quick profile
ReginaAll major operating systemsThe most popular free Rexx interpreter. Its large user community means ample support, and it works with all free tools and interfaces. It comes with many extra built-in functions and excellent, professional documentation. I recommend starting with Regina if you are new to Rexx.
Rexx/imcLinux, UNIX, BSDThis is a UNIX- and Linux-oriented interpreter that includes extensions for these environments. It has a proven track record for long-term support.
BRexxLinux, UNIX, Windows CE, Mac OS, 16- and 32- bit DOS, others This extremely fast interpreter has a small footprint and runs on many platforms, including resource-limited systems such as Windows CE, embedded Linuxes, and DOS. It offers many extra functions and interfaces and has a long-term track record of support.
ReginaldWindowsReginald customizes and extends Rexx for Windows programmers. It is easy to use and includes many Windows tools such as a GUI, speech, MIDI, and media functions. Reginald provides a standard language alternative to proprietary tools such as VBScript, and it is nicely documented with examples.
r4Windowsr4 extends Rexx for Windows with many tools including a GUI forms tool, color text file viewer and editor, more than 135 Windows command tools, visual accessories, XML to HTML auto-converter, GUI widgets, and others. It also includes excellent tutorials and example scripts.
Rexx for Palm OSPalm OSThis interpreter glues Palm OS applications and databases together via scripts that run without leaving the current application. These scripts can access all Palm data and resources, including TCP/IP, infrared, USB and serial communications, console, clipboard, and others. It runs natively, and it includes good example scripts with easy-to-follow tutorials.

In addition to these standard, procedural Rexx interpreters, there are also two object-oriented Rexx interpreters. These are fully object-oriented languages with complete and powerful class libraries. They support all OOP principles, including class hierarchies, inheritance, multiple inheritance, encapsulation, abstraction, and polymorphism. Both products are true supersets of standard, procedural Rexx, so you can start programming with them immediately using standard Rexx features and functions, then tip-toe into the object-oriented features at a rate with which you feel comfortable. In addition, your standard Rexx scripts will run under them with no alteration:

Object-oriented Rexx interpreters
InterpreterPlatform(s)Quick profile
Open Object RexxWindows, Linux, UNIXThis is a fully object-oriented superset of classic Rexx. Developed by IBM, it became open source in late 2004. Now enhanced and maintained by the Rexx Language Association, it is the most widely used object-oriented Rexx interpreter.
roo!Windowsroo! also is a fully object-oriented superset of classic Rexx. From the same company as the standard r4 Rexx interpreter, it comes with the same extensive set of Windows tools. It also includes quick, easy tutorials that bridge the gap between standard and object-oriented Rexx programming.

Lastly, I'll mention that there is even a Rexx for the Java environment, NetRexx. Of all the Rexx interpreters I've discussed in this article, this is the only one that does not meet the Rexx language standards—so it is perhaps best described as "Rexx-like." Its purpose is to bring Rexx’s ease of use to the Java environment. NetRexx scripts use Java classes, and they can create classes used by Java scripts. NetRexx lets you write applets, applications, and servlets without tangling with Java’s C-heritage syntax. You can even use it to create Java Beans and generate fully-commented Java code. NetRexx supports both client- and server-side scripting and runs on any Java Virtual Machine (JVM).


What's next

In my next article, I’ll get more technical about Rexx programming and provide some practical coding examples. These example scripts will demonstrate how you can use Rexx as a vehicle for quickly developing programs that access DB2 UDB data. Beyond teaching more about Rexx scripting, they are practical database examples in their own right. I’ll employ the Regina Rexx interpreter, accessing DB2 UDB on a Windows server for the example scripts. But both Rexx and the database interface I’ll use are portable across Linux, UNIX, and other operating systems.

The great thing about working with an open source language such as Rexx is there are so many free and readily available resources to learn more about it (see Resources).

The open source / free software movement is one of the great technology stories of our era. The other big trend today is scripting. Rexx stands at the intersection of these two trends. It offers a free, easy-to-use, powerful, standardized, and universal scripting language. With it, you can quickly program solutions to a wide variety of problems at very low cost. Happy scripting!

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=92131
ArticleTitle=Script for DB2 Universal Database using REXX: Learn REXX fast
publish-date=08182005