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
 

 

DB2 SPUFI Insert: a long text column cannot fit into 1 line

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sonia wen

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Wed Feb 16, 2011 7:47 pm    Post subject: DB2 SPUFI Insert: a long text column cannot fit into 1 line
Reply with quote

Hi,
I need to insert a DB2 record using SPUFI. A colume is defined as char(254) and I cannot fit the 254 bytes text within one line. What I can do to split one column content into more than one line?

The field is defined as char(254):
XSL_URL CHAR(254) NOT NULL WITH DEFAULT,

Thanks!
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Feb 16, 2011 8:16 pm    Post subject:
Reply with quote

Just code the data out to col 72 and then continue at col 1 of the next line. There is no continuation indicator SPFUI just concatenates the lines together until it encounters a semi-colon.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Feb 16, 2011 8:19 pm    Post subject:
Reply with quote

after the intial insert,
(which will contain as much of the literal as you can input,
use multiple UPDATE statements.

Code:

INSERT into your.table
 values('first 32 bytes of text')
;
commit
;
UPDATE your.table
  SET your.column = SUBSTR(your.column,1,32) !! '2nd 32 bytes of text'
;
commit
;
UPDATE your.table
  SET your.column = SUBSTR(your.column,1,64) !! '3rd 32 bytes of text'
;
commit
;


until you have the complete 254 bytes. you can use larger than 32,
but 32 was easy to remember as i increased the SUBSTR length parm.
Back to top
View user's profile Send private message
sonia wen

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Wed Feb 16, 2011 9:21 pm    Post subject:
Reply with quote

Thanks!!!
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 654
Location: Whitby, ON, Canada

PostPosted: Thu Feb 17, 2011 2:20 am    Post subject:
Reply with quote

I don't want to drift too far off topic, but the input data set LRECL restrictions imposed by SPUFI have bugged me for years.

It is important to note that this is not a DB2 restriction, it is an artificial restriction imposed by the SPUFI utililty.

Once you realize this the workaround becomes quite straightforward if you have some DSNREXX knowledge. Years ago I wrote a simple Rexx program that reads an SQL statement from an input file (of whatever LRECL) and passes it to DB2, unaltered, for processing via EXECUTE IMMEDIATE.

You can't use EXECUTE IMMEDIATE for SELECT statements, but for INSERT, UPDATE and DELETE it works great.
Back to top
View user's profile Send private message
cyrus.e.cabrera

New User


Joined: 25 Jul 2011
Posts: 14
Location: Philippines

PostPosted: Mon Jun 02, 2014 12:35 pm    Post subject: Reply to: DB2 SPUFI Insert: a long text column cannot fit in
Reply with quote

This might be a little late, but you'll never know; some people might still be looking up on HOW exactly can you fit a long INSERT value inside SPUFI's limitation of 1-72 lines.


What I did is by using the concat symbol "||".


example:

Code:
INSERT INTO <YOUR TABLE NAME>
       (A_VERY_LONG_256_BYTE_FIELD)
        VALUES
       ('THIS LONG VALUE THAT WILL ALMOST REACH 72 bytes'||  <-- look at that handy double pipe symbol :D
        'CONTINUATION OF THE LONG LOST FIELD');
     
COMMIT; <- just for obssessive purposes.


Long story short, it's exactly up to you how would you fit "n" number of bytes in one field; but you get the essence, whenever you want to indicate that "next" line is still a part of the previous line, you mimic the example above.
Back to top
View user's profile Send private message
sonia wen

New User


Joined: 15 May 2010
Posts: 3
Location: Toronto

PostPosted: Mon Jun 02, 2014 7:22 pm    Post subject: Reply to: DB2 SPUFI Insert: a long text column cannot fit in
Reply with quote

Thanks for all the posting! The first suggestion worked (after first line 72 bytes full, just continue with next line).
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 16 Thu Jul 13, 2017 2:11 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am


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