Problem registering Stored Procedure

[N. Shamsundar]

A stored procedure (listed below) that loads fine on Windows XP with DB2
V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with
the following message:

sh-2.05a$ db2 -td@ -f pgpa.db2
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 "P2340425.msg" cannot be opened.

What does this message mean? Where are the .msg files located under
Linux? Thanks for your suggestions in advance.

The databases on XP and Linux are identical; the Linux DB2 is a
production server, the Express DB2 is a testbed. The stanzas in the SP,
executed with a number literal substituted for the parameter, works fine
on the Linux server.

N. Shamsundar
University of Houston
__________________________________________________ ______
FILE pgpa.db2 contents:
-- register using db2 -td@ -vf pgpa.db2
create procedure pgpa(in ssni int)
dynamic result sets 5
language sql
reads sql data
begin
declare c1 cursor with return to client for
select lname,fmname,strt,city,st,zip,phone from xli
where ssn=ssni
for read only;
declare c2 cursor with return to client for
select g.sem,decimal(g.shrs+g.suhrs,3,0) shrs,
decimal(cumsum(g.shrs+g.suhrs),3,0) chrs,
decimal(g.sgpa,4,2) sgpa,
decimal(cumsum(g.sgpa*g.shrs)/cumsum(g.shrs),4,2) cgpa,
decimal(m.shrs+m.suhrs,3,0) smhrs,
decimal(cumsum(m.shrs+m.suhrs),3,0) cmhrs,
decimal(m.sgpa,4,2) smgpa,
decimal(cumsum(m.sgpa*m.shrs)/cumsum(m.shrs),4,2) cmgpa
from gpahist as g
left outer join
mgpahist as m
on g.ssn=m.ssn and g.sem=m.sem
where g.ssn=ssni
for read only;
declare c3 cursor with return to client for
select sem,secno,course,instr,lgrd from grades
where ssn=ssni order by sem desc,course
for read only;
declare c4 cursor with return to client for
select sem,course,secno,instr,hrs,lgrd from transfer
where ssn=ssni order by sem desc,course
for read only;
declare c5 cursor with return to client for
select course,secno,instr,hrs from cursem
where ssn=ssni order by course
for read only;
open c1;
open c2;
open c3;
open c4;
open c5;
end
@

[Eric Herber]

N. Shamsundar wrote:

A stored procedure (listed below) that loads fine on Windows XP with DB2
V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with
the following message:

sh-2.05a$ db2 -td@ -f pgpa.db2
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 "P2340425.msg" cannot be opened.

What does this message mean? Where are the .msg files located under
Linux? Thanks for your suggestions in advance.

The databases on XP and Linux are identical; the Linux DB2 is a
production server, the Express DB2 is a testbed. The stanzas in the SP,
executed with a number literal substituted for the parameter, works fine
on the Linux server.

N. Shamsundar
University of Houston
__________________________________________________ ______
FILE pgpa.db2 contents:
-- register using db2 -td@ -vf pgpa.db2
create procedure pgpa(in ssni int)
dynamic result sets 5
language sql
reads sql data
begin
declare c1 cursor with return to client for
select lname,fmname,strt,city,st,zip,phone from xli
where ssn=ssni
for read only;
declare c2 cursor with return to client for
select g.sem,decimal(g.shrs+g.suhrs,3,0) shrs,
decimal(cumsum(g.shrs+g.suhrs),3,0) chrs,
decimal(g.sgpa,4,2) sgpa,
decimal(cumsum(g.sgpa*g.shrs)/cumsum(g.shrs),4,2) cgpa,
decimal(m.shrs+m.suhrs,3,0) smhrs,
decimal(cumsum(m.shrs+m.suhrs),3,0) cmhrs,
decimal(m.sgpa,4,2) smgpa,
decimal(cumsum(m.sgpa*m.shrs)/cumsum(m.shrs),4,2) cmgpa
from gpahist as g
left outer join
mgpahist as m
on g.ssn=m.ssn and g.sem=m.sem
where g.ssn=ssni
for read only;
declare c3 cursor with return to client for
select sem,secno,course,instr,lgrd from grades
where ssn=ssni order by sem desc,course
for read only;
declare c4 cursor with return to client for
select sem,course,secno,instr,hrs,lgrd from transfer
where ssn=ssni order by sem desc,course
for read only;
declare c5 cursor with return to client for
select course,secno,instr,hrs from cursem
where ssn=ssni order by course
for read only;
open c1;
open c2;
open c3;
open c4;
open c5;
end
@

You might try to create the stored procedure as instance
owner to see if this work.
Then you need to check the permissions on the following path:

<INSTHOME>/sqllib/function/routine/sqlproc/<database>/<schema>/tmp

DB2 creates temporary files here during the stored procedure
compilation phase. There might be insufficient permissions
on the filesystem here.

Best regards

Eric
--
IT-Consulting Herber
******
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

[N. Shamsundar]

Thank you, you hit the nail on the head. The .../tmp directory did not
have write permission for "other".

N. Shamsundar

Eric Herber wrote:

N. Shamsundar wrote:

A stored procedure (listed below) that loads fine on Windows XP with DB2
V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with
the following message:

sh-2.05a$ db2 -td@ -f pgpa.db2
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 "P2340425.msg" cannot be opened.

What does this message mean? Where are the .msg files located under
Linux? Thanks for your suggestions in advance.

The databases on XP and Linux are identical; the Linux DB2 is a
production server, the Express DB2 is a testbed. The stanzas in the SP,
executed with a number literal substituted for the parameter, works fine
on the Linux server.

N. Shamsundar
University of Houston
________________________________________________ ________
FILE pgpa.db2 contents:
-- register using db2 -td@ -vf pgpa.db2
create procedure pgpa(in ssni int)
dynamic result sets 5
language sql
reads sql data
begin
declare c1 cursor with return to client for
select lname,fmname,strt,city,st,zip,phone from xli
where ssn=ssni
for read only;
declare c2 cursor with return to client for
select g.sem,decimal(g.shrs+g.suhrs,3,0) shrs,
decimal(cumsum(g.shrs+g.suhrs),3,0) chrs,
decimal(g.sgpa,4,2) sgpa,
decimal(cumsum(g.sgpa*g.shrs)/cumsum(g.shrs),4,2) cgpa,
decimal(m.shrs+m.suhrs,3,0) smhrs,
decimal(cumsum(m.shrs+m.suhrs),3,0) cmhrs,
decimal(m.sgpa,4,2) smgpa,
decimal(cumsum(m.sgpa*m.shrs)/cumsum(m.shrs),4,2) cmgpa
from gpahist as g
left outer join
mgpahist as m
on g.ssn=m.ssn and g.sem=m.sem
where g.ssn=ssni
for read only;
declare c3 cursor with return to client for
select sem,secno,course,instr,lgrd from grades
where ssn=ssni order by sem desc,course
for read only;
declare c4 cursor with return to client for
select sem,course,secno,instr,hrs,lgrd from transfer
where ssn=ssni order by sem desc,course
for read only;
declare c5 cursor with return to client for
select course,secno,instr,hrs from cursem
where ssn=ssni order by course
for read only;
open c1;
open c2;
open c3;
open c4;
open c5;
end
@

You might try to create the stored procedure as instance
owner to see if this work.
Then you need to check the permissions on the following path:

<INSTHOME>/sqllib/function/routine/sqlproc/<database>/<schema>/tmp

DB2 creates temporary files here during the stored procedure
compilation phase. There might be insufficient permissions
on the filesystem here.

Best regards

Eric

[Amit]

Is the c/c++ compiler installed and configured for DB2 on the linux
machine? Unfortunately, DB2 requires an external c/c++ compiler to
create sql stored procs.
"N. Shamsundar" <sh******************@nospam.xyz> wrote in message news:<c1***********@masala.cc.uh.edu>...

A stored procedure (listed below) that loads fine on Windows XP with DB2
V8.1.4 Express fails to load on Linux DB2 Workgroup server V8.1, with
the following message:

sh-2.05a$ db2 -td@ -f pgpa.db2
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 "P2340425.msg" cannot be opened.

What does this message mean? Where are the .msg files located under
Linux? Thanks for your suggestions in advance.

The databases on XP and Linux are identical; the Linux DB2 is a
production server, the Express DB2 is a testbed. The stanzas in the SP,
executed with a number literal substituted for the parameter, works fine
on the Linux server.

N. Shamsundar
University of Houston
__________________________________________________ ______
FILE pgpa.db2 contents:
-- register using db2 -td@ -vf pgpa.db2
create procedure pgpa(in ssni int)
dynamic result sets 5
language sql
reads sql data
begin
declare c1 cursor with return to client for
select lname,fmname,strt,city,st,zip,phone from xli
where ssn=ssni
for read only;
declare c2 cursor with return to client for
select g.sem,decimal(g.shrs+g.suhrs,3,0) shrs,
decimal(cumsum(g.shrs+g.suhrs),3,0) chrs,
decimal(g.sgpa,4,2) sgpa,
decimal(cumsum(g.sgpa*g.shrs)/cumsum(g.shrs),4,2) cgpa,
decimal(m.shrs+m.suhrs,3,0) smhrs,
decimal(cumsum(m.shrs+m.suhrs),3,0) cmhrs,
decimal(m.sgpa,4,2) smgpa,
decimal(cumsum(m.sgpa*m.shrs)/cumsum(m.shrs),4,2) cmgpa
from gpahist as g
left outer join
mgpahist as m
on g.ssn=m.ssn and g.sem=m.sem
where g.ssn=ssni
for read only;
declare c3 cursor with return to client for
select sem,secno,course,instr,lgrd from grades
where ssn=ssni order by sem desc,course
for read only;
declare c4 cursor with return to client for
select sem,course,secno,instr,hrs,lgrd from transfer
where ssn=ssni order by sem desc,course
for read only;
declare c5 cursor with return to client for
select course,secno,instr,hrs from cursem
where ssn=ssni order by course
for read only;
open c1;
open c2;
open c3;
open c4;
open c5;
end
@