RUNSQL, Spool Services & CL PGM

[LiQuiD_FuSioN]

Hello, all

I've hit a wall with trying to create and run a cl pgm that uses IBM Spool Services:
https://www.ibm.com/support/knowledg...utqentries.htm

Sample pgm:

0001.00 PGM
0002.00 RUNSQL SQL('CREATE TABLE MYTESTLIB/SPLFILES AS +
0003.00 (SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIME STAMP,+
0004.00 USER_NAME,DEVICE_FILE_NAME,FORM_TYPE,USER_DATA +
0005.00 FROM TABLE(QSYS2/OUTPUT_QUEUE_ENTRIES("MYTESTLIB","MYTESTQ","*NO")) +
0006.00 ORDER BY SPOOLNAME +
0007.00 WHERE STATUS = "HELD" +
0008.00 WITH DATA') +
0009.00 COMMIT(*NONE)
0010.00
0011.00
0012.00 ENDPGM

I keep running into errors when I call this pgm:
Message ID . . . . . . : SQL0199

Keyword BY not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT
ORDER UNION EXCEPT OFFSET.

My goal is to receive output of spooled files in a 'held' status. After I get the database output I plan on using this for another cl pgm to help me release those held spooled files. Thanks.

[Scott M]

Get your 'Select' statement running first by itself to help troubleshoot. Build it in stages. I was able to get this to run on our system:

SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIME STAMP, USER_NAME,DEVICE_FILE_NAME,FORM_TYPE,USER_DATA
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QGPL','KOQPGM',' *NO')) as t
WHERE STATUS = 'READY'
ORDER BY SPOOLED_FILE_NAME 

The changes I made, changed " to ' around the queue, library, *NO parms and added as alias to the table (as t). The 'as t' removed the error you were getting. The WHERE clause must be before the Order By. I changed my where because I didn't have any held.

[LiQuiD_FuSioN]

Get your 'Select' statement running first by itself to help troubleshoot. Build it in stages. I was able to get this to run on our system:

SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIME STAMP, USER_NAME,DEVICE_FILE_NAME,FORM_TYPE,USER_DATA
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QGPL','KOQPGM',' *NO')) as t
WHERE STATUS = 'READY'
ORDER BY SPOOLED_FILE_NAME 

The changes I made, changed " to ' around the queue, library, *NO parms and added as alias to the table (as t). The 'as t' removed the error you were getting. The WHERE clause must be before the Order By. I changed my where because I didn't have any held.

Thanks for the input Scott.

I did finally have luck using 'RUNSQLSTM' using this command:

RUNSQLSTM SRCFILE(MYTEST/MYSPLF) SRCMBR(SPLF) COMMIT(*NONE)

The SQL statement contents contain this:

CREATE TABLE MYTEST/SPLFILES AS
(SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ ','*NO')) A
WHERE STATUS = 'HELD')
WITH DATA 

This helped create a database file called: MYTEST/SPLFILES

That was really cool, first time getting spool file information like this. Now I'm on to trying to use this in a CL pgm next.

[LiQuiD_FuSioN]

So next step I wanted to try was creating a CL pgm that declares the database file:

0001.00 PGM
0002.00 DCLF FILE(MYTEST/SPLFILES) ALWVARLEN(*YES) ALWNULL(*YES)
0003.00
0004.00
0005.00
0006.00
0007.00 READ: RCVF
0008.00 MONMSG MSGID(CPF0864 CPF2204) EXEC(GOTO CMDLBL(END))
0009.00 RLSSPLF FILE(&SPOOL00001) JOB(&JOB_NAME) SPLNBR(&FILE_00001)
0010.00
0011.00 GOTO CMDLBL(READ)
0012.00 END: ENDPGM

It compiled successfully, but pgm throws messages:

Value '█QPWCDSKS' for parameter FILE not a valid name.
Value '█129890/C' for parameter JOB not a valid name.
Error found on RLSSPLF command.
CPF0001 received by RLSSPL2 at 900. (C D I R)

Job log strangely does include highlighted blocks of text.

It appears as if it reads part of the spool file name & job name information from database file, but not completely. I don't quite understand why. I'm thinking it's because of how the database file was created.

[LiQuiD_FuSioN]

Value '█QPWCDSKS' for parameter FILE not a valid name.

If I do an F1 to display additional information I see this message info:

Message ID . . . . . . : CPD0078 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic
Date sent . . . . . . : 12/12/18 Time sent . . . . . . : 09:22:22

Message . . . . : Value '█QPWCDSKS' for parameter FILE not a valid name.
Cause . . . . . : Value '█QPWCDSKS' contains characters that are not valid
in a name or is a single value that was specified as part of a qualifier. A
name must begin with an alphabetic character followed by alphanumeric
characters, or if the value is a string of characters, it must be enclosed
in quotation marks.
Recovery . . . : Correct the name or specify the single value alone, then
try the command again. More information on names can be found in the
Information Center.

Still troubleshooting.

[Scott M]

How are the columns created in the database file, I suspect they are varchar and those highlighted blocks are actually the length of the varchar value. Off hand I don't know how to handle varchar fields in CL.

[LiQuiD_FuSioN]

How are the columns created in the database file, I suspect they are varchar and those highlighted blocks are actually the length of the varchar value. Off hand I don't know how to handle varchar fields in CL.

Good question. Not sure how they're created, will have to research that.

Here's some info about the columns:

Column Name - System Column - Name Data - Type Description

SPOOLED_FILE_NAME - SPOOLNAME - VARCHAR(10) - The file name that was specified by the user program when the file was created, or the name of the device file used to create this file.

JOB_NAME - JOB_NAME - VARCHAR(28) - The qualified job name that produced the file.

FILE_NUMBER - FILENUM - INTEGER - The spooled file number of the specified file.

When I compile it's been automatically using these variables - in case they look different than what's on the IBM site:

Declared Variables
Name Defined Type Length References
&FILE_00001 200 *DEC 9 0 700
&JOB_NAME 200 *CHAR 30 700 900
&SPOOL00001 200 *CHAR 12 700

Source: https://www.ibm.com/support/knowledg...utqentries.htm

[LiQuiD_FuSioN]

I did see this old post, but not sure how to implement it in my cl pgm:

http://www.code400.com/forum/forum/iseries-programming-languages/clp-clle/6247-variable-charater-in-cl

[Scott M]

Maybe something like below, untested. This is just for the one field, test to see if you are getting the proper value for that, then you can do the other one.

DCL VAR(&FILENAME) TYPE(*CHAR) LEN(10)
DCL VAR(&SPOOL00001) TYPE(*CHAR) LEN(12)   /* Might not need this one */
DCL VAR(&LENGTH) TYPE(*UINT) LEN(2) STG(*DEFINED) DEFVAR(&SPOOL00001 1)
DCL VAR(&TEXT) TYPE(*CHAR) LEN(64) STG(*DEFINED) DEFVAR(&SPOOL00001 3)

CHGVAR VAR(&FILENAME) VALUE(%SST(&TEXT 1 &LENGTH))

[LiQuiD_FuSioN]

Maybe something like below, untested. This is just for the one field, test to see if you are getting the proper value for that, then you can do the other one.

DCL VAR(&FILENAME) TYPE(*CHAR) LEN(10)
DCL VAR(&SPOOL00001) TYPE(*CHAR) LEN(12) /* Might not need this one */
DCL VAR(&LENGTH) TYPE(*UINT) LEN(2) STG(*DEFINED) DEFVAR(&SPOOL00001 1)
DCL VAR(&TEXT) TYPE(*CHAR) LEN(64) STG(*DEFINED) DEFVAR(&SPOOL00001 3)

CHGVAR VAR(&FILENAME) VALUE(%SST(&TEXT 1 &LENGTH))

Thanks, Scott.

I added that to the CL pgm, however, it failed to compile. Unless you meant for me to try the 'DCL' statements first, in that case, my apologies. Received this in the job log/report:

* CPD086A 30 Declare of &TEXT failed, variable &SPOOL00001 is not large enough.

[Vectorspace]

Instead of selecting the OUTPUT_QUEUE_ENTRIES columns as-is, you could cast them to char(10). E.g. for column SPOOLED_FILE_NAME:

CREATE TABLE MYTEST/SPLFILES AS
(SELECT CAST(SPOOLED_FILE_NAME AS CHAR(10)) AS SPOOLED_FILE_NAME,
JOB_NAME,FILE_NUMBER
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ ','*NO')) A
WHERE STATUS = 'HELD')
WITH DATA

That way the column is a CHAR(10) in MYTEST/SPLFILES instead of VARCHAR(10) and you will be able to read it fine.

Note however that I think your CL program will only compile if MYTEST/SPLFILES already exists (I assume DCLF expects it to already exist so it can validate the field names used later in the program), so your program may not work when first run in a different environment (e.g. you compiled it in test and then copied it to production)

[LiQuiD_FuSioN]

Instead of selecting the OUTPUT_QUEUE_ENTRIES columns as-is, you could cast them to char(10). E.g. for column SPOOLED_FILE_NAME:

CREATE TABLE MYTEST/SPLFILES AS
(SELECT CAST(SPOOLED_FILE_NAME AS CHAR(10)) AS SPOOLED_FILE_NAME,
JOB_NAME,FILE_NUMBER
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ ','*NO')) A
WHERE STATUS = 'HELD')
WITH DATA

That way the column is a CHAR(10) in MYTEST/SPLFILES instead of VARCHAR(10) and you will be able to read it fine.

Note however that I think your CL program will only compile if MYTEST/SPLFILES already exists (I assume DCLF expects it to already exist so it can validate the field names used later in the program), so your program may not work when first run in a different environment (e.g. you compiled it in test and then copied it to production)

Thanks for the response!

Would the same apply for "JOB_NAME" as well? I've tried something like this, but pgm still failed to read job name.

CAST(JOB_NAME AS CHAR(28)) AS JOB_NAME

[Vectorspace]

It should work for all varchar fields.
How are you handling the catch 22 of the table must exist for the cl with its DCL-F to compile, but the Con is expecting to create the file? I would think you would need to delete the file, run the SQL manually to create the file, then compile the CL

[Brian Rusch]

Would the same apply for "JOB_NAME" as well? I've tried something like this, but pgm still failed to read job name.

CAST(JOB_NAME AS CHAR(28)) AS JOB_NAME

The same would apply to job name, however you also have to split the combined job name (JOB_NAME) into its component parts of job name, user, and job number, so the RLSSPLF command would be:

RLSSPLF FILE([I]file_name[/I]) JOB(&NUMBER/&USER/&JOB) SPLNBR([I]spool_file_number[/I])

Also, I would have the SQL use field names that are less than 10 characters so the field names don't end up with the numbers on the end of it:

CREATE TABLE MYTEST/SPLFILES AS
(SELECT CAST(SPOOLED_FILE_NAME AS CHAR(10)) AS FILE_NAME,
 CAST(JOB_NAME AS CHAR(26)) AS JOB_NAME, FILE_NUMBER AS FILE_NUM
 FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ ','*NO')) A
WHERE STATUS = 'HELD') WITH DATA

[LiQuiD_FuSioN]

It should work for all varchar fields.
How are you handling the catch 22 of the table must exist for the cl with its DCL-F to compile, but the Con is expecting to create the file? I would think you would need to delete the file, run the SQL manually to create the file, then compile the CL

Okay, let me try again and let you know.

On another note, going back to original SQL, do you think I could utilize 'CREATE_TIMESTAMP' in SQL statement to return spool file records older than 1 day?

I've tried running a few tests with this:

CREATE TABLE MYTEST/SPLFILES AS                                   
(SELECT SPOOLED_FILE_NAME,JOB_NAME,FILE_NUMBER,CREATE_TIMESTAMP     
FROM TABLE(QSYS2.OUTPUT_QUEUE_ENTRIES('QUSRSYS','MYOUTQ','*NO')) A
WHERE STATUS = 'HELD'                                               
AND CREATE_TIMESTAMP > DATEADD(DD,-1,current_timestamp))            
WITH DATA

But I get the message, 'Column or global variable DD not found.'

Something like this worked though:

AND CREATE_TIMESTAMP>='2016-01-01')