My requirements were quite easy, just create a new schema and copy the tables and their content into the new schema.
What initially started as a simple task reveled to be a hard task and resulted in a quite simple script.
The first problem was to execute several commands in the DB2 Command Editor. I was getting syntax errors because the statement termination character was set to be the same as the SQL end statement character ;.
Setting it to # solved my problem, but I did waste some time with this so keep in mind to change the statement termination character to avoid such problem.
I've created a specific schema for this automation task COPY_DATABASE_SCHEMA, so all the procedures will be created there.
Here is the procedure that will copy a table content:
Please note that the target table must already be created in order for this procedure to work.
/**
* 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#
This procedure was the hard part. 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.
Here is the procedure that will create a schema, create the tables on it and copy the contents:
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.
/**
* 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#
To use this just
call COPY_DATABASE_SCHEMA.COPY_DATABASE('originalSchemaName',
'newBackupSchemaName', '%');
Since I also need to discard old copies, I have created a procedure to drop a schema, including all the tables on it.
/**
* Drops an entire schema even if it has tables.
* Views are not supported.
* @schemaName: the name of the schema to drop
*/
CREATE PROCEDURE COPY_DATABASE_SCHEMA.DROP_SCHEMA(
schemaName VARCHAR(50)
)
LANGUAGE SQL
BEGIN
-- Variables
DECLARE stmtDropSchema VARCHAR(250);
DECLARE stmtDropTable VARCHAR(250);
DECLARE tableName VARCHAR(128);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;
DECLARE TGT_TABLE_CUR CURSOR WITH RETURN FOR V_STMT;
DECLARE CONTINUE HANDLER for not_found SET at_end = 1;
-- Copy tables and views
SET V_SQL = 'SELECT name FROM SYSIBM.SYSTABLES
WHERE CREATOR = ''' || trim(schemaName) || '''
AND CREATOR NOT LIKE ''SYS%''';
PREPARE V_STMT FROM V_SQL;
OPEN TGT_TABLE_CUR;
fetch_loop:
LOOP
FETCH TGT_TABLE_CUR INTO tableName;
IF at_end <> 0 THEN LEAVE fetch_loop;
ELSE
SET stmtDropTable = 'DROP TABLE ' ||
schemaName || '.' || tableName;
EXECUTE IMMEDIATE stmtDropTable;
END IF;
END LOOP fetch_loop;
CLOSE TGT_TABLE_CUR;
-- Drop schema
SET stmtDropSchema = char('DROP SCHEMA ' concat
char(schemaName) concat ' RESTRICT');
EXECUTE IMMEDIATE stmtDropSchema;
END#
This 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.
./M6
Posts
