Topic
8 replies Latest Post - ‏2013-11-15T09:02:25Z by MatthiasNicola
Gary_Scarcella
Gary_Scarcella
21 Posts
ACCEPTED ANSWER

Pinned topic DB2 LUW support for remote cursor database load for tables with XML columns

‏2012-04-13T18:37:29Z |
Does anyone know when DB2 LUW will support remote cursor database loads for tables with XML columns?

From the DB2 LUW V9.7 Information Center this is not currently supported.
(after connecting all of the dots)
The DATABASE option of the DECLARE CURSOR statement (also known as the remotefetch media type when using the Load API)...
To support this remotefetch functionality, the load utility makes use of infrastructure which supports the SOURCEUSEREXIT facility...
You cannot specify the SOURCEUSEREXIT option of the LOAD command when loading a table containing an XML column.

Here's the load command and error message received when trying to run this.
db2 DECLARE C1 CURSOR DATABASE SSTPEAP USER ######## USING ######## FOR SELECT T.* FROM STPEAP.EAPPL_PRTY_SECU_QUESTION T WITH UR;
db2 LOAD FROM C1 OF CURSOR MESSAGES SSTPEAP2.edm_udb_remote_cursor_load.2012_04_13_11:42:38.msg
REPLACE INTO STPEAP.EAPPL_PRTY_SECU_QUESTION NONRECOVERABLE;

SQL1407N The "SOURCEUSEREXIT" option is incompatible with "XML columns".
Updated on 2013-02-07T16:48:00Z at 2013-02-07T16:48:00Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    309 Posts
    ACCEPTED ANSWER

    Re: DB2 LUW support for remote cursor database load for tables with XML columns

    ‏2012-04-14T01:05:34Z  in response to Gary_Scarcella
    Hi Gary,

    if you want to use "load from cursor" to load XML data from a table T1 in database D1 into table T2 in database D2, you can use the following method:

    Setup federation between the two databases so that database D1 is a remote data source for D2. In D2, you can then define a local nick name for T1, and let's assume this nickname is "t1local". In D2 you can then declare a cursor on t1local and load from there into T2, like this:

    DECLARE c1 CURSOR FOR SELECT * FROM t1local;
    LOAD FROM c1 OF CURSOR REPLACE INTO t2;

    I just tried it with two databases and it seems to work fine. To setup federation I followed the steps in Figure 5.35 of the DB2 pureXML Cookbook, but you can also go with the regular documentation.

    Does that help?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    • Gary_Scarcella
      Gary_Scarcella
      21 Posts
      ACCEPTED ANSWER

      Re: DB2 LUW support for remote cursor database load for tables with XML columns

      ‏2012-04-14T02:07:10Z  in response to MatthiasNicola
      Hi Matthias,

      I saw the documentation that mentioned the alternative to use nicknames as you suggested, but that will not work well for me. I am very familiar with nicknames which I uses extensively at my prior company, but my current co-workers will throw a fit if I start introducing nicknames here.

      Also, nicknames would be a fine approach if I needed this for a just a few tables or databases, but my remote_cursor_load process is a generic script we set up to allow developers to do subset extract processing between database environments. This would add a lot more logic to the script to have to dynamically generate nicknames, etc.

      So, do you know if DB2 LUW support for remote cursor database load for tables with XML columns will be in V10 or some other release beyond that?

      Thanks,
      Gary.
      • MatthiasNicola
        MatthiasNicola
        309 Posts
        ACCEPTED ANSWER

        Re: DB2 LUW support for remote cursor database load for tables with XML columns

        ‏2012-04-14T20:59:53Z  in response to Gary_Scarcella
        Hi Gary, DB2 10 for Linux, UNIX, Windows (which has just been announced) does not contain any enhancements for remote cursor database load with XML. Unfortunately I cannot make any comments about future fixpacks or releases beyond DB2 10 GA.

        Matthias


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

          Re: DB2 LUW support for remote cursor database load for tables with XML columns

          ‏2013-02-06T10:45:51Z  in response to MatthiasNicola
          Hi,

          If i am correct, nothing new regarding this in FP 2 for 10.1. Waiting for support from V9.5. So disappointing..

          Regards,
          Ivan
          • MatthiasNicola
            MatthiasNicola
            309 Posts
            ACCEPTED ANSWER

            Re: DB2 LUW support for remote cursor database load for tables with XML columns

            ‏2013-02-07T16:48:00Z  in response to SystemAdmin
            Hi Ivan,

            the current solution is to use federation and define a local nickname for the remote table that you want to use as the input for the load operation. Have you tried that?

            Thanks,

            Matthias


            Matthias Nicola
            http://www.tinyurl.com/pureXML
            http://nativexmldatabase.com/
            • Q2DF_Ivan_Milojevic
              Q2DF_Ivan_Milojevic
              1 Post
              ACCEPTED ANSWER

              Re: DB2 LUW support for remote cursor database load for tables with XML columns

              ‏2013-05-08T14:59:51Z  in response to MatthiasNicola

              Hi Matthias,

              Sorry for delayed response..

              Yes, i use local nickname and that do the job, but like Gary mentioned things get complicated with more nicknames involved.. I hope that in future ibm will expand this functionality without nicknames for XML.

              Thanks,

              Ivan

              • Gary_Scarcella
                Gary_Scarcella
                21 Posts
                ACCEPTED ANSWER

                Re: DB2 LUW support for remote cursor database load for tables with XML columns

                ‏2013-11-14T14:51:01Z  in response to Q2DF_Ivan_Milojevic

                Hi Matthias,

                Just curious if DB2 LUW 10.5 includes support for remote cursor database load for XML columns (without the nickname workaround).

                Thanks,

                Gary.

                • MatthiasNicola
                  MatthiasNicola
                  309 Posts
                  ACCEPTED ANSWER

                  Re: DB2 LUW support for remote cursor database load for tables with XML columns

                  ‏2013-11-15T09:02:25Z  in response to Gary_Scarcella

                  Hi Gary,

                  sorry, nothing has changed in 10.5 regarding remote load of XML data.

                  Here is another idea, but I'm not sure if it will work. You could try the LOAD from remote cursor (as shown in your post at the very top of this topic)  but declare the cursor as follows:

                  DECLARE C1 CURSOR DATABASE SSTPEAP USER ######## USING ########
                  FOR SELECT c1, c2, c3, XMLSERIALIZE(xmlcol AS CLOB) FROM STPEAP.EAPPL_PRTY_SECU_QUESTION T WITH UR;

                  So, the idea is to select all columns explicitly by name and to wrap the XMLSERIALIZE function around your XML column. If the cursor reads the XML data and converts it to a CLOB, does that allow a remote LOAD from this cursor?

                  Maybe this is restricted too (I don't recall), but might be worth a quick test.

                   

                  - Matthias