Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Question Unanswered: DB2 Stored Procedure languages

    Hi all,
    I'm in the process of finding details about DB2 database development for a new project that is scheduled to be started soon. As you know DB2 allows to write stored procedured and UDFs in different languages. Can anyone tell the things you should consider when deciding on a language to use and other constraints to lookout for in each language. The project involved J3EE development also.

    Cheers
    Suren.

  2. #2
    Join Date
    Aug 2003
    Posts
    21

    Re: DB2 Stored Procedure languages

    The primay consideration is your developer's skillset.
    In my opinion, it will be good to adopt to writing SQL Stored Procedures. Anyone with the knowledge of SQL can pick up SQL Stored Procs in no time. SQL stored procedures are easier to develop, debug and maintain in the longer run.
    For procedures which cannot be written using SQL, you can go for other languages. For example, file io is not supported in SQL and you can use java




    Originally posted by tm_suren
    Hi all,
    I'm in the process of finding details about DB2 database development for a new project that is scheduled to be started soon. As you know DB2 allows to write stored procedured and UDFs in different languages. Can anyone tell the things you should consider when deciding on a language to use and other constraints to lookout for in each language. The project involved J3EE development also.

    Cheers
    Suren.

  3. #3
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63

    Re: DB2 Stored Procedure languages

    The skill set of DB developers are on SQL and C.

    What about the performance considerations of stored procedures written in languages other than SQL. In a DB2 documentation I saw that SQL stores procedures execute faster than other stored procedures since it is executed within the DBMS. The people assigned to DB development in the project has very good experiance in Oracle PL/SQL. The application development is on java. So will there be any advantage is writting stored procedured in java since the application is java? Will java stored procedures give more flexibility to the application than SQL stored procedures?

    Thanks in advance,
    Suren.



    Originally posted by janagana
    The primay consideration is your developer's skillset.
    In my opinion, it will be good to adopt to writing SQL Stored Procedures. Anyone with the knowledge of SQL can pick up SQL Stored Procs in no time. SQL stored procedures are easier to develop, debug and maintain in the longer run.
    For procedures which cannot be written using SQL, you can go for other languages. For example, file io is not supported in SQL and you can use java

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Stored Procedure languages

    Suren ... Every SQL Stored Procedure is converted to a C procedure and then registerd ... But, this auto-generated C procedure is most likely to be optimized for the db2 engine than a C proc written by a programmer ... So, SQL procs perform better ... Can be other reasons also, but not aware of any ...

    There may be no advantage writing a java SP when you can do the same thing in SQL Procedure ... As another member has indicated, you may need java/c if your stored proc does things not possible with SQL procs ..

    Another important point, if you are not aware of ...

    SQL Stored Procedures require a C compiler for compiling ... That is an additional cost on the project ...

    HTH

    sathyaram


    Originally posted by tm_suren
    The skill set of DB developers are on SQL and C.

    What about the performance considerations of stored procedures written in languages other than SQL. In a DB2 documentation I saw that SQL stores procedures execute faster than other stored procedures since it is executed within the DBMS. The people assigned to DB development in the project has very good experiance in Oracle PL/SQL. The application development is on java. So will there be any advantage is writting stored procedured in java since the application is java? Will java stored procedures give more flexibility to the application than SQL stored procedures?

    Thanks in advance,
    Suren.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    Do we need a C compiler to compile all types of routines (UDFs,SPs,etc..) or only need the compiler to compile stored procedures?

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    For SQL Stoed Procedures you need C Compiler ...

    I don't think you need for SQL UDFs ..

    Cheers
    Sathyaram

    Originally posted by tm_suren
    Do we need a C compiler to compile all types of routines (UDFs,SPs,etc..) or only need the compiler to compile stored procedures?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Sep 2003
    Location
    Colombo, Sri Lanka
    Posts
    63
    Yes Sathyaram you are correct.

    For UDFs you don't need a C compiler, but for stored procedured you do need.

    Thanks,
    Suren.

Posting Permissions

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