Hi folks,

I just installed a fresh OpenSolaris with the latest image available
(uname -a returns $ SunOS host01 5.11 snv_111b i86pc i386 i86pc
Solaris)

After installing OpenSolaris I set up also IBM DB2 Express C V9.7. The
installation process was successful and I tried to login as db2inst1
to create my first database.

$ db2 create database SAMPLE

This command returns an error. The error is:

SQL0101N The statement is too long or too complex. SQLSTATE=54001

I didn't any special configuration or setup modifications. Everything
runs straight forward. Do you have any idea what is the reason for
this error?

Cheers,
paul

Hi Paul,

Can you please post the last entries from db2diag.log (can be found in
sqllib/db2dump)?
Or zip the file and attach it to your next post.


--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On 2010-06-23 01:11, T.Paulus wrote:
You can ask db2 for more details regarding an error message via:

[db2inst1@... ~/bin] 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:
* Exceeds either a federated server system limit or a data source
system limit for length or complexity.
* 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:
* Break the statement up into shorter or less complex SQL statements.
* Increase the size of the statement heap (stmtheap) in the database
configuration file.
* Reduce the number of check or referential constraints involved in the
statement or reduce the number of indexes on foreign keys.
* Reduce the number of triggers involved in the statement.
* Federated system users: determine which data source is failing the
statement (see the Troubleshooting 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



/Lennart

On 23 Jun., 01:40, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Hi Helmut,

Here you can find the logfile:
http://www.file-upload.net/download-...2diag.log.html
It is cleaned and contains only the records from the beginning of the
CREATE DATABASE statement.

It seems like there are a couple of errors :/

Thanks for your help,
--paul

On 23 Jun., 06:07, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Thanks for your answer Lennart,

I already know how to get the error message and the error description
of a sqlcode # and a sqlstate #. I also found what this error means,
but imho it is not possible to write a CREATE DATABASE statement less
complex

Cheers,
--paul

"T.Paulus" <paul (AT) kapau (DOT) de> wrote

I realize that this does not solve your problem and that the above statement
should have worked, but is there some reason why you did not create the DB2
supplied sample database with db2sampl command?

Also, was the instance created during installation? What happens when you
run db2ilist?

Are you using the latest DB2 Express-C (9.7.2)? I would highly recommend
that you reinstall with latest version this if you don't have it.

2010-06-23-10.07.19.563112+120 I252478E528 LEVEL: Severe
PID : 599 TID : 49 PROC : db2sysc
INSTANCE: db2inst1 NODE : 000
APPHDL : 0-361 APPID: *LOCAL.db2inst1.100623080719
AUTHID : DB2INST1
EDUID : 49 EDUNAME: db2agent (instance)
FUNCTION: DB2 UDB, base sys utilities, sqleCreateDb, probe:65
MESSAGE : DB2_COMPATIBILITY_VECTOR string is invalid:
DATA #1 : Hexdump, 3 bytes
0x00000000004AD210 : 4D59 53 MYS




On Jun 23, 8:26*am, "Mark A" <no... (AT) nowhere (DOT) com> wrote:

Seems like the instance used by db2start isn't FP2.
(But the db2set used FP2 otherwise it would have complained...)

Cheers
Serge

PS: MYS gives you LIMIT/OFFSET

--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

I would drop the instance and reboot, and then create a new instance
(db2icrt with root - see the Command Reference manual) and then create new
database as instance owner. I have occasionally had problems when trying to
create an instance during the install.

Hi Paul,

Serge already answered your question: it seems that you are using a fp2
registry variable in a pre-fp2 instance.

What is the output of db2level?

Anyway, I'm not sure, if this is the only reason for your error message, since
you also have memory related messages in the db2diag.log.

I have recently worked with DB2 on Solaris/Intel, but I'm not a Solaris
expert, so please bear with me. If you are an experienced Solaris user, ignore
my next paragraphs.

In Solaris you _have_ to use at least the same amount of swap space as you
have physical memory. Solaris allocates swap space at the same time as it
allocates physical memory. (I had only 2GB of swap space defined, since I had
64GB physical memory. When I tried to allocate more than 2GB, I got memory
allocation errors.)

Solaris uses projects to handle user limits, so you will have to do something
like:

projadd -p 200 -c "instance user limits" \
-U db2inst1 \
-K "project.max-sem-ids=(priv,1024,deny)" \
-K "process.max-sem-nsems=(priv,2048,deny)" \
-K "project.max-shm-ids=(priv,256,deny)" \
-K "project.max-shm-memory=(priv,18446744073709551615,deny)" \
instlim

/etc/user_attr:
db2inst1:::roject=instlim

You can also try to search DB2 Express-C forum, if somebody has experienced
similar issues:

http://www.ibm.com/developerworks/fo...pa?forumID=805

Helmut

On 23.6.2010 04:28, T. Paulus wrote:

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/