DB2 Linux – Migrated Data with Special Characters Counted as 2 Characters

db2db2-luw

We are currently migrating data from DB2 10.1 AIX to DB2 11.1 Linux. Since we are using some features like versioning/history tables and rowchangetimestamps, db2move is not an option for some tables. As a result we do manual export and load for some tables.

The problem arrises for data having special character such as "Ñ".

Sample Data

PIÑATA

The AIX DB2 10.1 Counts the length for this as 6 characters and it fits fine to VARCHAR(6) columns. But when I load this to DB2 Linux 11.1, this is counted as 7 characters, thus data is truncated.

I tried CASTING this as VARCHAR(6) but the data is still truncated. I hope someone can point me to a possible solution.

Best Answer

Every character needs 1 byte in a single-byte database, but it may need from 1 to 4 bytes in a UTF-8 database.
When you specify VARCHAR(N) for a column, N is defined in string units.
OCTETS means bytes, and CODEUNITS32 means characters.
In your case N probably is OCTETS, and since Ñ character requires 2 bytes (OCTETS), then whole string length in the UTF-8 database is 7.
Character strings

Related Solutions

DB2 passwords with special characters

This is a long-standing bug in the GUI tools.

However, as long as your password does not contain a double quote (") character, then you can work around this by putting double quotes around the password to connect.

connect to sample user test using test!
--> SQL0104N

connect to sample user test using "test!"
--> connection succeeded.

This method will also work if the GUI tools prompt you for a password with a dialog box.

If you have passwords with double quotes in them, you may be out of luck. You could open a ticket with IBM DB2 support and/or just change the password.

Db2 – How to grant all privileges on all tables in a schema to a user in IBM DB2

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.

Best Answer

Related Solutions

DB2 passwords with special characters

Db2 – How to grant all privileges on all tables in a schema to a user in IBM DB2

Related Question