Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-04-29T16:08:16Z by jprof
jprof
jprof
14 Posts
ACCEPTED ANSWER

Pinned topic DB2 gives SQL1585N when performing a MERGE statement to CLOB column

‏2013-01-12T00:01:33Z |
Hello all,

Quick question: does this page also apply when using very long CLOB column merge statements?? It only says VARCHAR, but I wonder if this is a DB2 bug, or I didn't get a memo or something :)

http://www-01.ibm.com/support/docview.wss?uid=swg21393862

I'm using DB2 10.1, fp2 (under 64 bit Windows 7). I have a huge merge statement that inserts data into a CLOB column (see attached for sample), and no matter what I do in terms of tablespaces, it still errors.

I wrote a small sample script that can be used to replicate what I'm seeing. To use, simply run the following statements in a CLP window:

> db2sampl

Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "JPROF"...
Creating tables with XML columns and XML data in schema "JPROF"...

'db2sampl' processing complete.
> db2 connect to sample

Database Connection Information

Database server = DB2/NT64 10.1.2
SQL authorization ID = JPROF
Local database alias = SAMPLE
> db2 -td@ -f test_sample.txt
DB20000I The SQL command completed successfully.

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1585N A temporary table could not be created because there is no available
system temporary table space that has a compatible page size. SQLSTATE=54048
  • David.Zhu
    David.Zhu
    66 Posts
    ACCEPTED ANSWER

    Re: DB2 gives SQL1585N when performing a MERGE statement to CLOB column

    ‏2013-04-24T16:23:27Z  in response to jprof

    Hi,Jprof,

    From the message, you don't have the appropriate pagesize for system temporary tablespace.

    Execute following command and then can execute your script successful.

    1. db2 create bufferpool b32k pagesize 32768

    2.db2 create system temporary tablespace stty2 pagesize 32768 bufferpool b32k

    3.db2 -td@ -f test_sample.txt

     

    • jprof
      jprof
      14 Posts
      ACCEPTED ANSWER

      Re: DB2 gives SQL1585N when performing a MERGE statement to CLOB column

      ‏2013-04-24T17:12:22Z  in response to David.Zhu

      Hi David,

      I'd actually forgotten I'd asked this question, its been so long. :)

      I've just tested with your suggestions, and it didn't work. I probably need to do some more testing to be sure, but I think i'll just raise a PMR with IBM and let them figure it out.  Thanks for being my only reply person though :) 

      Thanks

      James

      • David.Zhu
        David.Zhu
        66 Posts
        ACCEPTED ANSWER

        Re: DB2 gives SQL1585N when performing a MERGE statement to CLOB column

        ‏2013-04-25T02:37:23Z  in response to jprof

        Hi,Jprof,

                    Before I create the large system temporary tablespace, I can get the same error with you, but after I create large system temperorary tablespace. It works well with you script except  I move the create table clause out and create the table in advance.  then you said it don't work. could you paste the error message it returned and I can check what's the reason?

         

        • jprof
          jprof
          14 Posts
          ACCEPTED ANSWER

          Re: DB2 gives SQL1585N when performing a MERGE statement to CLOB column

          ‏2013-04-29T16:08:16Z  in response to David.Zhu

          Hi - thanks for your persistence, but I have my answer now - IBM have said that I would need to put the long CLOB text into a format that the IMPORT command can understand, then import that. From a usability perspective, this is a bit of a pain, but I can live with it. IBM have no plans to change this in the near future, but I hope they do at some point. Its not something that immediately affects me, so I can wait. Thanks.