Can anybody tell me where goes wrong?
--->issue: db2 connect to genexp user express using password
--->result:
Database Connection Information
Database server = DB2/6000 8.1.0
SQL authorization ID = EXPRESS
Local database alias = GENEXP
--->issue: db2 put routine from SP_WAREHOUSE_INS.sql
--->result:
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR1PARM" has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
--->issue: db2 -td@ -vf SP_ACTIVITY_INS.sql
--->result:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0035N The file "P1305522.msg" cannot be opened.
--->The content of SP_ACTIVITY_INS.sql:
CREATE PROCEDURE sp_activity_ins(
IN v_activity_cd VARCHAR(10),
IN v_activity_desc VARCHAR(40),
IN v_activity_type CHAR(1))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT v_activity_cd AS activity_cd
FROM TABLE (VALUES 1) AS temp_table;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
IF EXISTS
(SELECT activity_cd
FROM o_activity
WHERE activity_cd = v_activity_cd) THEN
UPDATE o_activity
SET activity_cd = v_activity_cd,
activity_desc = v_activity_desc,
activity_type = v_activity_type,
SyncOperation = 'U',
SyncVersion = CURRENT TIMESTAMP
WHERE activity_cd = v_activity_cd;
COMMIT;
ELSE
INSERT INTO o_activity(activity_cd, activity_desc, activity_type,
SyncOperation, SyncVersion)
VALUES (v_activity_cd, v_activity_desc, v_activity_type,
'A', CURRENT TIMESTAMP);
COMMIT;
END IF;
OPEN temp_cursor;
END@
--->sample SP_WAREHOUSE_INS.sql
CREATE PROCEDURE express.sp_warehouse_ins(
IN v_whse_cd VARCHAR(10),
IN v_whse_desc VARCHAR(40),
IN v_whse_adr1 VARCHAR(40),
IN v_whse_adr2 VARCHAR(40),
IN v_whse_adr3 VARCHAR(40),
IN v_whse_adr4 VARCHAR(40),
IN v_whse_contact VARCHAR(20),
IN v_whse_fax VARCHAR(40),
IN v_whse_conPerson VARCHAR(40),
IN v_branch_cd VARCHAR(10))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT v_whse_cd AS whse_cd
FROM TABLE (VALUES 1) AS temp_table;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
IF EXISTS (SELECT whse_cd
FROM o_warehouse
WHERE whse_cd = v_whse_cd) THEN
UPDATE o_warehouse
SET whse_cd = v_whse_cd,
whse_desc = v_whse_desc,
whse_adr1 = v_whse_adr1,
whse_adr2 = v_whse_adr2,
whse_adr3 = v_whse_adr3,
whse_adr4 = v_whse_adr4,
whse_contact = v_whse_contact,
whse_fax = v_whse_fax,
whse_conPerson = v_whse_conPerson,
branch_cd = v_branch_cd,
SyncOperation = 'U',
SyncVersion = CURRENT TIMESTAMP
WHERE whse_cd = v_whse_cd;
COMMIT;
ELSE
INSERT INTO o_warehouse(whse_cd, whse_desc, whse_adr1, whse_adr2,
whse_adr3, whse_adr4, whse_contact, whse_fax,
whse_conPerson, branch_cd,
SyncOperation, SyncVersion)
VALUES (v_whse_cd, v_whse_desc, v_whse_adr1, v_whse_adr2, v_whse_adr3,
v_whse_adr4, v_whse_contact, v_whse_fax, v_whse_conPerson,
v_branch_cd, 'A', CURRENT TIMESTAMP);
COMMIT;
END IF;
OPEN temp_cursor;
SET NOCOUNT OFF is not translated.
END
--->issue: db2 connect to genexp user express using password
--->result:
Database Connection Information
Database server = DB2/6000 8.1.0
SQL authorization ID = EXPRESS
Local database alias = GENEXP
--->issue: db2 put routine from SP_WAREHOUSE_INS.sql
--->result:
SQL0443N Routine "SYSFUN.PUT_ROUTINE_SAR" (specific name "PUT_SAR1PARM" has
returned an error SQLSTATE with diagnostic text "-20135, 55046, 1".
SQLSTATE=38000
--->issue: db2 -td@ -vf SP_ACTIVITY_INS.sql
--->result:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0035N The file "P1305522.msg" cannot be opened.
--->The content of SP_ACTIVITY_INS.sql:
CREATE PROCEDURE sp_activity_ins(
IN v_activity_cd VARCHAR(10),
IN v_activity_desc VARCHAR(40),
IN v_activity_type CHAR(1))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT v_activity_cd AS activity_cd
FROM TABLE (VALUES 1) AS temp_table;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
IF EXISTS
(SELECT activity_cd
FROM o_activity
WHERE activity_cd = v_activity_cd) THEN
UPDATE o_activity
SET activity_cd = v_activity_cd,
activity_desc = v_activity_desc,
activity_type = v_activity_type,
SyncOperation = 'U',
SyncVersion = CURRENT TIMESTAMP
WHERE activity_cd = v_activity_cd;
COMMIT;
ELSE
INSERT INTO o_activity(activity_cd, activity_desc, activity_type,
SyncOperation, SyncVersion)
VALUES (v_activity_cd, v_activity_desc, v_activity_type,
'A', CURRENT TIMESTAMP);
COMMIT;
END IF;
OPEN temp_cursor;
END@
--->sample SP_WAREHOUSE_INS.sql
CREATE PROCEDURE express.sp_warehouse_ins(
IN v_whse_cd VARCHAR(10),
IN v_whse_desc VARCHAR(40),
IN v_whse_adr1 VARCHAR(40),
IN v_whse_adr2 VARCHAR(40),
IN v_whse_adr3 VARCHAR(40),
IN v_whse_adr4 VARCHAR(40),
IN v_whse_contact VARCHAR(20),
IN v_whse_fax VARCHAR(40),
IN v_whse_conPerson VARCHAR(40),
IN v_branch_cd VARCHAR(10))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE l_sqlcode INT DEFAULT 0;
DECLARE temp_cursor CURSOR WITH HOLD WITH RETURN TO CLIENT FOR
SELECT v_whse_cd AS whse_cd
FROM TABLE (VALUES 1) AS temp_table;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND
SET l_sqlcode = SQLCODE;
IF EXISTS (SELECT whse_cd
FROM o_warehouse
WHERE whse_cd = v_whse_cd) THEN
UPDATE o_warehouse
SET whse_cd = v_whse_cd,
whse_desc = v_whse_desc,
whse_adr1 = v_whse_adr1,
whse_adr2 = v_whse_adr2,
whse_adr3 = v_whse_adr3,
whse_adr4 = v_whse_adr4,
whse_contact = v_whse_contact,
whse_fax = v_whse_fax,
whse_conPerson = v_whse_conPerson,
branch_cd = v_branch_cd,
SyncOperation = 'U',
SyncVersion = CURRENT TIMESTAMP
WHERE whse_cd = v_whse_cd;
COMMIT;
ELSE
INSERT INTO o_warehouse(whse_cd, whse_desc, whse_adr1, whse_adr2,
whse_adr3, whse_adr4, whse_contact, whse_fax,
whse_conPerson, branch_cd,
SyncOperation, SyncVersion)
VALUES (v_whse_cd, v_whse_desc, v_whse_adr1, v_whse_adr2, v_whse_adr3,
v_whse_adr4, v_whse_contact, v_whse_fax, v_whse_conPerson,
v_branch_cd, 'A', CURRENT TIMESTAMP);
COMMIT;
END IF;
OPEN temp_cursor;
SET NOCOUNT OFF is not translated.
END