Intermittant error generating Excel from SQL statement

[petersioufi]

Hi Support,

We have a process that generates excel using statement:
vvOut_execSQLtoSS

Sometimes this works but often it fails. When it fails we repeat exactly the same and it may work or fail again. Eventually, it works. Each time, it's in a separate submitted job to ensure no legacy issues within the job or memory leaks.

The job log shows that a *DTAQ is created (my example is VV444809I) but is deleted before it needs to be used. I think there is a timing issue because this is a large spreadsheet. See relevant joblog segment:

CPC2191 Completion 00 17/09/21 13:48:04.739992 QLIDLOBJ QSYS 06B4 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657603
Message . . . . : Object VV444556O in VALENCE6P type *DTAQ deleted.
CPC2206 Completion 00 17/09/21 13:48:04.749416 QSYCHONR QSYS 0603 QLIINSRT QSYS 0553
Message . . . . : Ownership of object VV444809I in VALENCE6P type *DTAQ
changed.
Cause . . . . . : The ownership of object VV444809I in library VALENCE6P
type *DTAQ has changed.
CPC9801 Completion 00 17/09/21 13:48:04.750001 QMHQCRTQ QSYS 0902 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657600
Message . . . . : Object VV444809I type *DTAQ created in library VALENCE6P.
CPC2206 Completion 00 17/09/21 13:48:04.753947 QSYCHONR QSYS 0603 QLIINSRT QSYS 0553
Message . . . . : Ownership of object VV444809O in VALENCE6P type *DTAQ
changed.
Cause . . . . . : The ownership of object VV444809O in library VALENCE6P
type *DTAQ has changed.
CPC9801 Completion 00 17/09/21 13:48:04.754501 QMHQCRTQ QSYS 0902 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657600
Message . . . . : Object VV444809O type *DTAQ created in library VALENCE6P.
CPI2201 Information 00 17/09/21 13:48:04.755023 QSYGRAUT QSYS 1307 QSYGRAUT QSYS 1307
Message . . . . : Authority given to user PS for object VV444809I in
VALENCE6P object type *DTAQ.
CPC2201 Completion 00 17/09/21 13:48:04.755052 QSYGRAUT QSYS 1307 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657600
Message . . . . : Object authority granted.
CPI2201 Information 00 17/09/21 13:48:04.755589 QSYGRAUT QSYS 1307 QSYGRAUT QSYS 1307
Message . . . . : Authority given to user PS for object VV444809O in
VALENCE6P object type *DTAQ.
CPC2201 Completion 00 17/09/21 13:48:04.755611 QSYGRAUT QSYS 1307 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657600
5770SS1 V7R3M0 160422 Job Log SMGSYS 17/09/21 14:09:43 AEST Page 3
Job name . . . . . . . . . . : PSR176ERUN User . . . . . . : PS Number . . . . . . . . . . . : 444809
Job description . . . . . . : PS Library . . . . . : QGPL
MSGID TYPE SEV DATE TIME FROM PGM LIBRARY INST TO PGM LIBRARY INST
Message . . . . : Object authority granted.
CPC1221 Completion 00 17/09/21 13:48:04.756702 QWTCCSBJ QSYS 01C4 VVSRVPGM VALENCE6P *STMT
To module . . . . . . . . . : VVUTILITY
To procedure . . . . . . . : VVUTILITY_QCMD
Statement . . . . . . . . . : 657603
Message . . . . : Job 444810/PS/VBCH444809 submitted to job queue QZHBHTTP
in library QHTTPSVR.
CPF9801 Escape 40 17/09/21 13:52:01.754292 QSNDDTAQ QSYS 03CA VVBATCH VALENCE6P *STMT
To module . . . . . . . . . : VVBATCH
To procedure . . . . . . . : PROCESSDIRECTIVE
Statement . . . . . . . . . : 29600
Message . . . . : Object VV444809I in library VALENCE6P not found.
Cause . . . . . : The object VV444809I in library VALENCE6P type *DTAQ not
found. The object name, library name, or the object type is not correct.
If the library name is not specified, the object may be in a library that is
not contained in the library list. Recovery . . . : Correct the object
name, library name, or object type. If the library name was not specified,
specify the library name and try the request again.
RNQ0202 Sender copy 99 17/09/21 13:52:01.754466 QRNXIE QSYS *STMT QRNXIE QSYS *STMT
From module . . . . . . . . : QRNXMSG
From procedure . . . . . . : InqMsg
Statement . . . . . . . . . : 8
To module . . . . . . . . . : QRNXMSG
To procedure . . . . . . . : InqMsg
Statement . . . . . . . . . : 8
Message . . . . : The call to PROCESSDIR ended in error (C G D F).

[robert.swanson]

Hi Peter,

I suspect the cleanup routine is deleting those data queues because it thinks they're orphaned -- meaning the job using it for communication is no longer running.

Data queues for VVBATCH are assumed to be running in Valence, thus data queues VV444890I and VV444890O would be assumed to be running under a job named VALENCE6P, with a job number of 444890, and a user ID matching the creator of the data queue (typically QTMHHTTP).

If you're running the VVBATCH program outside of Valence, you probably need to give it a job name that matches Valence instance library you're using (VALENCE6P). So try that and see if the problem goes away.

Assuming switching your batch job name to VALENCE6P does indeed resolve the issue, perhaps we can adjust the logic in a future build to pull the job name from the text description associated with the data queue objects, instead of assuming it matches the Valence instance library. The data queues created by this process currently have a blank description, but that can be changed.

[petersioufi]

Hi Rob,

I changed this particular job to submit the excel generation batch job as "VALENCE6P". It ran successfully but finished the main excel piece in 66 secs because it's Saturday. The failed job took nearly 4 minutes to run the excel piece as per the job log. In a lightly loaded system the process works but during a normal workload the timing seems to be fail the process. I can't name every excel job VALENCE6P (we run quite a few reports into excel) and our test environment runs over VALENCE6D in the library list. Maybe cleanup can run later or wait longer than 4 minutes before deciding something is orphaned?

Thanks for the response.

Peter

[petersioufi]

Hi Rob,

The user ran the SQL to Excel job again today at 14:49.40 and the submitted job was called:
453000/LINGIERM/VALENCE6P

At 14:49.42 it deleted:
VV452991I in VALENCE6P
VV452991O in VALENCE6P
and created VV453000I type *DTAQ in VALENCE6P

Then at 14:53:46 (4 mins later) the job crashed because VV453000I *DTAQ was missing.

I ran the process again exactly the same as LINGIERM at 16:05 after the warehouse had closed and it all worked perfectly.

The resultant spreadsheet is just over 30MB and contains 673,200 rows. Megan needs this detail for her forecast analysis.

It looks like changing the submitted job name didn't fix the problem.

The previous excel job lmust have eft behind the other DTAQ's that this job cleaned up.

Sorry.

[robert.swanson]

I wonder if the owner of the data source was not matching the job... Regardless, I will email you an updated version of the VVBATCH program with some extra logging that will document exactly why it thinks the data queues it's deleting are orphaned.