IBM i Access Client Solutions

 View Only

  • 1.  ACS - Run SQL Scripts CL output display

    Posted Wed January 17, 2024 06:38 AM

    Hello ,

    From ACS Run SQL Scripts, if I run a CL command , is there a way to get the output displayed.

    For eg

    cl:DSPJOBLOG OUTPUT(*); is successfully executed but no output displayed. Are there any additional preferences to be set.

    I am on ACS v1.1.8.8 (a slightly old version )

    Any advice would be appreciated.



    ------------------------------
    Thomas Varkey
    ------------------------------


  • 2.  RE: ACS - Run SQL Scripts CL output display

    IBM Champion
    Posted Thu January 18, 2024 01:57 AM

    Dear Thomas

    CL's OUTPUT(*) does NOT work in Run SQL Script session from its beginning.  Instead, it produces the resulting screen as a spooled file.  I remember Scott Forstie posted SQL technique for displaying the content of a spooled file in IBM i SQL session but I do not remember wherher it will work with Run SQL Script or not.  But this method can cause a proliferation of spooled files that consume disk space if many people will use it a lot.  So, if you have a compelling reason to use OUTPUT(*), use a 5250 session. 



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 3.  RE: ACS - Run SQL Scripts CL output display

    Posted Thu January 18, 2024 04:28 AM

    Dear Satid,

    I was just curious why there was no output displayed, when the command executed without error.  Your explanation however makes sense. Thank you for your reply.



    ------------------------------
    Thomas Varkey
    ------------------------------

    Original Message


  • 4.  RE: ACS - Run SQL Scripts CL output display

    IBM Champion
    Posted Thu January 18, 2024 07:50 AM

    If DSPJOBLOG was just an example I apologize.  However if you really wanted to see the joblog of your current job click this icon

    It's also under View, Joblog.

    Or you could go the Services route and try adding your own selection criteria on:

    select * from table(qsys2.joblog_info('*')) A



    ------------------------------
    Robert Berendt IBMChampion
    ------------------------------

    Original Message


  • 5.  RE: ACS - Run SQL Scripts CL output display

    IBM Champion
    Posted Thu January 18, 2024 08:43 AM

    Definitely a good question!

    If you did need to read from a spooled file, you could do it as shown below.  Satid has made a great point though.  If you took an approach of generating spooled output and then reading it, you could end up generating a lot of spooled files in the system.

    --Description: read the contents of a spooled file
    SELECT *
        FROM TABLE (
                SYSTOOLS.SPOOLED_FILE_DATA(
                    JOB_NAME => '000000/USERPRF/JOBNAME', SPOOLED_FILE_NAME => 'SPLFNAME', SPOOLED_FILE_NUMBER => 1)
            );



    ------------------------------
    Steven Riedmueller
    Certified IBM i Admin
    Speaker, Mentor, and Advocate
    ------------------------------

    Original Message


  • 6.  RE: ACS - Run SQL Scripts CL output display

    IBM Champion
    Posted Thu January 18, 2024 09:11 PM

    Dear Thomas

    Are you aware of IBM i and DB2 for i Services?  This is basically a call from SQL interface to an IBM i server that gives you information you can get similar to running CL commands in 5250 session.  For example, instead of running DSPJOBLOG from Run SQL Script session, you do SELECT from QSYS2.JOBLOG_INFO() table function instead as described in these articles :  A Better SQL Joblog Function for #IBMi at  https://www.linkedin.com/pulse/better-job-log-sql-function-bob-cozzi   and Using SQL to get information from Job Logs at  https://www.rpgpgm.com/2015/06/using-sql-to-get-information-from-job.html  

    More information on the services here.  Keep in mind this is a work-in-progress growing set of services delivered by PTFs over the past few years and future. 

    https://www.ibm.com/support/pages/ibm-i-services

    https://www.ibm.com/support/pages/ibm-i-services-sql



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message


  • 7.  RE: ACS - Run SQL Scripts CL output display

    Posted Fri January 19, 2024 11:28 AM

    Hi Thomas,

    I just tried the same with ACS SQL Script tool, first with cl:DSPJOBLOG OUTPUT(*); and second with cl:DSPJOBLOG OUTPUT(*PRINT); both cases it generated the SPOOL file for the output. I tried other commands and all their output was spool file.

    I am using version 1.1.9.3, don't know if there is a way to display the output within the SQL script, but I am surprised to see that CL Prompting (F4) works in SQL Scripting in ACS.

    Regards



    ------------------------------
    Zahir Ahmed
    ERP Analyst
    City of Lacey
    WA
    ------------------------------

    Original Message


  • 8.  RE: ACS - Run SQL Scripts CL output display

    IBM Champion
    Posted Fri January 19, 2024 08:18 PM
    Edited by Satid Singkorapoom Fri January 19, 2024 08:23 PM

    Dear Zahir

    All of what you described has been like that since the early days of Run SQL Script tool. You can also use F1 for cursor-sensitive help info just like you do in a 5250 session.  Try it!

    Please read my post above about IBM i and DB2i Services that has been a new approach since IBM i 7.1 for getting the information you want from any SQL session that connects to IBM i server in a similar way you can get from running CL commands in a 5250 session.  This is good place to get start with learning about the services :   IBM i Tutorials, Demos, and SQL examples at  https://www.ibm.com/support/pages/ibm-i-tutorials-demos-and-sql-examples-0  and clicking on the hot links on IBM i Services and Db2 for i Services.   



    ------------------------------
    Chance favors only the prepared mind.
    -- Louis Pasteur
    ------------------------------
    Satid S.
    ------------------------------

    Original Message