DB2 - L

Expand all | Collapse all

Stored Procedures source not formatted for recreating

  • 1.  Stored Procedures source not formatted for recreating

    Posted Oct 03, 2021 07:58 PM
    I have many stored procedures to drop and recreate. Source I get from the catalog it is not formatted to 72 column length causing formatting errors.
    With many SPs to recreate, it is tedious to edit them all for formatting.
    Any advise?
    Thanks
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------


  • 2.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 08:04 AM
    IBM Data Studio is an option. It will pull from the catalog and then you can reformat.
    Dan




  • 3.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 08:15 AM
    is it easier to reformat when extracted thru Data Studio?
    I issue is not getting the source. The issue is having to reformat and edit thru thousands of lines of code.
    thanks
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 4.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 08:49 AM
    I personal have not tried it but will theorize for a moment. You can open a routine in data studio and manually edit it for sure. Data studio has a 72 character editing rule. For one, it should flag a warning for every line that exceeds 72 bytes making it easier to edit. You could also try copying the source to a second stored procedure editor and it may reformat to 72 bytes. I have never done this myself but you may want to try it.
    Dan




  • 5.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 09:00 AM

    We created a REXX that extracts all Stored Procedures from the catalog.

    All lines are re-formatted to FB72.

     

    Could share the REXX & JCL if interested.

     

    Thanks,

     

    John Lynt

    CBSA DBA

     






  • 6.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 09:52 AM
    Hi John,
    Yes! Extremely interested! Please share!
    I can give you my emails privately if you like?
    Bill

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 7.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 10:43 AM

    Hi Bill,

     

    Sent to your email as requested.

    Good luck!

     

    Thanks,

    John Lynt

    CBSA DBA

    613-668-3079

     






  • 8.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 11:14 AM

    Hi John,

    Could you share your Rexx with me too please?

    It's getting more challenging with Native Stored Procedures(NSP), especially when migrating to one Db2 to another.

    We use CA's  RC/M, but does not handle well with NSP yet.

     

    Thanks

    Ken

     






  • 9.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 12:11 PM
    Hello Kenneth Lee,

    Which release of RCM are you using since I am not aware of any major issues with NSP's ?

    Please feel free to contact me off this group @ steen.rasmussen@broadcom.com.

    I am aware of a recent PTF which solved a formatting issue with SET statements.

    Steen





  • 10.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 12:33 PM
    I'm sorry what is RCM?

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 11.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 01:29 PM
    Sorry for the abbreviation - RCM is RC/Migrator from Broadcom ( generating DDL) from the Db2 catalog

    Steen

    Sent from my iPhone




  • 12.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 01:49 PM
    we are not using Broadcom RCM

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 13.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 03:00 AM
    Hi Bill,

    what administration tool do you have?

    IBM Db2 Admin Tool can generate nicely formatted DDL for stored procedures

    ------------------------------
    Jørn Thyssen
    Rocket Software
    2021 IBM Champion
    ------------------------------



  • 14.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 04:45 AM
    we have BMC Catalog Manager and Change Manager.

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 15.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 05:05 AM

    Then you a truly blessed.  And just like years ago when some ex-pat Oz-based Brit somehow (incorrectly) interpreted my positive comments as defamatory, I'm again serious; they are great products and you're fortunate to have them.  You can (H)DDL the SP defs in Catalog Manager, or migrate them via Change Manager.  Either way you'll get beautifully-formatted SP defs.

     

    Cheers,

     

     

    Raymond

     






  • 16.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 05:52 AM
    I am using Catalog Manager option DDL and it is not coming out formatted.

    ------------------------------
    williamgiannelliMe
    ------------------------------



  • 17.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 02:46 AM
    John,

    can you please share your rexx and JCL in the library section of this forum?

    Thanks, Paul

    ------------------------------
    PaulJansenATOS
    ------------------------------



  • 18.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 05, 2021 08:44 AM

    Added the REXX & JCL to the IDUG code page.

    REXX extracts all Stored Procedures from the catalog.

    All lines are re-formatted to FB72.

     

     

    Code Place - IDUG

     

     

     

    Thanks,

    John Lynt






  • 19.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 09:01 AM

    Bill,

     

    I don't know if this is related to your length issue but I was doing a ddl on an old table that was defined back between v7 and v8 of DB2.

    When I did this the constraint which was extremely long went way over column 72! So I reformatted it to fit and did the redefine. Then when I did the ddl again all was ok. So I put an ticket in with IBM and they set up an  APAR PH40662 to fix the x'25's that were incorrectly inserted in the row in sysibm.syschecks.

     

    Could your stored procedure be having this same type of issue? Is it an extremely old routine?

     

    Just a thought..

     

    Don Isenstadt
    IMS/DB2 DBA
    Database Management and Support
    EIS

     

     

     

     






  • 20.  RE: Stored Procedures source not formatted for recreating

    Posted Oct 04, 2021 09:36 AM
    SQLInform, will get you close.