minjiezen
asked on
SQL0035N The file "P5353255.msg" cannot be opened
Hi,
I'm trying to create a SQL stored procedure, but after I ran
db2 -td@ -vf getAllChanges.db2
I got the following error:
--------------------
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 "P5353255.msg" cannot be opened.
--------------------
There is a file called P5353255.sqc being created in /home/db2inst1/sqllib/func tion/routi ne/sqlproc /SOMEDB/DB 2INST1/tmp directory, and the group id and user name are correct, I wonder why it cannot open the file P5353255.msg. Obviously it could created the file in the first place. My SQL stored procedure is as follows:
CREATE PROCEDURE get_all_changes ( IN p_group_name VARCHAR(15),
IN p_inst_name VARCHAR(15),
IN p_num_of_days INT)
SPECIFIC get_all_changes
DYNAMIC RESULT SETS 1
LANGUAGE SQL
gac: BEGIN
DECLARE v_dynSQL VARCHAR(200);
DECLARE v_inst_num SMALLINT DEFAULT 1;
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE v_hist_table VARCHAR(30);
DECLARE v_stmt STATEMENT;
DECLARE c_hist CURSOR WITH RETURN TO CALLER FOR v_stmt;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_no_data=1;
SET v_hist_table = p_group_name||'_HIST';
SELECT inst_num INTO v_inst_num FROM inst_lookup WHERE group_name=p_group_name AND inst_name=p_inst_name FETCH FIRST 1 ROW ONLY;
IF (v_no_data=0) THEN
SET v_dynSQL = 'SELECT param_name, from_value, to_value, date_modified FROM ' ||
v_hist_table || ' WHERE inst_num=v_inst_num AND modified_date>=current timestamp - p_num_of_days days ORDER BY param_name';
PREPARE v_stmt FROM v_dynSQL;
OPEN c_hist;
END IF;
END gac@
Any help will be appreciated.
I'm trying to create a SQL stored procedure, but after I ran
db2 -td@ -vf getAllChanges.db2
I got the following error:
--------------------
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 "P5353255.msg" cannot be opened.
--------------------
There is a file called P5353255.sqc being created in /home/db2inst1/sqllib/func
CREATE PROCEDURE get_all_changes ( IN p_group_name VARCHAR(15),
IN p_inst_name VARCHAR(15),
IN p_num_of_days INT)
SPECIFIC get_all_changes
DYNAMIC RESULT SETS 1
LANGUAGE SQL
gac: BEGIN
DECLARE v_dynSQL VARCHAR(200);
DECLARE v_inst_num SMALLINT DEFAULT 1;
DECLARE v_no_data SMALLINT DEFAULT 0;
DECLARE v_hist_table VARCHAR(30);
DECLARE v_stmt STATEMENT;
DECLARE c_hist CURSOR WITH RETURN TO CALLER FOR v_stmt;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET v_no_data=1;
SET v_hist_table = p_group_name||'_HIST';
SELECT inst_num INTO v_inst_num FROM inst_lookup WHERE group_name=p_group_name AND inst_name=p_inst_name FETCH FIRST 1 ROW ONLY;
IF (v_no_data=0) THEN
SET v_dynSQL = 'SELECT param_name, from_value, to_value, date_modified FROM ' ||
v_hist_table || ' WHERE inst_num=v_inst_num AND modified_date>=current timestamp - p_num_of_days days ORDER BY param_name';
PREPARE v_stmt FROM v_dynSQL;
OPEN c_hist;
END IF;
END gac@
Any help will be appreciated.
SQL0035N The file "<name>" cannot be opened.
Explanation: The message file "<name>" could not be opened.
Binding or precompilation has been terminated.
User Response: Ensure that the system can access the file.
Explanation: The message file "<name>" could not be opened.
Binding or precompilation has been terminated.
User Response: Ensure that the system can access the file.
ASKER
Thanks for your response.
I tried to use db2fenc1 as user and got the same error.
My instance owner is db2inst1, which has more privillages than fenc group (it's db2fgrp1 in my system). Should I add db2inst1 to db2fgrp1?
I tried to use db2fenc1 as user and got the same error.
My instance owner is db2inst1, which has more privillages than fenc group (it's db2fgrp1 in my system). Should I add db2inst1 to db2fgrp1?
try that.. and also give permissions to db2fgrp1 to do read/write/execute in that folder..
hth
hth
ASKER
I added db2inst1 into db2fgrp1, so if I do the following, it shows that now db2inst1 belongs to 2 groups:
> groups db2inst1
db2inst1 : db2grp1 db2fgrp1
However, when i ran
db2 -td@ -vf getAllChanges.db2 (getAllChanges.db2 is now owned by db2fenc1 and belongs to db2fgrp1)
I got the same error again. If I go to
/home/db2inst1/sqllib/func tion/routi ne/sqlproc /SOMEDB/DB 2INST1/tmp ,
all *.sqc files are created by db2inst1 and belongs to group db2grp1, does that signify anything incorrect? I thought at least the one I ran as db2fenc1 should be created by db2fenc1. And I still don't understand why it can create one file (the .sqc file) but not the other (the .msg file), unless .msg file is not being created in the same directory.
Thanks.
> groups db2inst1
db2inst1 : db2grp1 db2fgrp1
However, when i ran
db2 -td@ -vf getAllChanges.db2 (getAllChanges.db2 is now owned by db2fenc1 and belongs to db2fgrp1)
I got the same error again. If I go to
/home/db2inst1/sqllib/func
all *.sqc files are created by db2inst1 and belongs to group db2grp1, does that signify anything incorrect? I thought at least the one I ran as db2fenc1 should be created by db2fenc1. And I still don't understand why it can create one file (the .sqc file) but not the other (the .msg file), unless .msg file is not being created in the same directory.
Thanks.
ASKER
Sorry, I meant if I go to
/home/db2inst1/sqllib/func tion/routi ne/sqlproc /SOMEDB/DB 2FENC1/tmp ,
......
/home/db2inst1/sqllib/func
......
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
askanivg, thanks a lot!! It worked after I added write permission to 'other' for each directory in the path
/home/db2inst1/sqllib/func tion/routi ne/sqlproc /SOMEDB/DB 2INST1/tmp /
Apparently the application is writing as 'other', instead of as db2inst1 or db2fenc1 (which I assumed it would be), to the above directory.
/home/db2inst1/sqllib/func
Apparently the application is writing as 'other', instead of as db2inst1 or db2fenc1 (which I assumed it would be), to the above directory.
another user "db2fenc1" (I think this is the default) with another group
db2fadmin (or something like that), try to use that user or to add your
instance owner to the fenc admin group.