Create table with EBCDIC encoding in db2 i series

Question

I am working with AS400 system. I need to create tables with different encoding for validation task. I tested with

CREATE TABLE TESTSCH.EBCDIC_TBL (
COLUMN1 VARCHAR(100),
COLUMN2 INTEGER
) CCSID ebcdic;

However this is not working and throwing below error.

QL Error [42601]: [SQL0199] Keyword CCSID not expected

How to create a table with different encoding EBCDIC/ Unicode ASCII etc.

I am using Dbeaver to connect to AS400 system.

• mustaccio: What version of Db2 i? Did you have a chance to look at the CREATE TABLE syntax in documentation?
• Anand: Version 11. I followed this link ibm.com/docs/en/db2-for-zos/…
• mustaccio: There's no IBM i version 11, and your link is for z/OS.
• Charles: z/OS is not the same as IBM i (aka iSeries)...

Answer 1

The doc's you linked to in your comment are for Db2 for z/OS, which is not the same as IBM i (aka iSeries).

Here's the Db2 for IBM i docs for the CREATE TABLE statement.

And here's what your statement should look like assuming you want CCSID 37 US English

CREATE TABLE TESTSCH.EBCDIC_TBL (
COLUMN1 VARCHAR(100) ccsid 37,
COLUMN2 INTEGER
);

Other CCSID's can be found here

Also note that assuming a supported version of the OS, for Unicode UTF-16 you can use NVARCHAR instead of VARCHAR(xxx) ccsid 1200

CREATE TABLE TESTSCH.UTF16_TBL (
COLUMN1 NVARCHAR(100),
COLUMN2 INTEGER
);

• Anand: Thanks for clarification. Appreciate it.