I have an issue I've been working on that has proven to be quite troublesome. I already have an idea of what the anwser may be, but would like to solicit some suggestions or other ideas from you guys (and gals I'm sure).

We have an application that will store BLOB data (jpegs) along with character data. The data comes to us in an xml format.; and this xml document contains both the character and the blob/binary data. Here is the table DDL:

CREATE TABLE "AIMD"."AIMRETRIEVEDITEM" (
"AIMRETRIEVEDITEMID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"AIMRETRIEVALID" DECIMAL(13,0) NOT NULL ,
"ARCHIVEDOCUMENTID" VARCHAR(36) ,
"AIMDOCUMENTID" VARCHAR(256) ,
"CAPTUREDATE" TIMESTAMP ,
"ACCOUNTNUMBER" VARCHAR(24) ,
"ROUTINGTRANSITNUMBER" VARCHAR(9) ,
"IMAGETYPE" VARCHAR(5) ,
"STATUS" VARCHAR(256) NOT NULL ,
"IMAGESIZE" INTEGER ,
"SEQUENCENUMBER" INTEGER ,
"LOCATION" VARCHAR(24) ,
"SERVER" VARCHAR(24) ,
"FOLDER" VARCHAR(24) ,
"RESULTERRORSEVERITY" INTEGER ,
"RESULTERRORTYPE" VARCHAR(256) ,
"RESULTERRORMESSAGE" VARCHAR(256) ,
"IMAGEERRORSEVERITY" INTEGER ,
"IMAGEERRORTYPE" VARCHAR(256) ,
"IMAGEERRORMESSAGE" VARCHAR(256) ,
"CACHEDIMAGE" BLOB(65536) NOT LOGGED COMPACT ,
"ITEMVIEWCOUNT" INTEGER )
IN "USERSPACE1" ;


I know that I will have to use IMPORT in order to get the binary data into the database. The first of my questions is this: since IMPORT is strictly a CLP command, is there any way within DB2 to invoke a CLP session behind the scenes from within a stored procedure and then send CLP commands to that session?

The second issue has to do with the xml itself. Let's say for simplicity sake that CLP is not an issue. Is there a file type within the IMPORT command that would cover an input file type of xml? Or would we need to convert that file to some other type (ixf, csv, etc.), create a separate file for the binary data, and then import the two files separately?

Here's a sample of the XML:

15Oct03 10:07:50:884 Reply XML: <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<imageReply>
<status>success</status>
<resultCount>1</resultCount>
<errorOutput>
<errSeverity></errSeverity>
<errType></errType>
<errMsg></errMsg>
</errorOutput>
<imageOutput>
<locationName>VP07T</locationName>
<serverName>VP07T</serverName>
<folderName>USBCMBINQ</folderName>
<imageList>
<imageDetail>
<imageErr>
<imageErrSeverity>null</imageErrSeverity>
<imageErrType>null</imageErrType>
<imageErrMsg>null</imageErrMsg>
</imageErr>
<docId>5013-5015-5017-DAA1-12FAAA-0-27413-1176427-27413-85-78-0-2-0-^</docId>
<docLocation>Unknown</docLocation>
<imageType>AFP</imageType>
<imageStatus>true</imageStatus>
<imageSize>27413</imageSize>
<image></image>
<imageFront> THIS IS WHERE THE BINARY DATA IS </imageFront>
<imageFrontSize>7507</imageFrontSize>
<imageFrontType>TIF</imageFrontType>

Any help or suggestions would be greatly appreciated.

Thanks in advance.

The answer to your first question (calling CLP from a stored procedure) depends on what kind of stored procedure it is. I think technically you could invoke a shell (and CLP) from a Java stored proc. However, I'm not sure if it would work on any platform; besides, it will definitely have some performance implications and pose a security risk (since the shell will have the authority of the instance owner).

I don't think you should be using IMPORT to accomplish the task in the first place. First of all, as you would know after reading the manual, IMPORT does not support XML source files. DB2 XML Extender shoul be able to do the trick though.

If for any reason XML Extender cannot be used then I would choose to create a Java application (or stored proc) to parse XML and update appropriate table(s). This should be easier and more straightforward than converting XML into a bunch of flat files and IMPORTing them afterwards.

I've done a lot of work with DB2 and stored procedures, but haven't worked with Java stored procedures ever. Do you think you could point me n the right direction on how to get startd? Or even better yet: I don't suppose you would have any samples that would show how to invoke a command line session in a stored proc?

Even if oyu could point me in the right direction that would be awesome.

Thanks!!

This: http://publib-b.boulder.ibm.com/Redb...5945.html?Open has a number of Java code samples.

This is how to invoke shell (or any external command) from Java:
http://java.sun.com/j2se/1.3/docs/ap...g/Runtime.html

Again, I'm not sure if it will work from within DB2.

I was speaking with one of the developers on the team and he mentioned that it is possible to convert our images to a Base 64 character string.....?

I'm assuming this means we would be able to use a CLOB in the table as opposed to a BLOB. If that's the case, could we then just use a plain old fashioned INSERT to get the data into the table?

Thanks!

I'm afraid you've lost me here... I thought you needed to parse an XML file prior to updating the database.

Also, there isn't much difference between working with CLOBs and BLOBS. If your application, whatever it does, can handle CLOBs as you say then I don't see why it can't handle BLOBs just as well...