What does the error (Reason "Can not enforced Maxcard > 1".) mean

Bob Sparks

I upgraded to stinger and got stung. Not all my code compiles. Most of
the problems are stinger seems to be more stringent on casting
varchars to vargraphics. This I am working out, however, one procedure
fails to compile and gives the following message.

[IBM][CLI Driver][DB2/NT] SQL0901N The SQL statement failed because
of a non-severe system error. Subsequent SQL statements can be
processed. (Reason "Can not enforced Maxcard > 1".) LINE NUMBER=67.
SQLSTATE=58004

Searches on google searches on maxcard db2 don't reveal much.

Any thoughts?

Bob

Nov 12 '05 #1

Jan M. Nelken

Bob Sparks wrote:

I upgraded to stinger and got stung. Not all my code compiles. Most of
the problems are stinger seems to be more stringent on casting
varchars to vargraphics. This I am working out, however, one procedure
fails to compile and gives the following message.

[IBM][CLI Driver][DB2/NT] SQL0901N The SQL statement failed because
of a non-severe system error. Subsequent SQL statements can be
processed. (Reason "Can not enforced Maxcard > 1".) LINE NUMBER=67.
SQLSTATE=58004

Searches on google searches on maxcard db2 don't reveal much.

Any thoughts?

Bob

Can you post here (or e-mail me: jasinek at attglobal dot net) smallest possible
example which demonstrates tyhis problem - including DDL to create objects needed?

Jan M. Nelken

Nov 12 '05 #2

Serge Rielau

Bob,

I have seen both issues.
In my experience the -901 appears only in procedures migrated from SQL
Server using MTK.. The result is some really weird SQL.
An example would be:
SET x = (SELECT DISTINCT c1 FROM T FETCH FIRST ROW ONLY)
(The "workaround" is to remove the DISTINCT)

Also I have seen issues with
SET varchar_v=vargraphic_v;
as trouble some.

Please open a PMR so we can verify you have indeed run into these known
issues.

Cheers
Serge

Nov 12 '05 #3

Bob Sparks

"Jan M. Nelken" <Un**********@Invalid.Domain> wrote in message news:<Ic********************@rogers.com>...

Bob Sparks wrote:

I upgraded to stinger and got stung. Not all my code compiles. Most of
the problems are stinger seems to be more stringent on casting
varchars to vargraphics. This I am working out, however, one procedure
fails to compile and gives the following message.

[IBM][CLI Driver][DB2/NT] SQL0901N The SQL statement failed because
of a non-severe system error. Subsequent SQL statements can be
processed. (Reason "Can not enforced Maxcard > 1".) LINE NUMBER=67.
SQLSTATE=58004

Searches on google searches on maxcard db2 don't reveal much.

Any thoughts?

Bob

Can you post here (or e-mail me: jasinek at attglobal dot net) smallest possible
example which demonstrates tyhis problem - including DDL to create objects needed?

Jan M. Nelken

Jan

My manager doesn't want me posting any real code. I have supplied some
similar code.

By divide and conquer I determined that it was not happy with the
"FETCH FIRST 1 ROWS ONLY"

here is the where clause it wasn't happy with

where x_value=coalesce(y_value,(select * from table
(fn_value_list('P')) as T FETCH FIRST 1 ROWS ONLY)) and value_cd =
v_input

had to be replaced by

where x_value=coalesce(y_value,(select min(value) from table
(fn_value_list('P')) as T FETCH FIRST 1 ROWS ONLY)) and value_cd =
v_input
notice I had to both explicilty name the single column and get a
single row by using min()

I have the same error message reported elsewhere but I have dug into
it yet.

I would say maxcard may mean "maximum cardinality" which means it
can't figure out how to get a single row.

Bob

Nov 12 '05 #4

Bob Sparks

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...

Bob,

I have seen both issues.
In my experience the -901 appears only in procedures migrated from SQL
Server using MTK.. The result is some really weird SQL.
An example would be:
SET x = (SELECT DISTINCT c1 FROM T FETCH FIRST ROW ONLY)
(The "workaround" is to remove the DISTINCT)

Also I have seen issues with
SET varchar_v=vargraphic_v;
as trouble some.

Please open a PMR so we can verify you have indeed run into these known
issues.

Cheers
Serge

Serge

You are correct this code did originate on MS-SQL and MTK or similar
was used. I thought it was the "FETCH FIRST ROW ONLY" causing the
problem but indeed I can can fix it by removing the "distinct" only.
The benefit that I get is slightly faster code.

Thanks for you insight.

Bob

Nov 12 '05 #5

Serge Rielau

:-) Now.. if you can in return solve teh puzzle and explain to me where
this DISTINCT is coming from that would help.

My assumption is
TSQL:
SELECT DISTINCT var = c1 FROM T;
=>
SQL/PSM Standard (produced by MTK):
SET var = (SELECT DISTINCT c1 FROM T FETCH FIRST ROW ONLY);

Why do TSQL developers use DISTINCT?
Is it to reduce the number of assignments to VAR? That could be
beneficial if c1 was indexed, otherwise it will backfire since the SORT
cost should be worse....

Cheers
Serge

Nov 12 '05 #6