DSNTIAD - The Dynamic SQL Update Program

In my last blog entry I posted about DSNTEP2, the batch SQL program. But there is another batch SQL program named DSNTIAD that is less widely-known and used.

DSNTIAD is an assembler application program that can issue the same DB2 dynamic SQL statements as DSNTEP2, with the exception of the SELECT statement. For this reason, applications programmers almost always prefer to use DSNTEP2 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.

Here is sample JCL for running DSNTIAD:

//DB2JOBU JOB (UTILITY),'DB2 SAMPLE UPD',MSGCLASS=X,
// CLASS=X,NOTIFY=USER
//*
//**************************************************
//*
//* DSNTIAD - SAMPLE DB2 UPDATE PROGRAM
//*
//**************************************************
//*
//JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR
//BATUPSQL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIAD6) -
LIB('DSN810.RUNLIB.LOAD')
END
/*
//SYSIN DD *
UPDATE DSN8810.DEPT
SET DEPTNAME = 'CHANGED NAME'
WHERE DEPTNO = 'D01' ;

INSERT INTO DSN8510.ACT
VALUES (129, 'XXXXXX', 'SAMPLE ACCT') ;

DELETE FROM DSN8510.EMP
WHERE SALARY < 1000 ;

CREATE DATABASE TESTNAME
BUFFERPOOL BP12
STOGROUP DSN8G510 ;

GRANT DBADM ON TESTNAME TO USERA ;
/*
//

So why would anyone consider using DSNTIAD over DSNTEP2? Well, DSNTIAD supports the LABEL ON statement, whereas DSNTEP2 does not. But unlike DSNTEP2, 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.

DSNTEP2 aka Batch SPUFI

DSNTEP2 is an application program that can be used to issue DB2 dynamic SQL statements. It is sometimes referred to as “Batch SPUFI” because it allows you to submit SQL in batch similar to how SPUFI allows online SQL execution.

The following sample JCL demonstrates the capability of DSNTEP2 to issue DCL, DDL, and DML dynamically.

//DB2JOBU JOB (BATCHSQL),'DB2 SAMPLE SQL',MSGCLASS=X,
// CLASS=X,NOTIFY=USER
//*
//***********************************
//*
//* DB2 SAMPLE SQL PROGRAM
//*
//***********************************
//*
//JOBLIB DD DSN=DSN810.DSNLOAD,DISP=SHR
//BATCHSQL EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTEP2) PLAN(DSNTEP81) -
LIB('DSN810.RUNLIB.LOAD')
END
/*
//SYSIN DD *
SELECT * FROM SYSIBM.SYSTABLES;

UPDATE DSN8810.DEPT
SET DEPTNAME = 'CHANGED NAME'
WHERE DEPTNO = 'D01' ;

INSERT INTO DSN8810.ACT
VALUES (129, 'XXXXXX', 'SAMPLE ACCT');

DELETE FROM DSN8810.EMP
WHERE SALARY <>

/*

The DNSTEP2 program is written in the PL/I programming language. Prior to DB2 V6, you needed to have a PL/I compiler to use DSNTEP2. However, as of V6 IBM now provides both the source code and an object code version of DSNTEP2 with DB2. So, you no longer need a PL/I compiler to use DSNTEP2.

Because DSNTEP2 is an application program, and the PL/I source code is provided with DB2, a knowledgeable PL/I programmer can easily modify the code. After doing so, of course, it must be compiled, linked, and bound before it can be used.

DSNTEP2 can process almost every SQL statement that can be executed dynamically. DSNTEP2 accepts
· The GRANT and REVOKE DCL statements,
· The ALTER, COMMENT ON, CREATE, and DROP DDL statements,
· The DELETE, INSERT, SELECT, and UPDATE DML statements, and
· The COMMIT, ROLLBACK, EXEC SQL, EXPLAIN, and LOCK statements.

The only important statement that DSNTEP2 does not support is the LABEL ON DDL statement. Of course, DSNTEP2 can be modified to support this statement if you have PL/I knowledge and a PL/I compiler.

When Does DSNTEP2 Commit?

Well, the simple answer to that question is that the results of the SQL are committed upon completion of all the SQL. A helpful answer is a little longer.

First off, you need to know that DSNTEP2 has an internal parameter named MAXERRORS that controls the number of failing statements that can occur before it stops. A failing statement is one which returns a negative SQLCODE. The value of MAXERRORS is set to 10 inside the program, so DSNTEP2 will allow 9 failing SQL statements but when it hits the 10th failing statement, it will exit, COMMITting all other work.

This is ugly because it can wreak havoc on the integrity of your data. I mean, who wants to figure out what was run, what was impacted, and then try to rebuild a job to fix data and/or restart at the right place? To rerun DSNTEP2, remember that all SQL statements that completed with a 0 SQL code were committed. These statements should not be rerun. All SQL statements completed with a negative SQL code must be corrected and reprocessed.

Certain severe errors cause DSNTEP2 to exit immediately. One severe error is a -101 “SQL statement too long or too complex".

If any SQL errors occurred during the execution of DSNTEP2, a return code of 8 is returned by the job step.

At any rate, DSNTEP2 never issues an explicit COMMIT or ROLLBACK by itself. A COMMIT occurs at the end unless the program abends.

Specify Your SQL Statements Properly

The SQL to be run by DSNTEP2 is specified in SYSIN. Be sure to code the DSNTEP2 input properly.

DSNTEP2 reads SQL statements from an input data set with 80-byte records. The SQL statements must be coded in the first 72 bytes of each input record. SQL statements can span multiple input records and are terminated by a semicolon (;). Semicolons are not permitted in the text of the SQL statement.

Liberally Comment DSNTEP2 Input

Comments can be passed to DSNTEP2 in the SQL statements using two hyphens in columns 1 and 2 or a single asterisk in column 1. Doing so is good form and helps others to understand what your DSNTEP2 job is attempting to accomplish.

Bottom Line

DSNTEP2 is especially useful for running one-off SQL statements. Use DSNTEP2 when you have ad hoc SQL to run in a batch environment. DSNTEP2 is easier than writing your own quick and dirty programs to run ad hoc SQL in batch. It is simple to set up and saves time. But be careful if you have multiple SQL modification statements (INSERT, UPDATE, DELETE) because of the above-mentioned possibility of causing data integrity problems if some of the statements fail and others succeed.

IBM Announces z/OS V1.8

On February 28, 2006 IBM announced the latest planned upgrade of its stalwart mainframe operating system, z/OS. IBM has announced some interesting new functionality for the new version, V1.8. Planned are improvements in scale, availability, security, and resource optimization. Also, there is an increased focus on simplification to make z/OS easier to use.

The actual IBM announcement can be read here.

Further details can be found in this handy summary of z/OS futures planning put together by Jim Schesvold, a technical editor for IBM Systems Magazine.

IBM plans for z/OS V1.8 to be available in September 2006.

DB2 Versions, Service, and Such

Keeping up-to-date with the latest and greatest DB2 versions and functionality can be a time-consuming task. Every 18 to 36 months IBM announces a new version of DB2 with even more features and functionality than ever before.

DB2 will soon celebrate its 25th anniversary. The basis for DB2 began with IBM’s System R research project. In 1982, IBM delivered SQL/DS on VM and VSE, and then a year later in 1983, IBM released DB2 for MVS Version 1. Through subsequent versions and releases IBM has improved the functionality and performance of DB2.

Functionality aside, though, it can be difficult to keep track of new DB2 versions. Questions like “What version has which feature?”, “How much longer will IBM support the DB2 version we are running?”, and “When should we begin to migrate to a new versions… and which one?” are very common in this day and age of rapid technology change. Let’s examine some of these questions.

First of all, we need to understand some basic terminology: “withdrawal from marketing” and “withdrawal from service.” When IBM withdraws a product from marketing the product will no longer be advertised or sold by IBM; but IBM will continue to service and support customers. When IBM withdraws a product from service you will no longer be able to get technical support for that product… unless you negotiate extended service (at an extended price) with IBM.

So, the current version of DB2 for z/OS is Version 8 and it has been available for just over 2 years now. And it seems that the announcement of the next version of DB2 is imminent, what with some of the Vnext presentations IBMers are delivering.

But the big question these days, for most shops, is not the next version, but the current version. Most DB2 users are still running Version 7, but they will need to migrate to Version 8 soon. If you are running an earlier version of DB2 (than V7) you really should waste no time in getting to V7 – not only is it a solid release, but you’ll need to start worrying about V8 soon because the projected end of service date for DB2 Version 7 is September 2007.

You can keep an eye on the current versions of DB2 that are available by bookmarking this link http://www.ibm.com/software/data/db2/zos/support/plc/. IBM keeps this information up-to-date regarding the end of marketing and end of service dates for DB2 versions.

The bottom line, of course, is that more functionality is available to you by keeping up-to-date with the latest DB2 version. But issues such as rapid versioning, complexity, difficulty of migration, and managing new versions can make keeping up-to-date difficult. And diligence is required to keep everything straight. At least we have a lot of information available to us:

• The IBM DB2 UDB for z/OS Home Page
• DB2 UDB for z/OS Information Roadmap
• More information on DB2 V7 end of service
• IBM Pitch on V8 Migration and DB2 Vnext (pdf)
• z/OS, z/OS.e, and OS/390 marketing and service announce, availability, and withdrawal dates

Good luck with DB2…

Returning Only Numeric Data

I frequently get e-mail from folks asking about ways to accomplish things in DB2 and SQL. A recent question I got went something like this:

Is there any option to check whether data “IS NUMERIC” in a DB2 table? We want to examine CHAR data but return only those where the entire data consists only of numbers. For example, can we write a query like this?

SELECT *
FROM TABLENAME
WHERE VAR IS NUMERIC.

The VAR variable is defined as a CHAR(5) column and it will contain data like below.
123aa
2234a
34256
32102
add91

Out of the above 5 records we would want only the 3rd and 4th records to be returned. We tried CAST (VAR as integer), but any other option is there for fetching like above. Please explain

Well, if you try to cast non-numeric data to numeric you will get an error. But you can test the data beforehand - digit by digit - using the SUBSTR function. You’d have to break the VAR column down using SUBSTR to get each individual character and test whether that character is between 0 and 9 – then only if all characters are between 0 and 9 would the result be returned.

Here is what the SQL might look like:

SELECT *
FROM TABLENAME
WHERE SUBSTRING(VAR,1,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,2,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,3,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,4,1) BETWEEN '0' AND '9'
AND SUBSTRING(VAR,5,1) BETWEEN '0' AND '9';

This will return only those rows where every digit in the VAR column is a number between zero and nine.