I'm trying to let users run this in a batch job via a CL <BR>
program...In the job log it denies them due to authority. <BR>
What type of special authority do they need in order to run <BR>
this? <p>Shane

. . to run what ? <BR>
Where do you get the error ?

To run the SQL2JXL command...<br>
<br>
I'm pasting the code from the CL below. When a user other than myself
tries to run it, it comes up with the following error:<br>
<br>
CPF9898 Escape 40 09/24/04 11:00:14.100576<br>SQL2JXLR QGPL *STMT SQL2JXLC QGPL *STMT<br>
From module . . . . . . . . : SQL2JXLR<br>
From procedure . . . . . . : SNDPGMMSG<br>
Statement . . . . . . . . . : 435<br>
To module . . . . . . . . . : SQL2JXLC<br>
To procedure . . . . . . . : SQL2JXLC<br>
Statement . . . . . . . . . : 4500<br>
Message . . . . : Error CrtSQLstmt : open() failed. 3401 : Permission denied..<br>
Cause . . . . . : This message is used by application programs as a general escape message.<br>
<br>
Shane

<!--mccodelink_begin-->
<!-- do not remove -->
<hr width=50 align=left><small><a href='http://www.mcpressonline.com/mc/showcode@@.6b176865/2' target='_blank'>Code</a></small>
<!--mccodelink_end-->

Maybe you should give the authorithy to the ifs folder /excel. You can do it with wrklnk or with OpNav.

I already gave authority to *PUBLIC to use this...and I'm still <br>
getting the error message...I'm posting the authority below...<br>
<br>
Shane

<!--mccodelink_begin-->
<!-- do not remove -->
<hr width=50 align=left><small><a href='http://www.mcpressonline.com/mc/showcode@@.6b176865/4' target='_blank'>Code</a></small>
<!--mccodelink_end-->

We IPLd our iSeries and it works now...that was weird...<br>
<br>
Shane

There is a new PTF available to solve the problem of returning correct COLHDG with native driver. <p>These are the instructions from Michael Swenson of IBM: <p>====================== <p>The test PTF, SI16233, is available for this problem. Prior to ordering the PTF there are a couple of things to consider: <p>- TEST PTFs should not be permanently applied in the event <BR>
&nbsp;&nbsp;&nbsp;&nbsp;there is a problem discovered with the PTF. <p>- Prior to ordering this PTF, you should order and install the <BR>
&nbsp;&nbsp;&nbsp;latest available superseded PTF(s): SI14670 <p> Installing the superseded PTF(s) is required to ensure the PTF can be properly removed (if needed) and to prevent the PTF from being <BR>
permanently applied by the system due to requisite PTF processing. <p>- Make sure the to review the cover letters for all PTFs delivered <BR>
&nbsp;&nbsp;&nbsp;(see the DSPPTF or DSPPTFCVR command) <p>===========

Hey all, <p>I'm experiencing an error when I try to run this command... <p>I'm passing in the sql statement from an RPG program...basically, I'm <BR>
letting the user select which file they want to download and which <BR>
months data they want...so I'm building my sql statment in my RPG <BR>
program and passing a CL the statement... <p>When the user runs this command, they get the following error in their <BR>
job log: <p> 700 - SQL2JXL SQLSTMT('select usgdated, usgprt#, usgmach, usgamt,<br>
usgauom, usguser from caspusg where usgdated between ''2004-09-01''<br>
and <BR>
''2004-09-30'' order by usgmach') TOXLS('/excel/caspusg.xls')<br>
SHEETNAME('Paint Usage') COLHDRS(*LABEL) TITLE('Paint Usage')<br>
TITLEALIGN(*CENTER)<br>
&nbsp;&nbsp;4500 - CALL PGM(SQL2JXLR) /* The CALL command contains <BR>
&nbsp;&nbsp;parameters */<br>
&nbsp;&nbsp;9200 - RUNJVA CLASS('SQL2JXL')<br>
&nbsp;&nbsp;PARM(X'6185A783859361E2D8D3C9C4F1F2F8F4F1F94B8481A300' <BR>
&nbsp;&nbsp;'/excel/caspusg.xls' ' ' 'Paint Usage' 'L' 'Paint Usage' '-1' 'C' 'Y' <BR>
&nbsp;&nbsp;'Y' 'N' 'N' 'Y' 'N' 'SYS' 'N' 'NATIVE' 'LOCALHOST' ' ' ' ' 'N') <BR>
&nbsp;&nbsp;CLASSPATH('/QIBM/ProdData/OS400/jt400/lib/jt400.jar:/excel:/excel/jxl.ja <BR>
&nbsp;&nbsp;r') OPTION(*NONE) OUTPUT(*NONE)<br>
Java program completed with exit code 1<br>
Function check. JVA0122 unmonitored by SQL2JXLC at statement 0000009200, instruction X'0000'.<br>
&nbsp;12200 - SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Error running <BR>
&nbsp;&nbsp;SQL2JXL') MSGTYPE(*ESCAPE)<br>
Error running SQL2JXL.<br>
Function check. CPF9898 unmonitored by CASQAQCC at statement 700, <BR>
&nbsp;&nbsp;instruction X'001A'.<br>
CPF9898 received by CASQAQCC at 700. (C D I R)<br>
CPF9898 received by CASQAQCC at 700. (C D I R) <p>Is there some kind of special authority a user needs in order to run <BR>
this command? <p>I'm posting my CL program. <p>Shane <p><!--mccodelink_begin--> <BR>
<!-- do not remove --> <BR>
<hr width=50 align=left><small><a href='http://www.mcpressonline.com/mc/showcode@@.6b176865/7' target='_blank'>Code</a></small> <BR>
<!--mccodelink_end-->

Please enable the Java console JDEBUG(*YES)and run the command again.

How do I do that? <p>Shane

write SQL2JXL into cmdline and press F4 and then F10. Scroll up to the end. The last parameter is JDEBUG. Put the value *YES.

Ok, I enabled it and ran the program in debug...again, it works fine <BR>
when I run the command...however, when a user runs the command, it <BR>
comes up with the following error in their job log: <p> 3500 - SQL2JXL SQLSTMT('select rundated, runmach, runtime, runuser from<br>
&nbsp;&nbsp;casprun') TOXLS('/excel/casprun.xls') SHEETNAME('Run Time')<br>
&nbsp;&nbsp;COLHDRS(*LABEL) TITLE('Run Time') TITLEALIGN(*CENTER) JDEBUG(*YES)<br>
&nbsp;&nbsp;4500 - CALL PGM(SQL2JXLR) /* The CALL command contains <br>
&nbsp;&nbsp;parameters */<br>
Error CrtSQLstmt : open() failed. 3401 : Permission denied..<br>
&nbsp;12200 - SNDPGMMSG MSGID(CPF9898) MSGF(QCPFMSG) MSGDTA('Error running <br>
&nbsp;&nbsp;SQL2JXL') MSGTYPE(*ESCAPE)<br>
Error running SQL2JXL.<br>
CPF9898 received by CASQAQCC at 3500. (C D I R)<br> <p>What must I do in order to let regular users use this utility in a CL <BR>
program? <p>Shane

I changed my CL to use the SQL2XLS utility instead of the SQL2JXL <BR>
utility...and it works...is there a difference between the two? <p>Shane

I have a question. Why do you MONMSG CPF0000 in your CL? <p> rd

They use a different set of Api's. <p>SQL2XLS and SQL2POI use Jakarta/POI api's that are more powerful while SQL2JXL uses Andy Khan's JExcelApi that is faster. <p>SQL2XLS uses RPG mixed with Java. This is excellent for small xls but cannot be used for large xls. For these ones, the pure Java SQL2POI and SQL2JXL are the best solution.

What do you mean ?

The question was supposed to follow Shane's posting of CL code which has a MONMSG MSG(CPF0000) in it. I was asking why, but if everything is working just disregard. <p> rd

Many people put a MONMSG CPF0000 to catch any extraneous errors from crashing a CL. The proper way would be to have a standard error routine to handle the situation, like MONMSG MSGID(CPF0000 CPA0000) EXEC(GOTO CMDLBL(ERROR)). To see examples of this, do a search on this site for STANDARD ERROR HANDLING. On the search results page look over to the right in the block "Results From the Forums". <p>Funny thing is, the first post shown when I did that was one of mine. :) <p>-dan

Refer to Dan Stevens post...

OK, that's cool...I changed the CL to use the SQL2XLS and it works <BR>
fine...So, I guess I'll just use that one...Thanx for all the input guys... <p>Shane

When i enter the command: <p> SQL2JXL SQLSTMT('select * from books') <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TOXLS('/xlsfiles/books.xls') <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COLHDRS(*FLDNAM) <BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;JDEBUG(*YES) <p>I get this in the Java Shell Display: <p> Connecting to LOCALHOST with "com.ibm.db2.jdbc.app.DB2Driver" <p> ERROR: INTERNAL ERROR: Creation of DB2Driver object for registering with DriverManager failed. <BR>
&nbsp;&nbsp;Java program completed with exit code 1 <p>Not sure what I am missing here.