DB2 STORED PROCEDURE AS400

Espero me puedan ayudar. Tengo un stored procedure :

La tabla TABHXTPL es del DB2 y es un tipo de dato L osea DATE. El stored me funciona normal cuando la variable está en duro (2018-12-21), si se dan cuenta tiene 3 apostrofes. Sin embargo, cuando trato de setear la variable SQLTEXT de la siguiente forma:

SET SQLTEXT = ‘SELECT FECHTP FROM FPT.TABHXTPL WHERE FECHTP = ‘ || V_FECHTP || ‘ ORDER BY FECHTP DESC’;

Me da un error al momento de ejecutar, me parece que me faltan algunos apostrofes cuando quiero concat V_FECHTP. Espero me puedan ayudar.

Gracias

CREATE PROCEDURE FPT.TEJ051_30 () DYNAMIC RESULT SETS 1 LANGUAGE SQL
BEGIN

DECLARE SQLTEXT VARCHAR (5000) ; DECLARE V_STUHTP INTEGER ; DECLARE V_FECHTP CHAR; DECLARE V_HINHTP VARCHAR(12);

DECLARE C1 CURSOR WITH RETURN FOR DYNSTATEMENT ;

SET V_STUHTP = 1; SET V_FECHTP = ‘2018-12-21′; SET V_HINHTP = ’07:00’; SET SQLTEXT = ‘SELECT FECHTP FROM FPT.TABHXTPL WHERE FECHTP = ‘ || ”’2018-12-21”’ || ‘ ORDER BY FECHTP DESC’;

PREPARE DYNSTATEMENT FROM SQLTEXT ;

OPEN C1 ;

END ;

Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

I have a Stored Procedure that I have written that deletes rows from a table in the database. When I will run the Stored Procedure in the Production environment it will run whilst another program is inserting rows into the same table. In case there is a deadlock I want my Stored Procedure to be the one that is killed.

In the Stored Procedure I have set DEADLOCK_PRIORITY to LOW.

When doing testing in my test environment I am finding that having this option set it makes the deletion run a lot slower. In the testing environment the process that inserts records is not running so, in theory, there is no locking issue.

I am finding that when the DEADLOCK_PRIORITY is set to LOW it is taking 1.5 hours to delete ~500,000 rows. When the DEADLOCK_PRIORITY is not set (ie using the default setting) the deletion of the rows only takes around 15 minutes.

Is there a reason why there is such a difference?

The database is using Simple Recovery model in the test environment. (In the Production environment it is using the FULL recovery model).

Thanks.

Printing primes up to say number n using stored procedure in sql server

DROP Procedure test GO Create Procedure test @Number INTEGER As Begin SET NOCOUNT ON DECLARE @i int =1; DECLARE @K int =1; DECLARE @j int =0; DECLARE @Result NVARCHAR(MAX) WHILE(@i <= @Number) BEGIN

       WHILE(@K <= @i)        BEGIN             if(@K % @i = 0)             BEGIN                  SET @j =@j + 1             END             SET @k = @k + 1        END   SET @K=1  print CONVERT(VARCHAR(10),@i)+'value has j'+CONVERT(VARCHAR(10),@j)  if(@j = 2)     BEGIN          SET @Result= ''+CONVERT(VARCHAR(10),@i) + ' & ';          SELECT @Result      END  SET @j = 0;  SET @i=@i+1;  END  SET NOCOUNT OFF 

END GO

EXEC test 15 

Hi…im not getting whats wrong in this code.output should be like 2&3&5&7&11&13

Procedure to decommission a Web Application on SharePoint 2016

I want to get rid of the Web Application that I had for MySites as I have recreated a new web app for that. I want to know the proper procedure and steps to follow to remove the Web app and its content database from the farm and from SQL as well to make sure I do it in the correct order. This is a test environment.

stored procedure stopped producing output after recreation

I’ve made changes to a custom stored procedure using the “create or replace procedure” syntax via Oracle SQL Developer, and after that store procedure stopped returning rows (saving to a file, the file length is now 0, while there is data). The change was minimal, just changing a DATE field for concatenation in a string. I reverted the sproc back to the original, but it is still not producing any results.

I am logged in as “installer” to both SQL Plus ans OSD, and sproc is invoked like this (from SQLPlus and O.S.D.): exec installer.sp_exportMemo(sysdate-3);

I was thinking that perhaps CREATE OR REPLACE made some permission changes, so I went to Perform Grant Action scree for that sproc, and selected “installer”, and in the left window I see EXECUTE GRANT And I tried clicking “Grant All”, and moving the EXECUTE and GRANT to the right window, but when I re-check, the EXEC and GRANT are still in the left window. I work with MS SQL mostly, but I am not familiar with Oracle to figure this one out, I can’t even output the result set to the screen – but there were no error during the compilation or running of this sproc.