Friday, March 31, 2006

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.

4 comments:

Anonymous said...

This was helpful as our company uses DSNTIAD and I was struggling with finding the issue. Turns out it was my comments.

spurthi b said...

Hi there , wonderful information
http://www.google.com

korrapati said...

Hi Criag,

As part making some automation by using REXX to convert all Tables spaces which are reaching 50gb,im using one sql SELECT statement to generate all TS's who reached 50 gb by using DSNTIAD and getting error below
SELECT V1.DBNAME, V1.NAME, V1.TOTAL_SPACE
FROM (SELECT A.DBNAME, A.NAME, SUM(A.SPACE) AS TOTAL_SPACE
FROM SYSIBM.SYSTABLESPACESTATS A, SYSIBM.SYSTABLESPACE B
WHERE A.DBNAME = B.DBNAME
AND A.NAME = B.NAME
AND B.TYPE = ' '
AND B.NTABLES = 1
GROUP BY A.DBNAME, A.NAME
ORDER BY 3 DESC) V1
WHERE V1.TOTAL_SPACE > 20428800

SQL ERROR DURING EXECUTE IMMEDIATE
DSNT408I SQLCODE = -518, ERROR: THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID
DSNT418I SQLSTATE = 07003 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEDS1 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -515 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFDFD' X'00000000' X'00000000' X'FFFFFFFF' X'0000
INFORMATION



//SYSTSIN DD *
DSN SYSTEM(CB21)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIA10) -
LIB('CB21.RUNLIB.LOAD')
END

Could you please help me here how to run this select query...Here im trying to put select output into dataset using SYSPRINT..

Craig S. Mullins said...

Korrapati...

Re-read the blog post. DSNTIAD "can issue the same DB2 dynamic SQL statements as DSNTEP2, with the exception of the SELECT statement." You are trying to use it to run a SELECT. What you want to use is DSNTEP2 (see http://db2portal.blogspot.com/2006/03/dsntep2-aka-batch-spufi.html)

Regards,
Craig