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

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
    ACCEPTED ANSWER

    Re: Generic XML stored procedure

    ‏2012-01-13T09:40:26Z  in response to SystemAdmin
    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
    • SystemAdmin
      SystemAdmin
      230 Posts
      ACCEPTED ANSWER

      Re: Generic XML stored procedure

      ‏2012-01-13T09:58:14Z  in response to Henrik_Loeser
      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
        ACCEPTED ANSWER

        Re: Generic XML stored procedure

        ‏2012-01-13T13:42:47Z  in response to SystemAdmin
        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
          ACCEPTED ANSWER

          Re: Generic XML stored procedure

          ‏2012-01-15T12:08:59Z  in response to Henrik_Loeser
          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
            ACCEPTED ANSWER

            Re: Generic XML stored procedure

            ‏2012-01-18T14:12:27Z  in response to SystemAdmin
            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
  • MatthiasNicola
    MatthiasNicola
    321 Posts
    ACCEPTED ANSWER

    Re: Generic XML stored procedure

    ‏2012-01-13T09:46:15Z  in response to SystemAdmin
    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/