469,325 Members | 1,526 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,325 developers. It's quick & easy.

URGENT HELP on NICKNAMES

We have defined a NICKNAME to go to Mainframe table and when do a
SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error.

The same select from V7 client works fine. The SELECT returns 5 rows.

From IBM support site,

http://www-1.ibm.com/support/search....c=SSEPGG&rs=71

I could see two problems opened for V8 namely JR19670 and
IY56225, but It is a protected link and I am not able to go through
it.


V8, db2level is:
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL08015"
with level identifier "02060106".
Informational tokens are "DB2 v8.1.0.48", "s040212", "MI00069", and
FixPak "5".
Product is installed at "/opt/IBM/db2/V8.1".
Nov 12 '05 #1
8 3403
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
We have defined a NICKNAME to go to Mainframe table and when do a
SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error.


Maybe:
o Increase the size of the statement heap (stmtheap) in the database
configuration file.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

C:\SQL>db2 ? SQL0101N
SQL0101N The statement is too long or too complex.

Explanation:

The statement could not be processed because it exceeds a system
limit for either length or complexity, or because too many
constraints or triggers are involved.

If the statement is one that creates or modifies a packed
description, the new packed description may be too large for its
corresponding column in the system catalogs.

Federated system users should also check to see if the
statement:

o Exceeds either a federated server system limit or a data
source system limit for length or complexity.

o Violates some other data source specific limit.

The statement cannot be processed.

Note: Where character data conversions are performed for
applications and databases running under different
codepages, the result of the conversion is exceeding the
length limit.

User Response:

Either:

o Break the statement up into shorter or less complex SQL
statements.

o Increase the size of the statement heap (stmtheap) in the
database configuration file.

o Reduce the number of check or referential constraints
involved in the statement or reduce the number of indexes on
foreign keys.

o Reduce the number of triggers involved in the statement.

o Federated system users: determine which data source is
failing the statement (see the problem determination guide
for procedures to follow to identify the failing data source)
and determine the cause of the rejection. If the rejection
is coming from the federated server, increase the size of the
statement heap (stmtheap) in the database configuration
file.

sqlcode : -101

sqlstate : 54001

Nov 12 '05 #2
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
We have defined a NICKNAME to go to Mainframe table and when do a
SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error.


Maybe:
o Increase the size of the statement heap (stmtheap) in the database
configuration file.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

C:\SQL>db2 ? SQL0101N
SQL0101N The statement is too long or too complex.

Explanation:

The statement could not be processed because it exceeds a system
limit for either length or complexity, or because too many
constraints or triggers are involved.

If the statement is one that creates or modifies a packed
description, the new packed description may be too large for its
corresponding column in the system catalogs.

Federated system users should also check to see if the
statement:

o Exceeds either a federated server system limit or a data
source system limit for length or complexity.

o Violates some other data source specific limit.

The statement cannot be processed.

Note: Where character data conversions are performed for
applications and databases running under different
codepages, the result of the conversion is exceeding the
length limit.

User Response:

Either:

o Break the statement up into shorter or less complex SQL
statements.

o Increase the size of the statement heap (stmtheap) in the
database configuration file.

o Reduce the number of check or referential constraints
involved in the statement or reduce the number of indexes on
foreign keys.

o Reduce the number of triggers involved in the statement.

o Federated system users: determine which data source is
failing the statement (see the problem determination guide
for procedures to follow to identify the failing data source)
and determine the cause of the rejection. If the rejection
is coming from the federated server, increase the size of the
statement heap (stmtheap) in the database configuration
file.

sqlcode : -101

sqlstate : 54001

Nov 12 '05 #3
Hey Paul.

Thanks. I am issuing the select from nickname from a Db2 client. The
database server has STMTHEAP at a good number. SQL0101N only happens
when the select is issued from a V8 client. The nickname is created to
go to a mainframe Db2 table.
When the nickname is access thru V7 instance, it gives the result.

Any pointers? The total recs of the select is only 5.

Thanks

"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<c6**********@gazette.almaden.ibm.com>...
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
We have defined a NICKNAME to go to Mainframe table and when do a
SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error.


Maybe:
o Increase the size of the statement heap (stmtheap) in the database
configuration file.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

C:\SQL>db2 ? SQL0101N
SQL0101N The statement is too long or too complex.

Explanation:

The statement could not be processed because it exceeds a system
limit for either length or complexity, or because too many
constraints or triggers are involved.

If the statement is one that creates or modifies a packed
description, the new packed description may be too large for its
corresponding column in the system catalogs.

Federated system users should also check to see if the
statement:

o Exceeds either a federated server system limit or a data
source system limit for length or complexity.

o Violates some other data source specific limit.

The statement cannot be processed.

Note: Where character data conversions are performed for
applications and databases running under different
codepages, the result of the conversion is exceeding the
length limit.

User Response:

Either:

o Break the statement up into shorter or less complex SQL
statements.

o Increase the size of the statement heap (stmtheap) in the
database configuration file.

o Reduce the number of check or referential constraints
involved in the statement or reduce the number of indexes on
foreign keys.

o Reduce the number of triggers involved in the statement.

o Federated system users: determine which data source is
failing the statement (see the problem determination guide
for procedures to follow to identify the failing data source)
and determine the cause of the rejection. If the rejection
is coming from the federated server, increase the size of the
statement heap (stmtheap) in the database configuration
file.

sqlcode : -101

sqlstate : 54001

Nov 12 '05 #4
Hey Paul.

Thanks. I am issuing the select from nickname from a Db2 client. The
database server has STMTHEAP at a good number. SQL0101N only happens
when the select is issued from a V8 client. The nickname is created to
go to a mainframe Db2 table.
When the nickname is access thru V7 instance, it gives the result.

Any pointers? The total recs of the select is only 5.

Thanks

"Paul Vernon" <pa*********@ukk.ibmm.comm> wrote in message news:<c6**********@gazette.almaden.ibm.com>...
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
We have defined a NICKNAME to go to Mainframe table and when do a
SELECT * FROM NICKNAME from a version 8 client we get SQL0101N error.


Maybe:
o Increase the size of the statement heap (stmtheap) in the database
configuration file.

Regards
Paul Vernon
Business Intelligence, IBM Global Services

C:\SQL>db2 ? SQL0101N
SQL0101N The statement is too long or too complex.

Explanation:

The statement could not be processed because it exceeds a system
limit for either length or complexity, or because too many
constraints or triggers are involved.

If the statement is one that creates or modifies a packed
description, the new packed description may be too large for its
corresponding column in the system catalogs.

Federated system users should also check to see if the
statement:

o Exceeds either a federated server system limit or a data
source system limit for length or complexity.

o Violates some other data source specific limit.

The statement cannot be processed.

Note: Where character data conversions are performed for
applications and databases running under different
codepages, the result of the conversion is exceeding the
length limit.

User Response:

Either:

o Break the statement up into shorter or less complex SQL
statements.

o Increase the size of the statement heap (stmtheap) in the
database configuration file.

o Reduce the number of check or referential constraints
involved in the statement or reduce the number of indexes on
foreign keys.

o Reduce the number of triggers involved in the statement.

o Federated system users: determine which data source is
failing the statement (see the problem determination guide
for procedures to follow to identify the failing data source)
and determine the cause of the rejection. If the rejection
is coming from the federated server, increase the size of the
statement heap (stmtheap) in the database configuration
file.

sqlcode : -101

sqlstate : 54001

Nov 12 '05 #5
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
Hey Paul.

Thanks. I am issuing the select from nickname from a Db2 client. The
database server has STMTHEAP at a good number.


What number? STMTHEAP no matter how big can always be blown if you issue
complex enougth SQL.

Check it on both your server and the server you are federating to.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #6
"db2sysc" <db*****@yahoo.com> wrote in message
news:69**************************@posting.google.c om...
Hey Paul.

Thanks. I am issuing the select from nickname from a Db2 client. The
database server has STMTHEAP at a good number.


What number? STMTHEAP no matter how big can always be blown if you issue
complex enougth SQL.

Check it on both your server and the server you are federating to.

Regards
Paul Vernon
Business Intelligence, IBM Global Services
Nov 12 '05 #7
A "select * from nickname" shouldn't blow the statement heap.
There is something fishy going on here.
There is nothing in there to use up memory that I coudl think of.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #8
A "select * from nickname" shouldn't blow the statement heap.
There is something fishy going on here.
There is nothing in there to use up memory that I coudl think of.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by dbtoo_dbtoo | last post: by
28 posts views Thread by Tamir Khason | last post: by
3 posts views Thread by N. Spiker | last post: by
reply views Thread by VikingDK | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.