Error: SQLCODE=-1585, SQLSTATE=54048, SQLERRMC=null, DRIVER=3.57.82

K.Sundaresan · #1 (**permalink**) 08-10-09, 08:18

I am using DB2 9.7
while selecting the view it shows me the error
Error: SQLCODE=-1585, SQLSTATE=54048, SQLERRMC=null, DRIVER=3.57.82

Qry

SELECT * FROM view_tfw T
where MST_ID='279' AND (COL_STATUS IS NULL OR COL_STATUS=1) AND COL_ADD_HIDE!='1'
AND (SERVICE_USER_ID='1' OR SERVICE_USER_ID IS NULL)
ORDER BY TABORDER,GROUP_ID,COL_USERORDER,COL_INDEX

the view has 121 column, if i remove the order by in this qry running it's smooth

Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 9216
Useable pages = 9212
Used pages = 8640
Free pages = 572
High water mark (pages) = 8640
Page size (bytes) = 32768
Extent size (pages) = 4
Prefetch size (pages) = 64
Number of containers = 1

any idea to run this view with order by

unsosenty · #2 (**permalink**) 08-10-09, 08:24

miley cyrus <a href=http://www.kaboodle.com/bertas>miley cyrus</a> MILEY CYRUS <a href=http://www.smosh.com/forums/index.php?showuser=585621>MILEY CYRUS</a>

n_i · #3 (**permalink**) 08-10-09, 10:05

Code:

$ db2 "? sql1585"


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

Explanation:

 One of the following conditions could have occurred:

1.    The row length of the system temporary table exceeded the
    limit that can be accommodated in the largest system
    temporary table space in the database.

2.    The number of columns required in a system temporary table
    exceeded the limit that can be accommodated in the largest
    system temporary table space in the database.



 The system temporary table space limits depend on its page size.
These values are:


  Max          Max   Page size of
  Record       Cols  temporary
  Length             table space
  -----------  ----  ------------
  1957  bytes  244   2K
  4005  bytes  500   4K
  8101  bytes  1012  8K
  16293 bytes  1012  16K
  32677 bytes  1012  32K


User Response:

 Create a system temporary table space of a larger page size
supported, if one does not already exist.  If such a table space
already exists, eliminate one or more columns from the system
temporary table.  Create separate tables or views, as required,
to hold additional information beyond the limit.

db2girl · #4 (**permalink**) 08-10-09, 11:44

Looks like a tempspace of 32K already exists

n_i · #5 (**permalink**) 08-10-09, 11:53

Quote:

Originally Posted by db2girl

Looks like a tempspace of 32K already exists

You must know something that I don't ...

db2girl · #6 (**permalink**) 08-10-09, 11:59

My apology... I don't know what I was thinking... 32K bufferpool exists, but we don't know about 32K tempspace.

Lenny77 · #7 (**permalink**) 08-10-09, 17:23

Try to use:

Fetch first N rows only,

Or instead of Select * use Select <only needs columns> ....

Good Luck !

n_i · #8 (**permalink**) 08-10-09, 19:03

Quote:

Originally Posted by Lenny77

Try to use:

Fetch first N rows only,

Or instead of Select * use Select <only needs columns> ....

Selecting only the required columns is always a good idea, that is true, and it may help here indeed. However, FETCH FIRST won't make any difference by itself, since it is applied after the sort, and the problem is in the width of the sort key.

Marcus_A · #9 (**permalink**) 08-10-09, 23:31

I always create a 32K tempspace on every database because I never know what crazy SQL a programmer or end-user may submit. Since my temp tablespaces are SMS (all temp tablespaces should be created as SMS) they take up no space if not needed. Of course, I have to also create a 32K bufferpool.

Internet Services

Web Development

Digital Marketing

Consumer Tech