Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2005
    Posts
    9

    Question Unanswered: Table space reaching max size - 64 GB

    Hi Guru,
    I'm the informix 9.40, 64-bit machine. As I do monitor the table and found the two tables reaching 64 gb - (ppoix 49 Gb & bsis 44 Gb). What should I thing now for the two table ? What approach should I decide ? Please help.

  2. #2
    Join Date
    Sep 2002
    Posts
    102
    fragment.the.tables.

  3. #3
    Join Date
    Jul 2005
    Posts
    9

    Question Table space reaching max size - 64 GB

    Hi All,
    if the table get fragmented. If there effected to application program, eg. Abap/4 program, access method sequential or indexing ?

    How could I do fragment the table? Any example could I refer ? could I do fragment the table by reorganization process ?

  4. #4
    Join Date
    Nov 2004
    Posts
    143
    Hi,

    No, infact there will be improvement in performance. You can also frament the index. Fragmentation can be done in 2 ways, one is round robin and other is based on expression. Based on ur requirement, fragment the table on multiple dbspaces(preferably on diff disks) and also the index.

    Bye

    Nitin

  5. #5
    Join Date
    Jun 2004
    Location
    Madrid, Spain
    Posts
    47
    Quote Originally Posted by mohdsaid
    Hi All,
    if the table get fragmented. If there effected to application program, eg. Abap/4 program, access method sequential or indexing ?

    How could I do fragment the table? Any example could I refer ? could I do fragment the table by reorganization process ?
    The apps. won't be afected... in most cases they will get better performance!. BTW: if you can put the database in non-logging mode, or get enough logical log space to hold your entire table (before LTXHWM is reached).

    The sintax is something like this:
    Round robin:
    alter fragment on table your_table
    init fragment by round robin in dbspace1, dbspace2, etc
    (it will keep the table indexes in the first dbspace)
    (and I'm not sure if your actual table will be reorganized)

    Expression:
    alter fragment on table your_table
    init fragment by expression
    (expression: for example column1<0 ) in dbspace1,
    (expression: for example column1>0 and column1 < 100 ) in dbspace2,
    ...
    (optional) remainder in dbspace3;

    The table indexes will also be fragmented, unless you fragment them with other expressions:
    alter fragment on index your_index
    init fragment by expression
    index_column1<0 in dbspace1,
    index_column1>=0 in dbspace2

    Keep in mind that every fragment will inherit the extent sizes you establish in the table creation.

    Important: read the IDS Adiminstrator's Performance manual before you decide anything!.

    And remember to use the -ss option for your dbexports/dbschemas!

    Hope it helps.
    José Luis Matute.

    Regards from Spain.

  6. #6
    Join Date
    Jul 2005
    Posts
    9

    Question Table space reaching max size - 64 GB

    Hi All,
    I'm very sorry and not very sure about the process.
    Q1 Is the dbspace need to be create fist? eg: dbspace1, dbspace2, etc.

    Q2: are the existing data records will be automatically copied over as the command "alter fragment on table your_table
    init fragment by round robin in dbspace1, dbspace2, etc" ?

    Thank you

  7. #7
    Join Date
    Sep 2002
    Posts
    102
    yes and yes

  8. #8
    Join Date
    Aug 2004
    Posts
    5

    Smile

    I would create 8 different dbspaces.

  9. #9
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,
    The name of dbspaces must be begin with "psap".
    Look this scripts:
    #!/bin/ksh
    export DELIMIDENT=1
    log=fragment.log
    echo "Inicio procedo de fragment `date`" >$log
    dbaccess qp1 1>>$log 2>&1 <<EOF
    set pdqpriority 80;
    alter fragment on table bsis init
    fragment by round robin in
    psapbsis1,psapbsis2,psapbsis3 ;
    !echo "Fin de fragment `date`"
    !echo "Inicio de Indice 1 `date`"
    create unique index "sapr3"."bsis~0" on "sapr3".bsis
    (mandt,bukrs,hkont,augdt,augbl,zuonr,gjahr,belnr,b uzei) in psapbsisidx;
    !echo "Inicio de Indice 2 `date`"
    create index "sapr3"."bsis~z01" on "sapr3".bsis
    (mandt,bukrs,hkont,gjahr,monat) in psapbsisidx;
    !echo "Inicio de Indice 3 `date`"
    create index "sapr3"."bsis~z02" on "sapr3".bsis
    (mandt,bukrs,hkont,budat) in psapbsisidx;
    !echo "Inicio de Indice 4 `date`"
    create index "sapr3"."bsis~z99" on "sapr3".bsis (mandt,xarch) in psapbsisidx;
    !echo "Fin de Indices `date`"
    EOF
    echo "Fin proceso de fragment `date`" >>$log

    Gustavo.

  10. #10
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,
    Excuse me, remember that the database must be without logging.

    Gustavo.

  11. #11
    Join Date
    Jun 2004
    Location
    Madrid, Spain
    Posts
    47
    Dbspaces must be created first.
    The dbspaces names don't need anyting special (apart from the care not to use reserved words).
    You can fragment a table in 2 to 32767 dbspaces (up to 9.40 you can't create more than 2047).
    You can fragment an index in 2 to 32767 dbspaces (up to 9.40 you can't create more than 2047).
    You can have 1 index fragment and 1 table fragment in the same dbspace (that's called a detached index - you can create it detached or detach it later).
    You can do fragmentation on a logged database. But it's highly advisable to disable it, for the time you will save and the long transactions that could last forever to rollback, or the total size of the logical logs you need: every single row movement will be logged!.

    Hope it helps
    José Luis Matute.

    Regards from Spain.

  12. #12
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,
    Attention please, if you work with SAP, then the new dbspace must be begin with "psap".

    Gustavo.

Posting Permissions

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