473,573 Members | 2,865 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3599
"db2sysc" <db*****@yahoo. com> wrote in message
news:69******** *************** ***@posting.goo gle.com...
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.goo gle.com...
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*********@uk k.ibmm.comm> wrote in message news:<c6******* ***@gazette.alm aden.ibm.com>.. .
"db2sysc" <db*****@yahoo. com> wrote in message
news:69******** *************** ***@posting.goo gle.com...
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*********@uk k.ibmm.comm> wrote in message news:<c6******* ***@gazette.alm aden.ibm.com>.. .
"db2sysc" <db*****@yahoo. com> wrote in message
news:69******** *************** ***@posting.goo gle.com...
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.goo gle.com...
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.goo gle.com...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1643
by: global | last post by:
Hi, we have UDB 8.1.4 on W2K and defined federated database with nicknames, which cann't be seen in control center. (filter is not activ). Thx
1
2410
by: dbtoo_dbtoo | last post by:
Hello If we have nicknames created in DB1 to, say, three other databases (DB2,DB3, and DB4) in the same instance. How can one find out what table/database is a nickname pointing to? For example, how do I know if a nickname called S1.nick1 in DB1 is referencing DB2, DB3, or DB4? DB2 Version: 7 or 8 Platform: AIX Thanks
1
1860
by: _link98 | last post by:
On Linux or Solaris with UDB 8.2.2, can an SQL/PL stored-procedure use two-phase-commit with nicknames and if DB2 provides the Transaction-manager? i.e. in the same transaction, can an SQL/PL procedure update a local table and also update a nicknamed-table (in another database on the same hostname), before committing ? Is this...
37
2102
by: Art | last post by:
Hello everyone, I am interested in starting an all volunteer website which will be directed at recovering missing children. I am aware that there are few other sites out there with the same mission. However, My perspective I believe is a little different and more pro-active. And, the way I view... worst case scenario, we recover a few...
28
3013
by: Tamir Khason | last post by:
Follwing the struct: public struct TpSomeMsgRep { public uint SomeId;
3
6448
by: N. Spiker | last post by:
I am attempting to receive a single TCP packet with some text ending with carriage return and line feed characters. When the text is send and the packet has the urgent flag set, the text read from the socket is missing the last character (line feed). When the same text is sent without the urgent flag set, all of the characters are read. ...
1
2508
by: Prasad | last post by:
Hi, We have DB2 version 7 fixpak 14 on AIX 5.1. We have nicknames to another RDBMS tables. These nicknames have views and specification only indexes. I would like to know how we can extract the definition of these nicknames and related objects. db2look does not seem to have any option to extract this information. I tried the command...
1
2239
by: ebusiness | last post by:
I am trying to create nicknames for tables in a remote database. The federated database and remote database are both DB2 UDB V8.2, both OS are AIX. Could anyone tell me how to create a nickname for a remote table with distinct data type? I try to create a type maaping for the remote distinct data type, but the system always returns the...
0
1470
by: VikingDK | last post by:
Hi all Please bear with me, I'm still very inexperienced when it comes to DB2, so my questions might seem a little silly. With that covered, now on to the question :) I have a DB2 Enterprise Server installation on a local server. I've created a remote database connection from that server to a backend server which holds some data I need in my...
1
3302
by: DataFinder | last post by:
Not wanting to re-create the wheel, I have a need for a two-column table that links nicknames and given names if they are a derivative or source of another. Can you help? Using an Access database, I ultimately need to have Steve Jones come up as a potential match for Stephen Jones, based on a link through this auxiliary table (and matches...
0
7755
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7679
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8190
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8048
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6385
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5571
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5281
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2183
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1027
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.