Hi everyone,

I got the error below when I want to create a table for DB2 database (create this table from a client to the DB2 server):
"Graphic data and graphic functions are not supported for this database. SQLSTATE: 56031, SQLCODE: -1216".

Could you please help me check it? thanks a lot

CREATE TABLE "schemaspe"."Product" (
"ProductKey" INTEGER NOT NULL,
"ProductAlternateKey" VARGRAPHIC(25),
"ProductSubcategoryKey" INTEGER,
"WeightUnitMeasureCode" GRAPHIC(3),
"SizeUnitMeasureCode" GRAPHIC(3),
"EnglishProductName" VARGRAPHIC(50) NOT NULL,
"SpanishProductName" VARGRAPHIC(50) NOT NULL,
"FrenchProductName" VARGRAPHIC(50) NOT NULL,
"StandardCost" DECIMAL(19,4),
"FinishedGoodsFlag" SMALLINT NOT NULL,
"Color" VARGRAPHIC(15) NOT NULL,
"SafetyStockLevel" SMALLINT,
"ReorderPoint" SMALLINT,
"ListPrice" DECIMAL(19,4),
"Size" VARGRAPHIC(50),
"SizeRange" VARGRAPHIC(50),
"Weight" DOUBLE,
"DaysToManufacture" INTEGER,
"ProductLine" GRAPHIC(2),
"DealerPrice" DECIMAL(19,4),
"Class" GRAPHIC(2),
"Style" GRAPHIC(2),
"ModelName" VARGRAPHIC(50),
"EnglishDescription" VARGRAPHIC(400),
"FrenchDescription" VARGRAPHIC(400),
"ChineseDescription" VARGRAPHIC(400),
"ArabicDescription" VARGRAPHIC(400),
"HebrewDescription" VARGRAPHIC(400),
"ThaiDescription" VARGRAPHIC(400),
"StartDate" TIMESTAMP,
"EndDate" TIMESTAMP,
"Status" VARGRAPHIC(7)
)


Thanks^_^
Winnie

The description for SQLCIDE -1216 in the Messages and Codes Vol 2 manual says:

"The code page of the database does not support graphic data."

With graphic data types, you need a code page for the database which supports double-byte character sets. This means UTF-8. This is specified when the database is created and cannot be changed.

Also, when you do try to create the table in a UTF-8 database, your row length is longer than 4K, so you need a bufferpool and a tablespace that is at least 8K (for this table I would suggest 32K bufferpool and tablespace unless most of the variable length columns will be empty).

If your new 8K tablespace is not part of the IBMDEFAULTGROUP then you will need to specify the 8K (or larger) tablespace in your create table command (IN TS8K after the last parenthesis), assuming that you named your tablespace TS8K.

Make sure you create the 8K (or larger) bufferpool first and then specify that bufferpool name when creating the tablespace.

I would also recommend that you create a system temporary tablespace with a page size equal to new tablespace (8K or larger) that would be used by DB2 for temporary work area for sorting result sets. DB2 comes with a 4K system temporary tablespace, but you need to create one for each page size of your regular tablespaces (or at least a larger page size).

One other thing.

You have created the table name and the column name with mixed case (upper and lower) in parenthesis. In DB2 this means that the programmers will have to use the exact case as in the create table, and may have use the parenthesis around the table name and column names in their SQL statements.

I would strongly suggest that you remove all the double quotes (") from your create table statement so that DB2 will accept upper or lower case names (case insensitive) in the SQL statements that access the table for the table name and column names.

If you don't make this change, chances are good that the programmers will lynch you. Even if you are installing a software package, it is possible that it will not work unless you use case-insensitive names.

Thanks lot Marcus
I did some change for my clause (please refer to below) according to your suggestion, and change the code page of the client (I want to create database via it) to UTF8, but still get the error above. Could you please give me more info about how to make the Code page to UTF8 if someone has created the database? I just want to create table in it. Thanks a lot

CREATE TABLE schemaspe.Product (
ProductKey INTEGER NOT NULL,
ProductAlternateKey VARGRAPHIC(25),
ProductSubcategoryKey INTEGER,
WeightUnitMeasureCode GRAPHIC(3),
SizeUnitMeasureCode GRAPHIC(3),
EnglishProductName VARGRAPHIC(50) NOT NULL,
SpanishProductName VARGRAPHIC(50) NOT NULL,
FrenchProductName VARGRAPHIC(50) NOT NULL,
StandardCost DECIMAL(19,4),
FinishedGoodsFlag SMALLINT NOT NULL,
Color VARGRAPHIC(15) NOT NULL,
SafetyStockLevel SMALLINT,
ReorderPoint SMALLINT,
ListPrice DECIMAL(19,4),
Size VARGRAPHIC(50),
SizeRange VARGRAPHIC(50),
Weight DOUBLE,
DaysToManufacture INTEGER,
ProductLine GRAPHIC(2),
DealerPrice DECIMAL(19,4),
Class GRAPHIC(2),
Style GRAPHIC(2),
ModelName VARGRAPHIC(50),
EnglishDescription VARGRAPHIC(200),
FrenchDescription VARGRAPHIC(200),
ChineseDescription VARGRAPHIC(200),
ArabicDescription VARGRAPHIC(200),
HebrewDescription VARGRAPHIC(200),
ThaiDescription VARGRAPHIC(200),
StartDate TIMESTAMP,
EndDate TIMESTAMP,
Status VARGRAPHIC(7)
)

Thanks
Winnie

The code page of the database on the database server must be UTF-8. It doesn't matter about the client (for the create table statement). The code page of an existing database cannot be changed.

Do you mean that if the code page is not UTF8 in the server, and I couldn't workaround this error? I have no access to that server, just have read and write permission to that Database Is there any workaround method?

No, there is no workaround if you have graphic data types. A new database will need to be created with code page UTF-8, since the code page cannot be changed on an existing database.

Starting in DB2 version 9.1 or 9.5 (I don't recall which one) UTF-8 is the default code page for new databases.

I mean, is there any datatype i can use to instead of graphic and vargraphic? I just want to create a table which their some columns are nchar and nvarchar in SQL Server. I'm a now one to DB2......

Thanks
Winnie

You still need double-byte characters to store Thai, Hebrew, Chinese, Arabic etc. However, most people use regular VARCHAR columns for this. You just need to increase the length by a factor of 3 (3 times bigger) because some Chinese characters take 3 bytes (even though it is called double-byte characters).

However, you can create a UNICODE table in a non-UNICODE database so long as there are no graphic or vargraphic.

But first you must change the alternate collating sequence of the database (you may not have authority to do these):

db2 connect to <db-name>
db2 update db cfg using ALT_COLLATE IDENTITY_16BIT;
db2 connect reset;
db2 force applications all (to make the change effective, all connections must be gone).

Then you can issue the following command (but if you store Chinese, Thai, Hebrew, Arabic, etc, in any of the varchar columns, multiply the size of the column by a factor of 3):

CREATE TABLE schemaspe.Product (
ProductKey INTEGER NOT NULL,
ProductAlternateKey VARCHAR(25),
ProductSubcategoryKey INTEGER,
WeightUnitMeasureCode VARCHAR(3),
SizeUnitMeasureCode VARCHAR(3),
EnglishProductName VARCHAR(50) NOT NULL,
SpanishProductName VARCHAR(50) NOT NULL,
FrenchProductName VARCHAR(50) NOT NULL,
StandardCost DECIMAL(19,4),
FinishedGoodsFlag SMALLINT NOT NULL,
Color VARCHAR(15) NOT NULL,
SafetyStockLevel SMALLINT,
ReorderPoint SMALLINT,
ListPrice DECIMAL(19,4),
Size VARCHAR(50),
SizeRange VARCHAR(50),
Weight DOUBLE,
DaysToManufacture INTEGER,
ProductLine VARCHAR(2),
DealerPrice DECIMAL(19,4),
Class VARCHAR(2),
Style VARCHAR(2),
ModelName VARCHAR(50),
EnglishDescription VARCHAR(200),
FrenchDescription VARCHAR(200),
ChineseDescription VARCHAR(200),
ArabicDescription VARCHAR(200),
HebrewDescription VARCHAR(200),
ThaiDescription VARCHAR(200),
StartDate TIMESTAMP,
EndDate TIMESTAMP,
Status VARCHAR(7)
)
CCSID UNICODE;

However, there are some extreme restrictions to having a UNICODE table in a non-UNICODE database. For example, you cannot join a UNICODE table with a non-UNICODE table:

"Tables or table functions created with CCSID ASCII, and tables or table functions created with CCSID UNICODE, cannot both be used in a single SQL statement (SQLSTATE 53090). This applies to tables and table functions referenced directly in the statement, as well as to tables and table functions referenced indirectly (such as, for example, through referential integrity constraints, triggers, materialized query tables, and tables in the body of views)."

For a list of the other restrictions, see the CREATE TABLE statement in the SQL Reference Vol 2.

Thannks a lot Marcus
I will try......

Winnie

That surprises me!
DB2 v8 for z/OS allows joining any two tables, of any encoding (including Unicode, ASCII and EBCDIC).
There goes my earlier belief that the "distributed world" (i.e., non-mainframe) would be superior when it comes to Unicode ...

Starting in DB2 LUW V9, all databases are created as UNICODE by default, so it will not be much of problem. Most sophisticated DB2 LUW shops already create all their databases in UNICODE.