As you work on your Db2 databases and applications, you inevitably will come across certain small, but important tasks that you need to perform. You know, things like moving data from one place to another or modifying a set of values in a table or just querying data. Of course, you can always write your own programs to do any of those things, but wouldn’t it be better if you didn’t have to?
Well, IBM supplies several Db2 productivity-aid sample
programs that you can use to simplify, automate, or optimize common database
tasks. There are four sample programs that are provided free-of-charge with Db2
that you can use as helpful productivity aids. These programs are
shipped as source code, so you can modify them and use them for whatever
purposes you may have.
OK, so what type of sample programs does IBM provide? Let’s see.
DSNTIAUL
The first Db2 productivity aid that most people encounter is
DSNTIAUL, a sample program for unloading data. Today, it is viewed as an
alternative to the UNLOAD utility, but it was around long before IBM ever offered
an UNLOAD utility (which was added in DB2 V7).
Prior to the introduction of the UNLOAD utility, data
generally was unloaded using the sample program DSNTIAUL (or perhaps a BMC or CA unload program). Fortunately, this DSNTIAUL sample
program is still available and can be used for unloading your Db2 data. And, of
course, the IBM Db2 utilities (or any other vendor utilities) must be purchased additionally to Db2, whereas
DSNTIAUL is free of charge.
DSNTIAUL is written in Assembler language. It can be
used to unload some or all rows from up to 100 Db2 tables. With DSNTIAUL, you
can unload data of any Db2 built-in data type or distinct type. DSNTIAUL
unloads the rows in a form that is compatible with the LOAD utility and
generates utility control statements for LOAD. You can also used DSNTIAUL to
execute any SQL non-SELECT statement that can be executed dynamically.
DSNTEP2
DSNTEP2
is a sample dynamic SQL program that can issue any SQL
statement that can be executed dynamically. DSNTEP2 is especially useful
for running ad-hoc SQL statements without requiring the overhead of writing and
compiling a full application/program.
DSNTEP2 can execute valid SQL statements dynamically. This includes SELECT, INSERT, UPDATE, DELETE, COMMIT, ROLLBACK,
and DDL statements (like CREATE and DROP). DSNTEP2 runs in batch mode, typically submitted using JCL.
The drawback is that DSNTEP2 does not allow advanced
features like conditional logic or loops. If you need to perform such tasks, you
will have to write a program with embedded SQL. Additionally, formatting the output
of DSNTEP2 is not as flexible as with custom programs.
DSNTEP2 is written in PL/I and available in two versions: a
source version that you can modify to meet your needs or an object code version
that you can use without the need for a PL/I compiler.
DSNTEP4
Everything that can be said about DSNTEP2 can also be said
about DSNTEP4. It is a sample program that can be used to issue SQL statements
dynamically. In point of fact, DSNTEP4 is identical to DSNTEP2, except that DSNTEP4 uses multi-row fetch. For this reason, I recommend using DSNTEP4 instead of
DSNTEP2 because it has the potential to improve performance.
Check out this
blog post for a comparison of DSNTEP2 and DSNTEP4.
Like DSNTEP2, DSNTEP4 is written in PL/I and available in a
source version that you can modify and an object code version (if you do not
have a PL/I compiler).
DSNTIAD
Finally, we have the DSNTIAD sample program. DSNTIAD is an
assembler application program that can issue the same DB2 dynamic SQL
statements as DSNTEP2/DSNTEP4, with the exception of the SELECT statement. For
this reason, applications programmers usually prefer to use DSNTEP2/4 rather than
DSNTIAD.
DSNTAID is written in Assembler language. Because DSNTIAD is a sample program, its source code could be modified to accept SELECT statements if you so desired. But this task is complex and should not be undertaken by a beginning programmer. And there is really no reason to do so given the availability of DSNTEP2/4.
So why would anyone consider using DSNTIAD over DSNTEP2/4?
Well, DSNTIAD supports the LABEL ON statement, whereas
DSNTEP2/4 does not. But unlike DSNTEP2/4, DSNTIAD does not accept comments
embedded in SQL statements.
Also note that DSNTIAD can be a little more efficient than
DSNTEP2 because it is written in Assembler.
Summary
Because these four programs also accept the static SQL
statements CONNECT, SET CONNECTION, and RELEASE, you can use the programs to
access Db2 tables at remote locations.
As a Db2 developer or DBA it is a good idea to know about
the Db2 productivity-aid sample programs and to understand what each does.
Using them appropriately can save you a lot of time and effort.
