Search code examples
db2backuprestore

How to make db2 backup and restore using db2expc or db2dec license on different server and autosize unlimited tablespaces avoid SQL1139n

I have to move database from one server(A) to another(B).
The server A have this license an version:
db2licm -l 

Nome prodotto:                     "DB2 Express-C"
Tipo di licenza:                   "Senza garanzia"
Data scadenza:                     "Permanente"
Identificativo prodotto:           "db2expc"
Informazioni sulla versione:       "10.5"
Numero massimo di CPU:             "2"
Quantità massima di memoria (GB):  "16"
Politica di restrizione:           "Interruzione parziale"

server B have this license an version:
db2licm -l 

Product name:                     "IBM DB2 Developer-C Edition"
License type:                     "Community"
Expiry date:                      "Permanent"
Product identifier:               "db2dec"
Version information:              "11.5"
Max amount of memory (GB):        "16"
Max number of cores:              "4"
Max amount of table space (GB):   "100"

These are two free license and doing backup and restore get an error:

SQL1139n The total size of the table space is too big

to get backup I use:

db2 backup database <DBNAME> to /home/db2inst1/backup

and to do restore I use: db2 restore database <db_name> from <location> taken at <timestamp>

this give an error on tablespace path too: on server B, not exist server A tablespace path.

Tablespaces are autosize and no limit.

How can fix?

Solution

  • Go on server A
    db2 => connect to <DBNAME> user db2inst1
    sb2 => LIST TABLESPACES SHOW DETAIL

    Will be show some detail. You have to look on all tablespace the Page size and tot pages
    use MB = Page size x tot pages to calc the MB tablespaces size.
    do not look at temp tablespace: is not necessary.

    let be an example:

    tablespace name page size       tot pages       MB
SYSCATSPACE     4096            32768           128
USERSPACE1      4096            8192            32
SYSTOOLSPACE    4096            8192            32
TBS_N0          32768           19456           608

    to be sure avoid problem make this size bigger: 

    ALTER TABLESPACE SYSCATSPACE  MAXSIZE 512 M
ALTER TABLESPACE USERSPACE1   MAXSIZE 128 M
ALTER TABLESPACE SYSTOOLSPACE MAXSIZE 128 M
ALTER TABLESPACE TBS_N0       MAXSIZE 1   G

    Now the tablespace have a maxsize and this will not raise a SQL1139n at restoring time.

    Now do the backup:
    db2 backup database <DBNAME> to /home/db2inst1/backup
    now we can put back last setting: 

    ALTER TABLESPACE SYSCATSPACE  MAXSIZE NONE
ALTER TABLESPACE USERSPACE1   MAXSIZE NONE
ALTER TABLESPACE SYSTOOLSPACE MAXSIZE NONE
ALTER TABLESPACE TBS_N0       MAXSIZE NONE

    copy backup on Server B and do:
    db2 restore database <DBNAME> from "/database" taken at <timestamp> redirect generate script restore.clp

    edit restore.clp to modify all path pointing location on Server A to match existing path on Server B.

    delete comment on row:
    ON 'path' and be sure path exist on Server B: is where the DB will create.

    delete comment on row: 

    SET STOGROUP PATHS FOR <IBMSTOGROUPNAME>
ON 'path'
;

    and be sure path exist on Server B: is where the DB will create new tablespaces.

    now do the restore by:
    db2 -tvf restore.clp

    If you have do some error and the restore go wrong, do:
    db2 restore database <DBNAMW> abort
    if this command do not work (give an error) do:
    db2 drop db <DBNAME>

    if all go right: 

    ALTER TABLESPACE SYSCATSPACE MAXSIZE  NONE
ALTER TABLESPACE USERSPACE1 MAXSIZE   NONE
ALTER TABLESPACE SYSTOOLSPACE MAXSIZE NONE
ALTER TABLESPACE TBS_N0 MAXSIZE       NONE

    New database will be create on Server B from backup copy of database in Server A.