Topic
  • 4 replies
  • Latest Post - ‏2013-04-29T16:08:16Z by jprof
jprof
jprof
14 Posts

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
  • jprof
    jprof
    14 Posts
    ACCEPTED ANSWER

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

    ‏2013-04-29T16:08:16Z  
    • David.Zhu
    • ‏2013-04-25T02:37:23Z

    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?

     

    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.

  • David.Zhu
    David.Zhu
    67 Posts

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

    ‏2013-04-24T16:23:27Z  

    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

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

    ‏2013-04-24T17:12:22Z  
    • David.Zhu
    • ‏2013-04-24T16:23:27Z

    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

     

    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
    67 Posts

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

    ‏2013-04-25T02:37:23Z  
    • jprof
    • ‏2013-04-24T17:12:22Z

    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

    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

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

    ‏2013-04-29T16:08:16Z  
    • David.Zhu
    • ‏2013-04-25T02:37:23Z

    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?

     

    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.