invalid attribute for SQL procedure及GET DIAGNOSTI

2019独角兽企业重金招聘Python工程师标准>>>

db2inst1@cyper-pc:/home/cyper/swap/procedure$ db2 -td@ -f FMSSPA01
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0628N  Multiple or conflicting keywords involving the "<invalid attribute 
for SQL procedure>" clause are present.  LINE NUMBER=29.  SQLSTATE=42613

解决
CREATE PROCEDURE FMSSPA01(IN IN_FUNDING_GRP_ID DECIMAL(15,0),
                          OUT OUT_SQLCODE INTEGER)
  specific FMSSPA01
  LANGUAGE SQL
  FENCED
  MODIFIES SQL DATA
  PROGRAM TYPE SUB
  DYNAMIC RESULT SETS 0

去掉不识别的attribute即可

db2inst1@cyper-pc:/home/cyper/swap/procedure$ db2 -td@ -f FMSSPFGA
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "1" was found following "GNOSTICS CONDITION  ".  
Expected tokens may include:  ".".  LINE NUMBER=20.  SQLSTATE=42601

将

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    GET DIAGNOSTICS CONDITION
       1 OUT_SQLCODE   = DB2_RETURNED_SQLCODE
       , OUT_SQLERROR  = MESSAGE_TEXT;

改为

  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    GET DIAGNOSTICS OUT_SQLCODE = DB2_RETURN_STATUS;
    GET DIAGNOSTICS EXCEPTION 1 OUT_SQLERROR  = MESSAGE_TEXT;

参考：http://sqlpl-guide.com/GET_DIAGNOSTICS

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005647.html?cp=SSEPGG_9.7.0%2F2-10-6-128

转载于:https://my.oschina.net/uniquejava/blog/325325