CREATE DATABASE returns SQL ERROR SQL0101N
T.Paulus
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
Helmut Tessarek
> SQL0101N The statement is too long or too complex. SQLSTATE=54001
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.
*/
Lennart Jonsson
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
T. Paulus
Hi Helmut,
Here you can find the logfile:
http://www.file-upload.net/download-2620563/db2diag.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
T. Paulus
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
Mark A
news:ce74a1b8-cd94-49fd...@b35g2000yqi.googlegroups.com...
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.
Sivaswami Jeganathan
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...@nowhere.com> wrote:
> "T.Paulus" <p...@kapau.de> wrote in message
> that you reinstall with latest version this if you don't have it.- Hide quoted text -
>
> - Show quoted text -
Serge Rielau
(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
Mark A
(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.
Helmut Tessarek
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::::project=instlim
You can also try to search DB2 Express-C forum, if somebody has experienced
similar issues:
http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805
Helmut
T. Paulus
> "T.Paulus" <p...@kapau.de> wrote in message
I used the very last version of the DB2 Express-C Database (9.7.2).
The problem is reproduceable. I tried it on different VMWare products
and on a physical workstation. Just setup a new OpenSolaris
installation. Use all default settings during the installation
process. After that install a new DB2 Express-C database, also with
all default parameters.
If this setup is finished, you can try to setup a new database as
db2inst1 user. Now you will see the described error.
Anyway, I changed the server operating system because I need a quick
fix for that problem. I switched to a Linux instance and then it works
like expected. Now I have a working instance of a DB2 Express-C
database but the problem with OpenSolaris isn't solved yet. If you
find a solution, maybe sombody else can use your solution.
Nevertheless thank you very much for your support,
--paul
Serge Rielau
Have you posted this problem in teh Db2 Express C support forum?
http://www.ibm.com/developerworks/forums/forum.jspa?forumID=805