SQL Database + LOAD + CLOB files = error SQL3229W

Question

I'm having trouble making loads of tables that have CLOBS and BLOBS columns in a 'SQL Database' database in Bluemix.

The error returned is:

SQL3229W  The field value in row "617" and column "3" is invalid. The row was 
rejected. Reason code: "1".

SQL3185W  The previous error occurred while processing data from row "617" of 
the input file.

The same procedures performed in a local environment functioned normally.

under the command you use to load:

load client from /home/db2inst1/ODONTO/tmp/ODONTO.ANAMNESE.IXF OF IXF LOBS FROM /home/db2inst1/ODONTO/tmp MODIFIED BY IDENTITYOVERRIDE replace into USER12135.TESTE NONRECOVERABLE

I believe LOB files must reside on the database server, not on the client, even when you specify `LOAD CLIENT`. — mustaccio, Dec 17 '15 at 19:10
I realized testing on remote server, but in other environment and work correctly... I believe to be some particularity of bluemix environments... — Gabriel, Dec 21 '15 at 16:19

score 0 · Answer 1 · edited Jan 20 '16 at 20:24

The only manner currently you can upload lob files to a SQLDB or dashDB is to load the data and lobs from the cloud. The option is to get data from a Swift object storage in Softlayer or a Amazon S3 storage. You should have an account on one of those services.

After that, you can use the following syntax:

db2 "call sysproc.admin_cmd('load from Softlayer::softlayer_end_point::softlayer_username::softlayer_api_key::softlayer_container_name::mylobs/blob.del of del LOBS FROM Softlayer::softlayer_end_point::softlayer_username::softlayer_api_key::softlayer_container_name::mylobs/ messages on server insert into LOBLOAD')"

Where: mylobs/ is the directory inside the Softlayer swift object storage container, defined in LOBLOAD is the table name to be loaded in

Example:

db2 "call sysproc.admin_cmd('load from Softlayer::https://lon02.objectstorage.softlayer.net/auth/v1.0::SLOS424907-2:SL523907::0ac631wewqewre8af20c576ad5214ec70f163d600d247bd5d4dfef5453f72ff6::TestContainer::mylobs/blob.del of del LOBS FROM Softlayer::https://lon02.objectstorage.softlayer.net/auth/v1.0::SLOS424907-2:SL523907::0ac631wewqewre8af20c576ad5214ec70f163d600d247bd5d4dfef5453f72ff6::TestContainer::mylobs/ messages on server insert into LOBLOAD')"

SQL Database + LOAD + CLOB files = error SQL3229W

1 Answers1