How to clone an SQL database structure?

Document ID : KB000010334
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

I have a database structure using Database-ID (DBID) 208 and need to clone this database.

The new database needs to have the same structure but use another DBID.

How can I create this new database? 

Environment:
CA Datacom 15.0 (valid for older versions)
Instructions:

 

1)run DDBTGLM on your database entity name which is DOCUMENT-IDX-ARCH 

 

//BTG EXEC PGM=DDBTGLM,REGION=4096K

//STEPLIB DD DSN=your 15.0 Datacom CUSLIB,

// DISP=SHR

// DD DSN=your 15.0 CABDLOAD,

// DISP=SHR

// DD DSN=your IPC 15.0 CAVQLOAD,

// DISP=SHR

//SYSPRINT DD SYSOUT=*

//SYSOUT DD SYSOUT=*

//SYSPUNCH DD SYSOUT=*

//DDSNAP DD SYSOUT=*

//SNAPER DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//AUXOUT DD DSN=your dsn ,DISP=(NEW,CATLG,KEEP),

// DCB=(RECFM=FB,LRECL=80,BLKSIZE=4000),UNIT=SYSDA,

// SPACE=(CYL,(2,1),RLSE)

//SYSIN DD *

-USR DATACOM-INSTALL,NEWUSER

-DEF PATH,STANDARD

-END

-BTG AUXOUT

-BTG START,DATABASE,DOCUMENT-IDX-ARCH(PROD),STANDARD

-END

 

2) edit your AUXOUT dataset - in card 2009 under -BTG DATABASE you find the database-id which is 00208 in your case.Change to 00209.

You also need to use a different database name so change DOCUMENT-IDX-ARCH in that -BTG DATABASE card to another name like DOCUMENT-IDX-ARCH-CLONE.

Put another SQLNAME in card 2901 in that -BTG DATABASE group.

 

You need to use another AREA name and TABLE name too so change the names in -BTG AREA and -BTG TABLE and change the 1000 CONNECT card to refer to the new database and area occurrences names.

Put in another unique AUTHID.Table SQLNAME in card 3152 .

 

3)run DDUPDATE using -USR DATACOM-INSTALL,NEWUSER and that AUXOUT dataset

 

4) step 3 adds the new cloned database in status T001 in DATA-DICT .

In DDOL run SET BAS on that new database followed by VER BAS .

If OK then copy the T001 database structure to PROD status , catalog the database and enable the database.

 

5) allocate new data sets for the index and data areas so for IXX209 and DIA209 and run DBUTLTY using

INIT AREA=IXX,DBID=209

INIT AREA=DIA,DBID=209

LOAD AREA=DIA,DBID=209,FORMAT=NONE,SORT=1

 

Note that if table SYSUSR.DOC_INDEX_ARCH has been created via an SQL CREATE TABLE statement in the past then you can add the new database and area entities and run that CREATE TABLE ... IN  new area name DATACOM NAME name (same three-character Datacom table name that you use in DBID 208).

You can run DDTRSLM EXPORT TABLE SYSUSR.DOC_INDEX_ARCH to see the CREATE TABLE statement and then use those cards to CREATE the cloned table.

But my guess is that you defined this table via DDOL or DDUPDATE so not via SQL.

 

DBUTLTY has functions CXXCLONE and CXXMAINT OPTION=DDPROD,NEWDBID=

but those functions cannot be used to clone an SQL database.

 

For an SQL database the DDD-DATABASE needs to be populated and that only happens during a catalog database.

Additional Information:

In some cases you can also use the DDUPDATE 1000 MODEL transaction. 

See the online documentation here :Creating a Replica Database Accessible by SQL