Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Can anybody tell me where goes wron

Status
Not open for further replies.

billytcj

Programmer
Sep 11, 2003
6
MY
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



 
The error message in the put routine indicates :

H:\>db2 ? sql20135n

SQL20135N The specified SQL archive does not match the target
environment. Reason code = &quot;<reason-code>&quot;.

Explanation: The specified SQL archive does not match the target
environment for one of the following reasons:


1 The operating system of the target environment is not the same
as the operating system on which the SQL archive was created.

2 The database type and level of the target environment is not
the same as the database type and level on which the SQL archive
was created.

User Response: Ensure that the environment on which the SQL
archive was created matches the target environment and reissue
the command. If the environments do not match, you must manually
create the SQL routine using the target environment.

sqlcode: -20135

sqlstate: 55046



More DB2 questions answered at
 
ok. i found the way to deploy SP in different platform already! Thank a lot sathyarams.
 
Of course...
Actually above error occur is because i am using w2k (DB2 client) to deploy SP into target DB2 server in AIX.

Later on i switch them back to same platform but different machine. It worked. I would like to say sorry!! it is not &quot;across paltform&quot;, it is &quot;across machine but with same platform&quot;.

It is the constraint in DB2 to deploy SP, at least for version 8.1 now.

However ... just would like to share is when u would like to deploy many SP using command &quot;db2 -td@ -vf your_SP_file.sql&quot; u may compile all those command in a bat(for win) or sh (for unix) file, and simply launch it for deployement.

Or if u a Java Programmer, u may write a small Java application execute (via JDBC) the content of file your_SP_file.sql, it also can obtain the same objective!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top