Topic
5 replies Latest Post - ‏2013-04-01T02:34:10Z by SystemAdmin
SystemAdmin
SystemAdmin
1632 Posts
ACCEPTED ANSWER

Pinned topic deploy stored procedure-internal error-Failed to get source

‏2013-02-11T12:39:38Z |
I'm attempting to deploy a stored procedure on Z/OS V1.8 AND DB2 V9.1 by IBM DATA STUDIO V3.2 IN WINDOWS 7 X64. When I RIGHT CLICK ON STORED PROCEDURED and then DEPLOY I get this error:

"Deploy TSTSHMA.ACCTING1(INTEGER, SMALLINT, SMALLINT, SMALLINT, SMALLINT, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, INTEGER, DECIMAL(18, 0))

Running
Internal error detected in BuildUtilities.getSource(DB2Routine): Failed to get source, possible model error."

thanks
yass
Updated on 2013-04-01T02:34:10Z at 2013-04-01T02:34:10Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    1632 Posts
    ACCEPTED ANSWER

    Re: deploy stored procedure-internal error-Failed to get source

    ‏2013-02-11T18:31:40Z  in response to SystemAdmin
    Hi yass,

    Can you provide more info about your environment and the stored procedure? What's the mode of your DB2 for z/OS v9.1? Is it in a New-function mode or a Compatibility mode?

    Where Did you right click on stored procedure? Was it in the Data Project Explorer, or Data Source Explorer, or Admin Explorer?

    What's the language of the stored procedure? If it's a SQL stored procedure, is it a native or external one? Can you share the source of your stored procedure, so that I can see if there's any error there?

    If you don't want to share the source in the forum, you can contact the support team, or e-mail the source to emilyz at us.ibm.com.

    Thanks!
    Emily
    • SystemAdmin
      SystemAdmin
      1632 Posts
      ACCEPTED ANSWER

      Re: deploy stored procedure-internal error-Failed to get source

      ‏2013-02-12T05:37:40Z  in response to SystemAdmin
      Hi Emily,
      we have DB2 v9.1(NFM) and z./OS 1.8.
      Recently I installed IBM Data Studio v3.2.0 client on windows 7(x64).
      I connect to remote DB2 on z/OS by Data Source Explorer panel and try to
      view source code of stored procedured
      (database connections-->schemas-->stored procedures-->properties-->source).
      we have both SQL STORED PROCEDURE Native and External.I can see some
      source code but not all of them(with SYSADM Privileges).

      THIS IS SOURCE CODE FROM DB2 IN Z/OS.
      --#SET TERMINATOR ?
      SET CURRENT SQLID='YASS'?
      CREATE PROCEDURE TSTSHMA.ABC
      (OUT PO_ERROR INTEGER ,
      IN PI_EMAIL VARCHAR(50) FOR SBCS DATA CCSID ASCII ,
      OUT PO_STEP INTEGER )
      EXTERNAL NAME 'ABC'
      LANGUAGE SQL PARAMETER CCSID ASCII NOT DETERMINISTIC FENCED
      CALLED ON NULL INPUT MODIFIES SQL DATA NO DBINFO
      COLLID DEVPACK1 WLM ENVIRONMENT WLMENVD1 ASUTIME NO LIMIT
      STAY RESIDENT NO PROGRAM TYPE MAIN SECURITY DB2
      INHERIT SPECIAL REGISTERS STOP AFTER SYSTEM DEFAULT FAILURES
      COMMIT ON RETURN YES LEAVE L0 ?
      --#SET TERMINATOR ;
      COMMIT;

      When try to Deploy by IBM DATA Studio,I get this error:

      "Deploy TSTSHMA.ABC(INTEGER, VARCHAR(50) FOR SBCS DATA CCSID ASCII, INTEGER)

      Running
      Internal error detected in BuildUtilities.getSource(DB2Routine):
      Failed to get source, possible model error."

      Thanks
      yass
      • SystemAdmin
        SystemAdmin
        1632 Posts
        ACCEPTED ANSWER

        Re: deploy stored procedure-internal error-Failed to get source

        ‏2013-03-14T03:43:03Z  in response to SystemAdmin
        Hi Yass,

        Thank you for using DS and bring up your questions.
        There are two things you need to do to make your procedure work.
        1. 'LEAVE L0' is not a valid statement. L0 reference has not been defined. You can just change it to 'return 0' to make it valid.
        2. I checked your DDL. You're creating a SQL external procedure. Either 'external name' or 'fenced' parameter distinguish it from native. It's not intended to be run directly. See the reference for it on IBM Info Center. But you can do this way to create SQL native procedures.
        http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sql_createproceduresqlexternal.htm

        You can use either Data Project Explore or Data Source Explore to create the procedure then deploy it right away.
        For DPE, you need first create a Data Development Project and connect it with the DB your will run against. Then under the Stored Procedure folder, right click to new a stored procedure.
        For DSE, just find the Stored Procedure folder under one schema and right click to create one.
        You might refer to Data Studio information center about creating stored procedures.
        http://pic.dhe.ibm.com/infocenter/dstudio/v3r2/index.jsp?topic=%2Fcom.ibm.datatools.routines.doc%2Ftopics%2Fcspcreate.html

        Let me know if you have further questions. Thanks!
        Equal
        • SystemAdmin
          SystemAdmin
          1632 Posts
          ACCEPTED ANSWER

          Re: deploy stored procedure-internal error-Failed to get source

          ‏2013-03-30T05:03:36Z  in response to SystemAdmin
          Hi Equal,

          I want to see stored procedure source which is created in IBM DB2 on z/OS by DS(Data Studio).I do not have any problem when i create it by DS but when it creates in z/os,I can not see source of it by DS.
          Also when i want to deploy i got the same Error code which i mentioned on my previous posts.
          Internal error detected in BuildUtilities.getSource(DB2Routine): Failed to get source, possible model error."

          Thanks
          yass
          • SystemAdmin
            SystemAdmin
            1632 Posts
            ACCEPTED ANSWER

            Re: deploy stored procedure-internal error-Failed to get source

            ‏2013-04-01T02:34:10Z  in response to SystemAdmin
            Hi Yass,
            When you say 'create it by DS' do you mean run the create DDL in DS SQL editor? If you run the create DDL directly to create a SQL external procedure, DB2 won't store the source of it so DS can't get the source. That's why I suggest you to create your external procedures using create procedure wizard in DPE or DSE. In that way DS will store your source.

            If you are creating your procedure in DPE or DSE using the create procedure wizard when you see the internal error, that might be a problem. In that case, I need further information of your steps.

            Thanks,
            Equal