Stored procedure failing on nightly cycle only - IBM AS400

This is a discussion on Stored procedure failing on nightly cycle only - IBM AS400 ; Hi, This is more of a general question... I have a series of stored procedures (chain of procedures) in db2 on an AS400 which are executed from a SQL Server Job, when I execute the job manually during the day ...

+ Reply to Thread
Results 1 to 6 of 6

Thread: Stored procedure failing on nightly cycle only

  1. Stored procedure failing on nightly cycle only

    Hi,
    This is more of a general question...

    I have a series of stored procedures (chain of procedures) in db2 on
    an AS400 which are executed from a SQL Server Job, when I execute the
    job manually during the day (or when I execute the stored procedures
    in db2 on the AS400 directly) everything completes successfully.
    However when the procedures are executed on its scheduled cycle at 3am
    in the morning it fails most every morning with a generic *N error.
    This has been happening for a month now and IBM support have had us
    put on a trace of activity on the AS400 which yielded a 100,000 page
    report for 10 minutes of timeframe - the solution they had me do was
    to drop and recreate the procedure which solved nothing unfortunately!

    I built my own logging into the procedures which captures when they
    start and finish and was able to trace the failure in the sequence
    down to a select statement in one of the procedures however this does
    not explain why it fails sometimes during the nightly cycle and not
    other times with a generic uninformative error!

    I am not an AS400 or DB2 expert but can anyone tell me the sort of
    things that I should be looking for on the AS400 or what sort of
    traces (sql trace, error logs, process activity etc) I can run on the
    AS400 directly to hopefully track down this problem...

    I can run this procedure all day long and it succeeds without any
    issues the failure only happens on the nightly cycle which leads me to
    think there is a conflict or something is clashing with it...

    Any help would be much appreciated as I really want to get to the
    bottom of this!

    Thanks


    Jeremy.


  2. Re: Stored procedure failing on nightly cycle only

    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?


  3. Re: Stored procedure failing on nightly cycle only

    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.


  4. Re: Stored procedure failing on nightly cycle only

    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.


  5. Re: Stored procedure failing on nightly cycle only

    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.

  6. Re: Stored procedure failing on nightly cycle only

    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.
    >


+ Reply to Thread