Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    18

    Unanswered: Problem with SQL statement that contains Order By Clause with columns of type VARCHAR

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    ORDER BY VARCHAR(AS_CURRENCY, 1000), VARCHAR(MATURITY_DATE, 1000)

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Try like this ...

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

    will give a truncation warning though...
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Sep 2009
    Posts
    18

    Problem with SQL statement that contains Order By Clause with columns of type VARCHAR

    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?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by naveen_swiz
    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?
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2009
    Posts
    18

    Problem with SQL statement that contains Order By Clause with columns of type VARCHAR

    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by naveen_swiz
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •