• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

EXECSQLTOSS timeout

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • EXECSQLTOSS timeout

    Using the download to Excel button (vvOut_execSQLtoSS) in an app created using Nitro Auto Code. The data source is complex. I have the result set on my screen, but when I attempt to download
    I get the following error - I'm guessing it takes longer than 60 seconds to retrieve the dataset? How can i fix this?
    Program: VVRMTDB Statement:
    Module: VVRMTDB Message ID: VV00068
    Procedure:
    PROCESSDIRECTIVE
    Message Text:
    Timeout encountered during processing of remote database command
    Help Text:
    No response was found in data queue VV156544O within timeout value of 60 seconds. If job VRMT156544, which is responsible for processing remote DB calls and populating the data queue, is no longer running then you must restart the VALENCE52P server instance before any more remote DB commands can be processed. If the job is running then there may be an excessive delay in processing the remote DB request on the remote server (mode=EXSQL).
    Valence Login ID:
    GWILBURN
    IBM i User:
    GWILBURN
    Timestamp:
    2020-04-02-13.19.43.835000
    Job Number:
    156544
    Calling Program:
    VVRMTDB
    Call Stack:
    VVCALL[VVCALL]-->AC1008[AC1008]-->AC1008[GETRECS]-->VVSRVPGM{VVOUT}[VVOUT_EXECSQLTOSS]-->VVRMTDB[VVRMTDB]-->VVRMTDB[PROCESSDIRECTIVE]
    Last edited by gwilburn; 04-02-2020, 12:35 PM.

  • #2
    So your remote SQL takes over a minute to process? Can you confirm that your VRMT156544 job is not in MSGW?

    Unfortunately there is no override to the 60 second timeout, though we could add that as a setting in the next release. That said, a minute is a pretty long time for a user to wait for a response. Have you tried creating indexes on the destination server to make it process the statement faster?

    Comment


    • #3
      Robert... yes. We have MSGW on VRMT<jobnbr> each time this happens:

      Message ID . . . . . . : RNQ0202 Severity . . . . . . . : 99
      Message type . . . . . : Inquiry
      Date sent . . . . . . : 04/16/20 Time sent . . . . . . : 09:19:39

      Message . . . . : The call to BATCHPROCE ended in error (C G D F).
      Cause . . . . . : RPG procedure VVRMTDB in program VALENCE52P/VVRMTDB at
      statement 025100 called program or procedure BATCHPROCE, which ended in
      error. If the name is *N, the call was a bound call by procedure pointer.
      Recovery . . . : Check the job log for more information on the cause of the
      error and contact the person responsible for program maintenance.
      Possible choices for replying to message . . . . . . . . . . . . . . . :
      D -- Obtain RPG formatted dump.
      S -- Obtain system dump.
      G -- Continue processing at *GETIN.
      C -- Cancel.

      If i look at the joblog, I can see:

      Job . . : VRMT224343 User . . : QTMHHTTP Number . . . : 224914

      >> CALL PGM(VVRMTDB) PARM('BATCH' 224343 ' ' 'VALENCE52P')
      Data area VVCONTEXT created in library QTEMP.
      Data area VVOUTBUFF created in library QTEMP.
      Receiver value too small to hold result.
      Function check. MCH1210 unmonitored by VVRMTDB at statement 0000051800,
      instruction X'0000'.
      The call to BATCHPROCE ended in error (C G D F).

      Comment


      • #4
        Hmm, that's peculiar. You can go ahead and kill those MSGW jobs. I would also suggest you restart your VALENCE52P server instance (easiest way from a command line is to do ADDLIBLE VALENCE52P followed by VVSTART).

        We just released a new 5.2+ build last night, could you please download that, update your VALENCE52P instance and then try it again? If it's still blowing up let us know and we'll get on a web meeting with you to have a closer look.

        Comment


        • #5
          We upgraded, but are still having issues with this particular app using the Excel download. There are no messages on the Valence jobs

          I tried restarting the Valence server instance as indicated above, but the result is the same.
          Error ID 64 REMOVE
          Program: VVRMTDB Statement:
          Module: VVRMTDB Message ID: VV00068
          Procedure:
          PROCESSDIRECTIVE
          Message Text:
          Timeout encountered during processing of remote database command
          Help Text:
          No response was found in data queue VV846987O within timeout value of 60 seconds. If job VRMT846987, which is responsible for processing remote DB calls and populating the data queue, is no longer running then you must restart the VALENCE52P server instance before any more remote DB commands can be processed. If the job is running then there may be an excessive delay in processing the remote DB request on the remote server (mode=EXSQL).
          Valence Login ID:
          PRUDDELL
          IBM i User:
          PRUDDELL
          Timestamp:
          2020-09-01-11.48.21.844000
          Job Number:
          846987
          Calling Program:
          VVRMTDB
          Call Stack:
          VVCALL[VVCALL]--&gt;AC1003[AC1003]--&gt;AC1003[GETRECS]--&gt;VVSRVPGM{VVOUT}[VVOUT_EXECSQLTOSS]--&gt;VVRMTDB[VVRMTDB]--&gt;VVRMTDB[PROCESSDIRECTIVE]

          This could be a result of a previous error:
          Error ID 1 REMOVE
          Program: VVSRVPGM Statement:
          Module: VVIN Message ID: VV00019
          Procedure:
          VVIN_DATE
          Message Text:
          Variable "shipby" could not be converted into an ISO date (value: )
          Help Text:
          Correct the format of the date being passed to vvIn_Date().
          Valence Login ID:
          GWILBURN
          IBM i User:
          GWILBURN
          Timestamp:
          2020-09-01-12.01.44.616000
          Job Number:
          850230
          Calling Program:
          VVSRVPGM
          Call Stack:
          VVCALL[VVCALL]-->AC1003[AC1003]-->AC1003[GETRECS]-->VVSRVPGM{VVIN}[VVIN_DATE]

          When I put this in debug, VVIN_DATE is not returning the date selected on the screen... this could cause a lot of records to be selected?


          UPDATE: If I "plug" the current date into variable "shipby" (hard code in my RPG pgm), the file downloads just fine.

          So the issue is related to date not being returned properly from:

          shipby = vvIn_date('shipby');
          Last edited by gwilburn; 09-01-2020, 01:28 PM.

          Comment


          • #6
            So if you open your browser's Dev Tools, what is your front-end passing to the back-end for "shipby"?

            Comment


            • #7
              So when I do the filtering:
              Code:
              http://tbfpwr7:8052/valence/vvcall.pgm?_dc=1598993232760&pgm=AC1003&action=getRecs&company=057&search=&shipby=2020-09-01T00%3A00%3A00&page=1&start=0&limit=25&sort=%5B%7B%22property%22%3A%22SHP_DATE%22%2C%22direction%22%3A%22ASC%22%7D%5D&sid=U7RSS3JGJDY7SERNB5XWL48QHCDNNYWXXICFUD1KNL2A4E0A5SNYEXF9O0OEAF6R&lang=en&app=1003&vvKey=15989931888129189&env=1001
              When I then click on the download button:
              Code:
              http://tbfpwr7:8052/valence/vvcall.pgm?sid=U7RSS3JGJDY7SERNB5XWL48QHCDNNYWXXICFUD1KNL2A4E0A5SNYEXF9O0OEAF6R&app=1003&download=true&pgm=AC1003&action=getRecs&company=057&search=&shipby=Tue%20Sep%2001%202020%2000%3A00%3A00%20GMT-0400%20(Eastern%20Daylight%20Time)&page=1&start=0&limit=25&sort=%5B%7B%22direction%22%3A%22ASC%22%2C%22property%22%3A%22SHP_DATE%22%7D%5D

              Comment


              • #8
                It's this bit where something is going wrong:

                &shipby=Tue%20Sep%2001%202020%2000%3A00%3A00%20 GMT-0400%20(Eastern%20Daylight%20Time)

                That's a bad date format being sent from the front end when the download is triggered. Can you post the relevant front end code that constructs this?

                Comment


                • #9
                  I'm not sure how... the front end was create using Sencha Architect. The "shipby" is a date filter in a dropdown.

                  I was poking around in the source using the source editor... where can i find the "relevant code" using the source editor?

                  Comment


                  • #10
                    This is resolved. Solution was just to wrap the date retrieval in a format utility so it would pass date as ISO (Y-m-d):

                    shipby = Ext.util.Format.date(field, 'Y-m-d');

                    Comment

                    Working...
                    X