Deploying a project (Stored Procedures and User Defined Functions)

ThomasLieser commented 11 months ago

Currently we are still relying on Datastudio to deploy complete folders of stored procedure and user defined functions. That functionality would help to deploy projects containing a lot of StPs and UdFs (we have now systems containg several hundred)

katsoohoo commented 11 months ago

Hi @ThomasLieser,

Thanks for letting us know about this feature as well. We'll take a look at #119 first before this one.

Happy Holidays!

ThomasLieser commented 11 months ago

@katsoohoo thanks a lot - sounds good! and: Happy Holidays to you as well!

ThomasLieser commented 10 months ago

@katsoohoo One additional information - as we are heavily using StPs/UdFs in DB2 V12 and Datastudio does not support V13 we will have to postpone the migration to V13. We will be very glad when we can migrate to VSCode - Thanks again for your support

b-tsao commented 7 months ago

Hi @ThomasLieser, in developer extension 2.1.5 release we now support external SQL and Java stored procedure deployment. Could you specify more information about the nature of stored procedures your team deploys for more information to help us prioritize?

Thanks!

ThomasLieser commented 7 months ago

@b-tsao Thanks for your question.

We are using Native SQL StoredProcedures and UserDefinedFunctions - the system I'm working on currently has about 600 native StPs and UdFs - there are systems in our company that also use StPs and UdFs (but our system was an early adopter, so we have the biggest number).

The process for deployment is as follows:

the developer deploys in test and integration environments (in my case about 10 different environments, because our software is used by several companies) - we use Datastudio and GIT
during deployment to production the DBA checks out the "release branch" and deploys it to the productive environment - obviously the DBA does this now using the functionality of Datastudio that deploys "all" StPs or UdFs in "one" go - he doesn't need to know which to select - he just deploys all (with a new "version"). When the "release" is activated (this is a process over all systems in the house) he just activates all StPs and UdFs of that "version" (this has helped us a lot as we can deploy some time before the activation, makes it much easier)

So what would we need? a) be able to deploy native UdFs as well b) deploy "all" UdFs and StPs in a folder or project in one go

I do like the developer extension - but without these two features we can't move our projects from Datastudio as you will understand. On the other hand time is running short for us - we've already had to postpone moving to DB2 V13 as Datastudio is not supported there.

Thanks for your help

ThomasLieser commented 7 months ago

@b-tsao To add: it would be good to know what your plans are - if the features we need to replace Datastudio are not on your schedule for 2024 we will have to start looking for alternative solutions (though I'm afraid they will be very cumbersome and so far I know of no other replace for Datastudio) - as we will need to move on to V13 next year.

ThomasLieser commented 6 months ago

@katsoohoo @b-tsao @ericradz So, I'm giving it one last try - can anybody say something about what is planned for the future? 2025 is getting nearer - and once we need to move on to V13 we need a replacement for Datastudio. If our needs are not on your list, we will have to think about a work-a-round solution. Sorry to keep pesting you but this a important topic for us

b-tsao commented 6 months ago

Hi @ThomasLieser,

I apologize about the lack of response. I have notified @amadhavia a few weeks ago about your requirements and she would be the proper person to provide the information you seek.

We do actively work to meet the needs of our users and consider every issue carefully.

ThomasLieser commented 6 months ago

@b-tsao thanks

amadhavia commented 6 months ago

@ThomasLieser Please send me an e-mail to mamirne@us.ibm.com. I will be happy to further discuss about your requirements and the roadmap and see how we can better assist you.

Regards, Madhavi Amirneni

ThomasLieser commented 6 months ago

@amadhavia eMail sent - thanks

DebbieB1 commented 6 months ago

Hello, our team is also facing this challenge. We have a quickly growing number of Native Db2 SQL stored procedures and are trying to transition from DataStudio to the Developer's Extension, paired with BitBucket.

One of the limitations listed of the Developers Extension is that it cannot handle NSP with input parameters of a datatype Array. I also have an example where it seems unable to handle a NSP with an output parameter of datatype Array.

Our roadmap has us moving to Db2 v13 by the end of the year. It sounds like we may have to postpone that until VSCode catches up? Is there an ETA please?

ThomasLieser commented 6 months ago

@DebbieB1 I was wondering that nobody else was facing that problem. Our deadline for DB2V13 is the end of 2025 (which of course means having tested everything on a test LPAR at least by September 2025). I'm still hoping to get a full solution in the VSCODE db2Extension well before that date....

DebbieB1 commented 2 months ago

Have there been any updates on the status of enhancements for handling Arrays as input/output data types? My team is working in Developer's Extension, but we have to continue to return to Data Studio to test many of our Db2 Native Stored Procedures.

Our v13 upgrade is on hold until this is resolved. If anyone has an ETA, I'd appreciate it.

amadhavia commented 2 months ago

@DebbieB1 We are currently working on the key features that are requested above (https://github.com/IBM/db2forzosdeveloperextension-about/issues/120#issuecomment-2022092264) like --

a) be able to deploy native UdFs as well b) deploy StPs in a folder or project in one go Able to deploy and debug SQLJ procedures, etc. We plan to release these features in our upcoming October release.

We will look into handling the Arrays as input/output for the procedures soon after the above features.

Regards, Madhavi Amirneni

DebbieB1 commented 2 months ago

@amadhavia Thank you for your quick reply! I will pass that along to upper management to set expectations.

mykael22000 commented 1 month ago

You can put together a utility that grabs a bunch of stored procedures and imbeds them into a batch job. Just upload it and submit it to deploy them...

ThomasLieser commented 1 month ago

@mykael22000

You can put together a utility that grabs a bunch of stored procedures and imbeds them into a batch job. Just upload it and submit it to deploy them...

Try that with UTF8, hundreds of StPs and UdFs, several schemata to deploy to and so on - not really a valid scenario.

And we are using GIT - I want a controlled way of deploying

IBM / db2forzosdeveloperextension-about

Deploying a project (Stored Procedures and User Defined Functions) #120