IBM Support

Duplicating Schema - Copy DB objects from one schema to another

Question & Answer


Question

How do I duplicate a schema and all the datbase objects from one schema (library) to another?

Cause

Depending on the objects and how they were created, sometimes CPYLIB or save/restore of the library does not work because the database objects and qualified to the original library. When the copy or save/restore is complete, they will still reference the original library and not the library they were copied to or restored to.

Answer

Using 7.1 version of IBM i Navigator with service pack - SI50567 installed and DB Group (SF99701) level of 27 or higher, you can use Generate SQL and remove the library qualification. This can be done to generate source for an entire schema. Removing the library qualification allows you to create the objects in a different library without having the objects referencing the origicnal library they were created in. The steps to do this are described below.

1. Sign into iSeries Navigator.
2. Expand the Database container
3. Expand the Database name
4. Expand Schemas
3. Right click on the Schema you want to duplicate and select Generate SQL

This will bring up a list of objects for which the source will be generated for:

4. Select the Options Tab and make sure the option - 'Schema qualify names for objects' is Unchecked. See screen shot below.



For example, consider the following stored procedure definition:

CREATE PROCEDURE MBAILEY.PMR35898 (
IN #VAR1 INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC MBAILEY.PMR35898
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION  ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = MBAILEY ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX  
BEGIN
 
INSERT INTO MBAILEY . QCUSTCDT VALUES ( '999999' , 'Bailey' , 'M' , '3605 Hwy 52' , 'Roch' , 'MN' ,
'55901' , 3500.00 , '1' , 19.00 , 1.92 ) ;
 
END  ;

Generating the SQL source, deselecting 'Schema qualify name for objects' results in:

CREATE PROCEDURE PMR35898 (
IN #VAR1 INTEGER )
DYNAMIC RESULT SETS 1
LANGUAGE SQL
SPECIFIC PMR35898 
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION  ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE , 
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX  
BEGIN
 
INSERT INTO           QCUSTCDT VALUES ( '999999' , 'Bailey' , 'M' , '3605 Hwy 52' , 'Roch' , 'MN' , '55901' , 3500.00 , '1' , 19.00 , 1.92 ) ;
 
END  ;

Notice, even the embedded SQL Insert had the library qualification removed! Once the script is generated, you can run the script and create all the database object in a library of your choosing by setting your current library to the new library.

If you're using SQL naming, add the following to the script as the first statement to run:

SET CURRENT SCHEMA NEWLIB ;

If you're using SYS naming, you need to run the CHGCURLIB CL command to set the library by adding the following command to the script as the first statement to run. The NEWLIB should be not ahead of QSYS.

CL: CHGCURLIB LIB(NEWLIB) ;

There is a connection attribute that controls what naming convention is used. To determine what you are using, in the Run SQL Scipt window select Connections->JDBC Settings and then the Format Tab:




[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.1.0"}]

Document Information

More support for:
IBM i

Software version:
7.1.0

Operating system(s):
IBM i

Document number:
706751

Modified date:
18 December 2019

UID

nas8N1020050

Manage My Notification Subscriptions