Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to extract stored procedure TEXT from SYSCAT.ROUTINES to not get text truncated?

    Hi,
    using DB2 v10.1 fixpack 1 on Linux. I would like to get whole stored procedure definition from SYSCAT.ROUTINES from db2cmd on Windows with DB2 v10.1 fixpack 1 client.

    I wrote an SQL:
    Code:
    SELECT TEXT FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA='<my_schema>' AND
    ROUTINENAME='<my_procedure_name>';
    but above SQL truncates the output.

    I have tried to use 'export' like:
    Code:
    EXPORT TO my_output_file.txt OF DEL
    MODIFIED BY NOCHARDEL
    SELECT TEXT FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA='<my_schema>' AND
    ROUTINENAME='<my_procedure_name>';
    this is much much better it displays a lot more of text, but still for large stored procedures whole text is still not displayed.

    Is there any other way to get whole text of stored procedure displayed in db2cmd?

    P.S. There is "db2look", but don't like it very much, it just displays way to many info.
    Thanks
    Last edited by grofaty; 09-24-13 at 08:05.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    EXPORT TO my_output_file.txt OF DEL 
    MODIFIED BY NOCHARDEL lobsinfile lobsinsepfiles
    SELECT TEXT FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA='<my_schema>' AND
    ROUTINENAME='<my_procedure_name>';
    Regards,
    Mark.

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Mark,
    thanks for help. My final code is:
    Code:
    EXPORT TO my_output_file.txt OF DEL
    LOBS TO myfolder LOBFILE DEFLOBS1, DEFLOBS2, DEFLOBS3
    MODIFIED BY NOCHARDEL LOBSINFILE
    SELECT TEXT FROM SYSCAT.ROUTINES
    WHERE ROUTINESCHEMA='<my_schema>' AND
    ROUTINENAME='<my_procedure_name>';
    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •