IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

inserting lob column larger than 32k into a table.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Kiran Chava

New User


Joined: 07 Jul 2010
Posts: 9
Location: India

PostPosted: Wed Jul 25, 2012 5:01 pm
Reply with quote

Hi,

I am trying to insert an xml record into a table column defined as below

NPPES-NPI-IP-DATA-XML USAGE SQL TYPE IS XML AS CLOB(1M).

My XML record is larger than 32Kb. I am getting an error while trying to insert this. Could someone help me understand how we can insert such records into the table.

FYI... We are using Db2 V10 if that might make any difference.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Wed Jul 25, 2012 5:15 pm
Reply with quote

A similar thread
ibmmainframes.com/viewtopic.php?t=49038&start=0
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Thu Jul 26, 2012 10:59 am
Reply with quote

Hello,
Are you trying to upload a 32K+ LRECL file from PC to mainframe and then load it into DB2?
If possible post some lines of your XML code with HEX ON mode.

I think the file can be loaded into USS and then FTP'ed to a mainframe PS file with WRAPECORD option. Which will spilt longed records into smaller records based on record separator.
Back to top
View user's profile Send private message
Kiran Chava

New User


Joined: 07 Jul 2010
Posts: 9
Location: India

PostPosted: Thu Jul 26, 2012 1:50 pm
Reply with quote

We are creating an xml using the data from different tables and then inserting that into a new table. below is the part o the xml we are creating.

<nmm:NpiNppesMsg><cod:CodeValue>String</cod:CodeValue></nmm:NpiNppesMsg>


once the xml is ready we are parsing it as below

EXEC SQL SET :NPPES-NPI-IP-DATA-XML =
XMLPARSE(DOCUMENT :WS-XML-RECORD)
END-EXEC.

Then inserting it into the table

EXEC SQL
INSERT INTO NPPES_NPI_IP_FD
( NPI_ID
,NPPES_NPI_IP_DATA_XML
)
VALUES ( :NPI-ID
,:NPPES-NPI-IP-DATA-XML
)
END-EXEC

The fields are defined as below

NPPES_NPI_IP_DATA_XML XML NOT NULL
10 NPPES-NPI-IP-DATA-XML
USAGE SQL TYPE IS XML AS CLOB(1M).
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Thu Jul 26, 2012 5:31 pm
Reply with quote

Hi,
I have worked with XML, but not worked with DB2 creating XML jobs. So my assumptions may be wrong.

One possible reason for the error is, the generated XML statement maybe breaking in-between after 32K and flowing into next line. Which causes error during insertion.

Does the XML creation step write the XML statements to a PS file or something?
Back to top
View user's profile Send private message
Kiran Chava

New User


Joined: 07 Jul 2010
Posts: 9
Location: India

PostPosted: Thu Jul 26, 2012 5:48 pm
Reply with quote

The XML is being generated in the same program. we have a join on 4 tables and will be getting the data and generating xml using generate XML command in cobol. The result is to be loaded to a table.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Fri Jul 27, 2012 10:24 am
Reply with quote

Kiran,

What is the error(SQLCODE's or any abends) you are getting, can you please post that information?

Thanks,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts How to load to DB2 with column level ... DB2 6
No new posts RC query -Time column CA Products 3
No new posts Multiple table unload using INZUTILB DB2 2
Search our Forums:

Back to Top