Topic
  • 9 replies
  • Latest Post - ‏2013-01-08T14:52:36Z by doole
lliang
lliang
15 Posts

Pinned topic Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

‏2012-12-10T10:00:19Z |
Hi All,

When migrating db from oracle to db2 (DB2 10.1 with fixpack 1), we found a strange issue as follows.

We have a "union" SQL with the pattern like follows:

select
(aa.c1 || ':' || bb.c2) f1,
nvl2(cc.c1, cc.c2 || ':' || cc.c3, null) f2,
decode(..., 'String') f3,
...
from aa, bb, cc, dd ...
where ...
union
select
(aa.c1 || ':' || bb.c2) f1,
nvl2(cc.c1, cc.c2 || ':' || cc.c3, null) f2,
decode(..., 'String') f3,
...
from aa, bb, cc, dd ...
where ...
order by f1

When this SQL is ran in DB2, the following exception is thrown (No problem running it in Oracle). The same error also occurs if we remove the last "order by f1, or replace "union" with "union all" and keep last "order by f1" .

"A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.58.82"

Alought all the 4 possible system temporary tablespaces have been created in DB2 with page sizes of 4K, 8K, 16K and 32K, this problem still persists.

And it sounds like (searched from some db2 forum on internet) that this problem happens because when DB2 internally handles untyped string (return from function?), the string would be converted to varchar(4000) in the situation where the returned result set needs sorting. And since union (or union all + order by) would do sorting internally, it might probably cause the above exception to occur.

And we then found that, if all the string returned from (i.e. nvl2, decode, "||") are wrapped inside char function as follows, then the error does disappear, which seemed somehow bolstered the above theory.

select
char(aa.c1 || ':' || bb.c2) f1,
char(nvl2(cc.c1, cc.c2 || ':' || cc.c3, null)) f2,
char(decode(..., 'String')) f3,
...
from aa, bb, cc, dd ...
where ...
union
select
char(aa.c1 || ':' || bb.c2) f1,
char(nvl2(cc.c1, cc.c2 || ':' || cc.c3, null)) f2,
char(decode(..., 'String')) f3,
...
from aa, bb, cc, dd ...
where ...
order by f1

We are wondering if this is a known issue in DB2 and expecting more professional answer to the issue if any.

I'd appreciate any help or comments you may provide.
Updated on 2013-01-08T14:52:36Z at 2013-01-08T14:52:36Z by doole
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-13T07:20:20Z  
    Hello,

    Indeed this error occurs when a result table has a line length greater than the pagesize of the temporary tablespace having the largest pagesize (32K in your case) and tablescan is needed for sorting.

    Your strategy of forcing type conversion to shorter types reduced the line length below 32K, and the query succeeded.

    Another strategy could be rearranging the query for the final order by to use an index, if you are interested in only the first lines of the result, which is often the case with this type of query, adding the clauses

    fetch first 10 rows only optimize for 10 rows

    Regards.

    Yves-Antoine Emmanuelli
  • lliang
    lliang
    15 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-13T09:12:20Z  
    Hello,

    Indeed this error occurs when a result table has a line length greater than the pagesize of the temporary tablespace having the largest pagesize (32K in your case) and tablescan is needed for sorting.

    Your strategy of forcing type conversion to shorter types reduced the line length below 32K, and the query succeeded.

    Another strategy could be rearranging the query for the final order by to use an index, if you are interested in only the first lines of the result, which is often the case with this type of query, adding the clauses

    fetch first 10 rows only optimize for 10 rows

    Regards.

    Yves-Antoine Emmanuelli
    Hi Yves-Antoine,

    Unfortunately, we are not just get interested in only first lines of the result in our case.

    BTW, I also tried "fetch first 10 rows only optimize for 10 rows" way, but it seems the error still persists.

    Anyway, thank you for your comments.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-21T16:47:40Z  
    Allen,

    It appears you may have columns of the form VARCHAR2(4000) in your tables.
    Adding all these columns from the multiple tables then exceeds the page size of the biggest page available to DB2 for temp-ing.
    Both UNION and ORDER BY need to have temps available to handle spilling of sort operations into the buffer pool.

    In case of Oracle the system will chain-pages to accommodate any rows which are actually bigger than the page size. DB2 10.1 does not support chaining and will therefore return an error if the defined size of the row exceeds 32K.

    The issue, as well as the importance to provide a fix in a future release are well understood.

    Having said that until then my recommended course of action is to take a hard look at any column that is defined as VARCHAR2(4000).
    Is the 4000 really a requirement originating from the application, or is it simply a function of the DB schema design using 4000 because it happens to be the maximum length of VARCHAR2 in columns?
    If so then what is the actual domain of strings for the application?
    If you can't cut down the size of the column itself then you would need to reduce the size of the rows feeding the UNION.
    CHAR as you suggested brings the size down to 254 for each expression, but it does have the downside of
    adding blanks... A more sensible approach would be to use SUBSTR(<expr>, 1, 254) or VARCHAR(<expr>, 254).
    Of course instead of 254 you can use any other number, as long as it helps reduce the length.

    Cheers
    Serge
    SQL Architect, DB2 for LUW
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-21T21:13:27Z  
    Allen,

    It appears you may have columns of the form VARCHAR2(4000) in your tables.
    Adding all these columns from the multiple tables then exceeds the page size of the biggest page available to DB2 for temp-ing.
    Both UNION and ORDER BY need to have temps available to handle spilling of sort operations into the buffer pool.

    In case of Oracle the system will chain-pages to accommodate any rows which are actually bigger than the page size. DB2 10.1 does not support chaining and will therefore return an error if the defined size of the row exceeds 32K.

    The issue, as well as the importance to provide a fix in a future release are well understood.

    Having said that until then my recommended course of action is to take a hard look at any column that is defined as VARCHAR2(4000).
    Is the 4000 really a requirement originating from the application, or is it simply a function of the DB schema design using 4000 because it happens to be the maximum length of VARCHAR2 in columns?
    If so then what is the actual domain of strings for the application?
    If you can't cut down the size of the column itself then you would need to reduce the size of the rows feeding the UNION.
    CHAR as you suggested brings the size down to 254 for each expression, but it does have the downside of
    adding blanks... A more sensible approach would be to use SUBSTR(<expr>, 1, 254) or VARCHAR(<expr>, 254).
    Of course instead of 254 you can use any other number, as long as it helps reduce the length.

    Cheers
    Serge
    SQL Architect, DB2 for LUW
    Hello,

    If the length of 4000 (and may be above?) is really required for your VARCHAR columns, how about using CLOB instead of VARCHAR for the VARCHAR(4000) columns?
    Add the LONG IN clause in the CREATE TABLE statement to store your CLOB data in a separate tablespace.

    Regards

    Yves-Antoine Emmanuelli
  • lliang
    lliang
    15 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-22T07:57:09Z  
    Hi Serge and Yves-Antoine,

    Thank you for your comments.

    I checked the tables, and we only have some varchar(100), varchar(50), varchar(30) columns in the tables.

    In real case, we actually used trim(char(...)) to elimiate the extra blanks. But yes, as Serge suggested, SUBSTR(<expr>, 1, 254) or VARCHAR(<expr>, 254) looks better which is more simple.

    As was mentioned, we also searched this issue on internet, and in some DB2 forum we saw some answer like follows:

    "This problem happens because when DB2 internally handles untyped string (returned from function?), the string would be converted to varchar(4000) in the situation where the returned result set needs sorting. And since union (or union all + order by) would do sorting internally, it might probably cause the above exception to occur"

    If this is case, it sounds like a DB2 issue itself.
  • lliang
    lliang
    15 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-27T10:47:04Z  
    Allen,

    It appears you may have columns of the form VARCHAR2(4000) in your tables.
    Adding all these columns from the multiple tables then exceeds the page size of the biggest page available to DB2 for temp-ing.
    Both UNION and ORDER BY need to have temps available to handle spilling of sort operations into the buffer pool.

    In case of Oracle the system will chain-pages to accommodate any rows which are actually bigger than the page size. DB2 10.1 does not support chaining and will therefore return an error if the defined size of the row exceeds 32K.

    The issue, as well as the importance to provide a fix in a future release are well understood.

    Having said that until then my recommended course of action is to take a hard look at any column that is defined as VARCHAR2(4000).
    Is the 4000 really a requirement originating from the application, or is it simply a function of the DB schema design using 4000 because it happens to be the maximum length of VARCHAR2 in columns?
    If so then what is the actual domain of strings for the application?
    If you can't cut down the size of the column itself then you would need to reduce the size of the rows feeding the UNION.
    CHAR as you suggested brings the size down to 254 for each expression, but it does have the downside of
    adding blanks... A more sensible approach would be to use SUBSTR(<expr>, 1, 254) or VARCHAR(<expr>, 254).
    Of course instead of 254 you can use any other number, as long as it helps reduce the length.

    Cheers
    Serge
    SQL Architect, DB2 for LUW
    Serge,

    I think I found a more simple SQL to reproduce this issue as follows.

    select space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1) from dual
    union
    select space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1) from dual
    With the above SQL running in DB2 10.1 LUW on my side, the error would just occur.

    "A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.58.82"
  • doole
    doole
    208 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2012-12-27T14:40:06Z  
    • lliang
    • ‏2012-12-27T10:47:04Z
    Serge,

    I think I found a more simple SQL to reproduce this issue as follows.

    select space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1) from dual
    union
    select space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1), space(1) from dual
    With the above SQL running in DB2 10.1 LUW on my side, the error would just occur.

    "A temporary table could not be created because there is no available system temporary table space that has a compatible page size.. SQLCODE=-1585, SQLSTATE=54048, DRIVER=3.58.82"
    If you look at the documentation on SPACE (http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000852.html), you'll see that the function is defined as returning VARCHAR(4000). You've got 9 calls to the function per row, so 9*4000 = 36000, which exceeds the capacity of a 32K page, and you're into the situation Serge described.

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs
  • lliang
    lliang
    15 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2013-01-08T04:25:37Z  
    • doole
    • ‏2012-12-27T14:40:06Z
    If you look at the documentation on SPACE (http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0000852.html), you'll see that the function is defined as returning VARCHAR(4000). You've got 9 calls to the function per row, so 9*4000 = 36000, which exceeds the capacity of a 32K page, and you're into the situation Serge described.

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs
    Thanks Doug,

    I just returned from my vacations.

    Yes, as you said, 9 calls of it just led to situation Serge described. And I knew the space function would return varchar(4000), and I was just trying to make a simple test case that can reproduce this issue.

    I am just guessing that if in any chances, will decode, nvl, nvl2, '||' or other functions ... somehow, generate varchar(4000) columns in union SQL?
  • doole
    doole
    208 Posts

    Re: Union (or union all + order by) will cause SQLCODE=-1585,SQLSTATE=54048 err

    ‏2013-01-08T14:52:36Z  
    • lliang
    • ‏2013-01-08T04:25:37Z
    Thanks Doug,

    I just returned from my vacations.

    Yes, as you said, 9 calls of it just led to situation Serge described. And I knew the space function would return varchar(4000), and I was just trying to make a simple test case that can reproduce this issue.

    I am just guessing that if in any chances, will decode, nvl, nvl2, '||' or other functions ... somehow, generate varchar(4000) columns in union SQL?
    Check out the online documentation. We describe the result types for all our scalar functions. (It might take a bit of jumping though. For example, NLV is a synonym for COALESCE, so you have to look up COALESCE to get the result type information. COALESCE refers you to "Rules for result datatypes" because functions with multiple inputs can be a little bit messy. In short though, NLV/COALESCE tend to have a result type that can store the longest input.)

    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs