Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    Unanswered: GET ROUTINE command

    Hi
    I'm trying to retrieve the SQL for an SP from DB2 9.7 LUW with this command:
    GET ROUTINE INTO INSERTCHECKDIGITTYPE.proc FROM PROCEDURE DATAREPOSITORYRULE.INSERTCHECKDIGITTYPE

    The response is
    SQL0443N Routine "GET_ROUTINE_SAR" (specific name "GET_SAR4PARM") has
    returned an error SQLSTATE with diagnostic text "-107, 42622, specific name
    too long". SQLSTATE=38000

    As you can see I'm not referring to it by specificname but using the fully qualified procedure name. It does seem to be a length limit though, as procs with shorter names are processed OK.
    Anyone experienced this / found a way around it?

    Cheers
    Mark

  2. #2
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by MarkGillis View Post
    Hi
    I'm trying to retrieve the SQL for an SP from DB2 9.7 LUW with this command:
    GET ROUTINE INTO INSERTCHECKDIGITTYPE.proc FROM PROCEDURE DATAREPOSITORYRULE.INSERTCHECKDIGITTYPE

    The response is
    SQL0443N Routine "GET_ROUTINE_SAR" (specific name "GET_SAR4PARM") has
    returned an error SQLSTATE with diagnostic text "-107, 42622, specific name
    too long". SQLSTATE=38000

    As you can see I'm not referring to it by specificname but using the fully qualified procedure name. It does seem to be a length limit though, as procs with shorter names are processed OK.
    Anyone experienced this / found a way around it?

    Cheers
    Mark
    I'm not familiar with "GET ROUTINE ...". Is select text from syscat.procedures an option?

    Code:
     
    select cast(text as varchar(...)) 
    from syscat.procedures 
    where procschema = 'DATAREPOSITORYRULE'
      and procname = 'INSERTCHECKDIGITTYPE'
    --
    Lennart

  3. #3
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    GET ROUTINE or any other method for getting SP code

    Unfortunately not. These are some big, big stored procedures: if I just select TEXT from SYSCAT.ROUTINES I get
    DB29320W Output has been truncated
    and if I try and use a CAST I get
    SQL0445W Value "CREATE PROCEDURE IPDATAREPOSITORY.MATCHBYAPPLICATIONNUMBER ("
    has been truncated. SQLSTATE=01004

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    There is an interesting thing in the error message:
    SQL0443N Routine "GET_ROUTINE_SAR" (specific name "GET_SAR4PARM") has
    returned an error SQLSTATE with diagnostic text "-107, 42622, specific name
    too long". SQLSTATE=38000
    42622 is the SQLSTATE corresponding to -107/SQL0107 and not a token for -107. "specific name" is also a token.

    What is the specific name for the procedure IPDATAREPOSITORY.'? I.e. what does
    Code:
    SELECT SPECIFICNAME
    FROM syscat.sysroutines
    WHERE routineschema = 'IPDATAREPOSITORY' AND
    routinename = 'MATCHBYAPPLICATIONNUMBER'
    return? You may want to double check that this name is shorter, i.e. by specifying a short name in the CREATE PROCEDURE stmt.

    Note that I have no idea if this is actually the problem...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by MarkGillis View Post
    Unfortunately not. These are some big, big stored procedures: if I just select TEXT from SYSCAT.ROUTINES I get
    DB29320W Output has been truncated
    and if I try and use a CAST I get
    SQL0445W Value "CREATE PROCEDURE IPDATAREPOSITORY.MATCHBYAPPLICATIONNUMBER ("
    has been truncated. SQLSTATE=01004
    I normally use

    Code:
    export to /dev/null of del lobs to  . modified by lobsinfile select CHR(10)||'Routine Starts'||text||CHR(10)||'@ ---Routine Ends'||repeat(CHR(10),3) from syscat.routines where ...
    This will dump all the routine code to a file in the local directory, using @ as the statement delimiter + a few blank lines.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    GET ROUTINE or any other method for getting SP code

    Thanks Sathy that did the trick. I just have a

    mv null.001.lob procname.proc

    at the end of my script and I have what I need. Thanks for your help
    Mark

  7. #7
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    GET ROUTINE or any other method for getting SP code

    Thanks for your input too Knut, but that doesn't get round the issue as the specificname is the same as the procname so including SPECIFIC and the specificname in the command gives the same error. I'm getting the impression that GET ROUTINE is no longer supported.
    Cheers
    Mark

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Out of curiosity, what is the actual size of your routine?

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you have a bunch of routines which you want in separate files, you can also do

    Code:
    export to  mvfiles.sh of del lobs to . modified by lobsinfile lobsinsepfiles nochardel coldel'x20' select 'mv ',text||CHR(10)||'@',' '||rtrim(routinename)||'.proc' from sy
    scat.routines where  ....
    execute the mvfiles.sh file to rename the lob files
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by MarkGillis View Post
    Thanks for your input too Knut, but that doesn't get round the issue as the specificname is the same as the procname so including SPECIFIC and the specificname in the command gives the same error. I'm getting the impression that GET ROUTINE is no longer supported.
    Cheers
    Mark
    Try to use a shorter specific name. Your procedure name is pretty long and I remember that there used to be an 18-char length limit for the specific name. Maybe GET_ROUTINE has some issue with longer specific names, still.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Feb 2012
    Location
    Bucks, UK
    Posts
    13

    GET ROUTINE or any other method for getting SP code

    Sathy: thanks again, that is exactly what I needed.

    Knut: I don't have any control over what the Procs are called, I'm just retrieving code from a production environment so that I can do some perfromance tuning

    N I: we've got quite a few but the largest is 66,000 bytes by the looks of things

    Cheers
    Mark

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My point was: try to create the SP with a shorter specific name. If things are then working, you know at least the source of the problem and can works towards a resolution with the procedure developers.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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