Using DSNTIAD or an alternative

[kathvp]

Hi,

I have been experimenting on ways to execute SQL statements for table updates on our DB2 tables in IBM Mainframe. I've started using DSNTIAD, a DB2 utility. With the manner I was using this, the SQL statement is committed one at a time. To illustrate, if I have 10 SQL UPDATEs and the fifth SQL statement has a syntax error, all statements will be executed and committed successfully except for the 5th statement.

Can anyone help me determine how I should set my job or control card so that the SQL changes will only be committed when no errors were encountered? Is there a similar DB2 utility tool available in case DSNTIAD will not be able to handle this?

Sample code:
//DDLEXEC EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=DSNDBL6.SDSNLOAD,DISP=SHR
// DD DSN=DSNDBL6.SDSNEXIT,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSTSIN DD *
DSN S(DBL6) R(0) T(0)
RUN PROGRAM(DSNTIAD) PLAN(DSNTIAD)
//SYSIN DD *
UPDATE DSN8610.PROJ SET DEPTNO='J01' WHERE DEPTNO='A01';
UPDATE DSN8610.PROJ SET DEPTNO='J02' WHERE DEPTNO='A02';
UPDATE DSN8610.PROJ SET DEPTNO='J02' WHERE DEPTNO='A03';
/*

 

[MarcLodge]

Kathvp,
I've not done this for a while, but....

1) you could set numerous DSNTIAD steps up within the JCL, each one performing one update. The return code or condition code could be checked within the jcl to determine whether the next step is executed.

2) I'm sure when I've run a SPUFI online which has multiple updates, when an error is encountered all updates are backed out. Try having a look at the deault options under SPUFI and the possibility of having it create the batch SPUFI for you.

Hope this helps

Marc

 

[gregsimpson]

Marc,

I agree with you about the SPUFI. All updates are rolled back when there is an error.

It is of course possible to put COMMIT statements, after your SQL statements, in your SPUFI member to reverse this.

Greg

 

[MarieMetcalf]

I am not really familiar with dsntaid as I prefer to use DSNTEP2 another db2utility. I believe that dsntaid does automatic commits and will actually keep processing until a pre-defined number of sql error have been encountered. I seem to remember having had this problem myself, which is why I switched to DSNTEP2. If DSNTEP2 encounters an error the job will abend and your changes will roll back.
Marie

 

[kathvp]

Thanks for your responses.

I just tried DSNTEP2 but it functioned similarly to DSNTIAD. The changes did not roll back. Could these be related to how the DB2 programs were initially bound?

SPUFI does roll back the changes when an error is encountered but I was hoping I can do it in batch and submit it as a job. I did some research on running SPUFI under batch, ironically it says that the batch version is the DSNTEP2 utility.

Any ray of light on this? Thanks.