Is journalling turned off at all during the night? (This could lead to
Commitment Control problems in the Stored Procedures.) Is there a
nightly backup? (This might be causing timeouts due to file / table
lock issues.) Can you post the full text and ID of the exact error
message?

On Jun 8, 9:45 am, "walker.l2" wrote:
> Is journalling turned off at all during the night? (This could lead to
> Commitment Control problems in the Stored Procedures.) Is there a
> nightly backup? (This might be causing timeouts due to file / table
> lock issues.) Can you post the full text and ID of the exact error
> message?

The error is being caught in the SQL Server Job which is calling a DTS
package to call the Stored procedure on the AS400 as one of its
processing steps, the error is very generic:

Executed as user: xxx\yyyy. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun
OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259
(80004005) Error string: [IBM][iSeries Access ODBC
Driver]*N Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 443
(1BB) Error string: [IBM][iSeries Access ODBC Driver]*N
Error source: Microsoft OLE DB Provider for ODBC Drivers Help
file: Help context: 0 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete.
Process Exit Code 1. The step failed.

Basically I can run this back to back from SQL Server during the day
without a single failure. I have been told by the AS400 manager that
there is no journaling or backup running at this time and IBM have
looked into this and their solution was to drop and recreate the
procedure which achieved nothing as it works most of the time! It is
also not failing consistently at night, some nights it works and
others it does'nt so far I have not been able to determine a pattern.

Is it possible to run a SQL trace on the AS400 directly at this time
and possibly trap a more meaningful error?

Thank you.

Hmm. I was hoping for an error description from the AS/400 side. The
Joblog from the AS/400 would be useful to see (the job would probably
be QSQSRVR or QZDASOINIT).

Provider error 443 seems just to indicate "Valid error SQLSTATEs
returned by an external routine or trigger." or "Error occurred while
calling a user-defined function, external procedure, or trigger" which
you already know.

jer006@gmail.com wrote:
> On Jun 8, 9:45 am, "walker.l2" wrote:
>> Is journalling turned off at all during the night? (This could lead to
>> Commitment Control problems in the Stored Procedures.) Is there a
>> nightly backup? (This might be causing timeouts due to file / table
>> lock issues.) Can you post the full text and ID of the exact error
>> message?
>
> The error is being caught in the SQL Server Job which is calling a DTS
> package to call the Stored procedure on the AS400 as one of its
> processing steps, the error is very generic:
>
> Executed as user: xxx\yyyy. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun
> OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259
> (80004005) Error string: [IBM][iSeries Access ODBC
> Driver]*N Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 443
> (1BB) Error string: [IBM][iSeries Access ODBC Driver]*N
> Error source: Microsoft OLE DB Provider for ODBC Drivers Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.
>
> Basically I can run this back to back from SQL Server during the day
> without a single failure. I have been told by the AS400 manager that
> there is no journaling or backup running at this time and IBM have
> looked into this and their solution was to drop and recreate the
> procedure which achieved nothing as it works most of the time! It is
> also not failing consistently at night, some nights it works and
> others it does'nt so far I have not been able to determine a pattern.
>
> Is it possible to run a SQL trace on the AS400 directly at this time
> and possibly trap a more meaningful error?

You said in your prior post that you think you've
isolated the SELECT statement for which the error is
occurring. You could add a general error handler that
would capture the value of SQLSTATE for any error, then
check for error values and call a program to get the
job log and other features of the database service job.

Depending on whether you want the procedure to try to
continue, or to exit and return to the caller, you
could code a CONTINUE or EXIT handler:

DECLARE sqlstate char(5);
DECLARE v_sqlstate char(5);
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET v_sqlstate = SQLSTATE;

Following the SELECT that you think is encountering the
error, check v_sqlstate for a value other than '00000'.
If you get an error, you could then call a program to
create a job log and/or send messages to the system
operator or some other message queue. For example:

[some SELECT statement...]
if v_sqlstate <> '00000' then
call mylib.mypgm;
end if;

You should look at the IBM redbook _Stored Procedures,
Triggers and User Defined Functions on DB2 Universal
Database for iSeries_
(http://ftp.inf.utfsm.cl/pub/Docs/IBM...s/sg246503.pdf)
in chapter 8 for more robust error handling solutions.

Here's a link to a newer version of the stored
procedures redbook that I referenced earlier:
http://www.redbooks.ibm.com/Redbooks...6503.html?Open

Look in Chapter 8 for error handling.


jer006@gmail.com wrote:
> On Jun 8, 9:45 am, "walker.l2" wrote:
>> Is journalling turned off at all during the night? (This could lead to
>> Commitment Control problems in the Stored Procedures.) Is there a
>> nightly backup? (This might be causing timeouts due to file / table
>> lock issues.) Can you post the full text and ID of the exact error
>> message?
>
> The error is being caught in the SQL Server Job which is calling a DTS
> package to call the Stored procedure on the AS400 as one of its
> processing steps, the error is very generic:
>
> Executed as user: xxx\yyyy. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun
> OnError: DTSStep_DTSExecuteSQLTask_2, Error = -2147467259
> (80004005) Error string: [IBM][iSeries Access ODBC
> Driver]*N Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 443
> (1BB) Error string: [IBM][iSeries Access ODBC Driver]*N
> Error source: Microsoft OLE DB Provider for ODBC Drivers Help
> file: Help context: 0 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_2 DTSRun: Package execution complete.
> Process Exit Code 1. The step failed.
>
> Basically I can run this back to back from SQL Server during the day
> without a single failure. I have been told by the AS400 manager that
> there is no journaling or backup running at this time and IBM have
> looked into this and their solution was to drop and recreate the
> procedure which achieved nothing as it works most of the time! It is
> also not failing consistently at night, some nights it works and
> others it does'nt so far I have not been able to determine a pattern.
>
> Is it possible to run a SQL trace on the AS400 directly at this time
> and possibly trap a more meaningful error?
>
> Thank you.
>