Hi,
I have a Large Tablespace of 16k and system temporary tablespace of 32k. I have the following problem when I use an SQL statement that has more than one row in the order by clause in DB2.

SQL Query : SELECT RECID FROM TAFJV_FSG1_SWAP WHERE "REVALUATION_TYPE" IS NOT NULL or "FWD_REVAL_PL_LCY" IS NOT NULL
and "VALUE_DATE" <= '20090706 ' ORDER BY "AS_CURRENCY","MATURITY_DATE"


Exception : SQL1585N A system temporary table space with sufficient page size does not exist.

The columns AS_CURRENCY,MATURITY_DATE are of type VARCHAR of length 32000.

Is there any way to increase the page size of System Temporary Tablespace? Or do i need to remove few columns from Order By Clause?

How about this?
ORDER BY VARCHAR(AS_CURRENCY, 1000), VARCHAR(MATURITY_DATE, 1000)

Try like this ...

ORDER BY cast ( AS_CURRENCY as varchar(16000)), cast ( MATURITY_DATE as varchar(16000))

will give a truncation warning though...

Thanks for the reply. In some scenarios, this works fine. But say if i need to retrieve a column of type VARCHAR(32000), as shown below

select AUDIT_DATE_TIME from tafjv_fbnk_currency order by VARCHAR(RANK,1000), VARCHAR(DEPT_CODE,1000)

I get the same error when i execute the above query. Do we have any option to increase the page size of system temporary table beyond 32K or is there any other solution in DB2 to handle this problem?

Your design seems silly. You have VALUE_DATE as a string (CHAR or VARCHAR) when it should be a DATE. You have MATURITY_DATE as VARCHAR(32000) when it should also be DATE (or something more reasonable if you want to store string characters).

DB2 does not support silly database designs.

No, you cannot have a system temp tablespace larger than 32K. You can use the SUBSTR, RTRIM, or CAST functions to make them smaller in the select list and/or the ORDER BY.

Actually its not a table. Its a view created from a table which has just one column of type CLOB and we use scalar user defined functions to create views with individual fields from that clob record. The problem is that the individual fields can be of any length. So we just defined the length of the VARCHAR as 32000 in the user defined function.

Obviously a date cannot be 32000, so maybe you need to change your View.