Sometimes one needs to automate a DB2 schema copy, clone or simple backup.
If the requirements are just to create a new schema and copy the tables and their content into the new schema, it seems to be a simple task.
But this simple task soon reveals to be a hard one, which can be achieved with a quite simple script.
The first step is to created a specific schema for this automation task, e. g. COPY_DATABASE_SCHEMA, so all the procedures will be created there.
The procedure that copies the table content is easy to code. Just declare a cursor that selects the source table data and load it into the target table.
Here is the procedure that performs the copy of the table content:
* Copies, with replacement, the content from the source table
* into the target table.
* It will not check for success, i.e. no loading validation
* will be performed.
*
* @sourceTable: the full qualified source table name
* @targetTable: the full qualified target table name
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.COPY_TABLE(
sourceTable VARCHAR(128),
targetTable VARCHAR(128))
LANGUAGE SQL
BEGIN
DECLARE v_version_number INTEGER DEFAULT 1;
DECLARE v_cursor_statement VARCHAR(32672);
DECLARE v_load_command VARCHAR(32672);
DECLARE v_sqlcode INTEGER DEFAULT -1;
DECLARE v_sqlmessage VARCHAR(2048) DEFAULT '';
DECLARE v_rows_read BIGINT DEFAULT -1 ;
DECLARE v_rows_skipped BIGINT DEFAULT -1;
DECLARE v_rows_loaded BIGINT DEFAULT -1;
DECLARE v_rows_rejected BIGINT DEFAULT -1;
DECLARE v_rows_deleted BIGINT DEFAULT -1;
DECLARE v_rows_committed BIGINT DEFAULT -1;
DECLARE v_rows_part_read BIGINT DEFAULT -1;
DECLARE v_rows_part_rejected BIGINT DEFAULT -1;
DECLARE v_rows_part_partitioned BIGINT DEFAULT -1;
DECLARE v_mpp_load_summary VARCHAR(32672) DEFAULT NULL;
SET v_cursor_statement =
'DECLARE C1 CURSOR FOR SELECT * from ' || sourceTable;
SET v_load_command =
'load from C1 of cursor insert into ' || targetTable;
CALL db2load(1, v_cursor_statement, v_load_command, v_sqlcode,
v_sqlmessage, v_rows_read, v_rows_skipped,
v_rows_loaded, v_rows_rejected, v_rows_deleted,
v_rows_committed, v_rows_part_read,
v_rows_part_rejected, v_rows_part_partitioned,
v_mpp_load_summary) ;
END#
Please note that the target table must already be created in order for this procedure to work, and the script terminator is # and not the usual ;.
This procedure is the hardest part of the script. Copying the contents of any table requires a dynamic prepared query statement to use as a cursor, and the load command is not available inside a procedure, so the db2load procedure from SYSPROC must be used.
The next procedure creates the schema, the tables, and copies the tables contents:
* Copies an entire schema into a new schema named with the current date.
* @sourceSchema: the source schema name
* @targetSchema: the target schema name
* @tableNameSelection: the tables name to include ('%' for all tables)
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.COPY_DATABASE(
sourceSchema VARCHAR(50),
targetSchema VARCHAR(50),
tableNameSelection VARCHAR(150)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtSchema VARCHAR(250);
DECLARE stmtTableStructure VARCHAR(200);
DECLARE stmtTableContents VARCHAR(250);
DECLARE stmtAlias VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE numPages BIGINT;
DECLARE nError INTEGER DEFAULT 0;
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE V_LOAD_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH HOLD WITH RETURN FOR V_STMT;
DECLARE LOAD_CUR CURSOR FOR V_LOAD_STMT;
DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1;
-- Create schema
SET stmtSchema = CHAR('CREATE SCHEMA ' concat CHAR(targetSchema));
EXECUTE IMMEDIATE stmtSchema;
SET CURRENT SCHEMA targetSchema;
-- Copy tables and views
SET V_SQL = 'SELECT name, npages FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || TRIM(sourceSchema) || '''
AND NAME LIKE ''' || TRIM(tableNameSelection) || '''
order by name';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;
fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName, numPages;
IF at_end <> 0 THEN
LEAVE fetch_loop;
ELSE
SET stmtTableStructure = 'CREATE TABLE ' ||
targetSchema || '.' || tableName || ' LIKE ' ||
sourceSchema || '.' || tableName ;
EXECUTE IMMEDIATE stmtTableStructure;
IF numPages > 0 THEN
CALL COPY_DATABASE_SCHEMA.copy_table(
sourceSchema || '.' || tableName,
targetSchema || '.' || tableName);
END IF;
END IF;
END LOOP fetch_loop;
CLOSE TGT_TABLE_CUR;
END#
It dynamically issues DB2 SQL commands to create the schema and the tables structure and makes use of the previously created COPY_TABLE to copy the tables content. See the code comments for more information.
To use this just
These procedures have some limitations, for instance views are not supported.
Nevertheless, it is a good start to all that need to clone a schema or a table, since all the missing items can be gathered from the database catalog.
If you to drop an old schema, for instance to recreate it with a a new updated version, you can drop drop the schema with this procedure.