LOB insert into a table

Shinoj Vijayakumar

LOB insert into a table

 

Hi All,

Needed help in understanding how to do an LOB insert operation on the columns created using the script below.

--CREATE TABLE TO HOLD LOB DATA
CREATE TABLE SVK.EMP_PHOTO_RESUME
 (EMPNO CHAR(6) NOT NULL,
 EMP_ROWID ROWID NOT NULL GENERATED ALWAYS,
 EMP_PHOTO BLOB(110K),
 RESUME CLOB(5K),
 PRIMARY KEY (EMPNO))
 IN LTMDBT.LTMTST
 CCSID EBCDIC;
--DEFINE A LOB TABLE SPACE AND AN AUX TABLE TO HOLD THE EMP RESUMES
CREATE LOB TABLESPACE RESUMETS
IN LTMDBT
LOG YES;
CREATE AUXILIARY TABLE EMP_RESUME_TAB
IN LTMDBT.RESUMETS
STORES SVK.EMP_PHOTO_RESUME
COLUMN RESUME;
CREATE UNIQUE INDEX XRESUME
ON EMP_RESUME_TAB;

 

Being naive to the LOB section in DB2 I tried reading docs and ended up creating the table and columns as you see above. I have two queries.

1. How can I move resume in PDF or DOC format to the Mainframe Terminal to be pushed to the columns (Tried FTP but ended up with errors)

--While transferring a pic the error encountered was : Copying files to remote side failed.

Invalid data set name "1.JPG". Use MVS Dsname conventions.

--While transferring DOC file the error encountered was : Copying files to remote side failed.
Invalid data set name "form.pdf". Use MVS Dsname conventions.

 

2. If transfer is not required how do I do it using SQL queries. I do not want to write a cobol application or any application programing to do the same.

 

The versions of product I use are:

zOs - 2.2

ISPF 7.2

DB2 V 11.0

Open Text Host Explorer 14 x64 for accessing the Mainframe terminal

 

Any help here will be appreciated.

Regards,

SVK

James Campbell

LOB insert into a table
(in response to Shinoj Vijayakumar)
The name of a dataset must confom to certain rules. Read, for example:
https://www.ibm.com/support/knowledgecenter/SSCP65_4.0.3/com.ibm.team.scm.doc/topics
/r_RTCz_datasetparms.html

What that doesn't explicitly say is that letters must be upper case. Normally a FTP server will
do this translation for you.

It will 'normally' also pre-pend a high-level qualifier to the name you supply. This is usually
the 'current directory' or uss directory.

EXACTLY what commands did you use to (try to) transfer these files.

James Campbell

On 14 Feb 2018 at 23:08, Shinoj Vijayakumar wrote:

>
>  
<snip>
>  
> Being naive to the LOB section in DB2 I tried reading docs and ended up creating the table and
> columns as you see above. I have two queries.
> 1. How can I move resume in PDF or DOC format to the Mainframe Terminal to be pushed to the
> columns (Tried FTP but ended up with errors)
> --While transferring a pic the error encountered was : Copying files to remote side failed.
> Invalid data set name "1.JPG". Use MVS Dsname conventions.
> --While transferring DOC file the error encountered was : Copying files to remote side failed.
> Invalid data set name "form.pdf". Use MVS Dsname conventions.
>  
> 2. If transfer is not required how do I do it using SQL queries. I do not want to write a cobol
> application or any application programing to do the same.
>  
> The versions of product I use are:
> zOs - 2.2
> ISPF 7.2
> DB2 V 11.0
> Open Text Host Explorer 14 x64 for accessing the Mainframe terminal
>  
> Any help here will be appreciated.
> Regards,
> SVK
>

Shinoj Vijayakumar

RE: LOB insert into a table
(in response to James Campbell)

Hi James,

 

I used winscp to connect to the Mainframe terminal to FTP the file. Picked a .jpg file and tried dropping it out on to the terminal. 

Also tried the Send file to host option on the Host explorer. 

 

In case you find the method is incorrect do let me know how to proceed further will give it a shot.

Regards,

SVK

J&#248;rn Thyssen

RE: LOB insert into a table
(in response to Shinoj Vijayakumar)

Hi Shinoj,

It is very difficult to do without an application.

You could basically have to write a query like:

INSERT INTO SVK.EMP_PHOTO_RESUME (EMPNO,EMP_PHOTO_BLOB)

VALUES ('000010',

 X'00010203040506.... all the hex values of your JPEG image' -- wrap at column 72 and continue

);

You might also be able to put the image in a PDS/E library and construct a Db2 LOAD utility job using file reference variables. 

However, I suggest you write a small application  in your favourite programming language

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Shinoj Vijayakumar

RE: LOB insert into a table
(in response to Jørn Thyssen)

Thanks a lot Jørn for the clarification. I shall proceed with writing an application.

One more question with regards to the image or document I put in a PDS. To transfer a resume in doc or pdf format into the mainframe terminal I get rid of the extension ("sample.doc/.pdf" to "sample"). Now when I write an application I shall use the file from the PDS and load it into DB2. When a customer queries the field where the resume is stored will get the file without the extension. Also in Mainframes the file will look distorted as resumes in doc/pdf will be formatted using tables, highlights etc. How is this taken care at the customer end?

(I transferred the doc/pdf/jpeg file using winscp (ftp)) 

Regards,

SVK

Roy Boxwell

LOB insert into a table
(in response to Shinoj Vijayakumar)
Always file transfer to/from the mainframe using BIN – That way your data will not get garbled....

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: Shinoj Vijayakumar [mailto:[login to unmask email]
Sent: Friday, February 16, 2018 5:22 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: LOB insert into a table


Thanks a lot Jørn for the clarification. I shall proceed with writing an application.

One more question with regards to the image or document I put in a PDS. To transfer a resume in doc or pdf format into the mainframe terminal I get rid of the extension ("sample.doc/.pdf" to "sample"). Now when I write an application I shall use the file from the PDS and load it into DB2. When a customer queries the field where the resume is stored will get the file without the extension. Also in Mainframes the file will look distorted as resumes in doc/pdf will be formatted using tables, highlights etc. How is this taken care at the customer end?

(I transferred the doc/pdf/jpeg file using winscp (ftp))

Regards,

SVK

-----End Original Message-----

J&#248;rn Thyssen

RE: LOB insert into a table
(in response to Shinoj Vijayakumar)

Hi,

If you want to save the contents of the file in a BLOB I suggest you add a column with the original filename, e.g., sample.pdf

Most likely the file will be uploaded using a web application? So the web application knows the filename as well as the contents and can drive the INSERT INTO ... VALUES ('sample.pdf', binarystuff);

If you are INSERTing the data using a Cobol program I would suggest uploading the file (binary) to a zFS directory with the original name. However, you would have to think about what to do if you have two sample.pdf files to be used for two different records.

You are moving into content management territory :) Good luck!

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal.