Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2015
    Posts
    6

    Unanswered: Need help to expand SYSCATSPACE DB2 9.5 FP 10 on AIX

    For other DMS managed spaces I just add a new container in and blow and go. Seems to me it says it is DMS but message says not really. Would love some help... I inherited the DB2 side of the house when somebody chose an opportunity elsewhere and I am primarily a SQLServer dba... Note 18GB free on the LUN. What am I missing thanks in advance.


    I tried variations on db2 “alter tablespace syscatspace extend (all 10000)” and received.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL20318N Table space "SYSCATSPACE" of type "AUTOMATIC STORAGE" cannot be
    altered using the "EXTEND" operation. SQLSTATE=42858

    Confuses me since when you look at the tablespace it indicates a DMS table space.

    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0000
    Detailed explanation:
    Normal


    When you look at container:

    db2 list tablespace containers for 0

    Tablespace Containers for Tablespace 0

    Container ID = 0
    Name = /db2/mo_vol1/xxxxxxxx/NODE0000/yyyyyyyyy/T0000000/C0000000.CAT
    Type = File


    Tablespace ID = 0
    Name = SYSCATSPACE
    Type = Database managed space
    Contents = All permanent data. Regular table space.
    State = 0x0800
    Detailed explanation:
    Backup in progress
    Total pages = 50000
    Useable pages = 49996
    Used pages = 49996
    Free pages = 0
    High water mark (pages) = 49996
    Page size (bytes) = 4096
    Extent size (pages) = 4
    Prefetch size (pages) = 4
    Number of containers = 1

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    As the error message says, automatic tablespaces, though they may be implemented as SMS or DMS internally, cannot be resized -- they grow and shrink, well, automatically. Why do you think you need to extend it?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2015
    Posts
    6

    table Cration failed

    When I tried to create a new table in the database it said the could not allocate a new page in SYSCATSPACE

    CREATE Faketablename ( PKSTAGINGID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE )
    , FKCLAIMID BIGINT NOT NULL
    , FKINVOICEID BIGINT
    , STATUS CHARACTER (4)
    , CONSTRAINT myPK PRIMARY KEY ( PKSTAGINGID) ) IN XXXX_TS1 INDEX IN XX_INDEX ;

    SQL0289N Unable to allocate new pages in table space
    "SYSCATSPACE
    ".;
    Last edited by krispc; 01-08-15 at 10:48.

  4. #4
    Join Date
    Jan 2015
    Posts
    6

    Solution

    The problem was the syscatspace had a max space of about 200 MB on it and it had grown as far as it could go...

    Solution was to expand max so for me this fixed the issue: DB2 alter tablespace SYSCATSPACE increasesize 50 M MAXSIZE 500 M

    Set the growth to 50 MB chunks and MAXSIZE to 500MB

  5. #5
    Join Date
    Apr 2012
    Posts
    156
    Why are you creating a user table in syscatspace in the first place? This tablespace should only be used for the db2 catalog. Create a new tablesapce and create all user tables in thier.

  6. #6
    Join Date
    Jan 2015
    Posts
    6

    New Table

    I am not putting any tables in syscatpace. Apparently syscatspace stores meta data about tables in it. The table being created was in a different table and had a different index space. Note previous entry where the create table syntax was provided.

Posting Permissions

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