Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

inserting lob column larger than 32k into a table.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: inserting lob column larger than 32k into a table.
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: 1504
Location: Chennai

PostPosted: Wed Jul 25, 2012 5:15 pm    Post subject: Reply to: inserting lob column larger than 32k into a table.
Reply with quote

A similar thread
http://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: 1504
Location: Chennai

PostPosted: Thu Jul 26, 2012 10:59 am    Post subject:
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    Post subject: Reply to: inserting lob column larger than 32k into a table.
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: 1504
Location: Chennai

PostPosted: Thu Jul 26, 2012 5:31 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Fri Jul 27, 2012 10:24 am    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 18 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us