Difference between revisions of "Database Tips"

From Opentaps Wiki
Jump to navigationJump to search
(DB2 Tips)
(DB2 Tips)
Line 39: Line 39:
To figure out what it is, you have to run db2 from the command line:
To figure out what it is, you have to run db2 from the command line:
$ db2 ? sql-530
$ db2 ? sql-270

Revision as of 22:36, 19 August 2008

MySQL Tips

Table Name Case Sensitivity

If you use Linux or Unix for your MySQL server, the table names may be case sensitive, so PRODUCT and product are not the same table. You can turn this off by configuring mysqld on startup to ignore table names with the lower-case-table-names flag, such as this example from /etc/init.d/mysql:

 $bindir/mysqld_safe --datadir=$datadir --lower-case-table-names=1 --pid-file=$server_pid_file $other_args >/dev/null 2>&1 &

See MySQL manual on identifier case sentivity

UTF-8 Support

By default, MySQL supports the Latin1 character set, which is intended for European languages such as English. If you wish to use MySQL for other language types, you may need to set up a database or UTF-8 character set encoding. To do this, you would need to create your database using UTF-8 first:

   mysql> create database opentaps default character set utf8 collate utf8_general_ci;

Then you would need to set your framework/entity/config/entityengine.xml file for the MySQL database to use the UTF-8 character set:


Note that it is not clear that my SQL supports case sensitive UTF-8 coalition at this point, although you may be able to use UTF-8 binary collation.

DB2 Tips

You must configure DB2 to have tablespaces of 8K or more. This can be done when you create the database from the Control Center: DB2 tablespace.PNG

If you get an error message from DB2, you will get a SQLCODE like below:

DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=63, DRIVER=3.50.152

To figure out what it is, you have to run db2 from the command line:

$ db2 ? sql-270

Some of the more popular codes are:

  • SQL-204: <name> not recognized. Most likely, you are referencing a table that doesn't exist.
  • SQL-270: Function not supported. See the SQLERRMC for the message code. If you get sql-270 with sqlerrmc=63, it means that you are trying to select a CLOB/BLOB type with a scroll insensitive cursor.
  • SQL-286: insufficient page size for CREATE TABLE
  • SQL-530: foreign key violation
  • SQL-803: operation violates a unique value constraint

Check Valid DB2 field types for correct field types.

DB2 does not support scrollable resultsets for certain data types (see [1] and about resultset types).