DB2 - L

 View Only
Expand all | Collapse all

Db2 LUW talking to Db2 12 z/OS - STOPROC issues

  • 1.  Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 09:35 AM
    hi all,

    posting on behalf of one of our developers (and no I don't know the answer myself :):

    can we code REORG in a STOPROC that can be invoked from a remote client, since REORG cannot be invoked remotely via Db2 Connect?

    If yes, can you please give me an example?

    They are pretty good but everything they tried comes back with "db2 errorcode=-4463 sqlstate=42601"...

    Oh did I mention TFI Friday?

    TIA.

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------


  • 2.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:09 AM
    Hi Aurora,

    have you tried ADMIN_COMMAND_DB2 https://www.ibm.com/docs/en/db2-for-zos/12?topic=db2-admin-command yet?
    Or maybe ADMIN_UTL_SCHEDULE https://www.ibm.com/docs/en/db2-for-zos/12?topic=db2-admin-utl-schedule

    I think, this should do the trick.

    Ciao
    Roland

    ------------------------------
    Roland Schock
    ARS Computer und Consulting GmbH
    ------------------------------



  • 3.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:19 AM
    Hi Roland,

    wow that's fast service!

    I THINK they should be able to use ADMIN_UTL_SCHEDULE stored procedure - with UTILITY_NAME REORG.

    I'll forward them the info immediately and they can let me know.

    Oh, you haven't got any ideas for loading videos onto a Db2 z/OS LOB and then retrieving them to watch, by any chance, have you? Strictly cat videos, I promise...

    Thanks again.



    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------



  • 4.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:32 AM
    Hi Aurora,

    I just recently learned, that Tanmay Bakshi (on Twitter @TajyMany) has developed some sample code for Db2 (LUW) he called Movie Explorer.
    It was meant as a sample demo code and might popup on Github sometime...

    Ciao
    Roland


    ------------------------------
    Roland Schock
    ARS Computer und Consulting GmbH
    ------------------------------



  • 5.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:43 AM
    thanks! I've now tried to get in touch with him - I saw one of his presentations just last week too :)

    (these whizzkids scare me stiff, btw, if I had a quarter of their brains I'd be dangerous)...

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------



  • 6.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:47 AM
    We've looked at various ways of doing this.

    The SPs mentioned above (and the older DSNUTILU) have lots of issues and usage problems.

    Some of the issues we found were -

    1) Strange security model which meant we would have to give the users way too much power
    2) Problems interpreting the outputs.  Essentially you just get back the JCL output and it doesn't even set a return code other than 0 if you get a utility failure.  To try to even get an idea of whether it worked or not you have to manually parse the SYSPRINT.

    We decided that there must be a better way.

    We've found this in ZOWE.   If we have a change that requires utilities (e.g. an ALTER which needs a reorg to clear an AREO* state) we trigger this with the ZOWE CLI (we could also have used ZOWE REST).  A typical command would be -

    zowe zos-jobs submit local-file reorg.jcl -d "/tmp" --user $USERID --password $PASSWORD

    The reorg.jcl is a file with the reorg JCL that we store in our source code repository.  In fact we store a template of this, which we use sed to make into a environment specific file before submitting.  The "-d" option says where to write the job output to on our deployment server.   You don't need to pull the outputs down to your client, or even to wait for the reorg job to finish, but we choose to do this because we need to save this for deployment evidence.   

    The user / password isn't saved anywhere - we pick these up from our automation and plug them in at run time.

    This works really well, albeit you do need to have z/OSMF in place to allow you to use it.

    We're excited for the future about extensions to our tooling, from IBM and third party vendors, which will provide new ZOWE options for interacting with them directly rather than via this "JCL bridge".

    Phil






  • 7.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:50 AM
    I should also have mentioned, that all this is in a shell script ... and you can then invoke this from inside a CLPPLUS command line ...

    You could also use the LUW HTTP invocation functions to call ZOWE REST instead if you needed to do it from a standard SQL stream.





  • 8.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 05, 2021 10:56 AM
    thank you Phil, that's as thorough as I could hope and then some! I'll pass the info to the eager developer...

    say hi to the Auld Reekie for me please!

    Have a good weekend.

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------



  • 9.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 06, 2021 04:59 AM
    Hello, 

    We use since a long time admin_util_schedule to trigger all our utilities via a java application .Can be call remotly via ddf.
    We manage   copy, reorg, runstats , modify , quiesce ,  
    You can include this in a db2 stopro to add some internal checks , we can send you some example if necessary.

    regards 

    Alain

    ------------------------------
    alainparyDatabase Business Partners SPRL
    ------------------------------



  • 10.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 08, 2021 02:08 AM
    oh thanks Alain yes please - do send examples - I hope my email is visible?

    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------



  • 11.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 08, 2021 06:04 AM

    Alain, I would appreciate it if you could send me the examples as well,

     

    mvickers@cswg.com

     

    Email Signature 2021

     






  • 12.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 06, 2021 10:07 AM

    That's what I was going to say. I used it a lot when I was in another place.

     

    Regards,

    Marcus Davage CEng CITP MBCS

    Lead Product Developer

    Intelligent Z Optimization and Transformation

    BMC Software

    Direct

    +44 118 921 8517

     

    Mobile

    +44 7840 023 560

     

    Email

    marcus_davage@bmc.com

     

     

    Hi Roland,

    wow that's fast service!

    I THINK they should be able to use ADMIN_UTL_SCHEDULE stored procedure - with UTILITY_NAME REORG.

    I'll forward them the info immediately and they can let me know.

    Oh, you haven't got any ideas for loading videos onto a Db2 z/OS LOB and then retrieving them to watch, by any chance, have you? Strictly cat videos, I promise...

    Thanks again.




    ------------------------------
    Aurora


    Stay safe and healthy, y'all
    ------------------------------

    BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.





  • 13.  RE: Db2 LUW talking to Db2 12 z/OS - STOPROC issues

    Posted Nov 17, 2021 09:08 AM
    Just saw your message, have you considered DSNUTILS/DSNUTILU?

    Cheers
    Bruce

    ------------------------------
    Bruce Williamson
    Commonwealth Bank of Australia
    ------------------------------