SQL OK in 8.1.0 but not in 8.2 ?

[N. Shamsundar]

SYSTEM A) Linux RHEL3
-sh-2.05b$ uname -a
Linux Database.EGR.UH.EDU 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01 EST 2004 i686 i686 i386 GNU/Linux
-sh-2.05b$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".

SYSTEM B) Linux, RH8
sh-2.05a$ uname -a
Linux Database1.EGR.UH.EDU 2.4.20-28.7smp #1 SMP Thu Dec 18 11:18:31 EST 2003 i686 unknown
sh-2.05a$ db2level
DB21085I Instance "db2inst2" uses "32" bits and DB2 code release "SQL08010" with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".

Same table on both systems:

sh-2.05a$ db2 describe table grades

Column Type Type
name schema name Length Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
SSN SYSIBM INTEGER 4 0 No
SEM SYSIBM INTEGER 4 0 Yes
COURSE SYSIBM VARCHAR 8 0 Yes
DEPT SYSIBM CHARACTER 4 0 Yes
SECNO SYSIBM VARCHAR 5 0 Yes
INSTR SYSIBM VARCHAR 12 0 Yes
HRS SYSIBM DECIMAL 5 1 Yes
LGRD SYSIBM VARCHAR 2 0 Yes
GRD SYSIBM DECIMAL 4 2 Yes

9 record(s) selected.

On SYSTEM A, the query

select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem;

produces the error

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884

Whereas on SYSTEM B

the query is accepted and produces results.

N. Shamsundar
University of Houston

[AK]

> INSTR SYSIBM VARCHAR 12 0 Yes
can't verify at home, but INSTR surely sounds like a function name, isn't it?

[Ian]

N. Shamsundar wrote:

-sh-2.05b$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and
FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".
On SYSTEM A, the query

select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem;

produces the error

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

When you applied fixpack 7, did you follow all of the instructions in
the FixpackReadme.txt, including rebinding everything (there are three
binds that need to be done).

[Serge Rielau]

N. Shamsundar wrote:

SYSTEM A) Linux RHEL3
-sh-2.05b$ uname -a
Linux Database.EGR.UH.EDU 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01
EST 2004 i686 i686 i386 GNU/Linux -sh-2.05b$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and
FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".

SYSTEM B) Linux, RH8
sh-2.05a$ uname -a
Linux Database1.EGR.UH.EDU 2.4.20-28.7smp #1 SMP Thu Dec 18 11:18:31 EST
2003 i686 unknown sh-2.05a$ db2level
DB21085I Instance "db2inst2" uses "32" bits and DB2 code release
"SQL08010" with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".

Same table on both systems:

sh-2.05a$ db2 describe table grades

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
SSN SYSIBM INTEGER 4
0 No
SEM SYSIBM INTEGER 4
0 Yes
COURSE SYSIBM VARCHAR 8
0 Yes
DEPT SYSIBM CHARACTER 4
0 Yes
SECNO SYSIBM VARCHAR 5
0 Yes
INSTR SYSIBM VARCHAR 12
0 Yes
HRS SYSIBM DECIMAL 5
1 Yes
LGRD SYSIBM VARCHAR 2
0 Yes
GRD SYSIBM DECIMAL 4
2 Yes

9 record(s) selected.

On SYSTEM A, the query

select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem;

produces the error

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

Whereas on SYSTEM B

the query is accepted and produces results.

N. Shamsundar
University of Houston

Smells like query rewrite... Try this (replaced one E with an X):
select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='XLEE2331') and hrs > 0
group by sem;

Also I would like to see the result of:
VALUES CURRENT PATH

Cheers
Serge

[N. Shamsundar]

Serge Rielau wrote:

N. Shamsundar wrote:

SYSTEM A) Linux RHEL3
-sh-2.05b$ uname -a
Linux Database.EGR.UH.EDU 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24
20:34:01 EST 2004 i686 i686 i386 GNU/Linux -sh-2.05b$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and
FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".

SYSTEM B) Linux, RH8
sh-2.05a$ uname -a
Linux Database1.EGR.UH.EDU 2.4.20-28.7smp #1 SMP Thu Dec 18 11:18:31
EST 2003 i686 unknown sh-2.05a$ db2level
DB21085I Instance "db2inst2" uses "32" bits and DB2 code release
"SQL08010" with level identifier "01010106".
Informational tokens are "DB2 v8.1.0.0", "s021023", "", and FixPak "0".
Product is installed at "/opt/IBM/db2/V8.1".

Same table on both systems:

sh-2.05a$ db2 describe table grades

Column Type Type
name schema name Length
Scale Nulls
------------------------------ --------- ------------------ --------
----- ------
SSN SYSIBM INTEGER
4 0 No
SEM SYSIBM INTEGER
4 0 Yes
COURSE SYSIBM VARCHAR
8 0 Yes
DEPT SYSIBM CHARACTER
4 0 Yes
SECNO SYSIBM VARCHAR
5 0 Yes
INSTR SYSIBM VARCHAR
12 0 Yes
HRS SYSIBM DECIMAL
5 1 Yes
LGRD SYSIBM VARCHAR
2 0 Yes
GRD SYSIBM DECIMAL
4 2 Yes

9 record(s) selected.

On SYSTEM A, the query

select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem;

produces the error

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

Whereas on SYSTEM B

the query is accepted and produces results.

N. Shamsundar
University of Houston
Smells like query rewrite... Try this (replaced one E with an X):
select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='XLEE2331') and hrs > 0
group by sem;

Also I would like to see the result of:
VALUES CURRENT PATH

Cheers
Serge

Here you are:

Changing to XLEE did not change the response:

N. Shamsundar
Database server = DB2/LINUX 8.2.0
SQL authorization ID = DB2INST1
Local database alias = EGDB

db2 => values current path

1
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
----------------------------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","DB2INST1"

1 record(s) selected.

db2 => quit
DB20000I The QUIT command completed successfully.
-sh-2.05b$ db2 -t
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 SDK 8.2.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => select sem,count(ssn)
from graddb2 (cont.) => es g
where (coudb2 (cont.) => rse='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem; db2 (cont.) =>
SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884
db2 => select sem,count(ssn)
from gradb2 (cont.) => des g
where (cdb2 (cont.) => ourse='ECE 2331' or course='XLEE2331') and hrs > 0
group by sedb2 (cont.) => m;
SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884

[N. Shamsundar]

Ian wrote:

N. Shamsundar wrote:

-sh-2.05b$ db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.0.64", "s040812", "MI00086", and
FixPak "7".
Product is installed at "/opt/IBM/db2/V8.1".
On SYSTEM A, the query

select sem,count(ssn)
from grades g
where (course='ECE 2331' or course='ELEE2331') and hrs > 0
group by sem;

produces the error

SQL0440N No authorized routine named "SUBSTR" of type "FUNCTION"
having compatible arguments was found. SQLSTATE=42884

When you applied fixpack 7, did you follow all of the instructions in
the FixpackReadme.txt, including rebinding everything (there are three
binds that need to be done).

Thanks.

This was after a fresh install on a new server (the sysadmins did the install; they had some problems with the Java versions and had to do more than one attempt before succeeding). Should I still go in and do the rebinds?

N. Shamsundar

[N. Shamsundar]

AK wrote:

INSTR SYSIBM VARCHAR 12 0 Yes

can't verify at home, but INSTR surely sounds like a function name, isn't it?

Thanks.

Yes, but no such function found by looking in InfoCenter, and I did not create any such function.

Just to see, I dropped and recreated the table changing "INSTR" to "TEACHER", and I still ran into the same problem.

N. Shamsundar

[Ian]

This was after a fresh install on a new server (the sysadmins did the
install; they had some problems with the Java versions and had to do
more than one attempt before succeeding). Should I still go in and do
the rebinds?

Well, it wouldn't hurt to follow the steps in the FixpackReadme again,
for the binds.

[Serge Rielau]

Can you send me a full repro script by email? I'm not able to rero with
the information given.

Cheers
Serge