Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    7

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

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Looks like a tempspace of 32K already exists

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by db2girl
    Looks like a tempspace of 32K already exists
    You must know something that I don't ...
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    My apology... I don't know what I was thinking... 32K bufferpool exists, but we don't know about 32K tempspace.

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Try to use:

    Fetch first N rows only,

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

    Good Luck !

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    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
  •