Topic
  • 6 replies
  • Latest Post - ‏2012-01-18T14:12:27Z by Henrik_Loeser
SystemAdmin
SystemAdmin
230 Posts

Pinned topic Generic XML stored procedure

‏2012-01-13T09:23:00Z |
Good day
I am still new to DB2, and I would really appreciate your help regarding the problem that I have.

I have created a database in db2, and that is mainly for archiving, I am using XML based archiving, now the problem is I want to write a generic stored procedure that will tables from our production system and archive them. I have difficulty in db2 to write that stored procedure that will generictly take table structure and data and archive them without me specifying the table. i will give you an example:

in production, i have the following tables, account, activityllog, industry, customer, merchant, for example. I would like to have an archive table to be like this in db2: archive_date, table_name, Table_data(XML datatype).

Therefore I do not want to specify the table names in my stored procedure, but I only want the stored procedure to take all the tables that are in production, and store them in archive table like the way I specified. The reason I want to do that, is, if there are new tables added on the production system I do not want to go and change my stored procedure.

As I am new in Db2, I am struggling a bit with that code, and in mysql I managed to create something like that.

Your help with sample code will be highly appreciated.
Regards,
TMVincent
Updated on 2012-01-18T14:12:27Z at 2012-01-18T14:12:27Z by Henrik_Loeser
  • Henrik_Loeser
    Henrik_Loeser
    37 Posts

    Re: Generic XML stored procedure

    ‏2012-01-13T09:40:26Z  
    Hello TMVincent,

    How did you solve the task in MySQL? DB2 has a system catalog, too, that you can query for available tables. You can generate SQL statements in a stored procedure and then prepare and execute them (even in a loop) to perform the archiving task.

    I struggle with your table design. How big is your table data that you plan to store it in a single XML value? What tasks do you plan to perform on the archive data? How does the structure look like? Or is it simple and generated with a function like XMLROW or XMLGROUP?

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net
  • MatthiasNicola
    MatthiasNicola
    321 Posts

    Re: Generic XML stored procedure

    ‏2012-01-13T09:46:15Z  
    Hi TMVincent,

    I don't have the time right now to write this procedure for you, but I can give you some hints.

    Each DB2 database has a catalog table (actually a view) called syscat.tables that holds one entry for each table in the database. The following query returns the names of all tables in the database schema VINCENT:

    select tabname
    from syscat.tables
    where tabschema = 'VINCENT';
    Your stored procedure can loop over these tables and issue a query for each table to read the table data and return it in an XML format for archiving.

    You can use SQL functions such as XMLROW and XMLGROUP to easily convert the relational table data to XML, as shown here:
    http://nativexmldatabase.com/2011/06/08/how-to-quickly-produce-xml-from-relational-tables/

    Your definition of the archive table seems to imply that all the data from one table will stored as a single XML document. This might be OK for small tables but a bad idea for large tables. You wouldn't want a 100GB table to be archived as a single XML document. A better idea would be to produce one small XML document per row (XMLROW). If you want to export such data from DB2 to the file system, you can always use the EXPORT command to write many small XML documents to a single large file, if you want.

    Thx,

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • SystemAdmin
    SystemAdmin
    230 Posts

    Re: Generic XML stored procedure

    ‏2012-01-13T09:58:14Z  
    Hello TMVincent,

    How did you solve the task in MySQL? DB2 has a system catalog, too, that you can query for available tables. You can generate SQL statements in a stored procedure and then prepare and execute them (even in a loop) to perform the archiving task.

    I struggle with your table design. How big is your table data that you plan to store it in a single XML value? What tasks do you plan to perform on the archive data? How does the structure look like? Or is it simple and generated with a function like XMLROW or XMLGROUP?

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net
    This is mySql code: this is just a part where I can get tables from one database to the other with all the data in it, I want to do something similar in DB2, and I check the XMLGroup function, ofwhich I think I will be using that.
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `Vincent`.`GenericExtraction` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `Vincent`.`GenericExtraction`()
    BEGIN

    DECLARE DBName VARCHAR(50);
    DECLARE TableName VARCHAR(200);
    DECLARE isPrimary BOOLEAN;
    DECLARE run BOOLEAN;
    DECLARE done INT;

    DECLARE DatabaseCursor CURSOR FOR SELECT `SCHEMA_NAME`
    FROM `information_schema`.`SCHEMATA`;

    DECLARE TableCursor CURSOR FOR SELECT `TABLE_NAME`
    FROM `information_schema`.`TABLES`
    WHERE `TABLE_SCHEMA` = DBName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Code starts here

    OPEN DatabaseCursor;
    DatabaseLoop: LOOP
    SET done = 0;
    FETCH DatabaseCursor INTO DBName;

    IF done THEN
    CLOSE DatabaseCursor;
    LEAVE DatabaseLoop;
    END IF;

    -- Start Do Database Stuff here
    SET run = CASE
    WHEN DBName = 'Vincnet' THEN FALSE
    WHEN DBName = 'information_schema' THEN FALSE
    WHEN DBName = 'mysql' THEN FALSE
    ELSE TRUE
    END;

    IF run THEN
    OPEN TableCursor;
    TableLoop: LOOP
    SET done = 0;
    FETCH TableCursor INTO TableName;

    IF done THEN
    CLOSE TableCursor;
    LEAVE TableLoop;
    END IF;

    SET @createTableString = CONCAT('CREATE TABLE `Vincent`.`', DBName, TableName, '` SELECT * FROM `', DBName, '`.`', TableName, '` LIMIT 0;');

    PREPARE dynamicCreate FROM @createTableString;
    EXECUTE dynamicCreate;
    DEALLOCATE PREPARE dynamicCreate;

    SET @insertTableString = CONCAT('INSERT INTO `Vincent`.`', DBName, TableName, '` SELECT * FROM `', DBName, '`.`', TableName, '`;');

    PREPARE dynamicInsert FROM @insertTableString;
    EXECUTE dynamicInsert;
    DEALLOCATE PREPARE dynamicInsert;
    END LOOP;
    END IF;
    -- End Do Database Stuff here

    END LOOP;

    END $$

    DELIMITER ;
  • Henrik_Loeser
    Henrik_Loeser
    37 Posts

    Re: Generic XML stored procedure

    ‏2012-01-13T13:42:47Z  
    This is mySql code: this is just a part where I can get tables from one database to the other with all the data in it, I want to do something similar in DB2, and I check the XMLGroup function, ofwhich I think I will be using that.
    DELIMITER $$

    DROP PROCEDURE IF EXISTS `Vincent`.`GenericExtraction` $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `Vincent`.`GenericExtraction`()
    BEGIN

    DECLARE DBName VARCHAR(50);
    DECLARE TableName VARCHAR(200);
    DECLARE isPrimary BOOLEAN;
    DECLARE run BOOLEAN;
    DECLARE done INT;

    DECLARE DatabaseCursor CURSOR FOR SELECT `SCHEMA_NAME`
    FROM `information_schema`.`SCHEMATA`;

    DECLARE TableCursor CURSOR FOR SELECT `TABLE_NAME`
    FROM `information_schema`.`TABLES`
    WHERE `TABLE_SCHEMA` = DBName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    -- Code starts here

    OPEN DatabaseCursor;
    DatabaseLoop: LOOP
    SET done = 0;
    FETCH DatabaseCursor INTO DBName;

    IF done THEN
    CLOSE DatabaseCursor;
    LEAVE DatabaseLoop;
    END IF;

    -- Start Do Database Stuff here
    SET run = CASE
    WHEN DBName = 'Vincnet' THEN FALSE
    WHEN DBName = 'information_schema' THEN FALSE
    WHEN DBName = 'mysql' THEN FALSE
    ELSE TRUE
    END;

    IF run THEN
    OPEN TableCursor;
    TableLoop: LOOP
    SET done = 0;
    FETCH TableCursor INTO TableName;

    IF done THEN
    CLOSE TableCursor;
    LEAVE TableLoop;
    END IF;

    SET @createTableString = CONCAT('CREATE TABLE `Vincent`.`', DBName, TableName, '` SELECT * FROM `', DBName, '`.`', TableName, '` LIMIT 0;');

    PREPARE dynamicCreate FROM @createTableString;
    EXECUTE dynamicCreate;
    DEALLOCATE PREPARE dynamicCreate;

    SET @insertTableString = CONCAT('INSERT INTO `Vincent`.`', DBName, TableName, '` SELECT * FROM `', DBName, '`.`', TableName, '`;');

    PREPARE dynamicInsert FROM @insertTableString;
    EXECUTE dynamicInsert;
    DEALLOCATE PREPARE dynamicInsert;
    END LOOP;
    END IF;
    -- End Do Database Stuff here

    END LOOP;

    END $$

    DELIMITER ;
    The code for DB2 will look very similar. Is a "database" for you a different schema in a DB2 database, a database on the same instance, or even on a different server (machine)?

    Do you have specific questions other than "give me a working piece of code"?

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net
  • SystemAdmin
    SystemAdmin
    230 Posts

    Re: Generic XML stored procedure

    ‏2012-01-15T12:08:59Z  
    The code for DB2 will look very similar. Is a "database" for you a different schema in a DB2 database, a database on the same instance, or even on a different server (machine)?

    Do you have specific questions other than "give me a working piece of code"?

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net
    Hi Henrik,
    I have tried several options but still failing, just check my code and please tell me where I am going wrong, when I try to execute this procedure i'm getting SQLCODE = -87, I therefore do not know where the problem is:
    CREATE PROCEDURE "AA"."GenericArchive"()
    DYNAMIC RESULT SETS 1
    P1: BEGIN

    DECLARE TableName VARCHAR(200);
    DECLARE Isrun BOOLEAN;
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE dyn_stmt varchar(1500);
    DECLARE dyn_alter varchar(1500);
    DECLARE SchemaName Varchar(200);
    DECLARE OldSchemaName VARCHAR(200);
    DECLARE dyn_insert VARCHAR (1500);
    DECLARE TableCursor CURSOR FOR SELECT tabname FROM syscat.tables WHERE tabschema = 'HA';
    DECLARE SchemaCursor CURSOR FOR SELECT unique tabschema FROM syscat.tables WHERE tabschema = 'AA';
    DECLARE OldSchemaCursor CURSOR FOR SELECT unique tabschema FROM syscat.tables WHERE tabschema = 'HA';
    OPEN OldSchemaCursor;
    FETCH OldSchemaCursor INTO OldSchemaName;

    OPEN SchemaCursor;
    FETCH SchemaCursor INTO SchemaName;

    OPEN TableCursor;
    • Fetch the values from the cursor into local variables
    FETCH TableCursor INTO TableName;
    • Run the loop till SQLCODE is zero (which means the last cursor fetch was successful)
    WHILE (SQLCODE=0) DO
    SET dyn_stmt = 'create table ' CONCAT SchemaName CONCAT '.' CONCAT TableName;
    EXECUTE IMMEDIATE dyn_stmt;

    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN ArchiveDate TIMESTAMP';
    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN Tablename varchar(200)';
    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN TableDetails XML';
    EXECUTE IMMEDIATE dyn_alter;

    FETCH TableCursor INTO TableName;
    END WHILE;
    --Close the cursor
    CLOSE TableCursor;
    CLOSE SchemaCursor;
    • DO THE INSERT HERE

    OPEN TableCursor;
    FETCH TableCursor INTO TableName;
    WHILE (SQLCODE=0) DO
    • set dyn_insert = 'insert into ' CONCAT SchemaName CONCAT '.' CONCAT TableName
    • (CURRENT_TIMESTAMP, TableName , CONCAT ' select XMLGROUP * from ' CONCAT OldSchemaName CONCAT '.' CONCAT TableName ;

    • execute immediate dyn_insert;

    FETCH TableCursor INTO TableName;
    END WHILE;

    CLOSE TableCursor;
    END P1
  • Henrik_Loeser
    Henrik_Loeser
    37 Posts

    Re: Generic XML stored procedure

    ‏2012-01-18T14:12:27Z  
    Hi Henrik,
    I have tried several options but still failing, just check my code and please tell me where I am going wrong, when I try to execute this procedure i'm getting SQLCODE = -87, I therefore do not know where the problem is:
    CREATE PROCEDURE "AA"."GenericArchive"()
    DYNAMIC RESULT SETS 1
    P1: BEGIN

    DECLARE TableName VARCHAR(200);
    DECLARE Isrun BOOLEAN;
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE dyn_stmt varchar(1500);
    DECLARE dyn_alter varchar(1500);
    DECLARE SchemaName Varchar(200);
    DECLARE OldSchemaName VARCHAR(200);
    DECLARE dyn_insert VARCHAR (1500);
    DECLARE TableCursor CURSOR FOR SELECT tabname FROM syscat.tables WHERE tabschema = 'HA';
    DECLARE SchemaCursor CURSOR FOR SELECT unique tabschema FROM syscat.tables WHERE tabschema = 'AA';
    DECLARE OldSchemaCursor CURSOR FOR SELECT unique tabschema FROM syscat.tables WHERE tabschema = 'HA';
    OPEN OldSchemaCursor;
    FETCH OldSchemaCursor INTO OldSchemaName;

    OPEN SchemaCursor;
    FETCH SchemaCursor INTO SchemaName;

    OPEN TableCursor;
    • Fetch the values from the cursor into local variables
    FETCH TableCursor INTO TableName;
    • Run the loop till SQLCODE is zero (which means the last cursor fetch was successful)
    WHILE (SQLCODE=0) DO
    SET dyn_stmt = 'create table ' CONCAT SchemaName CONCAT '.' CONCAT TableName;
    EXECUTE IMMEDIATE dyn_stmt;

    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN ArchiveDate TIMESTAMP';
    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN Tablename varchar(200)';
    SET dyn_alter = 'Alter table' CONCAT SchemaName CONCAT '.' CONCAT TableName CONCAT 'ADD COLUMN TableDetails XML';
    EXECUTE IMMEDIATE dyn_alter;

    FETCH TableCursor INTO TableName;
    END WHILE;
    --Close the cursor
    CLOSE TableCursor;
    CLOSE SchemaCursor;
    • DO THE INSERT HERE

    OPEN TableCursor;
    FETCH TableCursor INTO TableName;
    WHILE (SQLCODE=0) DO
    • set dyn_insert = 'insert into ' CONCAT SchemaName CONCAT '.' CONCAT TableName
    • (CURRENT_TIMESTAMP, TableName , CONCAT ' select XMLGROUP * from ' CONCAT OldSchemaName CONCAT '.' CONCAT TableName ;

    • execute immediate dyn_insert;

    FETCH TableCursor INTO TableName;
    END WHILE;

    CLOSE TableCursor;
    END P1
    Hey,

    I was traveling and busy with other things. Are you asking what SQLCODE -87 means or what it means in your context?

    You say that you get the error when you try to execute the SP. That means that you already were able to create it. As you probably found out is the SQL error -87 caused by a NULL value. Are there any line numbers given? Have you tried debugging the SP in the Data Studio tool?

    --
    Henrik Loeser
    Read more about DB2 in my blog at http://blog.4loeser.net