Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009
    Posts
    1

    Unanswered: The old "SQL1585N" problem, please help

    I got the famous "SQL1585N" problem when I was trying to load data into a table. After I met the problem, I created a system temporary table with 32k pagesize, I even removed the table and tried to reload the data, but it still report the same error. The statistics are as follows (It is a little bit verbose, but I hope it can bring some details):

    ----------------
    $ db2 "list tablespaces show detail"
    ...........
    Tablespaces for Current Database

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 16384
    Useable pages = 16380
    Used pages = 13764
    Free pages = 2616
    High water mark (pages) = 13764
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1

    Tablespace ID = 1
    Name = TEMPSPACE1
    Type = System managed space
    Contents = System Temporary data
    State = 0x4000
    Detailed explanation:
    Offline

    Tablespace ID = 2
    Name = USERSPACE1
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 24576
    Useable pages = 24544
    Used pages = 19392
    Free pages = 5152
    High water mark (pages) = 19392
    Page size (bytes) = 4096
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    Tablespace ID = 3
    Name = TEMPSPACE3
    Type = Database managed space
    Contents = System Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 4096
    Useable pages = 4064
    Used pages = 64
    Free pages = 4000
    High water mark (pages) = 64
    Page size (bytes) = 32768
    Extent size (pages) = 32
    Prefetch size (pages) = 32
    Number of containers = 1

    Tablespace ID = 4
    Name = SYSTOOLSPACE
    Type = Database managed space
    Contents = All permanent data. Large table space.
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 8192
    Useable pages = 8188
    Used pages = 152
    Free pages = 8036
    High water mark (pages) = 152
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1

    Tablespace ID = 5
    Name = SYSTOOLSTMPSPACE
    Type = System managed space
    Contents = User Temporary data
    State = 0x0000
    Detailed explanation:
    Normal
    Total pages = 1
    Useable pages = 1
    Used pages = 1
    Free pages = Not applicable
    High water mark (pages) = Not applicable
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1
    -----------------------------

    As shown, TEMPSPACE3 has 32k pagesize. The tables are created in "USERSPACE1". The table is really a small one, which I think even 4k pagesize is sufficient: it is the TPCH partsupp, which has the schema:
    --------------
    CREATE TABLE PARTSUPP ( \
    PS_PARTKEY INTEGER NOT NULL CHECK (PS_PARTKEY>=0), \
    PS_SUPPKEY INTEGER NOT NULL, \
    PS_AVAILQTY INTEGER NOT NULL CHECK (PS_AVAILQTY>=0), \
    PS_SUPPLYCOST DECIMAL(16,2) NOT NULL CHECK (PS_SUPPLYCOST>=0), \
    PS_COMMENT VARCHAR(199), \
    PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY), \
    FOREIGN KEY (PS_PARTKEY) REFERENCES PART (P_PARTKEY), \
    FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER (S_SUPPKEY))
    ------------------
    Is there any other possibility for this error? For example:

    Should "userspace1" (the tablespace table are created) also be 32K pagesize? If true, it should not be "SQL1585N" error, right?

    Is "tempspace1" (the tablespace with 4k pagesize, and offline) confusing the system and prohibiting the load to use "tempspace3"?

    The db2 document also mentioned "If such a table space already exists, eliminate one or more columns from the system temporary table"? Does it mean some system temporary table's columns already been occupied? I it is, how do finish this "elimination" process. I don't anything about this "system temporary table".

    Thank you very much for any help

    --
    ul_denzel

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes, but tempspace3 is only 4000 pages and dms
    is autoresize on ? why dms for temp space ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ul_denzel

    The db2 document also mentioned "If such a table space already exists, eliminate one or more columns from the system temporary table"? Does it mean some system temporary table's columns already been occupied? I it is, how do finish this "elimination" process.
    It means a temporary table that is created by DB2 when processing a query, e.g. when it needs a sort that does not fit entirely in memory, or in any other case when intermediate results need to be stored.

    You should examine the execution plan of the statement that results in this error, find the step that creates the temporary table, and either eliminate it altogether or modify it such that the data fits onto a 32K page.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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