Topic
10 replies Latest Post - ‏2012-05-23T21:30:09Z by HuntDB2
fnapolitano
fnapolitano
7 Posts
ACCEPTED ANSWER

Pinned topic IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

‏2012-01-23T15:26:41Z |
Hi,

testing IDMT (2.00 b2114) for a migration of a DB2 LUW 9.7 DB to a DB2 DPF (InfoSphere Warehouse) DB.

I am testing on Linux and AIX, but having a couple of issues with Range Partition Tables:

1) I cannot captures ddl from source ( i do have flagged the check export partitions in the option toolbar) , I obtain only the base table ddl

2) when unloading and loading via Pipe the load hangs, it looks like it starts the load waiting on the pipe but no unload task starts.

Any hint will be much appreciated!

Thank you very much
Fabrizio
Updated on 2012-05-23T21:30:09Z at 2012-05-23T21:30:09Z by HuntDB2
  • SystemAdmin
    SystemAdmin
    708 Posts
    ACCEPTED ANSWER

    Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

    ‏2012-01-23T15:33:15Z  in response to fnapolitano
    Hi,

    (1) - is a known issue and I did not have time to fix it.
    (2) - Please make sure that the table exists at destination.

    I can not give estimated time to fix (1) as very busy at the moment. If you can find a workaround, please use it. If this is too much to handle if # of tables are too many, I will fix it but not immediately.

    Thanks
    • fnapolitano
      fnapolitano
      7 Posts
      ACCEPTED ANSWER

      Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

      ‏2012-01-23T15:44:48Z  in response to SystemAdmin
      I thanks alot for the quick reply.

      for 1 ok we have worked around it using db2look to get all the table data and recreating the full schema.

      for 2:

      Actually we have have all the tables with all range partitions recreated on the target DB prior to kick off the unload process.

      Source is a DB2 9.7 not partitioned. Target is a DB2 9.7 DPF enabled with 3 partitions. (coord + 2 data)
      Loading through pipes it loads all the not Range partition tables and then hang on the only range patritioned table.

      trying to recreate the test case.
      any idea?

      thanks again
      Fabrizio
      • SystemAdmin
        SystemAdmin
        708 Posts
        ACCEPTED ANSWER

        Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

        ‏2012-01-23T15:58:32Z  in response to fnapolitano
        OK. I did notrealize that it was DPF on target.

        I know that the LOAD statement generated for DPF is not correct. Please test it by hand using the generated script. If you find the source of the problem in LOAD, I can then fix that as that will be simple.

        LOAD on DPF through the tool was not tested - to be very frank.
        • fnapolitano
          fnapolitano
          7 Posts
          ACCEPTED ANSWER

          Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

          ‏2012-01-23T16:29:45Z  in response to SystemAdmin
          ok I 'll try and let you know

          thanks
          Fabrizio
          • fnapolitano
            fnapolitano
            7 Posts
            ACCEPTED ANSWER

            Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

            ‏2012-01-24T02:17:47Z  in response to fnapolitano
            Hi,

            I am doing some test on my Linux (Suse) VM but I am not getting on, i definitely need help to understand better ho to make use of the tool.

            just a few questions:

            1) is it mandatory to extract DDL and deploy it with the tool?
            I have created the 2 databases with the same directory and i get the error that table does no exists

            2) some times even a normal tabel will hang in the same manner as described above.
            Fine this one I got it - it depends on the fact that if the Exception table is not Partitioned as well then the load gets an error.

            how do I disabilitate exception tables in Load before generating the scripts?
            I have seen that there is a property in IDMTConfig.properties

            but if I set it to false manually when I regenerate the script it is agai replaced with true and then the LOAD script has it embedded the eception clause that with range partitioned tables will fail

            What should I check?

            hope it is clear.

            thanks for the support
            Fabrizio
            • SystemAdmin
              SystemAdmin
              708 Posts
              ACCEPTED ANSWER

              Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

              ‏2012-01-24T04:41:56Z  in response to fnapolitano
              I see that exception table could be an issue for DPF. Turn it off using config file.

              The GUI must be setting it to TRUE again so leave GUI. I will fix this bug.

              Change setting for temp table in the IDMTConfig.properties file.

              Run unload command from the shell so that it takes the one that you set.

              Thanks
              • fnapolitano
                fnapolitano
                7 Posts
                ACCEPTED ANSWER

                Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

                ‏2012-01-24T07:00:33Z  in response to SystemAdmin
                Hi,

                actually the issue is not related to DPF (I got the same behaviour on a non DPF instance), but more to the other bug on the capture of the Range Partitions.
                It looks like the exception table needs to have the same structure then the original table (not sure though).

                Issue with the work around is that you do not only need to edit the properties files, you need to manually change each load*.sql file to comment the Exception handling part.

                This is a non issue for the tests we are doing but migrating hundreds of Range partitioned tables may get a bit tricky this way.

                The thing that I do not understand is : why it is working with files and not with pipes...

                Thanks alot for the prompt reply, it is really much appreciated

                Fabrizio
                • fnapolitano
                  fnapolitano
                  7 Posts
                  ACCEPTED ANSWER

                  Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

                  ‏2012-01-30T11:09:46Z  in response to fnapolitano
                  Hi,

                  have you an idea of when you'll be able to fix the guy?

                  Customer finds that change manually thousands of load command would not be esay, if this may take time I will work on a script that automatically takes a load command and changes it commenting the exception part

                  Thanks
                  Fabrizio
  • HuntDB2
    HuntDB2
    5 Posts
    ACCEPTED ANSWER

    Re: IDMT DB2 LUW 9.7 to 9.7 cannot capture Range Part tab DDL

    ‏2012-05-23T21:30:09Z  in response to fnapolitano
    Has the piped load issue been resolved? Iam also trying to move from old DB29.7 AIX server to new DB29.7 AIX server. Would like to use the movement tool but can't get the piped loads to work. using IDMT 2.00-b2118. If this doesn't work then will do cursor loads from old server to new server but that can be difficult as well.