DSNTEP2 stops after MAXERRORS limit reached

DSNTEP2 stops after MAXERRORS limit reached

Problem description:
This happened when I was using multiple insert script through program DSNTEP2. Idea was to insert the record if that is not present in table.


The execution gave me MAX CC 0008. I knew that some inserts are going to fail due to duplicate record.

Observation: The number of insert statement executed was 10 out of 100. All were failed due to duplicate records.

Question: Why the utility did not process rest of the statement.

Here is why:

There is a parameter which controls the maximum error the DSNTEP2 will encounter before exit. This Parameter is MAXERRORS.

The default value for this parameter is 10.

Solution:

We can override the default value for MAXERRORS as shown below or to a specific definite number.

--#SET MAXERRORS -1

You can embed this command at the begin of the control card statement like

//SYSIN DD *
-#SET MAXERRORS -1

INSERT INTO tablex VALUES('a',1,'x');
COMMIT;

UNAVAILABLE RESOURCE REASON 00E70081

Problem:

You want to drop or alter a Table. But you are getting "DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E70081, TYPE OF RESOURCE 00000A00, AND RESOURCE NAME

Let's see what is reason code: 00E70081

Explanation: A DROP or ALTER statement was issued but the object cannot be dropped or altered. The object is referenced by a prepared dynamic SQL statement that is currently stored in the prepared statement cache and is in use by an application.

System action: The requested operation is not performed.

Programmer response: The requested operation cannot be performed until there are no applications that reference the object using dynamic SQL. Ensure that other applications have quiesced or performed a commit operation. Then try the request again.

Problem determination: An SQLCODE -904 is issued. The message tokens for that message report the name and type of the object that was being referenced by another application.

Solution:

On quering the resource type:

TYPE Code            Type of Resource       Name, Content,Format

00000A00             Table                  RD.CR.TB

where
RD is a DB2 Release dependency mark


CR the object creator and

TB the Table name
 
 
Runstat utility will be handy to resolve this problem. Runstat with option "update none report none" will clear the dynamic statement cache so that the resource is released to be used by the other application.

Here is the control statement you need to use:

//DSNUPROC.SYSIN DD *

RUNSTATS TABLESPACE . TABLE ALL INDEX ALL
UPDATE NONE REPORT NO 


.....

followup...