Question
igate global solutions
IN
Last activity: 17 Mar 2016 2:32 EDT
Error when trying to use truncate query using RDB-Delete method
Hi Everyone,
I wrote an activity where in step 1, I am using RDB-Delete method where my connect SQL rule delete tab contains truncate query like below.
TRUNCATE TABLE tablename IMMEDIATE;
The activity is a Agent Activity and is running for every 1 hour for truncating the table. I am getting the below error.
code: -428 SQLState: 25001 Message: DB2 SQL error: SQLCODE: -428, SQLSTATE: 25001, SQLERRMC: null
Could anyone help me in fixing this.
Please note that delete query in place of Truncate is working fine but there is a performance issue because the delete query like below is not having where clause so trying to acquire lock on whole table and deleting one row at a time there by causing performance issue at PEGA level and deadlock issue at DB level.
delete from tablename;
Thanks,
Naveen
Accepted Solution
igate global solutions
IN
truncate query will work.
But we have to write the truncate query followed by commit in a stored procedure.
This is the solution I found but didn't tested because I was asked to postpone doing it.
Pegasystems Inc.
US
Truncate commands do not write to the redo logs so they need to be in a separate transaction. You should create a stored procedure which does the commit followed by the truncate and then call the stored procedure from your RDB
igate global solutions
IN
Please help me in creating procedure. Below is the syntax I am using for creating procedure, but i got the error like below
Error: com.ibm.db2.jcc.b.co: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=BEGIN;empty_kpiportal"
AS
;:, DRIVER=3.53.70, SQL State: 42601, Error Code: -104
Error occured in:
create procedure "rugy"."sp_empty_kpiportal"
AS
BEGIN
COMMIT
---------------------------------------------------
create procedure "rugy"."sp_empty_kpiportal"
AS
BEGIN
COMMIT;
truncate table "RUGY"."UGY0_KPI_PORTAL" immediate;
END;
Thanks,
Naveen
Pegasystems Inc.
US
That does not look like the correct syntax for a DB2 stored procedure. Why do you have AS?
For a stored procedure with no parameters I think the syntax should be like this:
CREATE OR REPLACE PROCEDURE MyStoredProc()
BEGIN
igate global solutions
IN
Again same error.
Error: com.ibm.db2.jcc.b.co: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=COMMIT;Y_KPIPORTAL()
BEGIN
;END, DRIVER=3.53.70, SQL State: 42601, Error Code: -104
Error occured in:
create procedure rugy.sp_empty_kpiportal()
BEGIN
COMMIT
------------------------------------------------------------------plz find the Script below
create procedure rugy.sp_empty_kpiportal()
BEGIN
COMMIT;
truncate table "RUGY"."UGY0_KPI_PORTAL" immediate;
END
Thanks,
Naveen
Pegasystems Inc.
US
I don't see anything obviously wrong with the syntax you have. I would try it without the "" around the schema and table name. Also make sure that you have changed the default delimiter in whatever tool you are using to be any special character other than semicolon.
igate global solutions
IN
I am using SQuirrel SQL Client tool. I tried after removing the quotes around schema and table name but still the same error.
Updated: 16 Mar 2016 15:11 EDT
Were you able to resolve this issue.
From RDB-delete you should try to delete the class with this command -> Truncate table {class: name}
Accepted Solution
igate global solutions
IN
truncate query will work.
But we have to write the truncate query followed by commit in a stored procedure.
This is the solution I found but didn't tested because I was asked to postpone doing it.