Db2

Did you check limitation for index key:

Looks like a bug in 12.1. It hangs on non-Docker 12.1.1 as well.
It works on 11.5.
Even we have some index limitations, the query shouldn't hang of course.
Moreover, the connection initiated this query can't be forced off the database.

You may open a Case with IBM Support if you are interested...

PS.

I verified this on my V12.1.2 - your DDL executed on non-docker Db2 correctly presents an error message:

db2inst1@VMUbuntu2404:~$ db2 -tvf test.db2 
create schema "my_schema"
DB20000I  The SQL command completed successfully.

create table "my_schema"."my_table" ( "id" INT NOT NULL PRIMARY KEY, "text" VARCHAR(32672) )
DB20000I  The SQL command completed successfully.

create index "my_table_text" on "my_schema"."my_table" ("text")
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "my_table_text" cannot be created or 
altered because the combined length of the specified columns is too long.  
SQLSTATE=54008

Hanging may be bug in V12.1.1 - or it may be some weird limitation.

In what tablespace you create table and index?

What is pagesize of this tablespace?

What is default table organization - row or column?

I will try to test it on V12.1.1 in one of my VM's when you provide those answers.

OK - retried with Db2 V12.1 at this level:

db2inst1@DESKTOP-F5VOQKN:~$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL12010" with level
identifier "02010110".
Informational tokens are "DB2 v12.1.0.0", "special_50594",
"DYN2411071022AMD64_50594", and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V12.1".


Db2 reports correct error message:

db2inst1@DESKTOP-F5VOQKN:~$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 12.1.0.0
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

db2inst1@DESKTOP-F5VOQKN:~$ db2 -tvf test.db2
create schema "my_schema"
DB20000I  The SQL command completed successfully.

create table "my_schema"."my_table" ( "id" INT NOT NULL PRIMARY KEY, "text" VARCHAR(32672) )
DB20000I  The SQL command completed successfully.

create index "my_table_text" on "my_schema"."my_table" ("text")
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "my_table_text" cannot be created or
altered because the combined length of the specified columns is too long.
SQLSTATE=54008

Error message explanation:

db2inst1@DESKTOP-F5VOQKN:~$ db2 ? sql0614n

SQL0614N  The index or index extension "<index-name>" cannot be created
      or altered because the combined length of the specified columns is
      too long.

Explanation:

The index could not be created or altered because the sum of the key
column internal lengths would exceed the index key length limit. Also,
an index cannot be created using a LONG VARCHAR, LONG VARGRAPHIC, or LOB
column. If the index is defined on a typed table, there is additional
index overhead that reduces the maximum length by 4 bytes. The index may
be altered by an ALTER TABLE or ALTER NICKNAME statement that alters the
data type of one or more columns.

An index extension could not be created because the sum of the columns
returned by the GENERATE KEY function would exceed the index key length
limit.

The index key length limit is based on the page size of the tablespace
used by the index:

Max Key Length  Page size
--------------  ---------
1K              4K
2K              8K
4K              16K
8K              32K

The statement cannot be processed. The specified index or index
extension was not created or the table or nickname could not be altered.

User response:

To modify the index definition or alter column, eliminate one or more
key columns and reduce the key length to the permitted maximum. For an
index extension definition, specify a different GENERATE KEY function,
or redefine the function to reduce the length of the returned row.

sqlcode: -614

sqlstate: 54008

Hi,

I think the magic inbetween lies in the ALTER COLUMN before the CREATE INDEX.

Maybe it is an indirect hint, Db2 needs a REORG of the table before the CREATE INDEX.

I'm currently not in the situation to test this and run 'db2pd -d dbname -locks' in a separate session to check, if there is some locking in the play.

With the Db2 V12.1.1 - given the proper bufferpool definition, tablespace definition and index key length - it works:

db2inst1@DESKTOP-F5VOQKN:~$ db2 create bufferpool my_pool size automatic pagesize 32k
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 create tablespace my_space pagesize 32k managed by automatic storage bufferpool my_pool
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 create temporary tablespace my_temp pagesize 32k managed by automatic storage bufferpool my_pool
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 -tvf test.db2
create table "my_schema"."my_table" ( "id" INT NOT NULL PRIMARY KEY, "text" VARCHAR(8000) ) in my_space
DB20000I  The SQL command completed successfully.
create index "my_table_text" on "my_schema"."my_table" ("text")
DB20000I  The SQL command completed successfully.

Note - index key length comes from SQL and XML Limits:

This issue is not new to Db2 12.1, it's visible in 11.5.9.0 CSB 55285.  The EDU sits in UOW-Executing state, consuming CPU.  This appears to be an issue when EXTENDED_ROW_SZ is enabled (which is the default).

It definitely looks like a bug in the code.  Even performing an offline reorg after changing the "text" column to VARCHAR(128) does not resolve the issue.

When EXTENDED_ROW_SZ is disabled, you can't even create this table in a 32kb tablespace, because the row length exceeds the maximum row length (32677 bytes) for a 32k tablespace.

Reducing it from VARCHAR(32672) to VARCHAR(32668) will resolve the issue IF you create the table in a 32kb tablespace (even when EXTENDED_ROW_SZ is enabled).

Hi,

are you using AMD processors?

There is a known problem with the crypt library that causes exactly that behavior.

the can be fixed by changing a setting in the crypt library to use different algorithm to generate random numbers.

it took me weeks to find that out.

If you think that this could be the solution, i will look it up.

regards,

Nikolai

Given the all limitation:

Maximum length of VARCHAR (in bytes or OCTETS) - 32,672
Maximum length of a row in a row-organized table, including all overhead - 32,677
Maximum length of an index key, including all overhead - index pagesize/4

this is a maximum working settings:

db2inst1@DESKTOP-F5VOQKN:~$ db2 create bufferpool bp size automatic pagesize 32k
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 create tablespace ts pagesize 32k managed by automatic storage bufferpool bp
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 create temporary tablespace tts pagesize 32k managed by automatic storage bufferpool bp
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(C1 int not null primary key, text varchar(32672)) in ts"
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "I" cannot be created or altered
because the combined length of the specified columns is too long.
SQLSTATE=54008

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table t
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(C1 int not null primary key, text varchar(8192)) in ts"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "I" cannot be created or altered
because the combined length of the specified columns is too long.
SQLSTATE=54008

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table t
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(C1 int not null primary key, text varchar(8190)) in ts"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "I" cannot be created or altered
because the combined length of the specified columns is too long.
SQLSTATE=54008

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table t
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(C1 int not null primary key, text varchar(8189)) in ts"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB20000I  The SQL command completed successfully.

db2inst1@DESKTOP-F5VOQKN:~$ db2 get db cfg | grep Ext
 Extended row size support             (EXTENDED_ROW_SZ) = ENABLE

db2inst1@DESKTOP-F5VOQKN:~$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL12012" with level
identifier "02030110".
Informational tokens are "DB2 v12.1.2.0", "s2503011430", "DYN2503011430AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V12.1".

Interesting experiments by @Jan Nelken, but it just confirms the docs that at least 4 index entries have to fit on an index page.

@Ian hits the point with EXTENDED_ROW_SZ and the EDU is spinning around. And @Nicolai mentions the issue with AMD cores.

So my question is: Jan, are you on AMD or Intel? Anyone listening and running on AMD who can confirm?

Sorry @Roland - I am traditional:

My CPU is 13th Gen Intel(R) Core(TM) i7-1355U   1.70 GHz

I do not think this has anything to do with Intel vs AMD.  The reason Jan is getting different results is that he is missing the ALTER TABLE step to reduce the size of the VARCHAR column that the OP included.  

The key to reproducing the issue here is that the ORIGINAL text column exceeds the page size maximum row length (for whatever page size you’re using), AND that EXTENDED_ROW_SZ is on when you create the table.  

The ALTER TABLE statement “should” fix the row length issue - it changes the text column to VARCHAR(128) 

As I said earlier, even adding an offline REORG after reducing the column length does not prevent the issue.

@Ian 

I would assume that firstly it would be beneficial to verify what the correct settings are to create table and index rather than trying to explain what is happening with ALTER command when table definition is incorrect; I may be wrong (it is known to happen before).
I will play with the scenario with ALTERing table definition after creation of the table with acceptable column length, which is unacceptable for index creation.
Knowing the limits has some merits, though.

OK - that was uneventful:

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table T
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(c1 int not null primary key, text varchar(32672)) in ts"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "alter table t alter column text set data type varchar(8189)"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table t
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t(c1 int not null primary key, text varchar(32672)) in ts"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "alter table t alter column text set data type varchar(8190)"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0614N  The index or index extension "I" cannot be created or altered
because the combined length of the specified columns is too long.
SQLSTATE=54008

Thank you all for your answers, and sorry for not being able to get back to you earlier.

To help you reproduce the issue, below are the container settings I am using on a Mac OS with an M4 chip. But I have the same issue on an Intel chip

docker run -d \
  --name db2 \
  --platform linux/x86_64 \
  --restart always \
  --privileged \
  -p 50000:50000 \
  --env DB2INSTANCE=db2inst1 \
  --env DB2INST1_PASSWORD=db2inst1 \
  --env DBNAME=test_db \
  --env LICENSE=accept \
  --env IS_OSXFS=true \
  --env PERSISTENT_HOME=true \
  icr.io/db2_community/db2:12.1.1.0

I am experiencing the same issue on Ubuntu as well.

The schema and table creation I shared in my first post are located in the test_db database created by the installation script of the container by setting the environment variable DBNAME
I did not alter any default settings of Db2.

To answer your questions: 
- The table is created in the default tablespace USERSPACE1, which has a page size of 4096
- Extended row size is enabled
- The container startup script that creates the `test_db` database uses the following statement:
  db2 create db test_db using codeset utf-8 territory us collate using identity
- The table organization is ROW

I would like to contact the IBM software support about this, but as far as I know, I can't contact them without a support license.

Hi,

I have looked up the solution we found for a similar problem:

echo ICC_SHIFT=3 >> /opt/ibm/db2/V11.5/lib64/gskit_db2/N/icc/icclib/ICCSIG.txt
echo ICC_SHIFT=3 >> /opt/ibm/db2/V11.5/lib64/gskit_db2/C/icc/icclib/ICCSIG.txt

You might need to correct the path to the corresponding version.

We have created own docker containers form the IBM db2 container, by adding the following lines in the Dockerfile:

# SETTING ICC_SHIFT=3 as workaround for problems with AMD CPU
RUN echo ICC_SHIFT=3 >> /opt/ibm/db2/V11.5/lib64/gskit_db2/N/icc/icclib/ICCSIG.txt && \
    echo ICC_SHIFT=3 >> /opt/ibm/db2/V11.5/lib64/gskit_db2/C/icc/icclib/ICCSIG.txt

I hope that also might help you.

Regards,

Nikolai

@Vincent,

In Db2 V12.1.2 default table space pagesize is set to 8192; did you explicitly dropped and recreated tablespace USERSPACE1 with PAGESIZE 4096?
In any case - this test (when USERSPACE1 table space has 8k pages) completes successfully - as long as use allowed length for both creating table and altering table:

db2inst1@DESKTOP-F5VOQKN:~$ db2 drop table t
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create table t (id int not null primary key, text varchar(32672))"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "alter table t alter column text set data type varchar(8189)"
DB20000I  The SQL command completed successfully.
db2inst1@DESKTOP-F5VOQKN:~$ db2 "create index i on t(text)"
DB20000I  The SQL command completed successfully.

 

@Jan Nelken
DB2 12.1.2.0 has not been released yet.
The DB2 for LUW default page size is still 4K on all currently publicly available versions and releases including the latest one: 12.1.1.0.
Don't you mix it up with a SAMPLE database created with the `db2sampl` utility, where the database page size is really 8K?

@MARK BARINSTEIN

Sorry Mark - you are correct: I use Sample for quick and dirty tests.

V12.1.2 should be released anytime - so they promise :-)

With newly created db Test (all defaults) I reproduced the issue on V12.1.2 as well :-(

So @Vincent Guilpain - can you create tablespace USERSPACE2 with pagesize 8192 and retry?

Funny as it sounds - this can be reproduced with tablespace page size 4k where both table and index are going to be created.

It seems, that it hangs in libc.so.6:
Here is an excerpt from `db2pd -stack <edu>`:

<StackTrace>
-----FUNC-ADDR---- ------FUNCTION + OFFSET------
0x00007F990D85756F _Z25ossDumpStackTraceInternalmR11OSSTrapFileiP9siginfo_tPvmm + 0x00ef 
		(/home/db2inst1/sqllib/lib64/libdb2osse.so.1)
0x00007F990D8582E5 ossDumpStackTraceV98 + 0x0025 
		(/home/db2inst1/sqllib/lib64/libdb2osse.so.1)
0x00007F990D853C45 address: 0x00007F990D853C45 ; dladdress: 0x00007F990D5C4000 ; offset in lib: 0x000000000028FC45 ; 
		(/home/db2inst1/sqllib/lib64/libdb2osse.so.1)
0x00007F991AC1EDFB sqlo_trce + 0x07db 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991ADBA03F sqloDumpDiagInfoHandler + 0x025f 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F990D01A520 address: 0x00007F990D01A520 ; dladdress: 0x00007F990CFD8000 ; offset in lib: 0x0000000000042520 ; 
		(/lib/x86_64-linux-gnu/libc.so.6)
0x00007F991BF280D5 _Z18sqlrl_create_indexP8sqlrr_cbPhsS1_sS1_sS1_sP11sqlrg_xinfosmS1_P16sqlrl_index_parmsP22sqlrl_extendedIdx_infomiiP18sqlrg_xmlIndexInfoP16SQLD_SCANINXINFOP27sqlnqIndexExpTextAggregatorb + 0x6f65 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991A46BCB5 _Z22sqlnq_create_index_endP9sqlnq_qtbiii + 0x1de5 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991A478600 _Z23sqlnq_create_index_stmtPP8stknode_i10actiontypePhP3loc + 0x03c0 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9919F0FD55 _Z12sqlnp_smactnP8sqlnp_cbi + 0x02d5 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9919F03817 _Z12sqlnp_parserP8sqlnp_cb + 0x1217 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9919F05A00 _Z10sqlnp_mainP12sqlnq_stringbP3locPP9sqlnq_qur + 0x0e20 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F99199AB317 _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sqlrr_cmpl_enviiPP9sqlnq_qurP12sqlrr_modifyP12sqlrr_sqlopt + 0x1b77 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F99199B647E _Z10sqlnn_cmplP8sqeAgentP11sqlrrstrings17sqlnn_compileModesP14sqlrr_cmpl_envP12sqlrr_modifyP12sqlrr_sqlopt + 0x001e 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991B899E22 _Z17sqlra_compile_varP8sqlrr_cbP14sqlra_cmpl_envPhitiiiiiP14SQLP_LOCK_INFOP16sqlra_cached_varPiS5_Pb + 0x17d2 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991B8CD738 _Z14sqlra_find_varP8sqlrr_cbP17sqlra_cached_stmt13sqlra_stmt_idjjPhjthP14sqlra_cmpl_env15sqlra_fill_modePiiS8_iiiS8_P14SQLP_LOCK_INFOPP16sqlra_cached_varS8_bSA_Pb + 0x1b38 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991B97CC0D _Z13sqlra_get_varP8sqlrr_cbiibPbS1_ + 0x1fed 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991BB408F7 _Z21sqlri_ddl_get_sectionP8sqlrr_cb + 0x0107 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991BB441E2 _Z16sqlri_ddl_commonP8sqlrr_cb + 0x0432 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991BB44B07 _Z8sqlriddlP8sqlrr_cb + 0x0027 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991BB830BF _Z15sqlriSectInvokeP8sqlrr_cbP12sqlri_opparm + 0x054f 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991B5D4C14 _Z23sqlrr_execute_immediateP8sqlrr_cbi + 0x08c4 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991B5A1D8B _Z14sqlrr_execimmdP14db2UCinterfaceP16db2UCprepareInfo + 0x010b 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9918F32D22 _Z19sqljs_ddm_excsqlimmP14db2UCinterfaceP13sqljDDMObject + 0x1e82 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9918EF40BB _Z21sqljsParseRdbAccessedP13sqljsDrdaAsCbP13sqljDDMObjectP14db2UCinterface + 0x03eb 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9918EF4587 _Z10sqljsParseP13sqljsDrdaAsCbP14db2UCinterfaceP8sqeAgentb + 0x03d7 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9918EEC0BF address: 0x00007F9918EEC0BF ; dladdress: 0x00007F990DBD0000 ; offset in lib: 0x000000000B31C0BF ; 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F9918EEF670 _Z17sqljsDrdaAsDriverP18SQLCC_INITSTRUCT_T + 0x0f70 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F99187AFD90 _ZN8sqeAgent6RunEDUEv + 0x0980 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991CCE37B4 _ZN9sqzEDUObj9EDUDriverEv + 0x01a4 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F991ADBBD0E sqloEDUEntry + 0x028e 
		(/home/db2inst1/sqllib/lib64/libdb2e.so.1)
0x00007F990D06CAC3 address: 0x00007F990D06CAC3 ; dladdress: 0x00007F990CFD8000 ; offset in lib: 0x0000000000094AC3 ; 
		(/lib/x86_64-linux-gnu/libc.so.6)
0x00007F990D0FE850 address: 0x00007F990D0FE850 ; dladdress: 0x00007F990CFD8000 ; offset in lib: 0x0000000000126850 ; 
		(/lib/x86_64-linux-gnu/libc.so.6)
</StackTrace>

@MARK BARINSTEIN

I verified that all my virtualised Ubuntu copies have indeed libc.so at the same version; installed V12.1.1 and created additional 8k bufferpool and 8k tablespace in preparation for next attempt to test this beast.

Stay tuned ...

As @Vincent Guilpain mentioned, this should be brought to development aka opened as a case, I sent an email to a good friend in the IBM Db2 dev team to have a look at this thread. [Mike, I owe you a beer!]

Here is his answer:

Interesting problem.  However, I can’t reproduce this on a bare Db2 build.  Don’t really see how the container could be an issue.

The stack shows they are processing the  create index statement in catalog services?  So unlikely related to reorg or any real data processing.  Fact it is hung in libc  is odd and points more to possibly an issue with host OS.

So, if this is a customer system with support and maintenance (and is running on a supported platform), please open a case.

@Jan Nelken
I tried using an 8K page-size tablespace and reproduced the issue. That is what you wanted me to try, right?

create bufferpool bp size automatic pagesize 8k;
create tablespace ts pagesize 8k managed by automatic storage bufferpool bp;
create temporary tablespace tts pagesize 8k managed by automatic storage bufferpool bp;
create table t(C1 int not null primary key, text varchar(32672)) in ts;
alter table t alter column text set data type VARCHAR(128);
create index "idx_text" on t (text);

Ahh, an interesting topic arose in some background discussion. Is your database, where the index creation fails using any of these features?

• Encrypted
• Using table compression 
• Using table and index compression

The ICC_SHIFT=3 is somehow well known as a workaround for a GSKIT issue. So how do you bring in GSKIT in the failing scenario.

Search for gskit and icc_shift via Google and you'll find numerous hits...

Cheers 

Roland