SQL Server存储过程的IBM DB2等效语法-部署时出错

Question

我是IBMdb2的新手。需要将下面提到的SP转换为db2语法。但我被Db2中使用或可用的许多等价物卡住了。即使是谷歌的研究也没有显示我们如何像我在SQL Server存储过程中那样准确地比较db2中表的对象id。有人能给我建议正确的方法吗？

EDIT：我已经用等效的DB2语法进行了更新，但在特定行部署时面临以下错误。有人能识别并帮助我了解此语法有什么问题吗？或者问题出在过程中的其他地方吗

第25行：DECLARE v_sqlstate CHAR(5)；

BACKUPTABLE：25:在"后面发现意外的令牌"<variable declaration>。预期的令牌可能包括："。。SQLCODE=-104，SQLSTATE=42601，DRIVER=4.18.60在"之后发现意外的标记变量声明。预期的令牌可能包括："。。SQLCODE=-104，SQLSTATE=42601，DRIVER=4.18.60

SQL Server存储过程语法：

CREATE PROCEDURE [dbo].[BackUpTable] 
@TableName sysname
AS
BEGIN
SET nocount ON 
DECLARE @sql VARCHAR(500) 
IF EXISTS (SELECT * 
FROM   sys.objects 
WHERE  object_id = Object_id(N'[dbo].[' + @TableName+'_EST' + ']') 
AND TYPE IN ( N'U' )) 
BEGIN 
SET @sql = 'declare @Done bit
set @Done = 0
while @Done = 0
begin
delete top (100000)
from ' + @TableName + '_Bak' + 
' if @@rowcount = 0     
set @Done = 1  
end;' 
SET @sql = @sql + 'insert into ' + @TableName + '_Bak select * from ' + 
@TableName +'_EST'
EXEC(@sql) 
END 
ELSE 
BEGIN 
DECLARE @err_message VARCHAR(300) 
SELECT @err_message = 'The table "' + Isnull(@TableName, 'null') + 
'" does not exist' 
RAISERROR (@err_message, 16, 1) 
END  
END

迄今为止创建的DB2语法：

CREATE OR REPLACE PROCEDURE BackUpTable (IN TableName VARCHAR(128))
DYNAMIC RESULT SETS 1
BEGIN
DECLARE dynamicSql  VARCHAR(500); 
IF(EXISTS( 
SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME =  TableName||'_EST'
) 
)
THEN 
SET dynamicSql  = 'DELETE FROM '||TableName ||'_BAK';
SET dynamicSql  = dynamicSql  ||'insert into ' || TableName || '_BAK select * from ' || 
TableName || '_EST';
EXECUTE IMMEDIATE dynamicSql; 
ELSE
DECLARE v_sqlstate CHAR(5);    
DECLARE v_sqlcode INT;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';   
DECLARE SQLCODE INT DEFAULT 0;
DECLARE EXIT HANDLER FOR SQLEXCEPTION  
BEGIN
SELECT SQLSTATE, SQLCODE
INTO v_sqlstate, v_sqlcode
FROM sysibm.sysdummy1;    
SET O_Error_Msg = 'TABLE IS NOT AVAILABLE:: SQLState : '||v_sqlstate||' SQLCode : '||v_sqlcode ;   
END;
END IF;
END

Answer 1

在z/os上你可以做到：

IF( EXISTS( SELECT 1 FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA = 'YOURLIB' AND TABLE_NAME = 'YOURTABLENAME')) THEN
DROP TABLE YOURLIB.YOURTABLENAME;
END IF;