CODEWITHSUNDEEP
db2backuprestore
user2053904
user2053904

Reputation:

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?

Upvotes: 0

Views: 1339

Answers (1)

user2053904
user2053904

Reputation:

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.

Upvotes: 1

Related Questions